Case File 01 — Paid Ads × CRM Funnel

The Brief

Two tools, two different stories.

The agency was good at getting leads. The reporting was the problem. Meta counted one number of leads. The CRM counted another. Nobody knew which one was right.

Every week someone exported files and stitched them together by hand. The report was out of date before anyone read it. Budget decisions were based on guesswork.

It was a reporting problem, not a marketing problem.

THE GOAL: make Meta and the CRM show the same numbers, automatically, every morning.

✗ THE PROBLEMS

✗ Meta and the CRM showed different costs per lead — sometimes 30% apart
✗ Leads were counted twice, and sales weren’t linked back to ads
✗ Weekly reports were built by hand from exports
✗ Nobody knew which campaigns led to booked calls or sales
✗ New leads sometimes sat for hours before anyone called them

The System

How the system works.

Ad data and CRM data are pulled into BigQuery every morning. SQL combines them into one set of numbers, shown in Looker Studio, with alerts and an AI summary on top.

Meta Ads API

Spend, impressions, clicks, creative + adset detail. Pulled on schedule, never screenshotted.

GoHighLevel API

Leads, stages, calls, deals. The source of truth for what a “lead” actually is.

→

Pipelines

Scheduled cloud functions land both sources daily at 07:00 GST. Failures alert instantly.

→

BigQuery

RAW → STAGING → MARTS. SQL models join ad → lead → deal, dedupe, and define one CPL for everyone.

→

Looker Studio

Six decision-ready pages: overview, campaigns, creatives, lead quality, CRM pipeline, alerts.

→

Action Layer

Slack alert rules + a Monday 07:00 AI summary that ends in suggested actions, not adjectives.

RAW SIGNAL IN → AUTOMATED ACTION OUT — THE SAME SIX-STEP SYSTEM FROM THE HOMEPAGE, DEPLOYED FOR REAL.

The Process

The process, step by step.

Audit

Traced the pixel-vs-CRM lead gap to its causes
Mapped every GoHighLevel stage in use (and misuse)
Flagged duplicate + unattributed conversions

Measurement Plan

UTM + naming convention the buyers actually follow
Stage definitions signed off by sales AND media
KPI tree with CPL defined off CRM leads only

Implementation

Scheduled API pipelines: Meta + GHL → BigQuery
SQL models joining ad → lead → deal
QA: totals reconciled against Ads Manager

Dashboard Build

Six Looker pages, one per decision
Pixel and CRM shown side by side — the gap is the insight
Filters wired to campaign, adset, source, rep

Automate & Optimise

07:00 daily refresh, failure-alerted
Five Slack rules: CPL breach, pacing, zero-lead adsets…
Monday AI digest with suggested actions

The Build

The dashboard, page by page.

Three more pages from the dashboard, built in HTML with sample data. The demo further down lets you try it yourself.

Campaigns — Deep Dive

PAID ADS × CRM // CPL VERIFIED AGAINST GOHIGHLEVEL

LAST 30 DAYS ▾SM

DATE: LAST 30D ▾CAMPAIGN: ALL ▾ADSET: ALL ▾SOURCE: META ▾

CAMPAIGNS — PIXEL VS CRM

CAMPAIGN	SPEND	LEADS · PIXEL	LEADS · CRM	CPL · CRM
LEADFORM — BROAD META · ACTIVE 30D	$4,820	241	214	$22.5
RETARGET — 30 DAY META · ACTIVE 30D	$2,140	108	96	$22.3
LOOKALIKE 2% — US META · ACTIVE 30D	$3,610	171	142	$25.4
UGC TEST — HOOK A META · ACTIVE 30D	$1,480	66	58	$25.5
BRAND SEARCH META · ACTIVE 30D	$1,205	61	58	$20.8
BROAD — NO FORM Q META · ACTIVE 30D	$953	74	34	$28.0

CPL — CRM VERIFIED · DAILY

CREATIVES — TOP HOOKS

“Still doing X by hand?”

$19.8 CPL

“3 signs you need…”

$23.1 CPL

FOUNDER TALKING HEAD

$24.9 CPL

STATIC — OFFER CARD

$29.4 CPL

ADSET HEAT — CPL BY DAY (LOW → HIGH)

LEADFORM

RETARGET

LOOKALIKE

