Why forecasting matters for Marketing Analysts
Forecasting turns raw marketing data into forward-looking decisions. With reliable forecasts, you can set realistic targets, plan budgets, allocate channels, and catch risks early. In this skill, you’ll learn simple, business-friendly techniques that produce clear numbers stakeholders can trust.
What you’ll be able to do after this skill
- Build baseline and seasonal forecasts for traffic, leads, and revenue.
- Estimate best/base/worst cases for clear stakeholder planning.
- Translate budget to expected outcomes using CPA/ROAS logic.
- Forecast conversion funnels end-to-end (impressions → revenue).
- Run sensitivity analysis for CPA and CVR to show risk/impact.
- Validate forecasts with simple backtests and error metrics.
Who this is for
- Marketing Analysts and Growth/Performance Marketers who need clear, defensible forecasts.
- New analysts moving from reporting to planning.
- Generalists who manage budgets and want quick, practical forecasting.
Prerequisites
- Comfort with spreadsheets (SUM, AVERAGE, simple formulas).
- Basic marketing metrics: impressions, clicks, CTR, CPC, CVR, CPA, ROAS, AOV.
- Familiarity with time series basics (daily/weekly/monthly data).
Learning path
Establish a baseline trend
Use a simple linear trend or moving average to estimate the underlying direction.
Milestone tasks
- Create a Month index column (1, 2, 3, ...).
- Compute a 3- or 4-period moving average as a quick baseline.
- Optionally compute linear trend with slope and intercept.
Layer in seasonality
Quantify recurring patterns (e.g., weekday vs. weekend, monthly peaks).
Milestone tasks
- Calculate seasonality indices (ratio-to-average).
- Decide on multiplicative (common) vs. additive adjustments.
- Apply seasonal factors to your baseline.
Convert budgets to outcomes
Translate spend into clicks, conversions, and revenue using CPA/ROAS logic.
Milestone tasks
- Budget → clicks using CPC, then → conversions using CVR.
- Budget → conversions via CPA; or → revenue via ROAS/AOV.
- Check consistency across approaches.
Build scenarios
Create Best/Base/Worst forecasts to reflect uncertainty.
Milestone tasks
- Define optimistic/pessimistic CPC and CVR assumptions.
- Show the impact on CPA, conversions, and revenue.
- Summarize in a compact 3-line table for stakeholders.
Run sensitivity analysis
Map how CPA or revenue changes as CPC or CVR moves.
Milestone tasks
- Create a two-way grid of CPC vs. CVR to see CPA hot spots.
- Highlight thresholds where targets break.
- Capture takeaways as risks/opportunities.
Validate with backtesting
Hold out recent periods to evaluate forecast accuracy.
Milestone tasks
- Split data: train (older) vs. test (most recent).
- Compute MAPE or WAPE on the holdout.
- Refine model and document assumptions.
Worked examples
1) Baseline linear trend forecast (spreadsheet)
Data: Month index in A2:A13 (1..12), sessions in B2:B13.
- Slope:
=SLOPE(B2:B13, A2:A13) - Intercept:
=INTERCEPT(B2:B13, A2:A13) - Forecast month 13:
=FORECAST.LINEAR(13, B2:B13, A2:A13)or=slope*13 + intercept
Use this as the baseline before seasonal adjustment.
2) Simple moving average (3-month)
Data: monthly leads in B2:B13. Place a 3-month SMA in C4:
=AVERAGE(B2:B4) and copy down. Forecast month 13 using the average of months 10–12: =AVERAGE(B10:B12).
3) Seasonality index (multiplicative)
Suppose overall average monthly revenue is 200 and January average is 240. January index = 240 / 200 = 1.2. If baseline (trend) for next January is 250, seasonal forecast = 250 * 1.2 = 300.
4) Budget → outcomes
Given budget = 20,000, CPC = 1.00, CVR = 2%, AOV = 60.
- Clicks =
Budget / CPC = 20,000 - Conversions =
Clicks * CVR = 20,000 * 0.02 = 400 - Revenue =
Conversions * AOV = 400 * 60 = 24,000 - CPA =
Budget / Conversions = 20,000 / 400 = 50
5) Best/Base/Worst scenario
Budget = 50,000; CPC = 1.25; Base CVR = 2%; Best CVR = 2.5%; Worst CVR = 1.5%.
- Clicks =
50,000 / 1.25 = 40,000 - Base conversions =
40,000 * 0.02 = 800 - Best conversions =
40,000 * 0.025 = 1,000 - Worst conversions =
40,000 * 0.015 = 600
Summarize these in a small 3-row table for communication.
6) Backtesting with MAPE
Hold out last 3 months as test. For each test month i, compute: APE_i = ABS(Actual_i - Forecast_i) / Actual_i. Then MAPE = AVERAGE(APE_i). Example: Actuals = {1000, 1100, 900}; Forecasts = {950, 1150, 930}; APE = {0.05, 0.045, 0.033}; MAPE ≈ 4.3%.
Drills and quick exercises
- Compute a 4-period moving average and compare it to a linear trend on the same series. Which fits better visually?
- Build monthly seasonality indices and identify the top 2 peak months.
- Convert a 15,000 budget to clicks, conversions, and revenue using two sets of CPC/CVR assumptions.
- Create Best/Base/Worst projections for next quarter. State your assumptions clearly in a note.
- Make a 2D sensitivity grid of CPA for CPC in {0.8, 1.0, 1.2} and CVR in {1.5%, 2.0%, 2.5%}.
- Backtest your latest forecast on the last 4 weeks and calculate MAPE.
Mini project: Quarterly demand forecast
Goal: Produce a quarterly forecast for leads and revenue with baseline trend, seasonality, scenarios, and validation.
- Assemble 18–24 months of weekly or monthly data (impressions, clicks, conversions, revenue).
- Compute baseline: use linear trend or moving average.
- Quantify seasonality and adjust the baseline (multiplicative recommended).
- Translate budget to outcomes: calculate clicks, conversions, revenue; reconcile with trend+seasonality to ensure consistency.
- Create Best/Base/Worst scenarios by varying CPC and CVR by ±20–30%.
- Validate: hold out the last 2–3 periods; report MAPE and what you’d improve.
- Deliverable: one slide or page with assumptions, a small table for scenarios, and a chart comparing forecast vs. actuals.
Practical projects you can ship
- Marketing forecast template: A spreadsheet with tabs for Baseline, Seasonality, Scenarios, and Backtesting. Reusable by your team.
- Channel planning sheet: Budget-to-outcome calculator that compares CPA and ROAS targets across 2–3 channels.
- Funnel simulator: Change CTR, CPC, and CVR to see impact on revenue; includes conditional formatting to flag risk.
Common mistakes and how to avoid them
Using future data (data leakage)
Don’t compute seasonality or averages using periods that occur after the forecast date. Always use information available at the time of forecasting.
Overfitting with complex models
Polynomials and heavy models often look great on history but fail on new data. Start simple; only add complexity if backtests prove it helps.
Ignoring uncertainty
Single-number forecasts hide risk. Always include Best/Base/Worst with clear, documented assumptions.
Mixing additive and multiplicative seasonality
Pick one approach and apply it consistently. Most marketing data fits multiplicative (trend * seasonality).
No validation
Always hold out recent periods and report MAPE or WAPE. If accuracy is poor, reassess seasonality or shorten the averaging window.
Debugging tips
- Plot actuals vs. forecast; look for systematic offsets (under/over in peaks).
- Test different moving average windows (3 vs. 4 vs. 6) and pick the one with lower holdout error.
- Check if spikes come from tracking changes or one-off campaigns—treat them separately.
- Sanity-check CPA and CVR assumptions against the last 3–6 months.
Subskills
- Baseline Trend Forecasting — Create linear or moving-average baselines.
- Seasonality Awareness — Quantify recurring patterns and adjust forecasts.
- Simple Moving Average Forecasts — Smooth noise and project near-term values.
- Scenario Forecasting Best Base Worst — Frame uncertainty with clear cases.
- Budget To Outcome Forecasts — Convert spend into expected clicks, conversions, revenue.
- Conversion Funnel Forecasting — Forecast full-funnel from impressions to revenue.
- Sensitivity Analysis For CPA And CVR — Visualize risk and thresholds.
- Forecast Validation And Backtesting Basics — Measure accuracy with holdouts and MAPE/WAPE.
Next steps
- Finish the drills, then complete the mini project.
- Take the skill exam to check your readiness.
- Apply this to your active campaigns and update monthly with new data.
Skill exam
Available to everyone. If you log in, your progress and results will be saved.