Recommended tables
- Orders:
obt_orders - Customers:
obt_customers - (Optional) Order lines:
obt_order_lines
Define the cohort
Most commonly:- Cohort grain: first order month
- Cohort key: customer’s first valid purchase date (using SourceMedium-valid orders)
Build the analysis (high level)
- Filter to SourceMedium-valid orders (
is_order_sm_valid = TRUE). - Determine each customer’s cohort (first valid order date).
- For each cohort, compute:
- Total orders
- Distinct customers
- Average lifetime orders =
total orders / distinct customers
- Add breakdowns (channel, product category, geography) only after the cohort math looks right.
Common pitfalls
- Mixing invalid/test/refunded orders with valid orders (always start with
is_order_sm_valid = TRUE). - Comparing cohorts with different “age” (newer cohorts haven’t had time to accumulate repeat purchases).
- Blending channels (e.g., Amazon + Online DTC) when the question is channel-specific.
Next steps
- Pair this with retention-focused analysis: Customer retention
- If you’re reconciling to Shopify: start with Why don’t Executive Summary & Shopify match?