Market basket — trailing 13 weeks

Loyalty-cohort affinity analysis on the AMG PDI Snowflake replica

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).

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.

WarningDraft / 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

MARKET BASKET — INTERACTIVE Open full screen ↗ · Download market_basket_queries.sql{download}

---

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

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

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.

TipWhy 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.

NotePerformance 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