What is renewal forecast accuracy?
Definition
Renewal forecast accuracy compares what the team said would renew at the start of a period against what actually renewed. It only means something if the forecast was frozen — a snapshot written on day one of the quarter. Grading a forecast that has been edited all quarter measures nothing but the team's ability to update fields.
Formula: Forecast accuracy = 1 − |forecast ARR − actual ARR| / forecast ARR, per period
What data do you need?
- A forecast snapshot table with period, account, forecast category, and ARR
- The snapshot written by a scheduled job at period start, never updated
- Actual outcomes with renewed, churned, and downgraded ARR per account
- Forecast category definitions that are stable across quarters
- At least four to six closed periods, so the rolling view has something to say
SQL pattern
Forecast vs. actual by quarter, rolling (PostgreSQL)PostgreSQL
-- renewal_forecast_snapshots is written once, on day one of each
-- quarter, and never updated. That immutability is the whole metric.
WITH forecast AS (
SELECT
period_start,
SUM(forecast_arr_usd) FILTER (
WHERE forecast_category IN ('commit', 'likely')
) AS forecast_arr
FROM renewal_forecast_snapshots
GROUP BY period_start
), actual AS (
SELECT
date_trunc('quarter', r.close_date)::date AS period_start,
SUM(r.arr_usd) FILTER (WHERE r.stage = 'closed_renewed') AS actual_arr
FROM renewal_opportunities r
WHERE r.stage IN ('closed_renewed', 'closed_churned')
GROUP BY 1
)
SELECT
f.period_start,
ROUND(f.forecast_arr, 2) AS forecast_arr,
ROUND(COALESCE(a.actual_arr, 0), 2) AS actual_arr,
ROUND(COALESCE(a.actual_arr, 0) - f.forecast_arr, 2) AS variance_arr,
ROUND(
100.0 * (1 - ABS(COALESCE(a.actual_arr, 0) - f.forecast_arr)
/ NULLIF(f.forecast_arr, 0)), 1
) AS accuracy_pct,
ROUND(
AVG(100.0 * (1 - ABS(COALESCE(a.actual_arr, 0) - f.forecast_arr)
/ NULLIF(f.forecast_arr, 0))) OVER (
ORDER BY f.period_start ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
), 1
) AS accuracy_4q_rolling_pct
FROM forecast f
LEFT JOIN actual a USING (period_start)
WHERE f.period_start < date_trunc('quarter', CURRENT_DATE)
ORDER BY f.period_start;Common pitfalls
Comparing the current, live forecast against actuals.→ Freeze a snapshot at period start. Without it you are grading a moving target, and accuracy will look suspiciously excellent.
Netting overcalls against undercalls.→ Use absolute variance. Two accounts that miss in opposite directions are two forecasting errors, not zero.
Judging a quarter in isolation.→ Report a four-quarter rolling accuracy alongside the single period; one deal can swing a quarter, and the bias (consistently high or low) is the more actionable signal.
Changing what 'commit' means between quarters.→ Write down the category definitions and version them. A redefinition mid-year makes the entire trend uncomparable.
Where does this metric apply?
This metric commonly uses data from Salesforce, HubSpot, Gainsight, Planhat, Vitally, plus any warehouse models built on account, subscription, and product-usage tables at the same grain.
Related
Dashboards
Integrations
Metrics
Analytics
FAQ
Should accuracy be measured in ARR or in deal count?
ARR for the business view, count for coaching. A team can call 90% of deals right and still miss the quarter badly if the one miss was the largest renewal on the list — so show both.
What about deals that slip rather than churn?
Track slippage as its own outcome. A renewal that moves from Q2 to Q3 is not churn, but it is a forecast error, and rolling it silently into the next quarter hides a systematic timing bias.