Contribution margin is an extremely powerful tool for marketing, operations, and finance teams to quantify the future health of their retail brand. But contribution is a complex metric to understand and requires a high degree of rigor that often can’t be left up to agents and LLMs to measure. At Latticework Insights, we’ve combined the power and intelligence of agents and LLMs with the diligence and deterministic qualities of traditional software engineering, and packed it up into a Claude skill. If you have not set up a skill yet, start with the LTV:CAC guide and the CAC payback guide first.
In its simplest form, contribution margin is what is left of a customer’s order after accounting for all of the costs associated with that order: discounts, returns, shipping, duty, and COGS. A data warehouse like Snowflake is perfect for this job, since Snowflake can store and combine data from multiple source systems that represent each dimension of contribution. This guide shows the SQL, the rules we give the agent, and how to interpret the results.
Every number below comes from a synthetic DTC apparel and supplements company we created called Lattice Workout. This imaginary company makes about $100M a year, has over 300,000 customers, and contains no client data.
Contribution margin sits between gross margin and net profit. It includes all of the variable costs of getting an order to the customer, unlike net profit on the P&L, which also includes headcount and operational expenses. It is extremely powerful when trying to model the future health of a retail brand, since it gives us a picture of how much incremental revenue we can expect for each incremental purchase.
How do you build a Claude skill for calculating contribution margin?
1. Define contribution in markdown files for the agent
A skill is basically just a folder with some markdown files inside. The markdown file gives an agent some definitions like:
- Net revenue on a per-order basis
- Cost of Goods Sold (COGS) as the catalog unit cost of each line item.
- Fulfillment for pick, pack and postage.
- Contribution as the net revenue of an order minus COGS, minus fulfillment, divided by net revenue.
2. Define the SQL model, Python logic, and JavaScript interactivity
We don’t allow agents to determine the SQL model at runtime, especially for something as precise as contribution margin. Since agents are highly unpredictable, we create a SQL model up front, so the agent simply needs to read the data.
CREATE OR REPLACE TEMP TABLE ord AS
WITH boxes AS (
SELECT order_id, COUNT(*) AS n FROM shipments GROUP BY 1
),
cogs AS (
SELECT li.order_id, SUM(li.quantity * p.cost) AS cogs
FROM order_line_items li JOIN products p USING (product_id)
GROUP BY 1
)
SELECT o.order_id, o.customer_id, o.processed_at,
ROW_NUMBER() OVER (PARTITION BY o.customer_id
ORDER BY o.processed_at, o.order_id) AS seq,
DATE_DIFF('day', c.first_order_at, o.processed_at) AS days,
o.subtotal AS gross,
o.discount_amount AS discounts,
o.refund_amount AS returns,
o.shipping_cost AS shipping,
o.total_amount - o.refund_amount AS net,
COALESCE(cg.cogs, 0) AS cogs,
COALESCE(b.n, 0) * $fulfillment AS fulfillment,
o.total_amount - o.refund_amount - COALESCE(cg.cogs, 0)
- COALESCE(b.n, 0) * $fulfillment AS contribution
FROM orders_raw o
JOIN cust c USING (customer_id)
LEFT JOIN boxes b USING (order_id)
LEFT JOIN cogs cg USING (order_id);
Then we calculate the “walk”, or each step from gross to contribution.
SELECT COUNT(DISTINCT c.customer_id) AS n,
COUNT(o.order_id) AS orders,
SUM(o.gross) AS gross,
SUM(o.discounts) AS discounts,
SUM(o.returns) AS refunds,
SUM(o.shipping) AS shipping,
SUM(o.net) AS net,
SUM(o.cogs) AS cogs,
SUM(o.fulfillment) AS fulfillment,
SUM(o.contribution) AS contribution,
(SELECT SUM(cac) FROM cust c2
WHERE c2.matured AND c2.in_window) AS cac
FROM cust c
JOIN ord o USING (customer_id)
WHERE c.matured AND c.in_window
AND o.days < 270;
The resulting Snowflake SQL data is run through a handful of Python scripts. The scripts are deterministic and predictable, unlike agents.
walk = {k: r[k] / r["n"] for k in
("gross", "discounts", "refunds", "shipping", "net",
"cogs", "fulfillment", "contribution")}
walk["orders"] = r["orders"] / r["n"]
walk["cac_per"] = r["cac"] / r["n"]
walk["margin"] = walk["contribution"] / walk["net"]
3. Read the output
The dashboard illustrates the walk from gross sales to LTV − CAC, with each step represented as a share of net revenue. Each step in the walk is something the dashboard user can tweak to illustrate possible future scenarios.
Getting to contribution margin is a huge milestone for a retail brand, especially for one that has data stored in many source systems. The contribution margin allows them to run scenarios to measure how changes in discounts, COGS, and fulfillment impact revenue outcomes. Many times, small changes in metrics yield unexpectedly high-leverage outcomes. This is what we call “Moneyball” metrics, where oftentimes subtle or counterintuitive metrics are revealed to create enormous leverage for a brand.
FAQ
What is contribution margin and what does it include?
Contribution margin is a measure of an individual order’s revenue minus the variable costs of serving that order: discounts and returns, product cost and fulfillment. Definitions vary between companies, so the skill states which costs exist at each step and applies them uniformly every time. It is the number CAC payback should be measured against, because vanilla revenue overstates what individual customers are worth.
Do I need a data warehouse to analyze contribution margin?
For brands that sell and market on only one channel, a spreadsheet does the trick. But once a company scales, maintaining these spreadsheets becomes extremely resource-intensive and a data warehouse becomes a mandatory step in their success. Combining data (or “joining”) across product cost, shipments, refunds and ad spend is what warehouses such as Snowflake, Databricks, and BigQuery are built for.
Should I build a Claude skill or an agent-as-a-folder to analyze contribution margin?
An agent-as-a-folder is the most flexible and powerful option: point Claude Code at a folder or several old project folders, ask it to read the context, and then pick up where you and your team left off last. A skill is the formalized option: once the method works, you can package the system with a name and a description, and Claude loads it whenever a request matches. Opt for the folder if you’re developing a project and collaborating with teams through GitHub.