Module 6: Accumulating And Cumulative Patterns

Modeling Kiosko's session funnel

Next →

Description

This lesson builds fact_sessions in full: seventeen rows, one for each browsing session that appears in Kiosko's 32 events, using a single aggregate query that applies lesson 2's mechanism — milestone per column — to every session at once. Before writing that query, this lesson solves a problem lesson 2 deliberately avoided: events doesn't carry store_id in any of its columns, so you need to declare, explicitly and fixed, which store each session belongs to.

Connection to the module. This lesson takes the mechanism verified in lesson 2 (one session, three milestones, one row) and applies it to Kiosko's full session population, using MAX(CASE WHEN ...) instead of manual INSERT/UPDATE — the equivalent of a complete recalculation from scratch. Lesson 4 is going to rebuild the same table event by event, to compare the two approaches.

Kiosko's 32 canonical events, with no change at all

events is the same dataset that dbt-analytics-engineering-guide declared as source — same event_id, event_type, session_id, event_ts, without inventing a single new event. This guide reuses it exactly:

# events.py
RAW_EVENTS = [
    # 2026-08-03
    ("E5001", "page_view", "SESS-01", "2026-08-03T08:00:12"),
    ("E5002", "add_to_cart", "SESS-01", "2026-08-03T08:02:45"),
    ("E5003", "purchase", "SESS-01", "2026-08-03T08:03:10"),
    ("E5004", "page_view", "SESS-02", "2026-08-03T08:05:00"),
    # 2026-08-04
    ("E5005", "page_view", "SESS-03", "2026-08-04T08:10:00"),
    ("E5006", "add_to_cart", "SESS-03", "2026-08-04T08:12:30"),
    ("E5007", "purchase", "SESS-03", "2026-08-04T08:13:05"),
    ("E5008", "page_view", "SESS-04", "2026-08-04T08:20:00"),
    ("E5009", "page_view", "SESS-05", "2026-08-04T08:45:00"),
    # 2026-08-05
    ("E5010", "page_view", "SESS-06", "2026-08-05T08:00:00"),
    ("E5011", "page_view", "SESS-07", "2026-08-05T08:15:00"),
    ("E5012", "add_to_cart", "SESS-07", "2026-08-05T08:16:20"),
    # 2026-08-06
    ("E5013", "page_view", "SESS-08", "2026-08-06T08:05:00"),
    ("E5014", "add_to_cart", "SESS-08", "2026-08-06T08:07:15"),
    ("E5015", "purchase", "SESS-08", "2026-08-06T08:08:00"),
    ("E5016", "page_view", "SESS-09", "2026-08-06T08:30:00"),
    # 2026-08-07
    ("E5017", "page_view", "SESS-10", "2026-08-07T08:00:00"),
    ("E5018", "add_to_cart", "SESS-10", "2026-08-07T08:03:10"),
    ("E5019", "purchase", "SESS-10", "2026-08-07T08:04:00"),
    ("E5020", "page_view", "SESS-11", "2026-08-07T08:20:00"),
    ("E5021", "add_to_cart", "SESS-11", "2026-08-07T08:22:00"),
    ("E5022", "page_view", "SESS-12", "2026-08-07T08:50:00"),
    # 2026-08-08
    ("E5023", "page_view", "SESS-13", "2026-08-08T07:55:00"),
    ("E5024", "add_to_cart", "SESS-13", "2026-08-08T07:58:00"),
    ("E5025", "purchase", "SESS-13", "2026-08-08T07:59:10"),
    ("E5026", "page_view", "SESS-14", "2026-08-08T08:10:00"),
    ("E5027", "add_to_cart", "SESS-14", "2026-08-08T08:12:45"),
    ("E5028", "purchase", "SESS-14", "2026-08-08T08:13:30"),
    ("E5029", "page_view", "SESS-15", "2026-08-08T08:40:00"),
    # 2026-08-09
    ("E5030", "page_view", "SESS-16", "2026-08-09T09:00:00"),
    ("E5031", "page_view", "SESS-17", "2026-08-09T09:20:00"),
    ("E5032", "add_to_cart", "SESS-17", "2026-08-09T09:22:00"),
]