SOURCE: META ADS API + GOHIGHLEVEL → BIGQUERY → LOOKER STUDIOMOCK DATA · REFRESHED 07:00 GST · PAGE 2 / 6

PAGE 02 / 06 — CAMPAIGNSLIVE HTML · MOCK DATA

EXHIBIT B

Campaign Deep-Dive

Ad leads vs real leads.

■ Ad-reported leads and CRM-verified leads sit side by side. The gap between them is the point.
■ Cost per lead is tracked against a $25 target, using CRM numbers only.
■ Ads are ranked by their real cost per lead, not by clicks.

Lead Quality & CRM

PAID ADS × CRM // GOHIGHLEVEL STAGE TRUTH

LAST 30 DAYS ▾SM

MQLS402

MQL RATE66.8%

SPEED-TO-LEAD4M 12S

CONTACT RATE78%

BOOKED RATE24.6%

CPL — CRM$23.60

STAGE CONVERSION — WITH DROP

NEW LEADS

602

↓ 78%CONTACTED

470

↓ 32%BOOKED

148

↓ 71%SHOWED

105

↓ 44%WON

SOURCE QUALITY MAP — VOLUME × MQL%

REPS — SPEED & OUTCOMES

REP	LEADS	MEDIAN RESPONSE	CONTACTED	BOOKED	WON	NOTE
A. RAHMAN	214	3M 40S	81%	58	19	★ FASTEST
S. KHAN	198	4M 05S	79%	49	15
M. PATEL	118	6M 55S	74%	27	8
UNASSIGNED	72	2H+	41%	14	4	⚠ FIX ROUTING

SOURCE: GOHIGHLEVEL API → BIGQUERY → LOOKER STUDIOMOCK DATA · REFRESHED 07:00 GST · PAGE 3 / 6

PAGE 03 / 06 — LEAD QUALITYLIVE HTML · MOCK DATA

EXHIBIT C

Lead Quality & CRM

Where leads drop off.

■ Each CRM stage shows how many leads make it to the next one.
■ The map shows each lead source by volume and quality, with a clear action for each corner.
■ Response times per rep showed that unassigned leads were the biggest leak.

Alerts & AI Summary

AUTOMATION // BIGQUERY RULES → SLACK + AI → INBOX

LAST 30 DAYS ▾SM

LIVE ALERT FEED — #PERFORMANCE

⚠CPL BREACH — LOOKALIKE 2%

TODAY 09:12

CRM CPL hit $31.40 vs $25 target for 3 days. Rule suggests: shift 20% budget to LEADFORM — BROAD.

▲LEAD SPIKE — +38% WoW

TODAY 07:31

Leadform volume up. Speed-to-lead holding at 4m 12s. No action needed.

✓PIPELINE REFRESH — OK

TODAY 07:02

Meta + GoHighLevel → BigQuery completed 07:00. 14,208 rows. 0 errors.

⚠ZERO-LEAD ADSET — STATIC OFFER

YESTERDAY 18:44

$212 spent, 0 CRM leads in 72h. Auto-flagged for pause review.

RULES ACTIVE

CPL BREACHSPEND PACINGZERO-LEAD ADSETREFRESH FAILSPEED-TO-LEAD SLA

AI WEEKLY SUMMARY — MON 07:00

Leadform — Broad carried the week: 214 CRM-verified leads at $22.5, comfortably under the $25 target. Retarget stayed the cheapest path to booked calls.

Lookalike 2% is the watch-item: CPL drifted above target while lead quality (MQL rate) fell to 54%. The volume is real, the intent is not.

Speed-to-lead held at 4m 12s — keep the unassigned routing fix on the list; those 72 leads convert at half the team rate.

SUGGESTED ACTIONS

Shift 20% of LAL budget → Leadform

Pause STATIC — OFFER creative

Fix UNASSIGNED lead routing

Lift Retarget cap by $30/day

DELIVERED TO: SLACK #PERFORMANCE · EMAIL · WHATSAPP
MODEL: WEEKLY DIGEST v3 · SOURCES: 6 MART TABLES

SOURCE: BIGQUERY SCHEDULED RULES + LLM SUMMARY → SLACK / EMAILMOCK DATA · REFRESHED 07:00 GST · PAGE 6 / 6

PAGE 06 / 06 — ALERTS + AILIVE HTML · MOCK DATA

EXHIBIT D

Automation Layer

Alerts come to the team.

