lesson 57 Advanced product analysis · advanced

Usage cost & contribution margin

Compare usage revenue with costs to calculate contribution margin.

the idea

Contribution margin is revenue minus the variable cost of serving it. Revenue comes from cash collected (invoice payments net of refunds) in the period; usage cost is each logical event multiplied by its unit cost. Compute both at the same grain, then subtract.

Dataset: Product / SaaS Analytics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Read the unit cost table. Run this. Each event_name maps to a cost in microdollars—the variable cost you multiply by logical event counts.the pattern

   example.sql Run

   sql›

   SELECT event_name, cost_microdollars
   FROM event_costs
   ORDER BY cost_microdollars DESC;

2. 2

   See May billing cash. Run this. invoice_paid and refund_issued rows show money collected in May 2025—the revenue side of margin.

   step-2.sql Run

   sql›

   SELECT event_type, COUNT(*) AS events, SUM(amount_cents) / 100.0 AS usd
   FROM billing_events
   WHERE occurred_at >= '2025-05-01' AND occurred_at < '2025-06-01'
   GROUP BY event_type;

common mistake

Multiplying raw deliveries by unit cost charges retries twice; deduplicate events before costing them.