Contact
All work

Order-level profitability analysis

For an online food brand, we analyzed profitability one order at a time. The analysis shows what is left from each order after every cost tied to it, reported by product, basket size and promotion.

Client
Online food brand
Service
Profitability analysis
Data
15,000+ orders over 12 months, 100+ menu items
Tools
Python, pandas, SQL, Excel

The questions

  1. What is left from an average order once every cost tied to it is paid?
  2. Which orders lose money, by basket size, product and promotion?
  3. What would a minimum basket or a tighter discount rule do to margin?

Example outputs

Client data stays confidential, so these charts use sample numbers in the same format as the delivered reports.

  • 41%of single-item orders lose money
  • 11%of all orders lose money
  • 47%contribution margin on baskets of five or more items

Where the order value goes

Average share of what the customer paid, across all orders.

  • Ingredients36%
  • Delivery15%
  • Packaging6%
  • Refunds and remakes4%
  • Payment fees3%
  • Contribution margin36%

Contribution margin by week

Margin as a share of what customers paid. Discounted orders are those placed with a discount code.

  • All orders
  • Discounted orders
0%10%20%30%40%W1W3W5W7W9W11All orders, W1: 35%All orders, W2: 36%All orders, W3: 34%All orders, W4: 37%All orders, W5: 36%All orders, W6: 35%All orders, W7: 38%All orders, W8: 37%All orders, W9: 36%All orders, W10: 37%All orders, W11: 36%All orders, W12: 35%All ordersDiscounted orders, W1: 17%Discounted orders, W2: 15%Discounted orders, W3: 11%Discounted orders, W4: 16%Discounted orders, W5: 14%Discounted orders, W6: 8%Discounted orders, W7: 13%Discounted orders, W8: 12%Discounted orders, W9: 10%Discounted orders, W10: 14%Discounted orders, W11: 13%Discounted orders, W12: 11%Discounted orders0%10%20%30%40%W1W4W7W10All orders, W1: 35%All orders, W2: 36%All orders, W3: 34%All orders, W4: 37%All orders, W5: 36%All orders, W6: 35%All orders, W7: 38%All orders, W8: 37%All orders, W9: 36%All orders, W10: 37%All orders, W11: 36%All orders, W12: 35%Discounted orders, W1: 17%Discounted orders, W2: 15%Discounted orders, W3: 11%Discounted orders, W4: 16%Discounted orders, W5: 14%Discounted orders, W6: 8%Discounted orders, W7: 13%Discounted orders, W8: 12%Discounted orders, W9: 10%Discounted orders, W10: 14%Discounted orders, W11: 13%Discounted orders, W12: 11%

Margin by basket size

Courier and packaging costs are charged per order, so they weigh more on small baskets.

BasketShare of ordersShare of salesContribution marginOrders at a loss
1 item18%7%4%41%
2 items31%23%26%8%
3 to 4 items36%42%40%2%
5 or more items15%28%47%1%
All orders100%100%36%11%

Approach

  1. 1

    Build the order table

    One row per order line, joined to product cost, packaging, the courier charge, payment fees, discount codes and refunds.

  2. 2

    Write down the cost rules

    Courier and outer packaging are charged once per order, so they are split across the order's lines by value. Every rule is written down so the client can check it.

  3. 3

    Calculate contribution margin

    Per order first, then rolled up by product, basket size, promotion and week. Fixed overheads such as rent and salaries stay out. The number says whether an order paid for itself.

  4. 4

    Test the fixes on past orders

    Rerun past orders under a proposed rule, such as a minimum basket or a limit on stacked discounts. Report the change in margin and the share of orders affected. The estimate assumes customers keep ordering as before, and the report says so.

Next projectMulti-currency sales and profit dashboardView project