)
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.”
Sales Ops and RevOps managers can calculate pipeline coverage ratio and sales velocity dynamically, per rep, per territory and per time period, in Google Sheets or Excel using Coefficient’s Salesforce connector, combining Opportunity data with quota data in a single refreshing dashboard. Salesforce native dashboards struggle with these metrics because they require joining pipeline data against quota data from a separate object or external source and calculating ratios dynamically across changing close dates and remaining quota balances.
A common challenge for Sales Ops teams: pipeline coverage and sales velocity are the two numbers sales leadership asks about most, yet both require calculations Salesforce can’t run natively without custom formula fields or a BI tool licence.
How to build a pipeline coverage and sales velocity dashboard
Step 1. Import Opportunity data with stage and close date fields
Open Coefficient in Google Sheets or Excel and select Import from Salesforce. Use From Objects and Fields to pull Opportunity records with Amount, StageName, CloseDate, CreatedDate, OwnerId and Probability. Apply a dynamic date filter scoped to your current quarter close dates to keep the import focused on active pipeline. Set an hourly or twice-daily refresh to keep coverage calculations current as deals move.
Step 2. Import quota data and calculate remaining quota per rep
Create a second import for your quota source, a custom Salesforce object, a separate sheet, or an external file you paste in manually. Add a formula column calculating remaining quota for each rep: total quota minus closed-won amount for the period. This is the denominator for your coverage ratio and the input your sales velocity formula needs.
Step 3. Build coverage ratio and sales velocity formulas
Add a summary table with one row per rep. For pipeline coverage, divide total open pipeline value for the rep by their remaining quota: open pipeline sum divided by remaining quota. For sales velocity, use the standard formula: number of qualified opportunities multiplied by average deal value multiplied by win rate, divided by average sales cycle length in days. Use COUNTIFS, AVERAGEIFS and SUMIFS referencing your Opportunity import to keep each component dynamic.
Step 4. Add conditional formatting thresholds and trend tracking
Apply conditional formatting to the coverage ratio column: red for below 1.5x, amber for 1.5x to 3x, green for above 3x, the standard thresholds most sales orgs use. Use Coefficient’s snapshot feature to capture coverage ratio values at the end of each week, so you can add a trend column showing whether each rep’s coverage is improving or declining over the past four weeks.
What you get
Pipeline coverage and sales velocity update automatically as deals move and quota data refreshes. Sales leaders see which reps are under-covered before the quarter closes, not after. Trend data surfaces whether the pipeline is building or eroding week over week. For layout reference on how to present Salesforce pipeline metrics alongside rep-level data, see Coefficient’s Salesforce dashboard examples.
Start tracking pipeline coverage and sales velocity automatically at coefficient.io/get-started.
