Case Study Details
A production-shaped BigQuery pipeline that produces one unified quarterly churn view across two separate product lines — coupons and cashback — where a person who uses both is counted once. Ships with the full data model, a nine-page Looker Studio report design, and 21 data-quality assertions
Churn arithmetic is easy. Three things about this domain make it hard, and the model is shaped around all three: i) The same person arrives twice. Coupon and cashback are separate systems with separate user ids. Across 2025, activity arrives under 111 source identities that belong to 66 people. Count the identities and the active base is overstated by 68%. ii) The two channels disagree about time. Coupon activity is dated by event_date, cashback by purchase_date — and cashback also carries a status_updated_date that looks like a date column but records paperwork, not behaviour. Dating activity by it would migrate purchases into whichever quarter the affiliate network got around to approving them. iii) The newest quarter is always incomplete. A share of each quarter’s cashback is still pending at quarter close. Exclude it and the latest quarter shows a churn spike that heals by itself six weeks later
Data quality
21 assertions in dq_check_results, graded error (blocks publication) or warn (publish with a footnote). Three families
The reconciliation checks are the valuable ones: input checks catch bad data, identity checks catch bad code. A refactor that breaks the arithmetic fails here before it reaches anyone’s model.
Three checks fail by design on this extract — one duplicate coupon event, one duplicate transaction (both warn, handled correctly by staging), and one unmapped cashback user id (error — B9999 genuinely needs an identity-map fix). Every excluded row is listed in dq_excluded_events.
Information
Client:
Start Date:
End Date:
Category:
Cost:
Upwork
04 Aug, 2026
07 Aug, 2026
BQ – Churn Analysis
$34 / hour
Need Any Help
Need Any Help, Call Me for Support
Start Your Project?
Mauris ut enim sit amet lacus ornare ullamcorper. Praesent placerat neque eu.