Module 8: Project Kioskos Analytics Warehouse

Adding the session funnel and cumulative activity

Next →

Description

The star schema you built in the previous lesson answers questions about a sale: how much, of what category, at which store. But two of the brief's four requirements from lesson 2 — "how well do sessions convert?" and "how active was each store?" — can't be answered with fact_orders, no matter how many dimensions you add to it. This lesson brings into the warehouse the two fact tables module 6 built specifically for those questions: fact_sessions — an accumulating snapshot tracking the complete funnel, from someone looking at a product to buying it — and fact_store_activity — a cumulative table design accumulating each store's daily activity in rolling 7- and 30-day windows.

Connection to the module. This lesson adds, to the same warehouse that already has fact_orders and the complete star, two facts with a grain and update mechanism completely different from the transactional one. It doesn't modify a single row of what lesson 4 built — the two new tables coexist with fact_orders and dim_product_scd, sharing dim_store as a conformed dimension, with no need for any direct JOIN between them.

An analogy: the same business, seen from two different cameras

Think about a physical store with two completely different surveillance systems: a camera at the register, recording every exact transaction — who bought what, for how much, at what time — and a person counter at the door, which doesn't know what each visitor bought, but knows how many people came in, how many stopped to look at a shelf, and how many left without buying anything. Neither camera replaces the other — the register could never tell you the visitor-to-buyer conversion rate, and the person counter could never tell you a sale's exact revenue. A business that only looks at one of the two cameras has an incomplete view of what's really happening in the store.

fact_orders is the register: exact, transactional, one row per sale. fact_sessions and fact_store_activity are the other two cameras: one following each session's complete journey (view → cart → purchase), another accumulating each store's daily pulse. This lesson doesn't replace any camera — it adds the two the warehouse was missing.

Worked example: the funnel and activity, integrated into the same warehouse

Part 1 — fact_sessions: the funnel's accumulating snapshot

This script continues on top of lesson 4's same con connection, with fact_orders, the star, and dim_product_scd already built — and adds events, typed since lesson 3.

# capstone_funnel_activity.py -- Part 1: fact_sessions (continues on top of con, with events already typed)
STORE_ROTATION = ["S01", "S02", "S03"]


def store_for_session(session_id: str) -> str:
    session_number = int(session_id.split("-")[1])
    return STORE_ROTATION[(session_number - 1) % 3]


ALL_SESSIONS = [f"SESS-{n:02d}" for n in range(1, 18)]
con.execute("CREATE TABLE session_store_map (session_id VARCHAR, store_id VARCHAR)")
con.executemany("INSERT INTO session_store_map VALUES (?, ?)", [(sid, store_for_session(sid)) for sid in ALL_SESSIONS])

con.execute("""
    CREATE TABLE fact_sessions AS
    SELECT e.session_id, m.store_id, MIN(CAST(e.event_ts AS DATE)) AS session_date,
        MAX(CASE WHEN e.event_type = 'page_view'   THEN e.event_ts END) AS view_ts,
        MAX(CASE WHEN e.event_type = 'add_to_cart' THEN e.event_ts END) AS add_to_cart_ts,
        MAX(CASE WHEN e.event_type = 'purchase'    THEN e.event_ts END) AS purchase_ts,
        MAX(CASE WHEN e.event_type = 'purchase' THEN true ELSE false END) AS is_converted
    FROM events e JOIN session_store_map m ON e.session_id = m.session_id
    GROUP BY e.session_id, m.store_id
""")
funnel = con.sql("""
    SELECT COUNT(*), COUNT(view_ts), COUNT(add_to_cart_ts), COUNT(purchase_ts) FROM fact_sessions
""").fetchone()
conversion_pct = con.sql("SELECT ROUND(100.0 * COUNT(purchase_ts) / COUNT(*), 1) FROM fact_sessions").fetchone()[0]

print("Part 1 -- fact_sessions: the funnel's accumulating snapshot")
print(f"  fact_sessions  {con.sql('SELECT COUNT(*) FROM fact_sessions').fetchone()[0]:3} rows (17 sessions, mapped to S01/S02/S03)")
print(f"  funnel: total={funnel[0]} viewed={funnel[1]} cart={funnel[2]} purchased={funnel[3]}")
print(f"  total conversion: {conversion_pct}%")
assert funnel == (17, 17, 9, 6) and conversion_pct == 35.3

What to expect.

