Recommended tables
- Orders:
obt_orders - Customers:
obt_customers
Common retention cuts
- New vs repeat customers over time
- Repeat rate by acquisition channel
- Time to second order (and distribution)
- Retention by product purchased on first order
Suggested workflow
- Start with clean order filters:
is_order_sm_valid = TRUE
- Decide whether to include all channels or a focused channel (e.g., Online DTC only).
- Pick a retention definition:
- “Customer retained” = places any additional valid order within X days
- Or “Repeat customer” = has
order_index >= 2
- Break down by a single dimension (channel, first product, geography), validate, then layer on additional breakdowns.
Troubleshooting
- If you see unexpected counts when reconciling, check for:
- Exclude $0 orders logic
- Channel mapping rules