The majority of paid search accounts I’ve managed have run on a monthly budget. Being able to land on this budget at the end of each month while reaching or exceeding goals on other metrics is the name of the game. In order to see where your budget is pacing, it is a simple formula, right? That formula might look like this in Excel:
=(Current Spend/Amount of Days)*Total Days in the Month
This formula gives you the current daily spend average and multiplies it by the total amount of days in the month. Therefore projecting out each day to be an average day in terms of spend. Makes sense, right?
What this formula does not consider is the different amount of clicks received by day of week, or the recent changes you have made to the account that might impact the spend levels.
Budgeting By Day Of Week
A lot of accounts receive a drastically different amount of clicks per day of the week. An example of this scenario is business-to-business organizations, which normally receive less traffic on the weekends. Some companies see conversion rate drops on the weekends so bids are lowered during these days. Some ecommerce accounts will see that Sundays (when a lot of people are shopping from home) are their strongest days in terms of sales so they might raise bids to receive more traffic on this day. In all of these scenarios the common denominator is that you can not treat each day of the week the same when budgeting.
How can you budget based on the day of week? There are a few ways to go about this and each way essentially does the same thing.
Day of Week Projections Sheet
One way that a colleague of mine, Kevin Klein, who is known as a guy who doesn’t follow best practices, separates out day of week when budgeting is by utilizing an Excel sheet setup like below.
This sheet breaks out average spend per weekday, and takes into account the amount of days that have passed, along with the amount of days remaining for each day of the week. In the end it uses the average spend amounts for that day of the week in order to come up with the projected spend. This particular month appears to have started on a Sunday so adjustments must be made to budgeting around having five Sundays in the month. Trends would be different in months that begin on Mondays.
In order to get the total spend numbers by weekday for the month utilize the dimensions tab. Be sure the date range shows the entire month, and in the view section select “Time > Day of the Week” as shown below.
Here are the formulas associated with the Excel sheet used in this example.
Calendar Projection Sheet
In order to visualize this kind of projecting process, use calendar reporting. The reason it is called calendar reporting is because it is set up just like a calendar within an Excel sheet. After the first seven days of the month you can begin using the calendar and filling out spend numbers each day. Below is an example that does exactly what the sheet above shows by taking day of week into consideration when projecting spend.
Here is what the spend and projected spend numbers would look like using this calendar approach.
Once you get deeper into the month the projections will adjust based on the day of week average (as the previous budget sheet did). Each day you would go in and log the spend across all platforms on the calendar.
The calendar can also be developed to only base future spend on the previous weeks performance. This actually makes the formulas on the calendar easier to develop as seen below.
This strategy makes things very simple, and as days are filled out with actual spend the projected budget is adjusted. One issue with this formula is if one day has a high spend anomaly, projections will be made off of that day through the rest of the month.
The final version of the calendar report takes the average difference across the week after the initial two weeks worth of monthly performance to show what your optimizations have done to spend on a week over week average. This is a great sheet to utilize unless your optimizations included day of week bid modifiers. Below are the formulas associated with this calendar.
The calendar reporting technique is a great visual way to make sure you keep up with the spend trends within your accounts. Filling out spend numbers daily and having the automated adjustments to the monthly projection is helpful.
Weekly Spend Projections
If you are looking for a weekly update on spend the set up below is an easy way to get a feel for projections based upon optimizations from the previous 7 days.
This simple formula is similar to the last calendar projection sheet presented, but it gives numbers on a weekly rather than a daily level, so it is not as visually appealing.
Conclusion
Overall there are a lot of techniques advertisers can utilize for budgeting within Excel. The focus here was making sure budgeting is being done to accommodate the differences in the day of the week, and for the optimizations being made on the account throughout the week (whether on a daily, weekly, or monthly level). I would be very happy to see others share their budgeting ideas in the comments below.
