)
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 doesn’t store historical snapshots of calculated metrics like coverage ratios. Once deals move or close, you lose visibility into what your coverage looked like at specific past dates.
Here’s how to capture and query historical coverage ratios from any specific date going forward.
Build a historical coverage database using Coefficient
Coefficient addresses this limitation through systematic historical pipeline data capture. By connecting HubSpot to HubSpot spreadsheets, you can start building a searchable database of past coverage ratios.
How to make it work
Step 1. Start historical data collection.
Connect HubSpot to your spreadsheet via Coefficient and import current pipeline data including deal values, expected close dates, and quotas. This becomes your baseline for historical tracking.
Step 2. Implement daily or weekly snapshots.
Use Coefficient’s Snapshots feature to automatically capture your pipeline state at regular intervals. Each snapshot preserves total pipeline value, number and value of deals by stage, coverage ratio calculations, and individual rep coverage metrics.
Step 3. Create a historical database.
Each snapshot becomes a row in your historical coverage tracking sheet, timestamped with the capture date. This builds a searchable database where you can reference any past date’s coverage ratio.
Step 4. Query specific dates.
Once you’ve accumulated snapshots, you can easily reference any past date’s coverage ratio. Questions like “What was our Q3 coverage ratio on July 15th?” become simple lookups using VLOOKUP or INDEX/MATCH formulas.
Start capturing historical coverage data
While you can’t recover data from before implementing this system, you can start building this historical record immediately. Set up your automated coverage tracking to have full historical visibility going forward.
