Mini-project: Kiosko's funnel and cumulative activity

Description

This project closes the module by integrating the six previous pieces: the minimal accumulating snapshot mechanism verified on a single session (lesson 2), fact_sessions in full with the session-store mapping declared (lesson 3), the same fact rebuilt event by event with zero differences (lesson 4), the cumulative table design verified over three days of one store (lesson 5), fact_store_activity in full with array trimming and double verification (lesson 6), and the 7- and 30-day actives analysis (lesson 7). What's left is to bring it all together in a single flow, verified with assert at every step, over the same fact_orders (40 rows) and events (32 rows, canonical from dbt-analytics-engineering-guide) this module has used since lesson 1.

The project has five parts. First, you rebuild fact_orders and events, inherited unchanged. Second, you build fact_sessions with the session-store mapping explicitly declared. Third, you analyze the conversion funnel, by stage and by store. Fourth, you build fact_store_activity with the 7- and 30-day windows. Fifth, you document everything in ACCUMULATING_AND_CUMULATIVE_SUMMARY, the formal structure that closes the module.

Connection to the module. This project introduces no new concept — it's the final integration of the seven previous lessons, packaged as ACCUMULATING_AND_CUMULATIVE_SUMMARY, the structure this guide's module 7 can cite without rebuilding the evidence from scratch.

An analogy: the full weekly report, two distinct facts, a single close

Each lesson in this module solved one piece separately: how to stamp a shipping label without reprinting it, how to scale that stamp to seventeen sessions, how to verify that the real mechanism (event by event) matches the aggregate shortcut, how to grow a summary one day at a time without rereading the full year, and how to confirm that summary tells the truth. This project is the weekly close: the two facts — fact_sessions and fact_store_activity — built end to end, with every number verified by an assert before moving to the next, exactly the rigor a real data team would apply before handing a Kiosko manager a report that mixes "how our sessions convert" with "how active each store is."

The material: everything this module built, in a single flow

You need, in the same folder: kiosko.py, raw_orders.py (identical to the previous modules) and events.py (the 32 canonical events, introduced in this module's lesson 3). You don't need any additional file — the session-store mapping and the per-store activity calculation are defined directly in this project's script, just like in the previous projects.

The reference solution, verified

Part 1 — Rebuilding fact_orders and events, inherited unchanged

# kiosko_accumulating_cumulative_project.py -- module 6's closing mini-project
from datetime import datetime
import duckdb

from kiosko import DIM_PRODUCT, DIM_STORE, Order, transform_fact_orders
from raw_orders import RAW_ORDERS
from events import RAW_EVENTS

print("=== Kiosko: funnel and cumulative activity, module 6's final delivery ===\n")

con = duckdb.connect()

orders = [
    Order(order_id=r[0], store_id=r[1], product_id=r[2], quantity=r[3],
          unit_price=r[4], order_ts=datetime.fromisoformat(r[5]))
    for r in RAW_ORDERS
]
fact_orders = transform_fact_orders(orders, DIM_STORE, DIM_PRODUCT)
con.execute("""
    CREATE TABLE fact_orders (
        order_id VARCHAR, store_id VARCHAR, product_id VARCHAR,
        quantity INTEGER, unit_price DOUBLE, revenue DOUBLE, order_ts TIMESTAMP
    )
""")
con.executemany(
    "INSERT INTO fact_orders VALUES (?, ?, ?, ?, ?, ?, ?)",
    [(r["order_id"], r["store_id"], r["product_id"], r["quantity"],
      r["unit_price"], r["revenue"], r["order_ts"]) for r in fact_orders],
)

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],
)

total_orders = con.sql("SELECT COUNT(*) FROM fact_orders").fetchone()[0]
total_events = con.sql("SELECT COUNT(*) FROM events").fetchone()[0]

print("Part 1 -- fact_orders and events, inherited unchanged")
print(f"  fact_orders   {total_orders:3} rows")
print(f"  events        {total_events:3} rows (canonical from dbt-analytics-engineering-guide)")
assert total_orders == 40 and total_events == 32

This first part builds nothing new — it rebuilds, exactly as in every lesson of this module, the two fixed input tables that everything that follows rests on.

Part 2 — fact_sessions, with the session-store mapping declared

STORE_ROTATION = ["S01", "S02", "S03"]


def store_for_session(session_id: str) -> str:
    """Fixed, deterministic, additive mapping: rotates S01/S02/S03 based on
    the session's sequence number. events (canonical source from
    dbt-analytics-engineering-guide) never declares store_id -- this
    guide adds it."""
    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
