Most paid media forecasts are either a gut feel or a spreadsheet someone built two years ago that nobody trusts. Both create the same problem: leadership challenges your numbers, you can't defend them with data, and the planning process becomes a negotiation based on vibes. This stack builds a bottoms-up forecast from your actual historical performance — CPL trends, conversion rates by channel, and pipeline contribution — runs it through Claude's gap analysis, and outputs a model with scenario ranges your finance team and CMO will actually engage with.
The Stack
The Prompt
This stack is built around the Pipeline Gap Analysis Prompt. Here's the abbreviated version — the full prompt with all variables and usage notes is on its own page.
You are a B2B demand gen strategist building a paid media forecast. Review the historical performance data and pipeline targets below. For each paid channel, calculate the implied CPL, MQL volume, and pipeline contribution needed to hit the stated pipeline target at current conversion rates. Identify the gap between projected output and pipeline target. Build a base case, upside, and downside scenario based on ±15% CPL variance.[ ... continued — see full prompt ]
The Workflow
-
Export 12 months of paid media performance data
Pull spend, CPL, MQL volume, MQL-to-SQL rate, and pipeline contribution by channel from your ad platforms and CRM. This is the foundation the forecast model is built on.
-
Pull Salesforce pipeline targets for the forecast period
Export next-quarter pipeline targets by segment. You need the goal before you can model whether current spend and conversion rates will get you there.
-
Paste both datasets into the Pipeline Gap Analysis prompt
Add the forecast period and any known variables upfront — planned budget increases, new channels, seasonality factors. Claude needs context to build an accurate model.
-
Review the bottoms-up model and gap analysis
Claude builds a channel-by-channel forecast at current conversion rates, calculates the gap to target, and produces base/upside/downside scenarios based on CPL variance ranges.
-
Export to Google Sheets and document in Notion
Paste the Claude model output into a Google Sheets forecast template. Add scenario tabs for each variant. Document assumptions and model logic in Notion for the next planning cycle.
What This Replaces
- Forecast models built on gut feel or outdated spreadsheets nobody trusts
- Planning conversations where you can't defend your numbers with actual historical data
- Manual scenario modeling that takes a full day to build from scratch each quarter
Related Stacks
New stacks drop weekly.
Each one includes the tools, the Claude prompt, and the workflow logic. Free — built for in-house B2B demand gen managers.