Define the metrics

Step 1: What needs defining

Farid needs to know which products are profitable, which channels are growing, and which products to consider dropping. Those three questions require metrics -- and nobody has defined them.

In P1, the metric definitions were provided. You checked them against the data and verified AI's computations. This time, you write the definitions.

Start by listing what needs to be defined. "Product profitability by channel" requires: channel revenue, product revenue, product cost, product profit, product margin. "Channel trends" requires: a time-series metric -- monthly or quarterly revenue by channel. "Products to discontinue" requires combining profitability with trend data.

Each of these is a decision. "Revenue" is not a fact waiting to be discovered. It's a definition you author -- gross or net? With or without returns? Which time period? Which channels included? Different answers produce different numbers from the same data.

Step 2: Write the plain language definitions

Start with plain language, not SQL. The plain language definition is the authoritative one. If the SQL disagrees with the plain language, the SQL is wrong.

For each metric, write a definition that Farid could read and understand. Be specific about what's included and excluded:

• Channel revenue: Is it the sum of all transactions, or only completed ones? Does wholesale include or exclude pending invoices? What about the online discount codes -- does revenue mean before or after the discount?
• Product cost: Is it just the cost_per_unit from the catalog, or does it include shipping for online orders?
• Product profit: Revenue minus cost, but which revenue and which cost?
• Channel growth: Period-over-period change -- but which periods? Month-over-month? Comparing first half to second half?

Write each definition out: "Channel revenue is the sum of [specific column], excluding [specific rows], grouped by [specific dimension], for [specific time period]." Each decision in that sentence changes the number.

Step 3: Check definitions against the data

Before writing any SQL, check whether the data can support each definition. This is a fitness judgment -- asking whether the data has what the definition requires.

Can you compute "product profit by channel" for wholesale? The wholesale data has invoice totals but no per-unit prices. You derived implied unit prices in Unit 2 when you parsed the invoices, but those are estimates. If your profit definition requires exact per-unit revenue, the wholesale channel can only be estimated. That's worth documenting.

Does the data distinguish between "gross" and "net" revenue? The online data has discount codes but no discount amounts. You can identify which orders had discounts but not how much they saved. If your revenue definition says "net of discounts," you need to decide what to do about missing discount amounts.

Direct AI to check each definition: "For each metric definition, verify that every referenced column exists in the unified dataset and that the data has the granularity needed. Flag any definition that requires data the dataset doesn't contain."

Some questions the data cannot answer. If Farid asks about repeat purchase rates, the retail data has no customer identifier. The professional response is to say "the data can't answer this" and explain why -- not to force an analysis from insufficient evidence.

Step 4: Generate and review SQL

Now direct AI to write the SQL expression for each metric. Give it your plain language definitions as input: "Here are my metric definitions in plain language. Write the DuckDB SQL for each one."

Review every SQL expression against the plain language. AI commonly produces SQL that looks right but diverges from the definition in subtle ways. The plain language might say "exclude pending wholesale invoices," but the SQL has no WHERE clause filtering on payment_status. The plain language might say "net revenue after refunds," but the SQL computes SUM(total_amount) without any refund handling.

This is where the dual expression earns its keep. Two representations of the same metric. When they disagree, you find it by comparing them side by side. AI treats metric definition as a single-pass task -- it writes the definition and moves on. The edge cases it misses are exactly the cases that produce wrong numbers later.

Step 5: Document edge cases and ask Farid

For each metric, identify the edge cases. What happens with:

• A wholesale invoice marked "overdue" -- is that revenue or not?
• A product in the catalog but never sold -- does it appear in the profitability analysis with zero revenue?
• An online order with a discount code -- is the recorded price before or after the discount?
• The discontinued products that still appear in historical data -- are they included in the trend analysis?

Each edge case produces a different number. "Should revenue include overdue wholesale invoices?" is not a technical question. It's a business question that Farid should answer.

Open the chat with Farid. Ask about the ambiguity that matters most for your definitions. Farid is precise and helpful. He'll confirm that "profitable" means revenue minus cost-of-goods only -- not shipping. He'll also mention something you didn't know: wholesale pricing has three tiers (cafes, hotels, bulk distributors) with different margins, but the spreadsheet just shows the final price without indicating the tier.

That changes your definition. Wholesale profitability is blurrier than it looked -- you know the total revenue per invoice but not which tier applies. Document this constraint in your metric definitions.