32 rows — 17 page_view, 9 add_to_cart, 6 purchase — spread across 17 distinct sessions (SESS-01 through SESS-17), exactly as the sibling guide declared them.

An analogy: the store directory, not a customer's receipt

store_id isn't in events for a real design reason, not an oversight: a delivery app's clickstream records what a customer does (view a page, add something, buy), not which physical store it happens in — many delivery apps don't even show the customer the concept of a "store," only a catalog. Assigning each session to a store is, then, a business decision that lives outside the event — similar to how a phone directory assigns each number to a branch, without the number itself carrying that information encoded.

Kiosko still doesn't have, in the data this guide uses, a real system for attributing a session to a store (something that would normally come from the customer's location, or which store catalog they were browsing). To be able to build fact_sessions with a complete store_id column, this guide declares a fixed, deterministic mapping, additive to what the sibling guide dbt-analytics-engineering-guide models — that guide never assigns sessions to stores, so this declaration doesn't contradict it, it only completes a piece Kiosko needs for this specific analysis.

The session -> store mapping, declared explicitly

The mapping rotates Kiosko's three stores in the same order they've appeared since module 1 (S01 Bogota, S02 Lima, S03 Santiago), assigning each session by its sequence number: SESS-01 -> S01, SESS-02 -> S02, SESS-03 -> S03, SESS-04 -> S01 (the rotation starts over), and so on.

# session_store_map.py
STORE_ROTATION = ["S01", "S02", "S03"]


def store_for_session(session_id: str) -> str:
    """Fixed, deterministic mapping of session to store: rotates S01/S02/S03
    based on the session's sequence number. SESS-01 -> S01, SESS-02 -> S02,
    SESS-03 -> S03, SESS-04 -> S01 (the rotation restarts), etc.

    This assignment is additive: events (the canonical source from the
    dbt-analytics-engineering-guide guide) never declares store_id, so this
    guide adds it to be able to build fact_sessions with store context.
    """
    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)]  # SESS-01 .. SESS-17

for session_id in ALL_SESSIONS:
    print(f"{session_id} -> {store_for_session(session_id)}")

What to expect. Running python3 session_store_map.py, the output is exactly this:

SESS-01 -> S01
SESS-02 -> S02
SESS-03 -> S03
SESS-04 -> S01
SESS-05 -> S02
SESS-06 -> S03
SESS-07 -> S01
SESS-08 -> S02
SESS-09 -> S03
SESS-10 -> S01
SESS-11 -> S02
SESS-12 -> S03
SESS-13 -> S01
SESS-14 -> S02
SESS-15 -> S03
SESS-16 -> S01
SESS-17 -> S02

Seventeen sessions, split 6/6/5 across the three stores (S01 and S02 with six each, S03 with five) — an arithmetic consequence of 17 not being a multiple of 3, not an additional business decision.

Worked example: fact_sessions in full, with MAX(CASE WHEN ...)

With events loaded into DuckDB and the mapping declared as an auxiliary table, the query that builds fact_sessions in full uses the same MAX(CASE WHEN event_type = '...' THEN event_ts END) pattern from lesson 2, now grouped by session_id for all seventeen sessions at once:

# fact_sessions_build.py -- continues on top of events loaded and session_store_map declared
import duckdb
from datetime import datetime

con = duckdb.connect()
con.execute("CREATE TABLE events (event_id VARCHAR, event_type VARCHAR, session_id VARCHAR, event_ts TIMESTAMP)")
con.executemany(
    "INSERT INTO events VALUES (?, ?, ?, ?)",
    [(r[0], r[1], r[2], datetime.fromisoformat(r[3])) for r in RAW_EVENTS],
)

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

print(f"fact_sessions rows: {con.sql('SELECT COUNT(*) FROM fact_sessions').fetchone()[0]}\n")
con.sql("SELECT * FROM fact_sessions ORDER BY session_id").show(max_width=300)

What to expect.

fact_sessions rows: 17

