Ecommerce · Unit economics
MixedFood & D2CPer-order contribution margin: spreadsheet, BI stack or AI workspace
Revenue is easy to see. What each order actually earned after product cost, shipping, RTO, gateway fees and ads is not. This guide compares three ways to build a per-order contribution margin P&L and when each one is the right call.
Valnora & Elric team6 min read
Compared in this guide
- ABest fit
- BGetting data in
- CFreshness
- DDefinitions
- EAlerts and follow-up
- FEffort to start
- GWhere it falls short
Bhoomi Snacks is a fictional D2C brand, used here as an illustration: about 600 orders a day on Shopify, roughly half COD, Meta and Google ads, two couriers through an aggregator. The founders know revenue to the rupee. They do not know whether a COD order from a Meta prospecting campaign into a high-RTO pincode makes money, and that is the order type growing fastest.
A per-order contribution margin P&L answers that. This guide compares three ways to build one and is honest about when each makes sense.
What a per-order P&L needs
Most D2C teams use three levels of contribution margin:
- CM1: net revenue minus product cost (COGS) and packaging.
- CM2: CM1 minus forward shipping, COD fees, payment gateway fees and the cost of returns and RTO.
- CM3: CM2 minus marketing spend allocated to the order.
The data sits in four places: Shopify for orders and discounts, ad accounts for spend, the courier aggregator for shipping and RTO charges, and the payment gateway for fees. Product cost usually sits in a finance spreadsheet. Whatever you choose has to join all five, every day, without someone copying numbers.
What the solution produces
One order, end to end (illustrative)
Record- Order
- #88231 · COD · Pune · Meta prospecting campaign
- Net revenue
- ₹1,199 after a ₹100 discount, excluding GST
- CM1
- ₹719 after COGS ₹420 and packaging ₹60
- Expected RTO cost
- ₹38: pincode RTO rate 18% × return charge ₹210
- CM2
- ₹566 after forward shipping ₹85, COD fee ₹30 and expected RTO cost ₹38
- CM3
- ₹216 after ad cost per order ₹350 for that campaign and day
The example above is illustrative. RTO is modelled as an expected cost from the pincode’s return rate until the parcel is delivered or returned, then replaced by the actual charge.
The three options
Three ways to build a per-order contribution margin P&L
| Question | Spreadsheet | BI tool plus ETL | Agent Serve dashboard |
|---|---|---|---|
| Best fit | Early brand, a few hundred orders a month, one person owns the numbers | A data team already running a warehouse and models | An ops or finance-led team without a data engineer, or one that wants agents on the same data |
| Getting data in | Manual exports from Shopify, ad accounts and the courier panel | Managed connectors where they exist; custom jobs for couriers and gateways | Shopify webhooks and blocks, Google Ads block, API block for Meta, couriers and gateways |
| Freshness | As fresh as the last export | Usually hourly or daily syncs | Order panels update as rows land; scheduled syncs as often as you set them |
| Definitions | In cell formulas, easy to drift between tabs and people | In the modelling layer, versioned by the data team | Semantic model measures shared by charts, monitors and agents |
| Alerts and follow-up | Someone has to look | Alerts on dashboards; follow-up is manual | Monitors every 5 minutes, and a Relay that investigates and asks before acting |
| Effort to start | Lowest | Highest: several tools, contracts and a person to run them | Moderate: workflows and a cost table to set up |
| Where it falls short | Breaks with volume and with more than one editor | Cost and lead time for a small team | Not a replacement for an existing warehouse model; connect to it instead |
None of these is wrong. A spreadsheet is the right start for a new brand: it forces you to define the measures, and that work carries over to whatever comes next. A BI tool with a managed ETL service and a warehouse is the right call if you already have a data team and models; adding a margin view there is incremental.
The gap is the brand in between: big enough that exports break, without a data engineer to run a stack.
Building it in Agent Serve
From orders, ad spend and courier charges to margin per order
Workflows · Data in
- 01Shopify ordersOrder webhooks write each order with SKU lines, discounts, payment method and UTM source.
- 02Ad spend syncDaily: Google Ads campaign performance, and Meta spend from the Marketing API via the API block.
- 03Logistics chargesCourier aggregator API via the API block: forward, COD and RTO charges per AWB.
- 04SKU cost tableCOGS and packaging cost per SKU, owned by finance and dated by batch.
Model · Margin per order
- 05Semantic modelOrders joined to costs, charges and fees; CM1, CM2 and CM3 defined once as measures.
- 06Margin dashboardCM per order by channel, campaign, SKU, payment method and region.
- 07Negative CM3 monitorAlerts growth when a campaign’s CM3 per order stays below zero after enough orders.
Four workflows keep the data current, each writing to Tables:
- Orders. A Shopify orders webhook points at a Webhook trigger and writes each order with SKU lines, discounts, payment method, pincode and UTM source.
- Ad spend. A daily schedule pulls campaign performance with the Google Ads block, which is read-only, and Meta spend from the Marketing API through the API block, with the token stored as a workspace secret.
- Logistics. Courier aggregators are not native integrations. The API block calls your aggregator’s API for forward, COD and RTO charges per AWB.
- Gateway fees. The same pattern reads your gateway’s settlement data, since gateways such as Razorpay are also connected through the API block.
Finance owns a SKU cost table with COGS and packaging per SKU and the date each cost applies from, so a new supplier price does not rewrite last month.
The semantic model joins orders to costs, charges, fees and spend, and defines CM1, CM2 and CM3 as named metrics. Every panel, monitor and agent reads the same definitions. If you already run BigQuery or Snowflake, connect it under Data sources and build on the tables there.
The dashboard shows CM per order by channel, campaign, SKU, payment method and region. Row-level access keeps COGS visible to finance and founders only, while growth sees CM3 by campaign.
What changes when the margin is visible
A monitor alerts growth when a campaign’s CM3 per order stays negative after enough orders to judge. A Relay can pick up that alert, break the loss down into ad cost, RTO and discount, and write a short note. Through Meta’s ads MCP server it can propose a budget change, which waits for approval as a spend call.
In Bhoomi’s example, the likely finding is not that COD is unprofitable. It is that one campaign sends COD orders into high-RTO pincodes with a deep discount, and the three together push CM3 below zero. That is a fix for one campaign and one checkout rule, not a change to the whole business.
For an example of a brand running ads, creative and operations in one workspace, see the Aestlo case study, a Valnora & Elric fashion brand.
How to choose
- Under a few hundred orders a week: start in a spreadsheet and write the definitions down.
- A data team and a warehouse already: add the measures there, and connect Agent Serve if you want monitors and Relays on top.
- Growing fast, no data engineer: build it in Agent Serve and keep the spreadsheet only for the cost table until finance moves it into Tables.
Controls and governance
Roles and permission groups
Admin, write and read roles per workspace, plus permission groups that limit which models, blocks and integrations a team can use.
Row-level access
Row-level policies narrow what each person sees in tables, dashboards, exports and scheduled reports.
Audit trail
Proposals, approvals, declines and executed actions are written to the workspace audit log, which admins can export.
Run logs
Every workflow run records each block’s inputs, outputs and errors, so a reviewer can trace how a result was reached.
Deployed versions
Workflows run from a deployed version, so a change is reviewed before it reaches production runs.
Self-hosting
Run Agent Serve in your own cloud or data centre, next to the systems it reads.
More on how Agent Serve handles access, data and audit on the security page.
For keeping metric definitions consistent once more people read them, see KPI definitions.
Questions teams ask
How do you allocate ad spend to an order?
Pick one rule and write it into the measure. A common start is spend per campaign per day divided by that campaign’s attributed orders that day, with unattributed orders sharing brand-level spend. The rule matters less than everyone using the same one.
We already have BigQuery or Snowflake. Do we rebuild this?
No. Connect it as a data source and build the dashboard and monitors on the tables you already have, live or refreshed on a schedule as often as every 15 minutes.
What you need
- Shopify admin access for an orders webhook and the Shopify block
- Google Ads account access, and a Meta access token stored as a workspace secret
- Your courier aggregator’s API key and its charge or invoice endpoints
- Your payment gateway’s settlement report or API, for fees per transaction
- A SKU cost table from finance: COGS and packaging, with the date each cost applies from
- Agreed definitions of CM1, CM2 and CM3, and how ad spend is allocated to orders