Compute the metrics

Step 1: Compute revenue by channel

The data is profiled and clean. Now compute the numbers Naledi actually needs.

Open materials/metric-definitions.md alongside the analysis spec. The first business question is which channel generates the most revenue. Direct Claude to compute it:

Using the metric definitions in materials/metric-definitions.md, compute net revenue by channel. Show me the total and the per-channel breakdown so I can verify against the targets in the analysis spec.

Claude will write a SQL query and return revenue totals for retail, commission, and online. Before you do anything with those numbers, compare the total against the verification target in materials/analysis-spec.md Section 4.

Step 2: Catch the error

Here is something worth knowing about AI and aggregations: AI commonly includes all rows in aggregations without checking business rules. If a metric definition says to exclude certain rows — refunds, test records, cancelled orders — AI will not apply that filter unless the prompt explicitly says to. The resulting number looks plausible. It is close to correct. And it is wrong.

Check Claude's total revenue against the verification target. The target in the analysis spec is ZAR 11,574,000. If Claude's number is lower, that is your signal. Open materials/metric-definitions.md and read the net channel revenue definition again. Look at what it says about which rows to include.

This is the core verification habit: a number from AI, a number from a trusted source, and the discipline to compare them. The gap between the two numbers is where the analytical work lives. Later projects will not hand you a target for every metric — but the practice of checking starts here.

Step 3: Correct and verify

Now that you know what went wrong, direct Claude to recompute with the correct filter:

Recompute net revenue by channel, excluding refund rows (negative sale_amount values). The metric definition says revenue = SUM(sale_amount) WHERE sale_amount > 0. Show me the total and per-channel breakdown.

Compare each number against the verification targets:

Every number should match. If one does not, check the query Claude wrote against the metric definition. The definition is the arbiter — not AI's interpretation of the word "revenue."

Step 4: Compute profit margins

Revenue alone does not answer Naledi's question. Retail brings in the most revenue, but retailers take 35-45% commission. The real question is what each channel keeps after costs.

Direct Claude to compute channel profit margin using the definition in materials/metric-definitions.md:

Compute channel profit margin for each channel using the formula in the metric definitions. Account for retailer commission (retail channel only), material costs, and delivery costs. Show me the margin percentage for each channel.

The metric definition specifies:

(net_revenue - retailer_commission - material_costs - delivery_costs) / net_revenue * 100

Verify against the targets. The retail channel's profit margin should be 28.2%. Commission should be 65.5%. Online should be 59.2%. If retail's margin seems surprisingly low — it is. That is the retailer commission at work. This is the number that will change how Naledi thinks about her channel mix.

Step 5: Compute revenue per workshop-week

This is the metric Naledi has never seen. She knows dining tables sell for more than shelving. What she does not know is how much workshop time each product consumes.

Direct Claude to compute revenue per workshop-week by product type:

Compute revenue per workshop-week for each product type. The formula is: SUM(sale_amount WHERE sale_amount > 0) / (SUM(production_days) / 5). Group by product_type and sort by revenue per workshop-week descending.

Check the results against the targets. Shelving should be at the top — ZAR 8,622 per workshop-week. Dining tables should be lower — ZAR 6,880. That gap is the insight: a dining table sells for more, but it ties up the workshop for weeks. Shelving moves through faster and generates more revenue per week of capacity.

This is the kind of finding that matters to a business owner. Naledi has five people and limited workshop space. Where she focuses production is a capacity decision, not just a pricing decision.

Step 6: Compute the channel-product cross-tabulation

The last business question asks whether certain products concentrate in certain channels. A cross-tabulation is a table that breaks down one variable by another -- here, revenue by both channel and product type at the same time. It reveals patterns that looking at channels or products separately would miss, like whether custom pieces cluster in the commission channel while shelving dominates online. Direct Claude to build a cross-tabulation:

Create a cross-tabulation of net revenue by channel and product type. Show each channel as a row and each product type as a column, with net revenue (excluding refunds) as the values.

This table reveals the patterns: which products sell through which channels, and where the revenue concentrations are. You will use this in the charts and the narrative — it is the detailed view behind the per-channel totals.