""")

total_sessions = con.sql("SELECT COUNT(*) FROM fact_sessions").fetchone()[0]
print("\nPart 2 -- fact_sessions, with the session-store mapping declared")
print(f"  fact_sessions {total_sessions:3} rows (17 distinct sessions from events)")
print(f"  mapping: SESS-01->S01, SESS-02->S02, SESS-03->S03, SESS-04->S01, ... (S01/S02/S03 rotation)")
assert total_sessions == 17

Part 3 — The conversion funnel, by stage and by store

funnel = con.sql("""
    SELECT COUNT(*) AS total, COUNT(view_ts) AS viewed,
           COUNT(add_to_cart_ts) AS added_to_cart, COUNT(purchase_ts) AS purchased
    FROM fact_sessions
""").fetchone()

conversion_pct = con.sql("SELECT ROUND(100.0 * COUNT(purchase_ts) / COUNT(*), 1) FROM fact_sessions").fetchone()[0]

print("\nPart 3 -- Kiosko's conversion funnel")
print(f"  total sessions: {funnel[0]}, viewed: {funnel[1]}, added to cart: {funnel[2]}, purchased: {funnel[3]}")
print(f"  total conversion: {conversion_pct}%")
assert funnel == (17, 17, 9, 6) and conversion_pct == 35.3

by_store = 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
""").fetchall()
print("  by store:")
for store_id, sessions, purchases, pct in by_store:
    print(f"    {store_id}: {sessions} sessions, {purchases} purchases, {pct}% conversion")

What to expect (Parts 1 through 3).

=== Kiosko: funnel and cumulative activity, module 6's final delivery ===

Part 1 -- fact_orders and events, inherited unchanged
  fact_orders    40 rows
  events         32 rows (canonical from dbt-analytics-engineering-guide)

Part 2 -- fact_sessions, with the session-store mapping declared
  fact_sessions  17 rows (17 distinct sessions from events)
  mapping: SESS-01->S01, SESS-02->S02, SESS-03->S03, SESS-04->S01, ... (S01/S02/S03 rotation)

Part 3 -- Kiosko's conversion funnel
  total sessions: 17, viewed: 17, added to cart: 9, purchased: 6
  total conversion: 35.3%
  by store:
    S01: 6 sessions, 3 purchases, 50.0% conversion
    S02: 6 sessions, 2 purchases, 33.3% conversion
    S03: 5 sessions, 1 purchases, 20.0% conversion

Part 4 — fact_store_activity, with the 7- and 30-day windows

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

