Google Sheets KPI Dashboard Development with SEO/PPC Performance Analysis

GOOGLE SHEETS KPI DASHBOARD (STRUCTURE)
You’re going to create 5 tabs:
1. RAW DATA (INPUT)
2. KPI DASHBOARD (CEO VIEW)
3. CHANNEL BREAKDOWN
4. LEAD QUALITY TRACKING
5. FORECAST + ROI

TAB 1: RAW DATA (INPUT)
This is where your VA / Verena / marketing team dumps data weekly.
Columns:
Date Channel Campaign Spend Clicks Impressions Leads Calls Qualified Leads Appointments Deals Closed Revenue Location
Sources:
• Google Ads → Spend, clicks, impressions
• GA4 → leads/forms
• CallRail → calls
• Salesforce → qualified leads, deals, revenue
• SEMrush → handled separately (SEO tab later if needed)
Rule: No missing data. Ever.

TAB 2: KPI DASHBOARD (CEO VIEW)
This is what YOU look at.
Top Section (BIG NUMBERS)
KPI Formula
Total Spend =SUM('RAW DATA'!D:D)
Total Leads =SUM('RAW DATA'!G:G)
Qualified Leads =SUM('RAW DATA'!I:I)
Deals Closed =SUM('RAW DATA'!K:K)
Revenue =SUM('RAW DATA'!L:L)
________________________________________
Core Metrics (THIS IS WHAT MATTERS)
KPI Formula
CPL =Spend / Leads
Cost per Qualified Lead =Spend / Qualified Leads
Close Rate =Deals / Qualified Leads
Revenue per Lead =Revenue / Leads
ROAS =Revenue / Spend
________________________________________
Targets (SET THESE)
KPI Target
CPL $150–$400 (depends on service)
CPQL $300–$800
Close Rate 20–35%
ROAS 3x–8x
If below → problem
If above → scale
________________________________________
Visuals (create charts):
• Spend vs Revenue (line chart)
• Leads vs Qualified Leads (bar chart)
• CPL trend (line chart)
________________________________________
TAB 3: CHANNEL BREAKDOWN
This is where agencies get exposed.
Pivot Table:
Rows:
• Channel (Google Ads, SEO, Facebook, etc.)
Values:
• Spend
• Leads
• Qualified Leads
• Revenue
________________________________________
Add Calculated Columns:
Metric Formula
CPL Spend / Leads
CPQL Spend / Qualified Leads
ROAS Revenue / Spend
________________________________________
This tells you:
• Which channel prints money
• Which one is garbage

TAB 4: LEAD QUALITY TRACKING (CRITICAL)
Most companies don’t do this → this is your edge.
Columns:
| Lead ID | Date | Source | Keyword | Job Type | Budget | Qualified (Y/N) | Reason if NOT | Sales Rep |

Create dropdown:
• Qualified: YES / NO
• Reason:
o No budget
o Wrong service
o Outside area
o Spam

KPI:
Metric Formula
% Qualified Leads Qualified / Total Leads
Waste % Unqualified / Total Leads

If waste > 40% → your PPC agency is trash

TAB 5: FORECAST + ROI
Inputs:
Metric Value
Avg Job Value $25,000 (example)
Close Rate 25%
Monthly Budget $50,000

Outputs:
KPI Formula
Expected Leads Budget / CPL
Expected Deals Leads × Close Rate
Expected Revenue Deals × Avg Job Value

This lets you answer:
“If I spend $100K… what do I make?”

AUTOMATION (IMPORTANT)
Step 1: Connect Data
• Google Ads → Supermetrics or Zapier → Sheets
• GA4 → export weekly
• Salesforce → export pipeline weekly
Step 2: Assign Ownership
• Verena → updates weekly
• Sales team → marks qualified leads daily

RULES (NON-NEGOTIABLE)
1. No guessing numbers
2. No “we think leads are good”
3. Everything tied to revenue
4. Update every Monday

WHAT THIS WILL DO FOR YOU
Within 2 weeks you’ll know:
• Which campaigns are garbage
• Which keywords waste money
• Whether your agency is lying
• Where to scale aggressively