)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
HubSpot’s native reporting falls short when you need to create year-over-year campaign comparisons segmented by business unit. The platform only supports basic current vs previous period comparisons and can’t save historical campaign data for true YOY analysis.
Here’s how to build a comprehensive YOY campaign dashboard that preserves historical data and provides real business unit insights.
Build dynamic YOY campaign dashboards using Coefficient
The key to effective YOY campaign analysis lies in capturing historical snapshots and creating dynamic comparisons. Coefficient solves HubSpot’s limitations by preserving campaign performance data over time and enabling sophisticated business unit filtering that updates automatically.
How to make it work
Step 1. Set up historical data capture with snapshots.
Configure monthly snapshots of your campaign data to preserve historical performance metrics. Capture campaign name, business unit, impressions, CTR, conversions, and revenue attributed. Schedule these snapshots to run automatically on the last day of each month so you build a historical database.
Step 2. Create dynamic business unit filtering.
Use dynamic filtering to point to cells containing your business unit names (like DDH, CMSSP, O142). Create separate imports for each business unit or use a master import with filter groups. Apply up to 25 filters with AND/OR logic to segment campaign data precisely by unit.
Step 3. Import current year campaign data with scheduled refreshes.
Set up HubSpot imports that refresh hourly or daily to capture current campaign performance. Import the same fields you’re capturing in snapshots: campaign name, business unit, impressions, CTR, conversions, and revenue.
Step 4. Build YOY calculation formulas.
Use VLOOKUP or INDEX/MATCH formulas to compare current year data against your historical snapshots. Calculate YOY growth rates with this formula: ((Current Year – Previous Year) / Previous Year) * 100. Set up separate calculations for each key metric.
Step 5. Create a multi-tab dashboard structure.
Organize your data with Tab 1 for live campaign data filtered by current year, Tab 2 for historical snapshots organized by year and month, Tab 3 for your YOY comparison dashboard pulling from both datasets, and Tab 4 for business unit summary with roll-up calculations.
Step 6. Configure automated alerts and updates.
Set up email or Slack alerts when YOY performance exceeds targets or falls below thresholds. Use Formula Auto Fill Down to maintain calculations as new campaigns are added. This keeps your dashboard current without manual intervention.
Start tracking YOY performance today
Building YOY campaign comparisons by business unit transforms how you analyze marketing performance over time. The combination of historical snapshots and dynamic filtering gives you insights that HubSpot simply can’t provide natively. Get started with your YOY dashboard today.
