Cancellation Dashboard (BigQuery --> Looker Studio)
Budget: $250 – $750 USD
Cancellation Mini-Dashboard (Milestone 1)
Objective
Build a one-page Looker Studio report (fed by a shared BigQuery project) that shows why users enter our cancellation flow and what they do:
Kept (Accepted Offer)
Paused (Accepted Offer)
Canceled
No Decision (Exited without terminal page)
No product filter in this milestone.
Data Access
We’ll share a BigQuery project containing a subset of our GA4-style data (nested event_params with page_location URL).
URLs to Parse (product is variable)
Use {productId}—do not hard-code.
Entry & reasons
Start: /account/subscription?cancel=&product={productId}
Price: /account/subscription?cancel=too-expensive&product={productId}&reasonId=7
Pause (intent): /account/subscription?cancel=pause&product={productId}&reasonId=8
UI/UX: /account/subscription?cancel=dont-know-how-to-use&product={productId}&reasonId=9
Data confusion: /account/subscription?cancel=cant-make-sense-of-data&product={productId}&reasonId=10
Other: /account/subscription?cancel=other&product={productId}&reasonId=6
Terminal signals
Kept (accepted offer): /account/subscription?cancel=upgraded&product={productId}
Paused (success): /account/subscription?cancel=pause-success&product={productId}
Canceled (final): /account/subscription?cancel=goodbye&product={productId}
Parsing & Attribution Rules
Extract from page_location:
product → productId (string)
cancel (string; may be empty on start)
reasonId (int; present on reason pages)
Reason map
7=Price · 8=Pause · 9=UI/UX · 10=Data confusion · 6=Other · missing/unknown=Unspecified
Sessionization & attribution
Use GA 30-minute session boundary.
Outcome = latest terminal signal in the session (after the last reason, if any).
If multiple reason pages, attribute the last reason before the terminal.
If Start/Reason seen but no terminal in session → No Decision.
Output one row per user per session (dedupe within session).
Output Data Model (derived rows)
Each row = one user-session flow:
date (DATE), user_id, session_id, productId
reasonId, reason_label
outcome ∈ {kept,paused,canceled,no_decision}
accepted_offer (BOOL; true for kept/paused)
path_depth (INT), optional first_seen_ts/last_seen_ts
(View or query is fine—no need to materialize—so long as the report is responsive.)
Looker Studio Report — Single Page
Controls
Date range picker (default: last 90 days)
KPI tiles
Total flows (distinct user-sessions)
Canceled
Accepted Offer (Kept + Paused)
No Decision
Acceptance rate = (Kept + Paused) / Total flows
Cancel rate = Canceled / Total flows
Visuals
Reasons table
Dim: reason_label
Metrics: distinct user-sessions, % of total, acceptance rate, cancel rate
Behavior: row click filters page to that reason
Daily trend (last 90 days)
Series: Kept, Paused, Canceled, No Decision (stacked area or multi-series line)
Simple funnel viz
Stages: Start → Reason → Outcome
Show counts and % drop-off between stages for the selected date range (respects filters)
Validation & Quality (minimal, visible)
Small bottom table showing counts by Outcome and counts by Reason for quick sanity checks.
Performance
Apply date constraints; only unnest fields needed for URLs and keys.
Deliverables & Ownership
Looker Studio report created under our Google account/org (we own it).
Data source connected to our BigQuery project with owner credentials; refresh enabled.
Provide the exact BigQuery query/view used (inline comment or small note inside the report).
Brief note inside the report describing:
ReasonId → label mapping
Outcome rules implemented
Acceptance Criteria
All four outcomes correctly classified.
Reason mapping matches the provided IDs.
Works for any {productId} (no hard-coding).
Date control, table, trend, and funnel respect filters.
Validation section totals align with straightforward counts.
Ideal Experience
BigQuery SQL with GA4-style nested schemas (UNNEST(event_params)), URL/query-string parsing, and Looker Studio modeling.
Why this matters: this nested-records + query-string pattern is where most mistakes and cost bloat happen; prior experience keeps the build correct and efficient.
Looker Studio performance tuning and clear, minimal data storytelling.
Objective
Build a one-page Looker Studio report (fed by a shared BigQuery project) that shows why users enter our cancellation flow and what they do:
Kept (Accepted Offer)
Paused (Accepted Offer)
Canceled
No Decision (Exited without terminal page)
No product filter in this milestone.
Data Access
We’ll share a BigQuery project containing a subset of our GA4-style data (nested event_params with page_location URL).
URLs to Parse (product is variable)
Use {productId}—do not hard-code.
Entry & reasons
Start: /account/subscription?cancel=&product={productId}
Price: /account/subscription?cancel=too-expensive&product={productId}&reasonId=7
Pause (intent): /account/subscription?cancel=pause&product={productId}&reasonId=8
UI/UX: /account/subscription?cancel=dont-know-how-to-use&product={productId}&reasonId=9
Data confusion: /account/subscription?cancel=cant-make-sense-of-data&product={productId}&reasonId=10
Other: /account/subscription?cancel=other&product={productId}&reasonId=6
Terminal signals
Kept (accepted offer): /account/subscription?cancel=upgraded&product={productId}
Paused (success): /account/subscription?cancel=pause-success&product={productId}
Canceled (final): /account/subscription?cancel=goodbye&product={productId}
Parsing & Attribution Rules
Extract from page_location:
product → productId (string)
cancel (string; may be empty on start)
reasonId (int; present on reason pages)
Reason map
7=Price · 8=Pause · 9=UI/UX · 10=Data confusion · 6=Other · missing/unknown=Unspecified
Sessionization & attribution
Use GA 30-minute session boundary.
Outcome = latest terminal signal in the session (after the last reason, if any).
If multiple reason pages, attribute the last reason before the terminal.
If Start/Reason seen but no terminal in session → No Decision.
Output one row per user per session (dedupe within session).
Output Data Model (derived rows)
Each row = one user-session flow:
date (DATE), user_id, session_id, productId
reasonId, reason_label
outcome ∈ {kept,paused,canceled,no_decision}
accepted_offer (BOOL; true for kept/paused)
path_depth (INT), optional first_seen_ts/last_seen_ts
(View or query is fine—no need to materialize—so long as the report is responsive.)
Looker Studio Report — Single Page
Controls
Date range picker (default: last 90 days)
KPI tiles
Total flows (distinct user-sessions)
Canceled
Accepted Offer (Kept + Paused)
No Decision
Acceptance rate = (Kept + Paused) / Total flows
Cancel rate = Canceled / Total flows
Visuals
Reasons table
Dim: reason_label
Metrics: distinct user-sessions, % of total, acceptance rate, cancel rate
Behavior: row click filters page to that reason
Daily trend (last 90 days)
Series: Kept, Paused, Canceled, No Decision (stacked area or multi-series line)
Simple funnel viz
Stages: Start → Reason → Outcome
Show counts and % drop-off between stages for the selected date range (respects filters)
Validation & Quality (minimal, visible)
Small bottom table showing counts by Outcome and counts by Reason for quick sanity checks.
Performance
Apply date constraints; only unnest fields needed for URLs and keys.
Deliverables & Ownership
Looker Studio report created under our Google account/org (we own it).
Data source connected to our BigQuery project with owner credentials; refresh enabled.
Provide the exact BigQuery query/view used (inline comment or small note inside the report).
Brief note inside the report describing:
ReasonId → label mapping
Outcome rules implemented
Acceptance Criteria
All four outcomes correctly classified.
Reason mapping matches the provided IDs.
Works for any {productId} (no hard-coding).
Date control, table, trend, and funnel respect filters.
Validation section totals align with straightforward counts.
Ideal Experience
BigQuery SQL with GA4-style nested schemas (UNNEST(event_params)), URL/query-string parsing, and Looker Studio modeling.
Why this matters: this nested-records + query-string pattern is where most mistakes and cost bloat happen; prior experience keeps the build correct and efficient.
Looker Studio performance tuning and clear, minimal data storytelling.