┌────────────┬──────────┬──────────────┬─────────────────────┬─────────────────────┬─────────────────────┬──────────────┐
│ session_id │ store_id │ session_date │       view_ts       │   add_to_cart_ts    │     purchase_ts     │ is_converted │
│  varchar   │ varchar  │     date     │      timestamp      │      timestamp      │      timestamp      │   boolean    │
├────────────┼──────────┼──────────────┼─────────────────────┼─────────────────────┼─────────────────────┼──────────────┤
│ SESS-01    │ S01      │ 2026-08-03   │ 2026-08-03 08:00:12 │ 2026-08-03 08:02:45 │ 2026-08-03 08:03:10 │ true         │
│ SESS-02    │ S02      │ 2026-08-03   │ 2026-08-03 08:05:00 │ NULL                │ NULL                │ false        │
│ SESS-03    │ S03      │ 2026-08-04   │ 2026-08-04 08:10:00 │ 2026-08-04 08:12:30 │ 2026-08-04 08:13:05 │ true         │
│ SESS-04    │ S01      │ 2026-08-04   │ 2026-08-04 08:20:00 │ NULL                │ NULL                │ false        │
│ SESS-05    │ S02      │ 2026-08-04   │ 2026-08-04 08:45:00 │ NULL                │ NULL                │ false        │
│ SESS-06    │ S03      │ 2026-08-05   │ 2026-08-05 08:00:00 │ NULL                │ NULL                │ false        │
│ SESS-07    │ S01      │ 2026-08-05   │ 2026-08-05 08:15:00 │ 2026-08-05 08:16:20 │ NULL                │ false        │
│ SESS-08    │ S02      │ 2026-08-06   │ 2026-08-06 08:05:00 │ 2026-08-06 08:07:15 │ 2026-08-06 08:08:00 │ true         │
│ SESS-09    │ S03      │ 2026-08-06   │ 2026-08-06 08:30:00 │ NULL                │ NULL                │ false        │
│ SESS-10    │ S01      │ 2026-08-07   │ 2026-08-07 08:00:00 │ 2026-08-07 08:03:10 │ 2026-08-07 08:04:00 │ true         │
│ SESS-11    │ S02      │ 2026-08-07   │ 2026-08-07 08:20:00 │ 2026-08-07 08:22:00 │ NULL                │ false        │
│ SESS-12    │ S03      │ 2026-08-07   │ 2026-08-07 08:50:00 │ NULL                │ NULL                │ false        │
│ SESS-13    │ S01      │ 2026-08-08   │ 2026-08-08 07:55:00 │ 2026-08-08 07:58:00 │ 2026-08-08 07:59:10 │ true         │
│ SESS-14    │ S02      │ 2026-08-08   │ 2026-08-08 08:10:00 │ 2026-08-08 08:12:45 │ 2026-08-08 08:13:30 │ true         │
│ SESS-15    │ S03      │ 2026-08-08   │ 2026-08-08 08:40:00 │ NULL                │ NULL                │ false        │
│ SESS-16    │ S01      │ 2026-08-09   │ 2026-08-09 09:00:00 │ NULL                │ NULL                │ false        │
│ SESS-17    │ S02      │ 2026-08-09   │ 2026-08-09 09:20:00 │ 2026-08-09 09:22:00 │ NULL                │ false        │
└────────────┴──────────┴──────────────┴─────────────────────┴─────────────────────┴─────────────────────┴──────────────┘

Seventeen rows — exactly the number of distinct session_ids in events, no matter that some sessions have a single event (SESS-02, with only one page_view) and others have all three (SESS-01, SESS-03, SESS-08, SESS-10, SESS-13, SESS-14). The MAX(CASE WHEN ...) pattern automatically leaves NULL in any column whose corresponding event_type never shows up for that session — the same "milestone not reached" meaning you saw in lesson 2, now applied without writing a single manual INSERT/UPDATE.

Analyzing the funnel: where Kiosko drops off

With fact_sessions built, the complete funnel is answered with COUNT() over each milestone column — COUNT() ignores NULLs automatically, so it counts exactly the sessions that reached each stage:

print("=== Count by funnel stage ===")
con.sql("""
    SELECT COUNT(*) AS total_sessions, COUNT(view_ts) AS viewed,
           COUNT(add_to_cart_ts) AS added_to_cart, COUNT(purchase_ts) AS purchased
    FROM fact_sessions
""").show(max_width=200)

