The Problem
Cheap Leads Can Cost More Than Expensive Ones
01
CPL ≠ Revenue per lead
Affiliate traffic generated the lowest CPL ($17.50) and the
highest ROAS (2.69x) — but also the worst valid-lead rate (59.1%).
Google leads cost $44–$46 but converted at 80–82% valid rate.
Optimizing toward CPL alone would systematically shift budget
toward lower-quality supply.
02
Platform conversions are overstated
Meta campaigns overstated conversions by 15–40%. Google overclaimed
by 5–20%. The blended overclaim rate across paid channels was 15.2%.
Budget decisions made on platform metrics were working from
systematically inflated signals.
03
No partner visibility
All 10 insurance partners were below the 80% acceptance target.
Four were below the 70% operational minimum. Without
visibility into which partners were rejecting leads — and why —
routing priority couldn't be adjusted to protect revenue.
The team didn't need more dashboard tiles. They needed one connected
system from acquisition spend to verified partner revenue.
The decision wasn't CPL.
It was revenue per valid, accepted lead.
Lead Funnel
Where 35,000 Sessions Become $356K in Revenue
The funnel mart (mart_lead_funnel) joined
100,928 quote events and 7,795 lead records to trace every session
through quote start, completion, submission, and validation — by
channel, vertical, and month.
Quote Starts
21,161 · 60.5%
Quote Completions
9,726 · 27.8% (↓ 46% drop from starts)
Leads Submitted
7,795 · 22.3% overall CVR
Valid Leads
5,834 · 74.8% valid rate
Channel Quality Is Not Uniform
| Channel |
Leads |
Valid Rate |
Dup Rate |
Revenue |
| Organic |
1,171 |
85.0% |
6.4% |
$53,900 |
| Google |
2,674 |
81.3% |
5.6% |
$122,011 |
| Direct |
860 |
80.5% |
7.2% |
$39,721 |
| Meta |
1,938 |
66.6% |
5.7% |
$85,887 |
| Affiliate |
1,152 |
59.1% |
5.3% |
$54,193 |
Organic and Google leads convert at the highest valid rate
(85% and 81%) but Affiliate — despite the lowest CPL and best surface
ROAS — produces the weakest quality at 59.1%. Before reallocating
budget toward Affiliate on CPL alone, the team needed to see this
downstream quality gap.
SQL Design: Preventing Fan-out
The mart pre-aggregates quote events and lead records separately
into the same channel × vertical × month grain before joining —
because a direct join would create one session row per quote event,
inflating session counts by the number of events per user.
Campaign Performance
Two Campaigns Below Break-Even. Low CPL Doesn't Mean Profitable.
The campaign mart (mart_campaign_performance) joined ad spend,
warehouse leads, and verified revenue across 5 campaigns — applying
three separate pre-aggregation CTEs before any join to prevent spend
fan-out. Without pre-aggregation, joining leads to ad spend records
would inflate total spend by 3x.
Spend: $20,164
Revenue: $54,193
CPL: $17.50
Valid Rate: 59.1%
Overclaim: 15.2%
Spend: $40,756
Revenue: $50,705
CPL: $42.02
Valid Rate: 66.3%
Overclaim: 17.9%
Spend: $60,208
Revenue: $65,139
CPL: $43.76
Valid Rate: 80.5%
Overclaim: 12.7%
Spend: $59,340
Revenue: $56,872
CPL: $45.72
Valid Rate: 82.2%
Overclaim: 13.2%
Spend: $40,233
Revenue: $35,182
CPL: $41.56
Valid Rate: 67.0%
Overclaim: 18.2%
⚠ 2 of 5 campaigns (C001, C003) operating below break-even — $100.5K spend generating $92.1K revenue
The Affiliate Paradox
C005 (Affiliate) shows the highest ROAS at 2.69x — but the lowest
valid-lead rate at 59.1%. This creates a deceptive signal: the channel
looks profitable on blended metrics, but nearly 4 in 10 leads generated
cost money to acquire and route without generating any revenue. Scale
this channel without addressing quality and the economics deteriorate.
Attribution: First-Touch vs. Submitted Channel
The attribution mart (mart_attribution) applied
QUALIFY ROW_NUMBER() = 1 ORDER BY event_timestamp ASC to isolate
each lead's earliest quote event as the first-touch channel. Approximately
15% of leads showed a different first-touch channel than the submitted
channel — cross-channel journeys that standard last-touch attribution
would misattribute entirely to one channel.
Partner Performance
All 10 Partners Below the 80% Target. Four Below the 70% Minimum.
The partner mart (mart_partner_performance) tracked every
routing attempt — delivered, accepted, rejected, capacity-exceeded —
across 10 insurance partners. A key design decision: a
7-day rolling acceptance rate using
RANGE BETWEEN 6 PRECEDING AND CURRENT ROW on
UNIX_DATE(route_date) to distinguish genuine trend from
one-day noise. RANGE (not ROWS) handles
partner gaps — partners with only ~70 active days out of 180.
UnitedHealth Direct
69.5%
Liberty Mutual Auto
60.0%
What the Rolling Rate Reveals
A partner's 6-month average acceptance rate can look stable while a
7-day trend shows a sharp recent decline. The rolling rate uses
volume-weighted aggregation
(SUM(accepted) / SUM(delivered) over the 7-day window)
rather than an average of daily rates — which would overweight
low-volume days. This distinguishes a genuine trend from a single
high-rejection day.
Platform Reconciliation
Platforms Claimed 1,031 More Leads Than the Warehouse Recorded
The reconciliation mart (mart_reconciliation) used a
FULL OUTER JOIN — not a LEFT JOIN — because organic and
direct channels generate warehouse leads with no corresponding ad spend
rows. A LEFT JOIN on the spend table would silently drop all organic
and direct leads from the comparison.
Why Overclaim Exists
Platform conversions fire on ad clicks or page events — before
lead validation, duplicate detection, or partner submission.
The gap accumulates from: attribution window differences, pixel
double-firing, cross-device mismatches, test traffic, and
leads rejected as duplicates in the warehouse.
At 15.2% blended overclaim, any ROAS calculation using platform
lead counts as the denominator overstates performance by that same
margin. Budget decisions and campaign optimization need to work
from warehouse-verified numbers.
Technical Architecture
Five SQL Marts. Five LookML Views. One Governed Semantic Layer.
The system is structured in three explicit layers so business logic
lives in one place and teams can't accidentally create conflicting
metric definitions in individual dashboard tiles.
BigQuery + SQL
5 Mart Tables, 1 Shared Grain per Mart
Each mart has a documented grain. Pre-aggregation CTEs prevent
spend fan-out on joins. FULL OUTER JOIN handles organic/direct
channels in reconciliation. QUALIFY ROW_NUMBER() isolates
first-touch events per lead. SAFE_DIVIDE throughout.
LookML Semantic Layer
5 Views · 5 Explores · Reusable Metrics
Ratio measures defined as type: number with
SAFE_DIVIDE(SUM_num, SUM_denom) — never average of
a pre-computed ratio. Every view has a composite primary key
to prevent fan-out on Explore joins.
value_format_name set in LookML for format consistency.
Rolling Window Design
RANGE vs. ROWS — Calendar Days, Not Row Count
Partner rolling acceptance uses RANGE BETWEEN 6 PRECEDING
AND CURRENT ROW on UNIX_DATE(route_date).
RANGE calculates over calendar-day distance — partners with
gaps in daily activity get correct 7-day windows. ROWS would
silently skip non-consecutive dates.
Streamlit Dashboard
Plotly Charts · Alert Logic
Executive Summary → Lead Funnel → Campaign Performance →
Partner & Reconciliation. Each page includes conditional
alert callouts (partner below 70%, campaign below break-even,
overclaim above 10% audit threshold). Sidebar filters by
channel, vertical, and month range.
SQL Mart Inventory
| Mart |
Grain |
Key Design Decision |
Business Question |
| mart_lead_funnel |
channel × vertical × month |
Two CTEs pre-aggregated before join; prevents session fan-out |
Where in the funnel does volume drop — and for which channels? |
| mart_campaign_performance |
campaign × month |
3 separate pre-aggregation CTEs; revenue bridged via leads (no campaign_id in revenue table) |
Which campaigns are above break-even after verified revenue? |
| mart_partner_performance |
partner × date |
RANGE window on UNIX_DATE for calendar-day rolling rates |
Is a partner's acceptance decline a real trend or daily noise? |
| mart_attribution |
1 row per lead |
QUALIFY ROW_NUMBER() for first-touch; revenue pre-aggregated in CTE |
Which first-touch channels generate the most monetized leads? |
| mart_reconciliation |
channel × month |
FULL OUTER JOIN; organic/direct get warehouse_only status; NULL-safe overclaim calc |
How much are platforms overclaiming vs. warehouse reality? |
Technology
Google BigQuery
BigQuery Standard SQL
dbt (modeling patterns)
LookML
Looker
Streamlit
Plotly
Python
pandas
Window Functions
QUALIFY / ROW_NUMBER()
FULL OUTER JOIN
RANGE BETWEEN on UNIX_DATE
First-Touch Attribution
Platform Reconciliation
Fanout Prevention
Source Code
Code & Documentation
The repository includes BigQuery SQL for all 5 marts, LookML views
and model file, Streamlit dashboard source, Python data-generation
script, and process documentation from raw data through dashboard delivery.
View on GitHub
What This Project Demonstrates
I connect fragmented acquisition, lead quality, partner routing,
and revenue data into a single governed system — and use that
system to surface the decisions that get missed when teams
optimize toward platform-reported metrics alone.
Platform Spend → Warehouse Leads → Partner Acceptance → Verified Revenue
Low CPL without verified ROAS. Platform overclaim without reconciliation.
High ROAS without quality — none of these tell the full story.