This is the fourth post in this series on online advertising revenue forecasting which has included thus far:
- Why do you need to reconsider your forecasting models?
- What are the current methods used to forecast advertising revenues?
- What are the required data points, for what period and from which sources, needed for proper advertising revenue forecasting?
I used my forecasting tool for 3 years, constantly keeping it up to date; even though some numbers started varying from month to month where projected and actuals were concerned, when I looked at quarterly or the annual forecast it ended up bang on with the actual yearend figures.
In my last post I spelled out which data points you needed on a monthly basis, going as far back as you could. Here I will explain in detail how to set up your traffic forecasting tool. Here again are the data points you’ll need:
– Monthly unique users of visitors
– Monthly total page views
– Monthly home page views
– Monthly section page views, for any section that’s of significant interest to you for sponsorship or integration purposes down the road.
For this exercise, I’ve taken numbers from one website I used to manage as an example. You’ll see I’ve filled in on the horizontal months. They actually start at December 2005, but for the sake of clarity I’ve only shown here 2009 through to the end of 2014. In other words I’ve taken the last three years’ worth of data for forecast the next 2. Ideally, you should try to go as far back as you possibly can, even if it’s only one month here and there – it’ll give you more background on which to rely.
On one line you’ll integrate your UV numbers per month for as far back as you can up to the last complete month. Be careful to use Unique Viewers and not unique visits or visits or visitors – every analytics suite offers a bunch of variations of this data, but Unique Visitors will give you the common denominator we’re looking for. On the line below it calculate as a percentage the growth rate from one month to the next (the formula would look like this: =AN3/AM3). This will become useful later.
Next plot out on another line the monthly total Page Views number for the entire website for the same months you did for UVs. Do not round any numbers unless you have no choice (rounding will importantly affect the ratios we’ll later use). On the line below this create a ratio for page viewed by unique visitors (pv/uv or =AN5/AN3) as a number with 1 or 2 decimal points as you wish. Please ensure to add notes to any specific months where a particular event occurred. These should include when the website got a makeover or was relaunched, if you made any major changes to the content in any given month, if a new competitor launched or became significant a one point, and even when you’ve run ad campaigns or major promotions to generate more traffic or reach to your website.
You can further add your Home Page page views, and on separate lines below you can add every section for which future planning could be useful. It’s easier to add them now as you build it and back tracking the same data 6 months down the road when a request comes in.
By the way, if all this information is available in a database of sorts, than you can either pull out or build a report, or export the data for the Ad Revenue part of this exercise which will be explained in the next week’s post.
As you see below, I’ve put in the numbers for January 2009 through (across the 3 images below) to December 2011. Everything beyond that is forecasting. Click on the image to expand it.
Tracking UV Growth
Below your data (numbers in black), you’ll add at least 3 more lines of formulas (more if you’ve added rows for specific section page views, one for each). These can only begin on the 13th month of your numbers. The first ratio will calculate the average growth for every prior same month over the previous for however far back your data goes. In this case we star in January 2010 (13th month of shown data), but as I said my data went back to 2005. The formula would look like this: =average(AZ4;AN4;AB4;P4;D4) where AZ4 is January 2010’s growth over December 2009, AN4 is January 2009’s growth over December 2008, and so on, D4 being the first historical instance of your data. Copy this formula to the right until the next 13th month (the next January in this case). Your formula should now look like this: =average(BL4;AZ4;AN4;AB4;P4), i.e. the columns have followed through 12 times to the right. Most importantly, here you will add to your formula the first instance of your data (D4) to add another year to the average, further stabilizing it.
Relying on the growth of just one particular year could lead to huge variations between forecasting and reality because a special unforeseeable event and not necessarily repeatable in the same month next year, could have given you huge growth or shrinkage. That’s why I average it over as many years as possible.
You’ll notice cell BH8 is highlighted in yellow and the font is in red. That’s to highlight where I’ve manually adjusted the formula you should normally repeat every month for as far forward as you have data to track (normally until last month). I’ve manually removed one month from this calculation, for every January going forward because January 2007 had 600%+ growth which is extraordinary and probably cannot be repeated (has not been repeated since). I consider that piece of data an anomaly so I don’t use it for forecasting. Any other such weird number or ratio needs to be highlighted and noted on to explain why such a month experienced a particular spike or dip. Even if you don’t use this info going forward as part of the regular formula, you might refer to if a similar event be foreseeable, then you would use that data and adjust it if need be.
Tracking PVs per UVs
Next you’ll set up, as in the case above, a recurring formula, starting on that same 13th month, averaging page views per unique viewer per month. The formula should look like this: =average(AZ6;AN6;AB6;P6;D6). As with the case above, you’ll want to re-add the “D6” cell to your formula every 13th month going forward so you’re always considering your entire historical data set.
This is important for forecasting because this is how your unique visitors behave on your site. If in a given month you have a recurring feature that generates more traffic than in other months, this model will factor it in for that month in the future. Your traffic grows and shrinks from month to month based on your unique visitors’ interests and needs. This could also be labeled seasonality.
Again as with the data above, should you see any number in a particular year that’s really out of what with the others, I’d remove that cell from the formula going forward and highlight it so you can always remember what data is “straight forecasting” and what’s been “adjusted.” This ratio will be used to forecast page views in future months based on the forecasted reach number (UVs) for that given month.
Tracking your home page’s share of your website
Repeat the same process here, however you’ll be averaging the home page’s share of total page view for every prior same month (ex.: all January months going back). This “share” should be repeated for every other section you’ve opted to include in your forecasting model. The formula should look like this: =average(AZ7;AN7;AB7;O7;D7)/MOYENNE(AZ5;AN5;AB5;O5;D5). Do not forget to add the first set of data every 13th month to include another year to your average.
This particular tracking is important because as for total page views, traffic on your site is never the exact same from month to month. As you see in my example above, the home page’s share of the total site varies from 34% to 51%. If you want to forecast a particular section’s probable traffic or total inventory for a month in the future, you should not rely on that section’s average share in general, but for that specific month, average over many years to get as close as possible to what will really happen. This ratio will be used to forecast your home page’s page views for a future month, based on that month’s average share of total.
So now you have your historical data, ratios and averages plotted out. Now let’s make those numbers work FOR you.
Forecasting UV Growth
As of your current month and future months, this is where you enter into forecasting proper. The basis of all our forecasting begins with UVs so you will now access the formula for the average growth for this month (January) over the past years and add that growth to your known UV count of the previous month. Explained differently, here I would take the average growth from December to January for every year I have data available (Dec.09-Jan.10 & Dec.10-Jan.11 at a minimum) which in this case is 107% (see cell BL8) and I multiply that average growth to my known December 2011 unique viewer count in cell BW3 to arrive at a prediction in cell BX3 (notice the growth expected in Jan12 vs Dec11 is the same as the average for previous years).
The formula in cell BX3 would look like this: =BW3*BL8. You can then copy this formula for every month going forward (see below I’ve gone up to December 2014 which is 3 years out).
You can further copy the % change, or growth formula in row 4, onwards to match up with however far into the future you choose to go.
Forecasting PV Growth
Now that we have a historical average pages viewed by unique visitor for a given month, and a forecasted unique viewer figure, we can multiply one by the other to forecast page views for a given month. In this case, I’d take the average pv/uv from January(s) past in cell BL9 and multiply with BX3 (UV estimate for Jan.12) to get expected total page views for this same month in cell BX5 (notice the pv/uv figure in BX6 is the same as the average of previous years). The formula would look like this: =BX3*BL9.
You can then copy both the forecast PV formula and the pv/uv formula for all months going forward (however far you wish to see).
Forecasting Home Page Traffic
Similarly, you’ll apply the average share for a specific page or section (home page in this example) to the now expected total page views, to see how much traffic would should expect for this section or page. In this case, we’re taking total page views in cell BX5 and multiplying it with the average share the home page has on past January months (found in cell BL10). The formula should look like this: =BX5*BL10.
Again you can copy this for every month you want to see going forward.
Further, you can now copy the colored averages below your forecasts for all coming months – saving you time in the future. You’ll just need to adjust any specific spike or dip you want to neglect from forecasting + add the original month’s data cells to the formula on every 13th month to track another year in your average, to predict not only next year, but the ones after that too.
In this case, based on historical data going back to December 2005, I can forecast with a high degree of confidence and accuracy that the unique viewer count for the month of December 2014 will reach almost 430 000, which is 21% higher than it was in December 2009.
Next steps:
What you need to do from here on in, is update this spreadsheet with every new month’s actual data (i.e. in February you’ll enter the January data). When you do this, take a mental note of how close or different your actual number is from the forecast. Then try to figure out what affected the difference if it’s significant. Also, you will notice that adding new actual data that’s different from the forecast automatically affects all future numbers. Changing the January number for reach for example, would affect all future January numbers for the years ahead in your spreadsheet AND it would affect every other month because the UV forecast is based on a particular month’s average growth multiplied by the previous month – it will thus affect February, March, and so on.
Do not worry when one new month’s worth of data skews all your future projections. Over the 3 years I worked with this model it did go up and down every month, however quarterly averages and annual averages will remain fairly stable over time – unless major occurrences impact your day to day business.
This may seem very intense and time consuming or even overkill to some of you. I’d like to repeat that the more granular the data you consider, the more precise your future forecasting will be. If ultimately budgets are affected by any type of forecasting, I’ve often found that more precision is always better – then you can add whatever buffers you want, but you’ll at least have a really clear idea of what to expect.
From here there are some manual adjustments you can make. Looking forward, you probably have an idea when you might launch a new ad campaign, new section or overhaul the website. Looking back you’ll glimpse an idea of what impact similar occurrences had on your UV and PV counts. You can then factor that kind of behavior (which you’d normally cancel out of your forecasting) to predict that a relaunch of the website will give it a 50% boost in UVs (affecting PV for that month, and all future forecasting).
Please make clearly identifiable notes wherever you manually adjust the formulas or numbers to take into account factors your history doesn’t cover (as discussed in the previous paragraph).
My next post will spell out in the same manner as this one, the precise method of tying together your website’s reach and traffic forecast to forecast ad revenue that’s built on a solid foundation.