print("=== Conversion rates by stage ===")
con.sql("""
    SELECT
        ROUND(100.0 * COUNT(add_to_cart_ts) / COUNT(view_ts), 1) AS view_to_cart_pct,
        ROUND(100.0 * COUNT(purchase_ts) / NULLIF(COUNT(add_to_cart_ts), 0), 1) AS cart_to_purchase_pct,
        ROUND(100.0 * COUNT(purchase_ts) / COUNT(*), 1) AS overall_conversion_pct
    FROM fact_sessions
""").show(max_width=200)

print("=== Conversion by store ===")
con.sql("""
    SELECT store_id, COUNT(*) AS sessions, COUNT(add_to_cart_ts) AS carts,
           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
""").show(max_width=200)

What to expect.

=== Count by funnel stage ===
┌────────────────┬────────┬───────────────┬───────────┐
│ total_sessions │ viewed │ added_to_cart │ purchased │
│     int64      │ int64  │     int64     │   int64   │
├────────────────┼────────┼───────────────┼───────────┤
│             17 │     17 │             9 │         6 │
└────────────────┴────────┴───────────────┴───────────┘

=== Conversion rates by stage ===
┌──────────────────┬──────────────────────┬────────────────────────┐
│ view_to_cart_pct │ cart_to_purchase_pct │ overall_conversion_pct │
│      double      │        double        │         double         │
├──────────────────┼──────────────────────┼────────────────────────┤
│             52.9 │                 66.7 │                   35.3 │
└──────────────────┴──────────────────────┴────────────────────────┘

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

Kiosko's complete funnel: all 17 sessions viewed at least one page (100%, by the dataset's definition), 9 added something to the cart (52.9%), and 6 bought (35.3% total conversion, 66.7% of those who reached the cart). The steepest drop happens between "viewed" and "added to cart" — almost half the sessions are lost there — not between "added to cart" and "bought," where two out of three sessions do end up buying. S03 (Santiago) has the lowest conversion (20%) with the smallest sample of the three stores — a number that, with only five sessions, needs to be read as a weak signal, not a firm conclusion.

Diagram: the funnel as a funnel

flowchart TD
    A["17 sessions\npage_view (100%)"] -->|"52.9%"| B["9 sessions\nadd_to_cart"]
    B -->|"66.7%"| C["6 sessions\npurchase"]
    A -.->|"35.3% total conversion"| C

Common mistakes

Forgetting the JOIN against session_store_map and losing or duplicating sessions. What happens: someone builds fact_sessions without joining against the store mapping, or uses a badly written LEFT JOIN that produces multiple rows per session. Why it happens: session_store_map is a new table, easy to forget when the focus is on the MAX(CASE WHEN ...) pattern. How to spot it: if SELECT COUNT(*) FROM fact_sessions gives a number different from 17 — more, from a JOIN that multiplies rows, or fewer, from an INNER JOIN that drops sessions without a mapping — the JOIN is wrong. How to fix it: session_store_map must have exactly one row for each of the 17 sessions, with none repeated — verify with SELECT COUNT(DISTINCT session_id) FROM session_store_map before building fact_sessions, and confirm it gives 17.

Confusing COUNT(*) with COUNT(column) when measuring the funnel. What happens: someone uses COUNT(*) to count how many sessions "added to cart," instead of COUNT(add_to_cart_ts). Why it happens: COUNT(*) is the most common pattern for counting rows, and it's easy to forget it behaves differently from COUNT(column) when the column has NULLs. How to spot it: if your count of "sessions that added to cart" gives 17 instead of 9, you're counting every row, not the ones with add_to_cart_ts filled. How to fix it: COUNT(column) automatically ignores that specific column's NULLs — exactly the behavior this funnel needs — while COUNT(*) counts rows without looking at any value. Use COUNT(*) only for the total number of sessions, and COUNT(column) for each funnel stage.

Assuming session_date is the date of the purchase, not the date the session started. What happens: someone uses session_date to answer questions about when a purchase happened, without realizing this column was computed as MIN(CAST(event_ts AS DATE)) — the date of the session's first event, typically the page_view. Why it happens: in most of Kiosko's sessions, every event happens on the same day, so the difference doesn't show up. How to spot it: if you need the exact date of a purchase, not just the day the session started, session_date is not the right column — that's what purchase_ts exists for, with full date and time. How to fix it: use session_date to group sessions by start day (the use this module gives it); use purchase_ts::DATE if you specifically need the conversion's date.

