September 2025
Multi-channel performance analysis across 7 channels, 10,000 sessions, and 4 attribution models. Goal: ad spend optimization.
Overview
A 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 anyways.
I built a full attribution analysis pipeline: simulated a GA4-style dataset, designed a normalized SQL schema, and ran attribution models (Last-Touch, First-Touch, Linear, and Markov Chain). Analysis extended 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.
View Attribution Dashboard → View Deep Analysis Dashboard
Dataset & Tools
Dimension | Detail |
Sessions | 10,000 |
Conversions | 467 |
Ad Spend | $16,133 |
Time Period | May-July 2025 (3 Months) |
Dimension | Detail |
Data | ChatGPT-5 Prompt Engineering |
Database | PostgreSQL · Postico · Custom SQL Helper Views |
Analysis | Python · Pandas · SQL · Jupyter |
Attribution | Custom Markov Chain (Monte Carlo, 20K simulations) |
Visualization | Power BI |
Reporting | Slide Deck · PDF Report |
Key Metrics
Metric | Value |
Total Revenue | $68,200 |
Total Ad Spend | $16,133 |
Blended ROAS | 4.2x |
Conversions | 467 |
Overall CVR | 4.67% |
Avg Order Value | $164 |
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 Chain 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.
Channel | Last-Touch | First-Touch | Linear | Markov Chain | Verdict |
Paid Search | $19,750 | $16,225 | $17,834 | $31,835 | ✅ Scale |
Display Retarg. | $6,375 | $3,775 | $4,715 | $16,573 | ✅ Scale |
Direct | $8,775 | $5,125 | $7,069 | $12,996 | 🔵 Maintain |
Organic Search | $14,250 | $16,825 | $14,146 | $6,796 | 🔵 Maintain |
Email | $8,550 | $9,450 | $9,419 | — | 🟡 Fix CRO |
Paid Social | $7,475 | $13,575 | $11,830 | — | 🔴 Cut |
Referral | $3,025 | $3,225 | $3,186 | — | 🔵 Maintain |
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.
Paid Channel ROAS by Model
Channel | Spend | Last-Touch ROAS | Linear ROAS | Markov ROAS |
Paid Search | $6,583 | 3.0x | 2.7x | 4.8x |
Display Retargeting | $1,582 | 4.0x | 3.0x | 10.5x |
Paid Social | $8,036 | 0.93x | 1.5x | 0.0x |
Funnel Analysis
Conversion Funnel — Drop-Off by Stage
Stage | Unique Users | Drop-Off |
Page View | 18,065 | — |
Product View | 8,918 | −51% |
Add to Cart | 6,790 | −24% |
Checkout Start | 4,459 | −34% |
Purchase | 2,246 | −50% |
The biggest single drop is checkout → purchase at 50%. At $164 AOV, recovering just 10% of those abandoned checkouts = ~$73K in incremental annual revenue.
Checkout Completion Rate by Channel
Channel | Checkout Completion |
Referral | 78.7% ✅ |
Display Retargeting | 76.1% ✅ |
Paid Social | 73.0% |
Direct | 72.4% |
Organic Search | 71.2% |
Paid Search | 71.1% |
Email | 67.8% ⚠️ |
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.
Keyword | Current Position | Volume/Mo | Difficulty | Incr. Visits | Incr. Revenue/Mo |
mental health therapy | #33 ⚠️ | 6,818 | LOW | +20,949 | +$176K/mo |
online doctor | #47 ⚠️ | 5,604 | LOW | +17,598 | +$148K/mo |
telemedicine services | #23 | 2,428 | LOW | +7,220 | +$61K/mo |
vitamin supplements | #17 | 1,377 | MED | +3,936 | +$33K/mo |
skin care treatment | #22 | 1,121 | LOW | +3,317 | +$28K/mo |
at-home test kits | #2 ✅ | 5,989 | MED | Already top-3 | — |
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
Age | CVR | AOV |
18–25 | 16.7% | $153 |
26–34 | 14.6% | $154 |
35–44 | 15.0% | $165 |
45–54 | 15.2% | $173 |
55–69 | 16.1% | $169 |
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.
CVR by Device & Country
Segment | CVR | AOV |
Tablet | 16.3% | $153 |
Mobile | 15.6% | $170 |
Desktop | 15.4% | $160 |
UK | 16.3% | — |
Canada | 16.3% | — |
US | 15.6% | — |
Australia | 14.2% | — |
Australia's 14.2% CVR is notably below the rest. Worth investigating for UX or localisation issues. UK and Canada overindex on CVR relative to their revenue share — they're underpenetrated markets worth targeting.
Recommendations
# | Action | Why | Est. Impact |
01 | Reallocate 50% of Paid Social budget to Display Retargeting | 0x Markov ROAS vs 10.5x. Move $4K/mo from worst to best paid channel | +$42K/quarter |
02 | Build SEO content for 'mental health therapy' + 'online doctor' | Positions #33 and #47, both LOW difficulty. Pillar pages + backlinks | +$324K/year |
03 | Build dedicated landing pages for email campaigns | 67.8% checkout completion vs 76–78% elsewhere. Offer-matched copy | +8–11% email CVR |
04 | Audit what changed between March and April cohorts | April converts 33 days faster with $12 higher AOV. Find the lever, keep it | +$12 AOV |
05 | Test 45–54 creative in UK and Canada | Highest AOV segment ($173), best-converting markets. Localised messaging | +2–3% CVR |
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 localisation gap, or just lower intent traffic. Hard to diagnose from aggregate metrics alone.
Methodology
<details> <summary><strong>1. Problem Definition & Requirements</strong></summary>
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.
</details> <details> <summary><strong>2. Data Simulation & Schema Design</strong></summary>
Prompt-engineered a synthetic DTC healthcare dataset using ChatGPT-5 to produce realistic GA4-style distributions across user demographics, session patterns, and conversion behaviours. Designed and validated a normalised Postgres schema (in Postico) covering: users, sessions, events, conversions, ad_clicks, semrush_keywords. Built three SQL helper views — session_channel_map, conversion_path, and campaign_costs — to power all downstream analysis without repeated joins.
</details> <details> <summary><strong>3. Exploratory Data Analysis</strong></summary>
Profiled all six tables in Jupyter Notebook. Key findings from EDA: user 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.
</details> <details> <summary><strong>4. Attribution Modelling — Baseline</strong></summary>
Implemented Last-Click, First-Click, and Linear attribution from scratch in Python. Compared credit allocation side-by-side to confirm that 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.
</details> <details> <summary><strong>5. Attribution Modelling — Markov Chain</strong></summary>
Built a removal-effect Markov Chain model using Monte Carlo simulation (20,000 paths per channel). Process: computed transition probabilities from full session histories, modelled 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 — it's 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.
</details> <details> <summary><strong>6. Deep Analysis</strong></summary>
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
</details> <details> <summary><strong>7. Outputs</strong></summary>
Delivered findings across four formats:
- Interactive HTML attribution dashboard (model toggle across all four models)
- Interactive HTML deep analysis dashboard (funnel, cohort, SEO, demographics)
- 9-page PDF report (consulting-style, with narrative, tables, and recommendations)
- 11-slide PowerPoint deck
</details>
Project completed September 2025. Dataset is synthetic and created for portfolio purposes.