)
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.”
Rolling forecasts provide better visibility than static monthly views, but HubSpot lacks native rolling forecast capabilities. You need dynamic time windows that adjust automatically and historical data to build predictive models.
Here’s how to build sophisticated rolling forecasts that update daily with your latest pipeline changes.
Build dynamic rolling forecasts using Coefficient
Coefficient enables sophisticated rolling forecasts by combining live HubSpot pipeline data with Google Sheets’ calculation power, creating forecasts that adjust automatically as time progresses.
How to make it work
Step 1. Set up live pipeline data import.
Connect HubSpot to Google Sheets via Coefficient and import deals with all active pipeline stages, expected close dates, deal amounts, probabilities, and historical win rates by stage. This creates your foundation for rolling calculations.
Step 2. Create dynamic rolling time windows.
Use Google Sheets formulas to automatically categorize deals into rolling periods: Next 30 days with, 31-60 days with, and 61-90 days for longer-term visibility.
Step 3. Implement historical snapshots for trend analysis.
Configure Coefficient Snapshots to capture pipeline state daily for trend analysis, weekly for forecast accuracy measurement, and monthly for historical comparisons. This builds the historical dataset needed for predictive modeling.
Step 4. Calculate rolling averages from historical data.
Analyze snapshot data to determine 13-week rolling average of new pipeline created, 4-week rolling close rates by stage, and 12-week rolling average deal velocity. These metrics inform your predictive calculations.
Step 5. Build predictive rolling models.
Combine current pipeline with historical trends using formulas like:to predict future performance based on rolling patterns.
Step 6. Automate updates and visualizations.
Schedule imports to refresh daily for real-time rolling views, before weekly forecast meetings, and with alerts for significant pipeline changes. Create charts showing pipeline coverage over rolling 90-day periods and forecast accuracy trending over time.
Get dynamic forecasting that adapts automatically
Rolling forecasts provide insights impossible with HubSpot’s static reporting, adjusting daily as your pipeline evolves and incorporating historical patterns for better predictions. Your forecasts become more accurate and actionable. Start building your rolling forecasts today.
