DIY Granular COGS Tracking

Case Study: Cookie Monster’s Monster Cookies

Jeff Goldkamp
Apr 5, 2019 · 6 min read
Image for post
Image for post

Purpose

We’ll implement an end-to-end system that tracks variable costs for different goods sold at a transaction level. The results give a detailed view of variable costs to help with strategic decision making for departments like procurement, supply chain, and inventory management.

Motivation

Controlling Cost of Goods Sold (COGS hereafter) is a requirement for running a sustainable business. Many factors play into COGS so they are often measured at a high-level, which consequentially sacrifices any insights into costs that change across products and transactions. Therefore, it is advantageous for companies to manage their costs at a granular level to continue delivering superior services in a sustainable manner.

Case Study: Cookie Monster’s Monster Cookies LLC

Cookie Monster’s Monster Cookies LLC (CMMC hereafter) wants to share their superior cookies in the best way possible. An important piece in doing so is controlling the COGS as CMMC expands their cookie offerings.

Data Sources

CMMC maintains 4 Google Sheets to keep track of their products (cookies), variable costs (ingredients, labor, packaging), and transactions.

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
(baking cost is per cookie)
Image for post
Image for post

Loading Datasets into Snowflake

Cookie Monster has much more important things to do (like eating cookies) than manually load these datasets each month. That’s why he created an Airflow DAG to automatically import each Google Sheet in to his analytics warehouse Snowflake. You can reference the attached file named “blog_load_cost_info.py”, but the basic idea is that each sheet has it’s own operator like so:

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

Dynamic Pivoting in Snowflake with Python

Within the attached “blog_create_order_margin_table.py” script we have the below two helper functions. These act as our substitute for dynamic pivoting in Snowflake by fetching whatever column names exist in a given table, formatting them, and calling those results in a query generator.

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Gross margin here is given as a percentage of the amount charged

COGS Analysis

Now that we have our order-level costs assigned, we can extract some detailed COGS insights such as:

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
CMMC’s unit margins (gross margin shown as a percent of revenue)
Image for post
Image for post
CMMC’s margin breakdowns

Ro Data Team Blog

Ro Data Team Blog: data analytics, data engineering, data…

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store