■ Rules watch the numbers and post to Slack when something breaks or costs rise.
■ Every Monday, an AI summary explains the week in plain language.
■ Every alert includes a suggested next step.

Live Demo

Try it yourself.

A working copy of the dashboard with sample data. Change the date range, switch pages, hover over the chart, and sort the table.

lookerstudio.google.com/paid-ads-crm — INTERACTIVE RECREATION

Paid Ads × CRM — Live

LAST 30 DAYS · REFRESHED 07:00 GST

MOCK DATA

AD SPEND—

CLICKS—

LEADS · CRM—

CPL · CRM—

BOOKED—

WON—

LEADS & SPEND — DAILY LEADSSPEND

CLICK → CLOSE FUNNEL

CLICKS

—

LEADS

—

BOOKED

—

SHOWED

—

WON

—

SPEND— · LEADS PIXEL— · LEADS CRM— · GAP—

					STATUS

STAGE CONVERSION — CRM

NEW

—

CONTACTED

—

BOOKED

—

SHOWED

—

WON

—

DROPS: 78% → 32% → 71% → 44% · SPEED-TO-LEAD 4M 12S

SOURCE QUALITY — VOLUME × MQL% HOVER THE DOTS

TRY: SWITCH THE RANGE ✦ HOVER THE CHART ✦ SORT BY CPL ✦ ALL MOCK DATA, ALL LIVE HTML

Under the Hood

A look at the SQL.

A simplified version of the main query: ad spend on one side, CRM leads on the other, one cost-per-lead out.

bigquery · marts/cpl_crm.sql

-- marry ad spend to CRM truth (simplified)
WITH spend AS (
  SELECT date, campaign_id, SUM(spend) AS spend
  FROM meta_ads.insights
  GROUP BY 1, 2
),
crm AS (
  SELECT l.campaign_id,
         COUNT(*) AS leads_crm,
         COUNTIF(d.stage = 'won') AS wins
  FROM ghl.leads l
  LEFT JOIN ghl.deals d USING (lead_id)
  GROUP BY 1
)
SELECT s.date, s.campaign_id, s.spend,
       c.leads_crm, c.wins,
       SAFE_DIVIDE(s.spend, c.leads_crm) AS cpl_crm
FROM spend s JOIN crm c USING (campaign_id)

Deliverables

What was delivered.

✓ Two scheduled API pipelines — Meta Ads + GoHighLevel
✓ BigQuery warehouse: raw → staging → marts
✓ SQL attribution models joining ad → lead → deal
✓ Six-page Looker Studio dashboard system
✓ UTM + naming convention doc the team follows
✓ Five Slack alert rules on live thresholds
✓ Monday 07:00 AI weekly summary with actions
✓ Handover docs + recorded walkthrough

The Results

The results.

ONE CPL.

One cost-per-lead number, verified in the CRM. Everyone works from the same figure.

07:00 DAILY.

Reporting went from a weekly manual job to an automatic daily refresh with alerts.

CLICK → DEAL.

Every campaign can be traced from the first click to booked calls and closed deals.

NOTE: CLIENT DETAILS GENERALISED · ALL NUMBERS ARE SAMPLE DATA · DASHBOARDS ARE HTML RECREATIONS

Two tools, two different stories.

✗ THE PROBLEMS

How the system works.

Meta Ads API

GoHighLevel API

Pipelines

BigQuery

Looker Studio

Action Layer

The process, step by step.

Audit

Measurement Plan

Implementation

Dashboard Build

Automate & Optimise

The dashboard, page by page.

Campaigns — Deep Dive

Ad leads vs real leads.

Lead Quality & CRM

Where leads drop off.

Alerts & AI Summary

⚠CPL BREACH — LOOKALIKE 2%

▲LEAD SPIKE — +38% WoW

✓PIPELINE REFRESH — OK

⚠ZERO-LEAD ADSET — STATIC OFFER

Alerts come to the team.

Try it yourself.

Paid Ads × CRM — Live

LEADS & SPEND — DAILY LEADSSPEND

CLICK → CLOSE FUNNEL

STAGE CONVERSION — CRM

SOURCE QUALITY — VOLUME × MQL% HOVER THE DOTS

A look at the SQL.

What was delivered.

The results.

ONE CPL.

07:00 DAILY.

CLICK → DEAL.

Want this for your business?