Forecasting by monthly run rate: Do it the right way

Monthly spend forecasting can often be a thorn in the side of a search marketer, as it is not always an easy question.  For today’s article, we will be focusing on calculating monthly run rates, and two relatively simple ways to do so.

The first, and least accurate, method involves just looking at a certain lookback, calculating the daily average spend, and applying that to the remaining days in the month.  I don’t need to tell any of you the big reason this could be inaccurate, but I am going to anyway.  This approach does not take into account the remaining days of the week, which can be a massive factor in almost any account.

To adjust for this, I prefer using a second approach that takes into account average spend by day of the week, and apply that to the remainder of the month.  This becomes more valuable as you get deeper into the month and fewer days remain to be forecasted.  The approach might sound tedious, but Excel makes it quite easy.

To start, download a day-of-week report in AdWords for a timeframe that makes sense for your account.  Accounts with low volume or accounts that can fluctuate dramatically day to day may want to look farther back.  If spend is pretty consistent week over week, you can get away with a shorter lookback.  Or, if you have made significant changes in the last two weeks, you may just want to look at the last week’s data.  The biggest thing is to make sure the lookback period is a multiple of 7.

Once that is pulled, you need to determine what your reference spend will be for each day of the week.  If you pulled one week of data, you don’t need to change spend numbers.  Otherwise, you will want to just divide spend by # of weeks included in the report.

Once this table is created, you need to take a second report that has the current month’s numbers through yesterday.  In Excel, fill down the date column to include the rest of the month.  Add another column titled Day of Week, and fill its cells with the formula =TEXT(<datecell>, “ddd”). From there you need only to a quick vLookup to fill in day of week averages for the remainder of the month.

Why bother splitting things out by day of week?  A common mistake I see is toward the end of the month, an account is on a fixed budget and overspending.  An easy assumption to make is that each day will spend equally, ignoring that weekends might spend less.  This leads to overcompensating and perhaps reducing spend during the workweek by too much, creating underspend for the entire month.