total_activity_rows = con.sql("SELECT COUNT(*) FROM fact_store_activity").fetchone()[0]
print(f"\nPart 4 -- fact_store_activity, 3 stores x 7 days")
print(f"  fact_store_activity {total_activity_rows:3} rows")
assert total_activity_rows == 21

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("  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}, 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"
print("  Verification OK: list_sum(revenue_array_7d) == fact_orders weekly revenue, for all 3 stores")

What to expect (Part 4).

Part 4 -- fact_store_activity, 3 stores x 7 days
  fact_store_activity  21 rows
  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: list_sum(revenue_array_7d) == fact_orders weekly revenue, for all 3 stores

Part 5 — Documenting it as a formal structure

ACCUMULATING_AND_CUMULATIVE_SUMMARY = {
    "fact_orders_rows": total_orders,
    "events_rows": total_events,
    "session_to_store_mapping": "fixed S01/S02/S03 rotation by session sequence number",
    "fact_sessions_rows": total_sessions,
    "funnel_total_viewed_cart_purchased": list(funnel),
    "funnel_conversion_pct": conversion_pct,
    "funnel_conversion_by_store": {store_id: pct for store_id, _, _, pct in by_store},
    "fact_store_activity_rows": total_activity_rows,
    "snapshot_2026_08_09": {
        store_id: {"revenue_7d": revenue_7d, "active_days_7d": active_7d, "active_days_30d": active_30d}
        for store_id, revenue_7d, active_7d, active_30d in snapshot
    },
    "revenue_7d_matches_weekly_revenue": True,
}
print("\nPart 5 -- the formal declaration: ACCUMULATING_AND_CUMULATIVE_SUMMARY")
for key, value in ACCUMULATING_AND_CUMULATIVE_SUMMARY.items():
    print(f"  {key}: {value}")

What to expect. Running python3 kiosko_accumulating_cumulative_project.py in full (all five parts together), the output ends exactly like this:

Part 5 -- the formal declaration: ACCUMULATING_AND_CUMULATIVE_SUMMARY
  fact_orders_rows: 40
  events_rows: 32
  session_to_store_mapping: fixed S01/S02/S03 rotation by session sequence number
  fact_sessions_rows: 17
  funnel_total_viewed_cart_purchased: [17, 17, 9, 6]
  funnel_conversion_pct: 35.3
  funnel_conversion_by_store: {'S01': 50.0, 'S02': 33.3, 'S03': 20.0}
  fact_store_activity_rows: 21
  snapshot_2026_08_09: {'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}}
  revenue_7d_matches_weekly_revenue: True

Stop on Parts 3 and 4 together, because they're the ones that summarize the whole module in a single picture: fact_sessions (17 rows, a true accumulating snapshot — more rows than you'd have if you counted events, fewer than you'd have if events didn't have repeated sessions) and fact_store_activity (21 rows, a true cumulative table design — every array built from the previous one, never recalculated from scratch, and still identical to the weekly revenue already known since module 1). ACCUMULATING_AND_CUMULATIVE_SUMMARY gathers, in a single structure, every number the seven previous lessons measured separately.

Diagram: the module's two tables, closed with evidence

flowchart TD
    A["events (32 rows, canonical)\n+ session_store_map declared"] --> B["fact_sessions\n17 rows, VERIFIED"]
    B --> C["Funnel: 17 -> 9 -> 6\n35.3% conversion, VERIFIED"]

    D["fact_orders (40 rows)\ngrouped by store and day"] --> E["fact_store_activity\n21 rows, VERIFIED"]
    E --> F["Snapshot 2026-08-09:\nrevenue_7d == weekly revenue\nVERIFIED"]

    C --> G["ACCUMULATING_AND_CUMULATIVE_SUMMARY\nthe formal contract this project delivers"]
    F --> G
    G --> H["Module 7: junk dimension,\nmore than one fact coexisting"]

Closing module 1's checklist, piece by piece

Seven of eight rows are now resolved. Module 7, next on the list, needs fact_orders, dim_product_scd, fact_sessions, and fact_store_activity exactly as they stand — unchanged — to explicitly name something that's already true since this module: Kiosko no longer has a single fact (fact_orders), it has three, each describing a different business process, with a different grain and a different type. Module 7 formalizes that coexistence with a junk dimension and contracts between bronze, silver, and gold.

Common mistakes

Delivering ACCUMULATING_AND_CUMULATIVE_SUMMARY without Parts 1 through 4's asserts. What happens: someone, in a hurry to show the summary structure as the final result, builds it directly after running the queries, without having gone through the asserts that confirm each number. Why it happens: the summary structure looks more presentable as "the deliverable," and the asserts feel like discardable preliminary steps. How to spot it: if your final delivery doesn't include any executed evidence that fact_sessions has 17 rows, that the funnel gives [17, 17, 9, 6], and that revenue_7d matches the known weekly revenue, you're documenting a process without having confirmed it worked. How to fix it: this project's asserts aren't optional — they're the guarantee that makes everything ACCUMULATING_AND_CUMULATIVE_SUMMARY documents trustworthy.

Assuming fact_sessions and fact_store_activity need to be joined together. What happens: someone, seeing two new facts in the same project, tries to write a JOIN between fact_sessions and fact_store_activity — for example, joining by store_id — assuming the project expects a combined analysis. Why it happens: previous modules' projects (like module 5's) did end with a central JOIN between two tables. How to spot it: if you search this project for a step that joins both fact tables, you won't find one — each one answers a different business question, with a different grain (session_id versus store_id + activity_date), and there's no question in this module that requires crossing them. How to fix it: fact_sessions and fact_store_activity are two independent deliverables of this project, not two halves of a single analysis — module 7, which does make several facts coexist, is where you're going to see that kind of integration, not here.

Thinking this project exhausted every type of fact table that exists. What happens: someone finishes this module thinking they now know "every type" of fact table — transactional (fact_orders), accumulating snapshot (fact_sessions), cumulative (fact_store_activity) — without considering the type Kimball calls periodic snapshot (one row per fixed period, like an account balance at the close of each month), which this guide named in lesson 2 but never built. Why it happens: two complete, well-verified patterns can feel like "the whole catalog" when they actually cover two of at least three types Kimball documents. How to spot it: if you can't explain, from memory, how a periodic snapshot table would differ from the two you did build in this module, you're missing that piece of Kimball's vocabulary. How to fix it: a periodic snapshot — for example, "each store's inventory at the close of each day" — also inserts a new row per period, but unlike the accumulating snapshot, each row describes a fixed instant, not an advancing process; and unlike the cumulative design, it doesn't accumulate a history array within the same row. This guide doesn't build one because Kiosko, in its current dataset, doesn't have a process that naturally fits that pattern — but it's worth knowing it exists.

