High-Value Customer Segmentation

The scenario

Marketing wants a targeted high-value audience based on recent purchase behavior, by store. The output needs to be a queryable table, not a one-off query.

The design is two models: a reusable activity layer (customer x store, 180-day window) and a segment model on top. Future churn, RFM, and dashboard work can sit on the activity layer without re-deriving the same aggregates.

Prompts, in order

Copy each prompt as written, or rephrase. Run in order.

1. Discovery

   Six entity types in scope: customers, stores, orders, order lines, products, categories.

   Find the models related to customers, stores, orders, order lines, products, and categories.

   Then ask for grain and joins.

   Show the grain and joins for those models.

   Two domains are easy to miss: a dedicated categories model (sometimes category is only a column on products) and a separate order lines model (sometimes folded into orders). Confirm both before continuing.
   Exercises: status, search
2. Schema understanding

   Confirm three things: where category lives (product / order-line / order header), the grain of orders vs. order lines, and whether store is on the order, on the customer, or both.

   Check recent order dates and category values needed for a 180-day segmentation model.

   Exercises: describe, lineage
3. Data inspection

   Two common bugs: a stale max(order_date) that silently shrinks the 180-day window, and a category field with nulls, whitespace, or mixed case that breaks category-loyal logic.

   Create a 180-day customer activity model by store.

   If the max order date is more than a few days old, decide whether to anchor the window on current_date or on max(order_date). If categories are dirty, decide exclude-vs-coalesce now.
   Exercises: warehouse, dbt_show on rolling-window anchor and distinct categories
4. Activity layer model

   Reusable intermediate model (not the segment model yet). Per customer x store aggregates over the trailing 180 days: transaction_count, avg_transaction_value, max_transaction_value, and category_transaction_count at customer x store x category grain.

   Create a segment model for VIPs, big spenders, and category-loyal customers, built on top of the activity model.

   Verify the 180-day window is applied consistently. Grain is customer x store (with customer x store x category for the category counts).
   Exercises: file edits, model creation
5. Segment model

   Built on the activity layer. Expected columns: customer identifier, store_name, segment_name, transaction_count, avg_transaction_value, max_transaction_value, category, category_transaction_count. Verify multi-segment logic and thresholds.

   Compile and preview the segment model. Exclude customers with no segment.

   Exercises: file edits, model creation on top of activity layer
6. Safe preview

   The SQL compiles, sample rows render, nothing lands in the warehouse. The "exclude no segment" filter doubles as a data-quality check.

   Materialize the segment model into my dev schema. Skip the verification pass - the preview already confirmed the output.

   Confirm multi-segment customers appear on multiple rows. An empty segment (e.g., zero VIPs) usually points to a threshold or join issue. Sanity-check the audience size.
   Exercises: dbt_compile, dbt_show
7. Materialize

   dbt Wizard runs the build against your dev schema (dev_lab_user_N). The instructor drops dev schemas after the lab via a cleanup script.

   Verification is skipped because the Step 6 preview already validated the output. A full verification pass would consume ~10% of the 20-minute lab budget.

   Exercises: dbt_run against dev schema

What gets built