Most marketing metrics move for three different reasons at once: a long-term direction of travel, a repeating seasonal pattern, and random noise that defies neat explanation. When search demand, sales or organic traffic dip, the first question is always the same — is this a genuine decline, or simply the time of year? Time series decomposition answers that question by splitting a single noisy line into its underlying parts, and you can do it in Excel before you ever reach for Python or R. This guide refreshes a long-standing walkthrough for the way analysts work today.
What decomposition actually does
To decompose a time series is to break a signal — sessions, conversions, revenue, keyword impressions — into the constituent elements that drive it. We are interested in three:
- Trend — the underlying long-term direction, such as the steady growth (or decline) rate of the signal.
- Seasonality — the repeating fluctuations, which may be annual, quarterly, monthly, weekly or even within a single day.
- Residual (noise) — the random behaviour we cannot attribute to either of the above.
Separating these lets you explain why a metric can look flat or falling in the short term while still growing year on year — and it gives you a defensible baseline to forecast against. The worked example below uses monthly web traffic with a strong annual season: high volumes in January, a summer lull, and clear year-on-year growth. The same method applies to any seasonal commercial metric.
Step 1 — Smooth the data with a centred moving average
First, smooth out the seasonal swings to expose the trend underneath. Because the cycle here is annual, the moving average must span all twelve months, so we use a centred moving average of order 12. For each month, average the six months either side of it. Because a 12-month window centred on a single month would straddle two half-months at each end, the convention is to include the boundary months at half weight: for example, when centring on July, January appears at both ends, so you halve those two values, sum the rest, and divide by 12. You inevitably lose six data points at the start and end of the series, where there aren’t six full months on one side.
Step 2 — Choose multiplicative or additive
Next, decide whether the seasonal effect grows with the trend (multiplicative) or stays a constant size (additive). A quick look at the chart usually settles it: if the peaks and troughs get bigger in later, higher-traffic years, the seasonal swing scales with the trend and a multiplicative model is correct.
For a multiplicative model, calculate the ratio of each raw figure to its corresponding moving-average figure — simply raw ÷ moving average. (For an additive model you’d take the difference instead.) This produces a series of numbers either side of 1.0. In our example, August comes in at 0.816 of the long-term average while January reaches 1.341 — exactly the seasonal shape the first chart suggested.
Step 3 — Average the seasonal coefficients
Run the calculation across the whole dataset and you’ll have several ratios for each calendar month. Average all the Januaries together, all the Februaries, and so on, to get more reliable coefficients and smooth out the noise. Because we’re working with ratios, the difference in absolute volume between, say, October 2008 and October 2012 doesn’t distort the result. Excel’s AVERAGEIF function does this in one formula per month.
You now have one ratio per month, but they probably don’t average exactly one. Scale them so they do: divide each monthly figure by the average of all twelve. This guarantees the seasonal adjustment neither inflates nor deflates the underlying figures — the twelve coefficients must sum to 12, averaging 1.0.
Step 4 — Quantify the trend
Plot the moving-average series and add a trendline. In the example a linear fit is almost perfect, with an R² close to one, and the chart equation tells the story directly: starting from a base of 36,074 visits, the data grew by an average of 428 visits per month across the period. Other datasets may follow a quadratic, exponential or more complex curve — fit whichever line your data actually supports rather than forcing a straight line.
Step 5 — Build a simple forecast
With a trend rate and a set of seasonal coefficients, a first-pass forecast is straightforward:
- Take a baseline — here, the average of the previous twelve months.
- Add the underlying growth (428 visits) for each successive month to project the trend forward.
- Multiply each projected month by its seasonal coefficient to restore the expected spikes and troughs.
Plotted against the historical data, the forecast preserves the overall trajectory while honouring the seasonal rhythm through the year. Treat it as an approximation: each predicted month builds on the previous prediction, so the projection can drift if new factors appear. More advanced work would attach a confidence interval to each point and lean on exponential smoothing or a method more powerful than basic decomposition.
Bringing the method into a modern stack
The Excel walkthrough above is still the clearest way to learn the mechanics, and it remains genuinely useful for quick, explainable analysis — exactly what you want when presenting to a client who needs to see the workings. For production forecasting at scale, the same logic now lives in dedicated tooling. A GA4 export to BigQuery, or a CSV of monthly conversions, drops straight into Python (statsmodels‘ seasonal_decompose and STL, or Prophet) or R (stl()), which handle multiple overlapping seasonalities, holidays and confidence intervals out of the box. The concepts don’t change — trend, seasonality, residual — only the horsepower does. Knowing how decomposition works by hand makes you far better at interrogating what those libraries return.
This is the everyday craft of our Data Science & Analytics team: turning noisy performance data into forecasts that stand up in a boardroom. The technique first appeared on the Search Laboratory blog and now lives here as part of the wider Search Laboratory move into Havas Market, where data-led decision-making sits at the centre of what we do.
Take your forecasting further
If seasonal demand is shaping your media, search or commerce performance and you want forecasts you can actually plan against, our Data Science & Analytics team can build them with you — from a clean Excel model to fully automated pipelines.