Exercises

Exercise 1 — Verify that active_days_7d summed across the three stores matches the total store-days with sales. Using fact_store_activity, write a query that sums active_days_7d across the three stores in the 2026-08-09 snapshot, and confirm the result matches counting, directly in fact_orders, how many distinct combinations of store_id + date had at least one order across the whole week.

sum_active_days = con.sql("""
    SELECT SUM(active_days_7d) FROM fact_store_activity WHERE activity_date = DATE '2026-08-09'
""").fetchone()[0]
store_days_with_orders = con.sql("""
    SELECT COUNT(DISTINCT store_id || '-' || CAST(order_ts AS DATE)) FROM fact_orders
""").fetchone()[0]
print(f"sum of active_days_7d (3 stores): {sum_active_days}")
print(f"store_id + date combinations with at least one order: {store_days_with_orders}")
assert sum_active_days == store_days_with_orders

sum of active_days_7d (3 stores): 20
store_id + date combinations with at least one order: 20

Exercise 2 — Extend ACCUMULATING_AND_CUMULATIVE_SUMMARY with the full funnel breakdown by store. The current summary only stores conversion_pct per store. Add a funnel_detail_by_store field with sessions, carts, and purchases per store, not just the final percentage.

detail_by_store = con.sql("""
    SELECT store_id, COUNT(*) AS sessions, COUNT(add_to_cart_ts) AS carts, COUNT(purchase_ts) AS purchases
    FROM fact_sessions GROUP BY store_id ORDER BY store_id
""").fetchall()

ACCUMULATING_AND_CUMULATIVE_SUMMARY["funnel_detail_by_store"] = {
    store_id: {"sessions": sessions, "carts": carts, "purchases": purchases}
    for store_id, sessions, carts, purchases in detail_by_store
}
print(ACCUMULATING_AND_CUMULATIVE_SUMMARY["funnel_detail_by_store"])

{'S01': {'sessions': 6, 'carts': 4, 'purchases': 3}, 'S02': {'sessions': 6, 'carts': 4, 'purchases': 2}, 'S03': {'sessions': 5, 'carts': 1, 'purchases': 1}}

Exercise 3 — Explain, from memory, what module 7 needs from this project to be able to start. Without looking at the guide's design, describe in a 4-6 sentence paragraph which pieces of fact_orders, fact_sessions, fact_store_activity, or ACCUMULATING_AND_CUMULATIVE_SUMMARY module 7 is going to need to name Kiosko's "messy domain" — more than one fact, more than one type of dimension — and formalize its Medallion contracts.

Summary and next step: the end of module 6

With this mini-project you close module 6 in full. You built fact_sessions — a true accumulating snapshot fact table, 17 rows, with the session-store mapping explicitly declared and verified two different ways (aggregate and event by event, zero differences) — and fact_store_activity — a true cumulative table design, 21 rows, with arrays built day by day without rereading the full fact_orders, verified against the weekly revenue already known since module 1. Kiosko's funnel converts 35.3% of its sessions, with the biggest drop before the cart; all three stores had near-daily activity, with S03 the only one with a no-sales day within the week.

You took the sixth step of an eight-module path: Kiosko no longer has a single fact — it has three, each describing a different business process, with a different grain and a different update mechanism, coexisting over the same conformed dimensions modules 1 through 5 already built.

Where you're headed next. Module 7 — messy-domains-and-medallion-at-depth — explicitly names what this module already built in practice: a domain with more than one fact and more than one type of dimension. It introduces the junk dimension (dim_order_flags), the degenerate dimension already declared since module 1 (order_id inside fact_orders), and formalizes the contracts between bronze, silver, and gold with a schema validation function run over this guide's four gold tables.

Resources

• Kimball Group — "Accumulating Snapshot Fact Table" — the formal definition behind fact_sessions, verified in every lesson of this module. kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/accumulating-snapshot-fact-table. In English.
• DataExpert-io — cumulative-table-design repository (Zach Wilson) — the pattern behind fact_store_activity, verified with two independent implementations in lesson 6. github.com/DataExpert-io/cumulative-table-design. In English.
• DuckDB — documentation on list functions (list_sum, list_filter, list_prepend) and window functions, the technical basis of this entire module's second block. duckdb.org/docs/current/sql/functions/list and duckdb.org/docs/current/sql/functions/window_functions. In English.
• DuckDB — official Python client documentation, the interface that ran every verification in this project. duckdb.org/docs/current/clients/python/overview. In English.