Plan Monthly Marketing Budgets With This Easy Google Trends Hack
You’ve just decided your annual marketing budget for 2018. Chances are you now need to plan how to distribute your budget over the next 12 months.
The easiest solution is to take your annual budget and divide by 12. This doesn’t feel right though. Some months are busier than others.
What about bank holidays? They might be busier, or quieter, than your average day. School holidays might be a boon for you, or perhaps they’re a lean spell? Then there’s Christmas too…
To make smart use of our marketing budgets and get the best overall return on your investment, we need to allocate our budgets strategically. Spend more when our customers are most interested in buying, spend less when the interest cools.
To help you, we’re sharing a simple technique for planning monthly marketing budget allocation.
To begin with, you need 3 things:
1. Access to Google Trends (https://trends.google.co.uk/trends/)
2. Keyword data from your Search Marketing campaigns — for example an Adwords keyword report from the last 30 days and another one from the last 12 months
3. An empty spreadsheet
Step 1. Open Google Trends.
Google Trends is a free tool from Google. It can show you real-time and historical search interest for any keyword you choose, relative to the overall search interest, in any country anywhere in the world.
Results are displayed in an index on a range of 0 to 100. 0 represents low or negligible search interest. 100 represents the peak, when relative search interest was at its highest.
(If you’re interested in the statistical approach you can read how Google Trends works here: https://support.google.com/trends/?hl=en#topic=6248052)
For example, let’s look at Gin.
We enter our keyword “Gin”, set our country and date range, and we can then see the search interest for Gin in the UK during 2016:
UK interest in Gin sees a brief rise in interest at the beginning of June, then more sustained interest in August (seemingly correlating with the start and end of School holidays), before peaking in the weeks before Christmas. Gin can be a great gift.
If we were marketing Gin, we would want to keep a big slice of our annual budget for November and December.
Different keyword themes have different interest trends though. If we were selling Champagne as well as Gin, our budget plan would need to adapt.
Interest in Champagne surpassed Gin only twice during the year, during the first week of January and in the week before Valentine’s day. Search interest for Champagne rises again in Summer months, then peak immediately after Christmas during the week leading in to New Year’s Eve.
Step 2. Open Keyword data.
To get a reasonable idea of how to deploy our budgets we need to pick a handful of keywords that are representative of our core product ranges. Google Trends allows us to compare up to five keywords, which is usually sufficient.
To choose our keywords we can use our keyword reports. We’re looking for our “head” keywords; the ones that bring us the most visitors, the most sales and the most revenue.
We can use a 12-month report to see which keywords brought us the most over the year, then sense check these keywords are still current by using our last-30-days report.
Our lower volume keywords — “Tail” keywords — will tend to be variations on the head keyword themes, making the head keywords a useful proxy for our wider campaigns.
Step 3. Back to Google Trends.
Once we have chosen our head keywords we can put them into Google Trends.
Before we proceed further we need to check the related queries. Scroll down in Google Trends. Related queries are shown beneath the trend lines graph. The related queries influence the trend lines that we’re basing our plans on. If there are any related queries that are not relevant to our business, we need to take them out.
If we don’t sell Gin Baubles we can excluded this query by entering our keyword as “Gin -baubles”. The minus sign excludes the keyword from the data:
One we’re happy that the data reflects the search terms that matter to our business we can download the data. Above the graph there is a download button. Click it to get a .csv spreadsheet table. Your table will look like this:
The table shows an interest index (scores from 0 to 100) segmented by week. 0 represents no search interest, 100 shows peak interest. The scores are relative to each other, meaning that in this table “gin” in the week before Christmas scores 100 while “Champagne” scores 67. If we take “Gin” out, the peak for “Champagne” becomes our new 100. This helps us to balance our budgets for the relative levels of interest in each of our product areas.
Step 4. Using the data.
Planning our budget distribution by week is possible, however key events such as bank holidays might fall in different weeks from year to year. Weather might influence search interest, however the hottest and coldest weeks of year will also change.
Planning budget distribution by month is usually more reliable and allows decisions about weekly budgets to be made closer to the event.
Our table doesn’t have a column for Months, so we need to add one. Insert a column and add a month next to the week. It doesn’t need to be precise. If 4 days land in June and 3 in July, enter the month value as “June”.
Next, add a “Totals” column. Sum the interest values in each row:
Next, we select the table data and insert a pivot table:
Once we have our pivot table open, we need to adjust the Value Field settings. The default in Excel is “Sum of totals”. Some months in our table have five weeks, others have four. If we use “Sum” it will skew our results. Click the drop-down arrow next to “Sum of totals” and change the setting to “Average”:
Now we have a pivot table showing the cumulative interest levels across all our products. We need to turn this back into an index with scores from 0 to 100. Copy the pivot table data and “paste as values” into a new table. Add a “Totals” row at the bottom and sum the values from “Average of Totals”. Then add a new column labelled “Index” and add a formula as follows:
=Monthly Average of Total / Sum of Average of Totals * 12
You can “dollarize” your “sum of” cell reference so the value doesn’t change when you copy the formula down to the other cells:
Now that we have an index we can apply it to our budget. Add another column labelled “Monthly Budget”. In the Total cell at the bottom of the column we can add out total marketing budget for the year. Add a new formula for each row:
=Annual budget / 12 * Monthly Index
Boom. We have a monthly budget plan:
Step 5. Sense checking.
Sense check #1. Maths. We want to make sure we’ve done our calculations correctly. Graphs are an easy way to do this. Create a line graph to show the monthly budgets and compare it to the original Google Trends graph. If the two graphs have the same shape, we can be confident we’ve got our maths right.
Sense check #2. Correlation. Trends data do not always correlate neatly with business data. Do the data from Google Trends resemble the trends we’ve seen in our own historical business data? Did we see more revenue in the same months that Google Trends reported more search interest? If not, can we explain why not?
Sense check #3. Year on year growth and decline. Few markets are stable from one year to the next. We need to understand if our target audience next year will grow or shrink. Return to Google Trends and change the date range from “Last Year” to “Past 5 years”.
We can see from the past 5 years’ view that our annual budgets might need to grow if we want to maintain our share of the Gin market.
Sense check #4. Influence of non-annual events. While seasonal events and holiday times occur every year, there are other cultural events that do not. Examples include Olympics, Royal Weddings and International Football. If our business sells football shirts during a World Cup Year we’ll need to look at the trends from at least the last four years and estimate what the event will mean for our budget planning:
If our business is influenced by non-annual events then we can base our budget distribution on trends data from the last time the event occurred, rather than last years’ data.
Sense check #5. Peer review. Share the data and the plan with your colleagues, your marketing team, your agency; can they spot any flaws or offer any extra advice? This can also help to ensure everyone is on board with our plan before we proceed.
Conclusion
Plans are useless, but planning is indispensable.
Almost certainly there will be an unpredictable event in the next 12 months that disrupts our plans. Be ready to recalculate and redistribute budgets whenever such an event becomes apparent. Having a plan before we set out means that we can easily adapt rather than losing our momentum.
If you’re lucky and your plan goes according to plan, then let us know and join us in popping a champagne cork. Merry Christmas & Happy New Year!
