Metric definitions and segmentation

Step 1: Define revenue

Three channels, three definitions of revenue. The taproom records EUR amounts per pint transaction. Distribution records invoice amounts per keg pack -- but consignment returns reduce actual revenue (you already decided how to handle those). Online records order amounts in EUR and GBP (you already decided the conversion approach).

Write a plain-language revenue definition first:

"Total revenue is the sum of all completed transaction amounts in EUR across all three channels. For distribution, consignment invoices marked 'returned' are subtracted. For online, GBP orders are converted to EUR at [your chosen rate]."

Then write the SQL expression that implements it. The plain-language definition is authoritative. When the SQL and the plain language disagree, the SQL is wrong.

Fill in the Metric Definitions section of materials/analysis-template.md for revenue: plain-language definition, SQL expression, edge cases (what about discounts on online orders? What about the payment_type nulls in the taproom data?).

Step 2: Define volume

Revenue tells Carmen how much money came in. Volume tells her how much beer went out. But the three channels measure volume differently.

The taproom sells pints. Distribution sells packs of kegs (x6, x12, x24). Online sells individual bottles (330ml) and 6-packs. Comparing "units sold" across channels is meaningless -- 1,000 taproom pints is not the same as 1,000 online bottles.

The product catalog has a volume_liters column. Use it to normalise everything to liters. Write the plain-language definition:

"Total volume in liters is the sum of quantity multiplied by volume_liters from the product catalog, across all three channels."

Then the SQL. Add this to the analysis template. Note the edge case: the distribution pack sizes (x6, x12, x24) are the number of bottles per pack, not the number of packs. Make sure the conversion accounts for this.

Step 3: Define growth

Carmen wants to know what's growing and what's slowing down. "Growth" needs a definition.

Week-over-week and month-over-month are both useful. But three of the twelve beer styles are seasonal -- Summer Wheat only brews in Q2-Q3, Winter Warmer in Q4-Q1, Harvest Ale in Q3-Q4. Comparing October's Winter Warmer sales to July's is meaningless because they don't brew it in July.

Decide how to handle seasonal styles in growth calculations. The options: exclude them from growth metrics entirely, compare only within their active quarters (Q4 2024 vs Q4 2023... except you only have one year of data), or flag them separately with a note.

Document the decision. This is the kind of definitional trade-off where Carmen's input matters -- but you're making the initial recommendation.

Step 4: Cross-check the definitions

This is a new verification technique. In P2, you used AI self-review -- asking the same AI to check its own work. Now you add cross-checking: directing a second AI with fresh context to review the definitions.

Open a new Claude Code session, or use a different model. Paste your three metric definitions (plain-language and SQL) and ask:

Here are three metric definitions and their SQL implementations. For each one, review whether the SQL actually calculates what the plain-language definition describes. List every gap you find.

The second AI has no context about the project -- it hasn't been part of your conversation. That fresh perspective is the value. It catches interpretation gaps that the first session normalised. Maybe the SQL handles consignment returns differently from the definition. Maybe the volume conversion doesn't match the plain-language description.

Fix any gaps the cross-check surfaces. Add the findings to the analysis template's cross-check column.

Step 5: Segment by channel and style

With governed definitions in place, run the segmentation analysis. Revenue and volume by channel, by beer style, and cross-tabulated.

Using the unified dataset, compute total revenue and total volume (in liters) by channel and by beer style. Sort by revenue descending, not alphabetically. Show me a pivot table with beer styles as rows and channels as columns.

AI will generate the pivot table, but check the sort order. AI defaults to alphabetical or by count -- not by business impact. A table sorted by revenue descending reveals that a small number of beer styles dominate. A table sorted alphabetically hides this.

Look for disproportionate segments. Which beer styles dominate distribution but barely exist online? Which are taproom favourites but don't travel well? A style that generates 3% of total volume but 15% of taproom revenue is worth noting.

Step 6: Carmen's response

Send Carmen a brief update: the three sources are unified, here are the key findings so far. Top-performing beer styles by channel, which channels are growing, and the unified revenue number.

Carmen responds quickly:

This is exactly what I needed! My head brewer is going to eat his words about the Pale Ale 😂

Then:

Can you also show me which beer styles are seasonal? I want to know if comparing October to March is even fair for some of them

This is a reasonable question. You've already thought about seasonal handling when defining growth. Prepare the seasonal breakdown -- which styles are active in which quarters -- and incorporate Carmen's request into the dashboard design.