Part 1 -- fact_sessions: the funnel's accumulating snapshot
  fact_sessions   17 rows (17 sessions, mapped to S01/S02/S03)
  funnel: total=17 viewed=17 cart=9 purchased=6
  total conversion: 35.3%

Seventeen sessions, each with a single row — the accumulating snapshot's hallmark: nobody inserted a new row when a session went from "viewed" to "added to cart"; instead, view_ts and add_to_cart_ts got filled inside the same row, with the MAX(CASE WHEN ...) you already built in module 6. Kiosko's complete funnel converts 35.3% of its sessions into purchases — a number that fact_orders, even with every sale perfectly recorded, could never calculate on its own, because it knows nothing about the sessions that did not end in a purchase.

Part 2 — fact_store_activity: the 7- and 30-day cumulative table design

con.execute("""
    CREATE TABLE fact_store_activity (
        store_id VARCHAR, activity_date DATE, daily_revenue DOUBLE,
        revenue_array_7d DOUBLE[], active_days_7d INTEGER,
        revenue_array_30d DOUBLE[], active_days_30d INTEGER
    )
""")
DAYS = ["2026-08-03", "2026-08-04", "2026-08-05", "2026-08-06", "2026-08-07", "2026-08-08", "2026-08-09"]
for day in DAYS:
    for store_id in STORE_ROTATION:
        daily_revenue = con.sql(f"""
            SELECT COALESCE(ROUND(SUM(revenue), 2), 0.0) FROM fact_orders
            WHERE store_id = '{store_id}' AND CAST(order_ts AS DATE) = DATE '{day}'
        """).fetchone()[0]
        prev = con.sql(f"""
            SELECT revenue_array_7d, revenue_array_30d FROM fact_store_activity
            WHERE store_id = '{store_id}' ORDER BY activity_date DESC LIMIT 1
        """).fetchone()
        if prev is None:
            new_7d, new_30d = [daily_revenue], [daily_revenue]
        else:
            new_7d = ([daily_revenue] + list(prev[0]))[:7]
            new_30d = ([daily_revenue] + list(prev[1]))[:30]
        active_7d = sum(1 for v in new_7d if v > 0)
        active_30d = sum(1 for v in new_30d if v > 0)
        con.execute("INSERT INTO fact_store_activity VALUES (?, ?, ?, ?, ?, ?, ?)",
            (store_id, day, daily_revenue, new_7d, active_7d, new_30d, active_30d))

activity_rows = con.sql("SELECT COUNT(*) FROM fact_store_activity").fetchone()[0]
snapshot = con.sql("""
    SELECT store_id, ROUND(list_sum(revenue_array_7d), 2) AS revenue_7d, active_days_7d, active_days_30d
    FROM fact_store_activity WHERE activity_date = DATE '2026-08-09' ORDER BY store_id
""").fetchall()

print("\nPart 2 -- fact_store_activity: the cumulative table design")
print(f"  fact_store_activity  {activity_rows:3} rows (3 stores x 7 days)")
print("  snapshot for 2026-08-09 (last available day):")
for store_id, revenue_7d, active_7d, active_30d in snapshot:
    print(f"    {store_id}: revenue_7d={revenue_7d:6}  active_days_7d={active_7d}  active_days_30d={active_30d}")

weekly_revenue = con.sql("SELECT store_id, ROUND(SUM(revenue), 2) FROM fact_orders GROUP BY store_id ORDER BY store_id").fetchall()
for (store_id, revenue_7d, _, _), (_, weekly) in zip(snapshot, weekly_revenue):
    assert revenue_7d == weekly, f"{store_id}'s revenue_7d doesn't match the known weekly revenue"
assert activity_rows == 21
print("  Verification OK: revenue_7d == fact_orders weekly revenue, for the 3 stores")

What to expect.

Part 2 -- fact_store_activity: the cumulative table design
  fact_store_activity   21 rows (3 stores x 7 days)
  snapshot for 2026-08-09 (last available day):
    S01: revenue_7d=  38.3  active_days_7d=7  active_days_30d=7
    S02: revenue_7d=  38.8  active_days_7d=7  active_days_30d=7
    S03: revenue_7d= 29.05  active_days_7d=6  active_days_30d=6
  Verification OK: revenue_7d == fact_orders weekly revenue, for the 3 stores