Exercises

Exercise 1 — Count how many sessions each store had, without looking at the funnel. Using only fact_sessions and GROUP BY store_id, confirm that the distribution of sessions per store is 6/6/5, as the lesson's mapping predicted.

See solution 
print(con.sql("SELECT store_id, COUNT(*) AS sessions FROM fact_sessions GROUP BY store_id ORDER BY store_id"))

Expected output:

┌──────────┬──────────┐
│ store_id │ sessions │
│ varchar  │  int64   │
├──────────┼──────────┤
│ S01      │        6 │
│ S02      │        6 │
│ S03      │        5 │
└──────────┴──────────┘

6 + 6 + 5 = 17, confirming the JOIN against session_store_map neither lost nor duplicated any session.

Exercise 2 — Find the sessions that reached the cart but didn't buy. Write a query that lists session_id, store_id, and add_to_cart_ts for the sessions where add_to_cart_ts is filled but purchase_ts is empty — Kiosko's "abandoned cart."

See solution 
print(con.sql("""
    SELECT session_id, store_id, add_to_cart_ts
    FROM fact_sessions
    WHERE add_to_cart_ts IS NOT NULL AND purchase_ts IS NULL
    ORDER BY session_id
"""))

Expected output:

┌────────────┬──────────┬─────────────────────┐
│ session_id │ store_id │   add_to_cart_ts    │
│  varchar   │ varchar  │      timestamp      │
├────────────┼──────────┼─────────────────────┤
│ SESS-07    │ S01      │ 2026-08-05 08:16:20 │
│ SESS-11    │ S02      │ 2026-08-07 08:22:00 │
│ SESS-17    │ S02      │ 2026-08-09 09:22:00 │
└────────────┴──────────┴─────────────────────┘

Three sessions — SESS-07, SESS-11, SESS-17 — are exactly this week's "abandoned cart": nine sessions reached the cart, six bought, and these three are the difference. This kind of query — filtering by a specific combination of filled and empty milestones — is exactly the kind of question an accumulating snapshot answers directly, without needing any additional JOIN against events.

Exercise 3 — Explain why is_converted and COUNT(purchase_ts) should always give the same result. In 2-3 sentences, explain the relationship between the boolean column is_converted and the timestamp column purchase_ts, and why counting either one should produce the same number of converted sessions.

See solution

is_converted was computed, in the same query that built fact_sessions, directly from whether a purchase event existed for that session (MAX(CASE WHEN event_type = 'purchase' THEN true ELSE false END)) — it's, in essence, a boolean version of the same information that already lives in purchase_ts. By construction, both columns are in sync: if purchase_ts is filled, is_converted is true; if it's empty, it's false. SELECT COUNT(*) FROM fact_sessions WHERE is_converted and SELECT COUNT(purchase_ts) FROM fact_sessions should always give the same number (6) — if they didn't match, that would be evidence of a bug in the query that built the table, not a real business difference.

Summary and next step

This lesson built fact_sessions in full: 17 rows, one per session, using MAX(CASE WHEN event_type = '...' THEN event_ts END) grouped by session_id over Kiosko's 32 canonical events — without inventing a single event — plus a fixed, declared session-to-store mapping (SESS-01 -> S01, rotating S01/S02/S03). The funnel analysis showed that Kiosko converts 35.3% of its sessions (52.9% reach the cart, and of those, 66.7% end up buying), with the biggest drop happening before the cart, not after.

Before moving on you should be able to: explain why events needed an additional store mapping that events itself doesn't provide; write from memory the MAX(CASE WHEN ...) pattern for building milestone columns from an events table; and calculate a conversion rate by stage using COUNT(column) instead of COUNT(*).

Lesson 4 rebuilds this same table, with the same final result, but in a completely different way: processing the 32 events one at a time, in chronological order, with INSERT only on each session's first event and UPDATE on each of the following ones — the real production mechanism, not this lesson's aggregate recalculation.

Resources

← PreviousThe accumulating snapshot fact tableNext →Updating milestones in place