--- title: "Market basket — trailing 13 weeks" subtitle: "Loyalty-cohort affinity analysis on the AMG PDI Snowflake replica" description: "Internal dashboard combining trailing-13-week market-basket KPIs, item-pair lift, store performance, and a loyalty-vs-non-loyalty cohort comparison. Snowflake SQL inline; data shape matches the AMG PDI Enterprise replication (MarketBasket_Header / Line_Items / Loyalty_Cards)." author: "Zad Rafi" date: 2026-04-29 date-modified: 2026-04-29 page-layout: full toc: true engine: sql toc-depth: 2 toc-location: right categories: [retail, dashboards, snowflake, internal] format: html: include-in-header: text: | code-fold: show code-tools: true code-copy: true code-overflow: wrap execute: eval: true --- ::: abstract Single-file interactive dashboard for inside-store market-basket activity over the trailing 13 weeks (91 days). Audience: marketing / loyalty. The tile-by-tile Snowflake SQL is documented below — each query is self-contained and uses the standard AMG filter set (Company Ops, non-closed sites, `inside_sales_flag = 1`, `Item_Type = 1`) plus AMG-issued loyalty card prefixes (`767%`, `420767%`, `639%`, `900%`). Sample numbers shown in the embedded dashboard are placeholders; replace each `// === Q# ===` block in `market_basket_dashboard.html` with the JSON output of the matching query. ::: ::: callout-warning ## Draft / internal This page is `draft: true` in the front matter — it won't appear in `blog.html` listings or feeds. The numbers in the embedded dashboard are fabricated for layout testing; do not quote them. ::: ## At a glance | Tile | Grain | Output shape | |-------------------|-------------------|---------------------------------| | KPI strip | Single row, T13W | sales, baskets, avg ticket, items/basket, loyalty attach %, loyalty lift | | Daily trend | One row per day | sales, baskets, avg ticket, loyalty attach % | | Department mix | One row per department | sales, units, baskets, sales/basket | | Top items | Top 25 by sales | item_id, desc, dept, category, units, sales, baskets containing | | Item pairs | Top 50 by lift | A, B, baskets-both, support %, conf A→B %, lift | | Store performance | One row per site | site_id, location, sales, baskets, avg ticket, loyalty % | | Loyalty cohort | Two rows | loyalty vs non-loyalty basket profile | ------------------------------------------------------------------------ ## Embedded dashboard :::: {.dashboard-embed .aspect-tall data-url="market_basket_dashboard.html"} ::: dashboard-embed-toolbar [MARKET BASKET — INTERACTIVE]{.title} [[Open full screen ↗] · [Download market_basket_queries.sql]{download}]{.actions} ::: ```{=html} ``` :::: [Open full screen ↗]: market_basket_dashboard.html {target="_blank" rel="noopener"} [Download market_basket_queries.sql]: market_basket_queries.sql ------------------------------------------------------------------------ ## Data sources The queries target the Snowflake replica of the AMG PDI Enterprise stack — specifically the views replicated from `BIReportingDB` / `PDI_Warehouse_1561_01`: - `MARKETBASKET_HEADER` — one row per POS transaction; carries `INSIDE_SALES_FLAG`, `ORGANIZATION_KEY`, `CALENDAR_KEY`. - `MARKETBASKET_LINE_ITEMS` — one row per item-on-receipt; `ITEM_TYPE = 1` for merch, `2` for fuel. - `MARKETBASKET_LOYALTY_CARDS` — loyalty-card swipes per basket; AMG-issued cards filtered by prefix. - `DIM_VW_ORGANIZATION` — site dimension; `DIVISION_DESC = 'Company Ops'` and `AMG_COT_DESC <> 'Closed'` are the standard filters. - `CALENDAR` — date dimension; join on the `_KEY` surrogate, never on a converted date. - `FULLPRODUCT` — product dimension; `DEPARTMENT_DESC` and `CATEGORY_DESC` for the breakdowns. Set `USE DATABASE` and `USE SCHEMA` to the replica before running, or fully qualify each table. ------------------------------------------------------------------------ ## Queries ### Q1 — KPI strip ``` sql WITH SOURCES AS ( SELECT h.MARKETBASKET_HEADER_KEY, h.ORGANIZATION_KEY, h.CALENDAR_KEY, h.INSIDE_SALES_FLAG, li.PRODUCT_KEY, li.ITEM_TYPE, li.QUANTITY_SOLD, li.EXTENDED_RETAIL, c.DAY_DATE, o.SITE_ID, o.LOCATION_DESC, o.DIVISION_DESC, o.AMG_COT_DESC, CASE WHEN lc.MARKETBASKET_HEADER_KEY IS NOT NULL THEN 1 ELSE 0 END AS HAS_LOYALTY FROM MARKETBASKET_HEADER h JOIN MARKETBASKET_LINE_ITEMS li ON li.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY JOIN CALENDAR c ON c.CALENDAR_KEY = h.CALENDAR_KEY JOIN DIM_VW_ORGANIZATION o ON o.ORGANIZATION_KEY = h.ORGANIZATION_KEY LEFT JOIN ( SELECT DISTINCT MARKETBASKET_HEADER_KEY FROM MARKETBASKET_LOYALTY_CARDS WHERE LOYALTY_CARD_NUMBER LIKE '767%' OR LOYALTY_CARD_NUMBER LIKE '420767%' OR LOYALTY_CARD_NUMBER LIKE '639%' OR LOYALTY_CARD_NUMBER LIKE '900%' ) lc ON lc.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY WHERE c.DAY_DATE >= DATEADD(day, -91, CURRENT_DATE) AND c.DAY_DATE < CURRENT_DATE AND h.INSIDE_SALES_FLAG = 1 AND li.ITEM_TYPE = 1 AND o.DIVISION_DESC = 'Company Ops' AND o.AMG_COT_DESC <> 'Closed' ), basket_roll AS ( SELECT MARKETBASKET_HEADER_KEY, MAX(HAS_LOYALTY) AS HAS_LOYALTY, SUM(QUANTITY_SOLD) AS UNITS, SUM(EXTENDED_RETAIL) AS BASKET_TOTAL, COUNT(*) AS LINE_COUNT FROM SOURCES GROUP BY MARKETBASKET_HEADER_KEY ) SELECT SUM(BASKET_TOTAL) AS TOTAL_SALES, COUNT(*) AS BASKETS, SUM(UNITS) AS UNITS, SUM(BASKET_TOTAL) / NULLIF(COUNT(*),0) AS AVG_TICKET, SUM(LINE_COUNT) / NULLIF(COUNT(*),0) AS ITEMS_PER_BASKET, 100.0 * SUM(HAS_LOYALTY) / NULLIF(COUNT(*),0) AS LOYALTY_ATTACH_PCT, SUM(CASE WHEN HAS_LOYALTY=1 THEN BASKET_TOTAL END) / NULLIF(SUM(CASE WHEN HAS_LOYALTY=1 THEN 1 END),0) AS LOYALTY_AVG_TICKET, SUM(CASE WHEN HAS_LOYALTY=0 THEN BASKET_TOTAL END) / NULLIF(SUM(CASE WHEN HAS_LOYALTY=0 THEN 1 END),0) AS NON_LOYALTY_AVG_TICKET FROM basket_roll; ``` ### Q2 — Daily trend ``` sql WITH basket_roll AS ( SELECT c.DAY_DATE, h.MARKETBASKET_HEADER_KEY, SUM(li.EXTENDED_RETAIL) AS BASKET_TOTAL, MAX(CASE WHEN lc.MARKETBASKET_HEADER_KEY IS NOT NULL THEN 1 ELSE 0 END) AS HAS_LOYALTY FROM MARKETBASKET_HEADER h JOIN MARKETBASKET_LINE_ITEMS li ON li.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY JOIN CALENDAR c ON c.CALENDAR_KEY = h.CALENDAR_KEY JOIN DIM_VW_ORGANIZATION o ON o.ORGANIZATION_KEY = h.ORGANIZATION_KEY LEFT JOIN ( SELECT DISTINCT MARKETBASKET_HEADER_KEY FROM MARKETBASKET_LOYALTY_CARDS WHERE LOYALTY_CARD_NUMBER LIKE '767%' OR LOYALTY_CARD_NUMBER LIKE '420767%' OR LOYALTY_CARD_NUMBER LIKE '639%' OR LOYALTY_CARD_NUMBER LIKE '900%' ) lc ON lc.MARKETBASKET_HEADER_KEY = h.MARKETBASKET_HEADER_KEY WHERE c.DAY_DATE >= DATEADD(day, -91, CURRENT_DATE) AND c.DAY_DATE < CURRENT_DATE AND h.INSIDE_SALES_FLAG = 1 AND li.ITEM_TYPE = 1 AND o.DIVISION_DESC = 'Company Ops' AND o.AMG_COT_DESC <> 'Closed' GROUP BY c.DAY_DATE, h.MARKETBASKET_HEADER_KEY ) SELECT DAY_DATE, SUM(BASKET_TOTAL) AS SALES, COUNT(*) AS BASKETS, SUM(BASKET_TOTAL) / NULLIF(COUNT(*),0) AS AVG_TICKET, 100.0 * SUM(HAS_LOYALTY) / NULLIF(COUNT(*),0) AS LOYALTY_ATTACH_PCT FROM basket_roll GROUP BY DAY_DATE ORDER BY DAY_DATE; ``` ### Q3 — Department mix, Q4 — Top items, Q5 — Item pairs, Q6 — Store performance, Q7 — Loyalty cohort The remaining five queries follow the same pattern. The full set with comments lives in [`market_basket_queries.sql`] — open it and run each block top-to-bottom, pasting each result into the matching `// === Q# ===` block in [`market_basket_dashboard.html`]. [`market_basket_queries.sql`]: market_basket_queries.sql [`market_basket_dashboard.html`]: market_basket_dashboard.html ::: {.callout-tip collapse="true"} ## Why a CTE per query instead of one shared base? Snowflake won't let CTEs span statements (each query is its own session-bound parse). The duplicated `SOURCES` block is the cost of keeping each query independently runnable. If you're scheduling these as a Snowflake task or pipeline, factor the shared filter set into a view: `CREATE VIEW MB_T13W_FILTERED AS …` — then the seven queries collapse to thin aggregations on top. ::: ------------------------------------------------------------------------ ## How to refresh 1. Open `market_basket_queries.sql` in Snowsight (or your tool of choice). 2. Set the active database/schema to the PDI replica. 3. Run each query; export results as JSON. 4. In `market_basket_dashboard.html`, find the matching `// === Q# ===` block and replace the JS array/object with the JSON payload. Column names already match. 5. Reload the dashboard in your browser — no rebuild required. ::: callout-note ## Performance caveat — Q5 The item-pair query is the expensive one. With a basket table in the tens of millions, even the top-200 candidate cap can run for several minutes. Drop the cap to 100, pre-filter by region, or materialize the candidate items into a transient table if you're running this often. ::: ------------------------------------------------------------------------ ## Caveats - **Sample numbers are fabricated.** The defaults in the embedded dashboard pick plausible c-store SKUs (Marlboro/Newport in tobacco, coffee+bagel as the highest-lift pair) so the layout reads correctly. Replace before sharing externally. - **Loyalty attach uses AMG-issued cards only.** Drop the prefix filters in the loyalty subquery to include third-party programs. - **`FULLPRODUCT.CATEGORY_DESC`** is conventional but verify your replicated schema before running Q3/Q4 — some replications flatten naming. - **Filters in the dashboard header are scaffolding.** They populate from `Q6_STORES` but don't apply across charts (each tile is a single pre-aggregated paste). To slice by store or cohort, re-run the SQL with the additional `WHERE` predicate. ------------------------------------------------------------------------ ## Related - [`market_basket_dashboard.html`] — the standalone dashboard - [`market_basket_queries.sql`] — full query set with comments - [Public dashboards] — the BP / PUFA / store-uptime collection (separate page) true [`market_basket_dashboard.html`]: market_basket_dashboard.html [`market_basket_queries.sql`]: market_basket_queries.sql [Public dashboards]: dashboards.html