Notice the build pattern, line by line: each new fact_store_activity row gets assembled from daily_revenue (today's number, calculated over fact_orders) and prev (yesterday's array, read from the same store's previous row) — the seven complete fact_orders rows never get reread to rebuild the array from scratch. That is, precisely, Zach Wilson/DataExpert's cumulative pattern: growing one day at a time, on top of the previous day's summary, not recalculating the whole history on every run. And the cross-verification — the last day's revenue_7d against fact_orders's weekly revenue — confirms something important: the accumulated array, even though built in a completely different way than a simple SUM(), reaches exactly the same number as the original transactional fact.

Diagram: two new facts, the same conformed dimension

flowchart TD
    subgraph Transactional["fact_orders (lesson 3-4)"]
        A["40 order lines\nrevenue = 106.15"]
    end
    subgraph Accumulating["fact_sessions (accumulating snapshot)"]
        B["17 sessions\nfunnel 17 -> 9 -> 6\nconversion 35.3%"]
    end
    subgraph Cumulative["fact_store_activity (cumulative)"]
        C["21 rows (3 stores x 7 days)\nrevenue_7d == weekly revenue"]
    end
    D["dim_store (3 rows)\nCONFORMED DIMENSION"]
    A --> D
    B --> D
    C --> D
    D -.shares store_id.-> A
    D -.shares store_id.-> B
    D -.shares store_id.-> C

The three fact tables never join directly against each other — they have different grains: order line, session, day+store — but all three share dim_store as a conformed dimension. That's what lets Kiosko's management ask "did S03 have low conversion AND low activity this week?" without needing a JOIN between fact_sessions and fact_store_activity — just aggregate each one separately, at the store level, and compare the results side by side.

Going deeper: why this lesson doesn't join fact_sessions with fact_store_activity

It's worth being explicit about something this lesson doesn't do, because the temptation to do it is real when two new tables show up together in the same module: fact_sessions (grain: a session) and fact_store_activity (grain: a store per day) never get joined against each other anywhere in this warehouse. The reason isn't a technical limitation — technically, a JOIN by store_id would be trivial to write; it's a grain reason, the same discipline module 1 taught you to declare so carefully. A fact_sessions row describes a complete session (which can last minutes); a fact_store_activity row describes a store's entire day (which groups dozens of sessions and sales). Joining them directly, by store_id alone, would multiply each session by each day of activity of its store, producing a result with no real business meaning at all — exactly the kind of meaningless fan-out module 7's lesson 1 exercise 3 already warned about.

If Kiosko ever needed to answer "did S03's sessions on August 5th have lower conversion than the rest of the week's?", the correct way wouldn't be a JOIN between the two tables — it would be aggregating fact_sessions by store_id + session_date first, and then comparing that result, already at the same grain, against fact_store_activity. Different grain always implies aggregating before comparing, never joining directly.

Common mistakes

Trying to join fact_sessions with fact_store_activity directly by store_id. What happens: someone, motivated by having both tables in the same connection, writes SELECT * FROM fact_sessions JOIN fact_store_activity ON fact_sessions.store_id = fact_store_activity.store_id, expecting a meaningful combined result. Why it happens: technically the JOIN produces no error at all — both tables have the store_id column — so the problem doesn't manifest as an exception, only as a meaningless result. How to spot it: if your query produces more than 17 rows (the number of sessions) or more than 21 (the number of store-days), you have a partial cartesian product — each store's session multiplied by each of that same store's seven activity days. How to fix it: as this lesson's deep dive explains, aggregate each table to a compatible grain before comparing their results — never join them directly by a shared dimension when their grains differ.

Building fact_store_activity out of order, skipping a day of the week. What happens: someone, adapting this lesson's code, iterates over DAYS in a non-chronological order, or skips a day by mistake. Why it happens: the cumulative pattern depends on each new row reading the same store's previous row with ORDER BY activity_date DESC LIMIT 1 — if the insertion order isn't chronological, that "previous row" isn't really yesterday's. How to spot it: if the last day's active_days_7d doesn't match the real number of days with sales, or if revenue_array_7d has fewer than seven elements when seven days have already passed, check your loop's order. How to fix it: DAYS, in this lesson's code, is declared in explicit chronological order — not generated with any dynamic date calculation — precisely so the "read yesterday's row, add today to it" pattern works unambiguously.

Thinking fact_sessions needs to join with fact_orders to calculate conversion. What happens: someone, looking to "confirm" fact_sessions's conversion, tries to cross it with fact_orders to verify each purchase_ts corresponds to a real sale row. Why it happens: it seems reasonable to want a double check between two sources of the same business reality (a purchase). How to spot it: if you look for a common column between fact_sessions and fact_orders — something like order_id inside fact_sessions, or session_id inside fact_orders — you're not going to find it in any module of this guide. How to fix it: as module 7's lesson 1 exercise 3 already explained, Kiosko's current domain has no key connecting a browsing session to the specific order that originated it — is_converted in fact_sessions comes exclusively from purchase-type events, with no direct relationship to fact_orders. Connecting both sources with a real key would be an extension of the model, not something this guide built.

Exercises

Exercise 1 — Calculate conversion by store, using fact_sessions. Without looking at module 6, write the query that groups fact_sessions by store_id and calculates each store's conversion rate.

See solution 
print(con.sql("""
    SELECT store_id, COUNT(*) AS sessions, COUNT(purchase_ts) AS purchases,
           ROUND(100.0 * COUNT(purchase_ts) / COUNT(*), 1) AS conversion_pct
    FROM fact_sessions GROUP BY store_id ORDER BY store_id
"""))

Expected output:

┌──────────┬──────────┬───────────┬────────────────┐
│ store_id │ sessions │ purchases │ conversion_pct  │
│ varchar  │  int64   │   int64   │     double      │
├──────────┼──────────┼───────────┼─────────────────┤
│ S01      │        6 │         3 │            50.0 │
│ S02      │        6 │         2 │            33.3 │
│ S03      │        5 │         1 │            20.0 │
└──────────┴──────────┴───────────┴─────────────────┘

S01 converts better than the complete funnel's average (50.0% against 35.3%), and S03 worse (20.0%) — the same breakdown you already built in module 6, now running inside the integrated warehouse, sharing dim_store with this lesson's other two fact tables.

Exercise 2 — Confirm active_days_30d can never be greater than 7 in this week of data. Without running any new query, explain in 2-3 sentences why, even though the revenue_array_30d array is designed to accumulate up to thirty days, active_days_30d can't exceed 7 in the 2026-08-09 snapshot.

See solution

active_days_30d counts how many values in the revenue_array_30d array are greater than zero, and that array can only have as many elements as days have elapsed since the store started recording activity. Since Kiosko only has fixed data for seven days (August 3rd through 9th), revenue_array_30d never reaches more than seven elements in this dataset, no matter that its maximum capacity — defined by the [:30] trim — is thirty. If Kiosko's dataset had a full month of data, only then could active_days_30d start to meaningfully differ from active_days_7d — with only a week of fixed data, both numbers are limited by the same real seven-day window.

Exercise 3 — Explain, from memory, why this lesson doesn't rebuild any point-in-time JOIN, even though dim_product_scd already exists in the warehouse. In 2-3 sentences, explain why fact_sessions and fact_store_activity don't need to join against dim_product_scd, unlike what lesson 4 did.

See solution

fact_sessions describes a session's browsing behavior (viewed, added to cart, purchased), without recording which specific product that session looked at or bought — this guide's canonical events don't include product_id — so there's no column connecting a session to a dim_product_scd row. fact_store_activity, in turn, aggregates revenue at the store-and-day level, without breaking it down by product either. Neither of the two business questions these two tables answer — funnel conversion, store activity — depends on knowing a specific product's category or cost, so lesson 4's point-in-time join, while still correct and available in the warehouse, simply isn't relevant to this lesson.

Summary and next step

In this lesson you added two completely different facts to the warehouse: fact_sessions — the funnel's accumulating snapshot, seventeen sessions, total conversion 35.3% — and fact_store_activity — the cumulative table design, twenty-one rows, with revenue_7d identical to the weekly revenue known since module 1 across all three stores. Neither joins directly with the other, nor with fact_orders: all three share dim_store as a conformed dimension, answering different business questions over the same warehouse.

Before moving on you should be able to: explain why fact_sessions and fact_store_activity never join directly against each other; recite from memory the complete funnel (17 → 9 → 6, 35.3%) and the last day's actives (S01: 7/7, S02: 7/7, S03: 6/6); and explain why neither table needs lesson 4's point-in-time join.

Lesson 6 closes the warehouse's gold layer: it builds mart_daily_sales_obt, the wide table the BI team is going to query directly, with lesson 4's point-in-time join already resolved inside — the piece lesson 2's brief explicitly requested.

Resources

← PreviousBuilding the star with historized dim_productNext →Publishing the OBT mart for the dashboard team