Case Study: End-to-End Data Analytics & Engineering for Taoist Wellness (Saas)
Client: Taoist Wellness Academy
Industry: Online Wellness Education (Subscription)
Scope: Data Engineering, Marketing, Growth & Youtube Intelligence
Executive Summary
Taoist Wellness Academy is an online subscription platform offering authentic Tai Chi and Qi Gong wellness courses. The business runs on a trial-to-paid model across multiple payment processors and advertising channels.
The challenge: marketing data was siloed across 6+ platforms with no unified view of the customer journey from ad click to recurring subscriber. Leadership had no way to answer fundamental questions like which campaigns actually drive paying students, what the real cost per acquisition is, or how revenue retention is trending.
Together with the client's input, DBedge built a complete data infrastructure from scratch — API pipelines (scope outsourced), a BigQuery data warehouse, layered SQL marts, and three production dashboards — giving the business real-time visibility into marketing performance, revenue health, and content analytics for the first time.
Data lived in silos: Stripe, SamCart, PayPal, Google Ads, Meta Ads, and GA4 each had their own dashboards with no cross-platform connection
No attribution: Impossible to know which campaign drove a trial, and whether that trial converted to a paying student
Revenue blindspot: No consolidated view of MRR, churn, net dollar retention, or customer lifetime value across payment processors
Manual reporting: Weekly performance reviews required hours of manual data pulling from multiple platforms
Multi-currency complexity: Revenue in both GBP and USD with no normalized reporting
The Problem
The Solution
The solution is a three-layer architecture: automated data ingestion, a structured SQL data warehouse, and two purpose-built dashboards.
Layer 1: Data Ingestion Layer (Python API Pipelines)
DBedge outsourced this layer to a capable and experienced API/Software developer , with the role of building custom Python scripts to pull data daily from 6 source systems into BigQuery via automated ETL pipelines.
-
Data Extracted: Invoices, subscriptions, customers, plans
Purpose: Subscription revenue (USD/GBP)
-
Data Extracted: Orders, charges, customers, subscriptions, refunds
Purpose: E-commerce orders + trial tracking
-
Data Extracted: Transactions, subscriptions, plans, products
Purpose: Subscription revenue (USD/USD)
-
Data Extracted: Campaign, ad group, ad, keyword stats
Purpose: Google ads performance
-
Data Extracted: Campaign, ad set, ad insights + dimensions
Purpose: Meta Ads performance
-
Data Extracted: Channel analytics, video performance.
Purpose: Content performance tracking
Total tables ingested: 290+ across all sources. Each source uses a staging-to-production pattern to ensure data integrity.
Layer 2: SQL Data Warehouse (BigQuery)
Raw data is transformed through a consistent, layered SQL architecture following analytics engineering best practices. Each analytical domain follows the same disciplined pattern, raw ingestion through to business-ready marts — ensuring reliability, maintainability, and a single source of truth across the warehouse.
-
Raw source tables are validated, typed, and normalized into a common schema. Platform-specific quirks (naming conventions, currency formats, ID structures) are resolved at this layer before any business logic is applied.
-
Normalized data from multiple sources is merged into unified views. Cross-platform joins (e.g. ad platforms with transaction processors, content platforms with subscriber data) are built here, with careful key mapping and deduplication logic to preserve data integrity
-
Metrics, KPIs, and attribution models are calculated in this layer. This is where spend is linked to revenue, content is linked to acquisition, and cohort behaviour is measured against business outcomes. Each mart encodes the specific rules agreed with the client — attribution windows, currency normalization, churn definitions, and engagement thresholds.
-
Final production views optimized for dashboard consumption. Purpose-built per analytical domain, these marts are the direct data source for all Looker Studio reports.
Across all marts, key engineering decisions follow a consistent philosophy: intelligent ID-to-name matching to handle platform data inconsistencies, revenue attribution that accounts for both initial transactions and recurring charges, and staging-to-production patterns that protect downstream dashboards from raw data anomalies.
Layer 3: Dashboard (Looker Studio Pro)
Dashboard 1: Marketing Intelligence
Purpose: Empower the marketing team to make budget and creative decisions based on real funnel data.
Campaign performance: Spend, impressions, clicks, CTR, CPC, trials, students, revenue, ROAS, cost per trial by campaign and date.
Funnel visualization: Trial volume, monthly vs annual split, conversion trends, student activation tracking.
Traffic quality: Sessions, checkout views, bounce rate, session duration with week-over-week comparison.
Email performance: Campaign-level orders, trials, students, and revenue attribution.
Creative analysis: Ad-level CTR, CVR, and cost per purchase to identify winning creative styles.
Dashboard 2: Growth Metrics & Stakeholder Intelligence
Purpose: Give leadership and stakeholders a single, authoritative view of business health, from revenue movement to subscriber growth to conversion performance.
Revenue overview: Total revenue by source (Stripe, PayPal), currency (GBP/USD normalized), and plan type (monthly/annual).
MRR tracking: Monthly recurring revenue with movement breakdown (new, expansion, contraction, churn, reactivation).
Net Dollar Retention scorecard: The single most important SaaS health metric, are existing students generating more or less revenue over time? Tracked with directional status indicators.
Churn analysis: Gross and net revenue churn rates with health status indicators.
Student lifecycle: Active student counts, trial-to-paid conversion tracking, recurring subscriber base.
Growth KPIs: Subscriber growth rate, plan mix evolution (monthly vs annual), and reactivation tracking, giving leadership a forward-looking signal beyond just revenue.
Stakeholder scorecard: Executive-level summary view with key health indicators consolidated, designed for quick access into business performance at a glance.
Dashboard 3: YouTube Intelligence
Purpose: Give the content and marketing teams visibility into how organic content performance connects to channel growth and audience development, closing the loop between content investment and business outcomes.
Channel overview: Subscribers gained/lost, net subscriber growth, and trend over time, understanding which periods and content types drive audience expansion.
Video performance: Views, watch time, average view duration, and engagement rate per video, identifying top-performing content and patterns worth replicating.
Audience engagement: Like rate, comment volume, and click-through rate from impressions to views, measuring how well content resonates beyond just view counts.
Traffic sources: Breakdown of where views originate (search, suggested, external, direct), informing SEO and distribution strategy.
Top content leaderboard: Ranked view of best-performing videos by key metrics, enabling the team to double down on winning formats and topics.
Results & Business Impact
Before
6+ hours manual data pulling per week.
No campaign-to-revenue attribution.
Revenue reported separately per processor.
No visibility into churn or retention.
Budget decisions based on platform vanity metrics.
Week-over-week analysis took 3+ hours.
After
Automated daily refresh, reports ready by morning.
Full funnel: ad click → trial → student → recurring revenue per campaign.
Unified, currency-normalized revenue across Stripe + PayPal.
NDR scorecard, churn rate tracking, MRR movement analysis with growth KPI layer.
Stakeholder scorecard giving leadership instant business health assessment.
Spend decisions driven by true ROAS and cost per paying student.
YouTube content performance tracked end-to-end — views, engagement, subscriber growth, and traffic source breakdown.
Structured WoW reporting with immediate action items in under 30 minutes.
Technology Stack
-
Technology: Python (custom API scripts)
Role: Outsourced to API/Software Developer, scope for Daily ETL from 6 source systems
-
Technology: Big Querry
Role: Central storage + SQL transformations
-
Technology: Github Copilot VS Code
Role: Layered mart development
-
Technology: Looker Studio Pro
Role: 3 production dashboards + scorecards
-
Technology: Google Tag Manager + GA4
Role: Event tracking across touchpoints
-
Technology: n8n
Role: Workflow automation and scheduling
Scope of Work Summary
Deliverables
API Data Pipelines: 6 source systems (Stripe, SamCart, PayPal, Google Ads, Meta Ads, YouTube) → BigQuery with staging + production tables.
BigQuery Schema: 290+ tables across 8 datasets with automated schema export and documentation.
SQL Mart Development: Layered SQL marts across Marketing Analytics, Growth Metrics, and YouTube Analytics domains — 12+ production views following a consistent normalize -> merge -> logic -> serve pattern.
Marketing Dashboard: Full-funnel campaign performance with trial, student, revenue, and creative-level analysis.
Growth & Stakeholder Dashboard: MRR tracking, NDR scorecard, churn analysis, growth KPIs, and executive stakeholder scorecard across multi-currency normalized revenue.
YouTube Analytics Dashboard: Channel growth, video performance, engagement metrics, traffic source breakdown, and content cadence analysis.
Weekly Analytics: Structured week-over-week reporting framework with actionable insights.
Conversion Tracking: GTM + GA4 event setup for Google Ads and Meta Ads optimization.
Ready to Empower Your Business
with Analytics Intelligence?
Your marketing, revenue, and content data, unified, automated, and turned into decisions. DBedge builds the complete analytics stack so you always know what's working, what's not, and what to do next.