Row count checks on seasonal data built from the prior year curve

Four hundred rows landed in the registration table on a Monday in week three of the campaign, and the pipeline was healthy. Four thousand rows landed on the Tuesday of show week, and the pipeline was healthy then too. A monitoring rule has to pass both of those days and still catch the Thursday when a broken form quietly halved the intake, which is why row count checks on seasonal data have to work on something other than the count.

The something else is the remainder left after the predictable part of the series has been removed.

Why the raw count carries almost no information

A daily registration series is dominated by two patterns that have nothing to do with data quality.

There is a within-week pattern. Business audiences register on working days, so Saturday and Sunday counts run at a fraction of Wednesday's, every week, all year, on every show in the portfolio. There is an annual pattern driven by the campaign, which climbs from almost nothing to a peak in the final fortnight and collapses to zero the day after the show closes.

Between them those two account for most of the variance in the series. A control limit applied to the raw count is therefore mostly measuring the calendar, and any threshold you set will be wrong for the majority of days in the year. Removing the two patterns first leaves a much smaller and much better behaved quantity to test.

Decompose first, then test what is left

The standard tool is STL, published by Cleveland, Cleveland, McRae and Terpenning in the Journal of Official Statistics in 1990. It splits a series into a trend component, a seasonal component and a remainder, using loess to fit the smooth parts.

Hyndman and Athanasopoulos, in the third edition of Forecasting: Principles and Practice, list what the method gives you. It handles any type of seasonality, so weekly and daily patterns are as available as monthly and quarterly. The seasonal component may change over time, at a rate the user controls, which matters on a show whose campaign shape has shifted across editions. The smoothness of the trend is also user controlled. There is an outlier resistant variant, which stops unusual observations distorting the trend and seasonal estimates while still leaving them visible in the remainder, and for monitoring work that variant should be switched on. The whole point of the exercise is that an outlier lands in the remainder instead of being quietly absorbed into the seasonal shape, where it would then raise the expectation for the same week next year.

The same source names two limits. STL does not automatically handle trading day or calendar variation, and it provides facilities for additive decompositions only, so multiplicative behaviour needs a transformation first.

The arithmetic of a remainder check

Work the two days from the opening through the method.

Fit the decomposition on the last 365 days of daily registration counts for one show. For the Tuesday of show week, the trend and seasonal components together give an expected 3,850 rows. The actual count is 4,000. The remainder for that day is 4,000 minus 3,850, which is 150.

For the Monday in week three of the campaign, the same fit gives an expected 372. The actual is 400, so the remainder is 28.

Now the control limit. Compute the standard deviation of the remainder series over the fitted year and suppose it comes out at 96 rows. A three standard deviation limit is 3 times 96, which is 288. Both days pass, because 150 and 28 are inside 288, and one rule covered a day of 4,000 rows and a day of 400 without anybody writing a special case.

Then the broken Thursday. Expected 3,610, actual 3,200, so the remainder is minus 410. That exceeds 288 and the check fires, on a day when the raw count of 3,200 would have looked entirely healthy against any absolute threshold set for the campaign.

Two details about that standard deviation are worth stating before somebody leans on it. It was computed across a year in which the series contained genuine incidents, so those incidents inflated it and made the limit wider than it should be. Recomputing it after excluding known incident days is a small piece of work with a real payoff, and on most series it narrows the limit by a tenth or more. The second detail is that the remainder is not guaranteed to be symmetric: a broken form removes rows and nothing adds a matching number of phantom ones, so the interesting tail is usually the lower one, and a one sided limit at three standard deviations below expectation catches what you care about with half the false alarms.

Should you decompose the counts or their logarithms?

Take logs, in almost every case, and the reason comes straight from the limitation named above.

Registration counts have variance that grows with the level. A quiet Monday of 372 expected rows might vary by twenty or thirty either way. A show week Tuesday of 3,850 varies by hundreds. An additive decomposition fits one remainder distribution across both, so the control limit ends up too wide for the quiet days and too narrow for the busy ones, and your alerts cluster in show week for no reason other than arithmetic.

Decomposing the logarithm converts that into a proportional model. On the show week Tuesday, 4,000 against an expected 3,850 is a ratio of 1.039, so the day ran 3.9 per cent above expectation. On the quiet Monday, 400 against 372 is 1.075, or 7.5 per cent above. In log space those two are directly comparable, and the second day is the more unusual of the pair even though its absolute remainder is a fifth of the size.

One practical detail. A log transform needs strictly positive values, and an events series contains genuine zeros in the closed season. Either fit the decomposition only on the active part of the campaign, or add one to every count before taking logs and remember to reverse it, and be aware that the second choice distorts the small values you care least about.

What the decomposition will not catch

The calendar effects STL leaves alone are exactly the ones an events business has most of.

Easter moves. A campaign that runs through March in one edition and April in the next has its holiday effect in a different week each time, and the annual seasonal component fits an average of the two, which is wrong for both. Public holidays differ by country, so a show with an international audience has several overlapping calendars. Marketing sends produce spikes that are entirely legitimate and entirely unpredictable from the series alone.

There are two workable responses. Add the known events as explanatory variables, which turns the exercise into a regression with a seasonal term and moves it toward forecasting, which is cluster O's ground. Or keep the decomposition simple and maintain a small suppression calendar of dates where alerts are downgraded to warnings, which takes an hour a year and handles most of it.

The suppression calendar has a second benefit. It is a written record of what the team believed was unusual, which is the beginning of the evidence you need the next time somebody claims a spike was organic.

How does this differ from comparing against prior editions?

Both approaches remove seasonality and they remove different amounts of it, so it is worth being clear about which problem each one solves.

Comparing today against the same index in prior editions, which is the expected range approach K18 sets out, uses the other editions as the model and needs no fitting. It handles the annual shape well, it needs at least three editions, and it is easy to explain to somebody who does not want to hear the word loess.

A decomposition uses the series' own recent history as the model. It handles the weekly pattern properly, it adapts as the campaign shape changes, and it works on a show with one edition of history where the prior year comparison cannot run at all. It costs a fit, a transformation decision and a limit.

Run the decomposition where you have daily data and a person who can maintain it. Run the prior edition comparison everywhere else. Neither of them handles the case where the correct count is zero, which needs a gate on the show calendar and is K20's subject.

Where this stops

The method needs history it often does not have, and it is retrospective in a way that surprises people.

Two full cycles of the longest period you want to remove is the practical minimum, so weekly seasonality is available within a few months and annual seasonality needs two editions. Even then, an annual peak is observed once per edition, so five editions gives five observations of the busiest fortnight of the year, and no amount of daily data changes that. The seasonal component around show week is therefore the least well estimated part of the fit and the part where the business most wants precision.

The retrospective problem is subtler. Refit the decomposition each night on a growing series and yesterday's remainder changes, because the trend and seasonal estimates have moved. An alert that fired on Tuesday may not reproduce on Friday, which makes incident review confusing unless you store the remainder as computed at the time rather than recomputing it. Store the fitted values with the alert, in the quality layer of the unified data record, and the argument goes away.

This week, pull 365 days of daily registration counts for one show into whatever tool you have, run a seasonal and trend decomposition with a weekly period, and plot the remainder. Look at the largest ten deviations and see how many of them you can explain from memory. The ones you cannot explain are the incidents your current monitoring missed.