This Marketing KPI Dashboard Template helps freelance and fractional marketers track key performance metrics for client engagements. It's designed to be simple yet comprehensive, providing both visual and numerical representations of performance data.
Content Marketing (page views, time on page, conversions)
Paid Advertising (ROAS, CPC, CTR, impressions)
SEO (keyword rankings, organic traffic, backlinks)
3. Project Tracker
Keep all deliverables organized:
Project name and description
Deadlines and status
Assigned team members
Priority level
Notes and comments
4. Client Goals Section
Document and track progress toward client goals:
Goal description
Key metrics for measuring success
Target values
Current progress
Visual progress bars
5. Budget Tracker
Monitor marketing spend and results:
Budget allocation by channel
Actual spend vs. planned
ROI calculations
Cost per lead/acquisition
Monthly and quarterly breakdowns
Customization Options
Adding New Metrics
Go to the "Data Entry" sheet
Add your new metric in an empty row
Update the formula references in the Dashboard sheet
Changing Date Ranges
Modify the date columns in the "Data Entry" sheet
Update any date references in chart settings
Adding New Channels
Add new channel to the "Marketing Channels" sheet
Create corresponding sections in the "Data Entry" sheet
Best Practices for Using This Template
Weekly Updates
Schedule time each week to update your metrics for the most accurate reporting.
Monthly Reviews
Use the dashboard for monthly client reviews to demonstrate progress and value.
Quarterly Goal Setting
The template includes sections for quarterly goals - revisit and update these every three months.
Custom Views for Different Stakeholders
Create filtered views for different client stakeholders:
Executive summary for leadership
Detailed channel metrics for department heads
Project tracking for day-to-day contacts
Additional Resources
Formula Reference
The template uses several helpful formulas:
SPARKLINE() - For mini trend charts
QUERY() - For dynamic data filtering
ARRAYFORMULA() - For processing multiple values
VLOOKUP() - For matching data across sheets
Troubleshooting
If charts aren't updating:
Check that data formatting is consistent
Verify data ranges in chart settings
Ensure formulas are correctly referencing data cells
// This code demonstrates the key formulas and setup for the Marketing KPI Dashboard in Google Sheets
// Note: This is a representation of the Google Sheets formulas and not executable JavaScript
// === DASHBOARD SETUP ===
// 1. Executive Summary Cards with Sparklines
// Format: =SPARKLINE(range, options)
const trafficSparkline = '=SPARKLINE(DataEntry!D2:D13, {"charttype","line";"color","#4285F4";"linewidth",2})';
// Month-over-month change calculation
const monthOverMonthChange = '=ARRAYFORMULA(IF(B2:B7="","",IF(C2:C7=0,"",ROUND((B2:B7-C2:C7)/C2:C7*100,1)&"%")))';
// Conditional formatting formula for positive/negative changes
const conditionalFormat = 'Format rule: Text contains "+" → Green, Text contains "-" → Red';
// 2. Goal Progress Tracker
// Dynamic progress calculation with QUERY and progress bar using SPARKLINE
const goalProgress = `=QUERY(ClientGoals!A2:G, "SELECT C, F/E*100 WHERE A = 'Q2 2025' LABEL F/E*100 'Progress'")`;
const progressBar = '=SPARKLINE(B2, {"charttype","bar";"color1","#34A853";"max",100})';
// 3. Channel Performance Chart
// Data aggregation for chart
const channelData = '=QUERY(MarketingChannels!A:E, "SELECT A, SUM(C) WHERE B = \'Leads\' GROUP BY A LABEL SUM(C) \'Total Leads\'")';
// === DATA ENTRY SHEET ===
// Data validation for channels and metrics
const channelValidation = 'Data Validation: List from range MarketingChannels!A2:A6';
const metricValidation = 'Data Validation: List from range (depends on selected channel)';
// Dynamic goal retrieval based on metric and channel
const goalLookup = '=ARRAYFORMULA(IF(A2:A="",,VLOOKUP(B2:B&C2:C,ClientGoals!K:M,3,FALSE)))';
// === MARKETING CHANNELS SHEET ===
// Channel performance calculation
const channelROI = '=IFERROR((F2-D2)/D2,0)';
const costPerLead = '=IFERROR(D2/E2,0)';
// Dynamic month labels with date formatting
const monthLabels = '=ARRAYFORMULA(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())-SEQUENCE(1,6,0,-1),1),"mmm yyyy"))';
// === PROJECT TRACKER ===
// Days remaining calculation
const daysRemaining = '=ARRAYFORMULA(IF(D2:D="","",IF(F2:F="Complete",0,D2:D-TODAY())))';
// Progress percentage based on status
const statusProgress = '=ARRAYFORMULA(IF(E2:E="","",IF(E2:E="Not Started",0,IF(E2:E="In Progress",0.5,IF(E2:E="Review",0.75,IF(E2:E="Complete",1,0))))))';
// Status dropdown with data validation
const statusValidation = 'Data Validation: List of items: "Not Started, In Progress, Review, Complete, Delayed"';
// === CLIENT GOALS SHEET ===
// Progress percentage calculation
const goalProgressCalc = '=ARRAYFORMULA(IF(F2:F="","",IF(E2:E=0,0,MIN(F2:F/E2:E*100,100))))';
// Conditional formatting for goal progress
const goalProgressFormat = 'Format rules: <25% Red, 25-75% Yellow, >75% Green';
// === BUDGET SHEET ===
// Budget variance calculation
const budgetVariance = '=ARRAYFORMULA(IF(B2:B="",,C2:C-B2:B))';
const variancePercentage = '=ARRAYFORMULA(IF(B2:B="",,IF(B2:B=0,0,D2:D/B2:B*100)))';
// ROI calculation
const marketingROI = '=ARRAYFORMULA(IF(D2:D="",,IF(D2:D=0,0,(G2:G-D2:D)/D2:D*100)))';
// === CHARTS SETUP ===
// Line chart for traffic trends
const trafficTrendChart = `Chart type: Line
Data range: 'Data Entry'!A:D
Filtered for: C = "Website Traffic"
X-axis: Date column (A)
Y-axis: Value column (D)
Title: "Website Traffic Trends"`;
// Bar chart for channel leads
const channelLeadsChart = `Chart type: Bar
Data range: QUERY(MarketingChannels!A:E, "SELECT A, SUM(C) WHERE B = 'Leads' GROUP BY A")
Title: "Leads Generated by Channel"`;
// Pie chart for budget allocation
const budgetPieChart = `Chart type: Pie
Data range: Budget!A:C
Filter: Current month only
Title: "Marketing Budget Allocation"`;
// Scatter plot for cost vs performance
const costPerformanceChart = `Chart type: Scatter
Data range: QUERY(MarketingChannels!A:E, "SELECT D, E WHERE B = 'Current Month'")
X-axis: "Cost"
Y-axis: "Leads"
Title: "Cost vs. Lead Generation by Channel"`;
