September 2025
Multi-channel performance analysis across 7 channels, 10,000 sessions, and 4 attribution models. Goal: ad spend optimization.
Overview
DTC Telehealth startup was spending across seven marketing channels with no clear picture of which were actually driving revenue and which were just taking credit for conversions that would have happened anyway.
Built a full attribution analysis pipeline: simulated a GA4 dataset, designed a normalized SQL schema, and ran attribution models (LT, FT, Linear, and Markov Chain). Analysis continued into funnel drop-off, cohort behavior, SEO opportunity sizing, and demographic segmentation.
The result:
- 3 findings that fundamentally change how the budget should be allocated and a reallocation recommendation estimated to generate +$42K/quarter from a single change.
Reallocating $4K/month (50%) from Paid Social to Display Retargeting projects +$42K per quarter based on Display’s current 10.5x ROAS.
Three Findings That Change Budget Decisions
🔴 Finding 1: Paid Social has zero incremental ROAS
Paid Social is the highest-spend channel at $8,036 but the Markov Chain model, which measures true incremental contribution by simulating what happens when each channel is removed, assigns it 0% revenue credit.
- First-Touch attribution inflates it to 19.9% by capturing discovery sessions. But Markov reveals those users would have converted via other channels regardless. Paid Social isn’t initiating unique journeys, it’s appearing in journeys that were already going to convert.
This is the single most urgent reallocation opportunity in the dataset.
🟢 Finding 2: Display Retargeting is severely underinvested at 10.5x ROAS
Last-Touch only gives Display Retargeting 9.3% revenue credit. Markov raises this to 24.3%, with a 10.5x ROAS — the highest of any paid channel
- It also has the highest checkout completion rate of all channels at 76.1%. Despite all of this, its current budget is just $1,582 — the lowest of any paid channel and a fraction of the $8,036 going to Paid Social.
This is the clearest budget mismatch in the analysis.
🟡 Finding 3: SEO holds a $324K/year opportunity in two low-difficulty keywords
Mental health therapy is currently ranked #33 (6,818 searches/mo, low difficulty) and online doctor is currently ranked #47 (5,604 searches/mo, low difficulty).
At current organic CVR (5.2%) and AOV ($164), moving either keyword to a top-3 position represents +$148-$176K/mo in incremental organic revenue with no additional ad spend. Both are low difficulty — this is an execution problem, not a competition problem.
Attribution Model Comparison
All four models run on the same 467 conversions and $68,200 in revenue.
Why trust Markov Chain over Last-Touch?
Last-Touch gives 100% credit to the final channel before conversion, systematically overvaluing closers (Direct, Paid Search) and ignoring everything that built intent. Markov Chain calculates each channel’s removal effect: how much does the overall conversion rate drop when this channel is taken out of the model? That’s a much better proxy for true incremental value.
Funnel Analysis
- Conversion Funnel: Drop-off by Stage
- Checkout Completion Rate by Channel
- The biggest single drop is checkout → purchase at 50%. At $165 AOV, recovering just 10% of those abandoned checkouts = ~$73K in incremental annual revenue.
- Email sits 10pp below the best performing channels. This is almost certainly a landing page mismatch — users arrive from an email expecting a specific offer and hit a generic experience.
SEO Opportunity Sizing
Assumption: moving to top-3 position captures ~30% CTR. Organic CVR: 5.2%. AOV: $164.
Top two keywords alone = $324K/year in incremental organic revenue, both at LOW keyword difficulty.
Cohort Analysis
Cohort | Users | CVR | Avg Days to Convert | Avg Order Value |
March 2025 | 1,504 | 15.2% | 95.7 days | $158 |
April 2025 | 1,496 | 13.7% | 62.7 days | $170 |
April’s CVR is slightly lower, but the cohort converts 33 days faster and spends $12 more per order. Something changed between March and April — in targeting, onboarding, creative or the product itself. That change is worth identifying and making permanent.
Demographic Performance
- CVR & AOV by Age Group
- CVR by Device & Country
- The 45-54 group has the highest AOV ($173) despite a middle-of-pack CVR. They’re the most valuable customers by revenue per conversion.
- Australia’s 14.2% CVR is notably below the rest. Worth investigating the UX or localization issues. UK and Canada over index on CVR relative to their revenue share — they’re under-penetrated markets worth targeting.
Recommendations
What I’d Do With More Data
A few analytical caveats worth flagging:
- Uniform CPCs (~$3.16-$3.38 across keywords) suggest broad match pooling in the ad click data, which limits keyword-level ROAS confidence. With exact-match data, the keyword analysis would be significantly more precise.
- The Markov Chain model assigns zero credit to Email and Paid Social, which is the technically correct answer given the journey data but in practice, these channels likely have brand-building value that a 3-month window doesn’t capture. A longer dataset would give a clearer picture.
- April cohort finding is promising but not yet causal. The 33-day improvement in conversion speed could reflect a targeting change, a product change, or simply seasonal effects. I’d want a 6-month cohort view before treating it as a reliable lever.
- Australia’s low CVR (14.2%) warrants a session recording review — it could be a payment friction issue, a localization gap, or just lower intent traffic. Hard to diagnose from aggregate metrics alone.
Methodology
- Problem Definition & Requirements
Outlined the core business questions: which channels drive conversions, how to allocate spend between awareness and retargeting, and how to properly credit multi-touch customer journeys. Translated marketing needs into data requirements covering sessions, conversions, events, ad clicks, and SEO keyword metrics.
- Data Simulation & Schema Design
Prompt-engineered a synthetic DTC telehealth dataset using ChatGPT-5 to produce realistic GA4-style distributions across user demographics, session patterns, and conversion behaviors. Designed and validated a normalized Postgres schema (in Postico GUI) covering:
users,sessions,events,conversions,ad_clicks,semrush_keywords.
Built three SQL helper views:
session_channel_map,conversion_path, andcampaign_coststo power all downstream analysis without repeated joins.
- Exploratory Data Analysis
Profiled all six tables in Jupyter Notebook. Key findings from EDA: users ages 18-69 (mean 43, US-dominant), Paid Social is the highest-spend channel, the 50% checkout abandonment rate immediately flags as a priority, and email-to-conversion paths are longer than other channels. This stage shaped the focus of the deeper analysis.
- Attribution Modeling: Baseline
Implemented Last-Click, First-Click, and Linear attribution in Python. Compared credit allocation side-by-side to confirm the single-model reliance actively misleads budget decisions. Organic and Paid Social inflate under First-Click; Paid Search dominates Last-Click; Email emerges as an assist channel under Linear. No single model tells the full story.
- Attribution Modeling: Markov Chain
Built a removal-effect Markov Chain model using Monte Carlo simulation (20,000 paths per channel). Process: computed transition probabilities from full session histories, modeled both converting and non-converting paths, then calculated each channel’s incremental contribution by measuring the drop in overall conversion probability when that channel is removed from the model.
This is the most technically rigorous of the four models: the only one that actually measures incremental value rather than assigning credit by position.
Result: Paid Social drops to 0%, Display Retargeting rises to 24.3% and Paid Search’s true contribution is revealed to be significantly higher than Last-Touch suggests.
- Deep Analysis
Extended across five dimensions after attribution:
- Funnel analysis - drop-off rates by channel and device at each stage
- Cohort analysis - conversion rates, speed-to-convert, and AOV by acquisition month
- Keyword ROAS - revenue per click across 2,019 attributed ad clicks
- SEO opportunity sizing - incremental revenue projections using CTR curves by position
- Demographic analysis - CVR and AOV segmented by age, device, gender and country
- Outputs
Delivered findings across three formats:
- Interactive Power BI (attribution dashboard and analysis dashboard)
- 9-page PDF report (with narratives, tables, and recommendations)
- 11-slide PowerPoint deck
Project completed September 2025. Dataset is synthetic and created for portfolio purposes.