One thing that is certain, apart from the proverbial Death and Taxes, is that if you run a digital marketing account, you’re going to need to use Excel. In addition, you’re going to need to deal with a bunch of data.
ESV Digital has come to the rescue with a couple of free Excel tools that focus on keyword analysis (the content of keywords)
The first tool can help you forecast a year’s worth of monthly spend and revenue performance based on 24 months of previous history. It uses a couple of forecasting techniques to give you the most realistic outcome month to month.
- It has to have monthly data covering a full 24 months to work; no more, no less.
- Include only the columns indicated in the instructions.
- The tool lets you set spend increase and Average Order Value increase estimates to see how the trends may be affected. The settings of the latter on the ROI tab forces the same in the other KPI tabs.
Scan Keywords for Themes
The second tool performs two functions. One tab will let you paste a set of keywords (or search queries) and tell you if there seems to be at least one of the UK or US city, town, state, country, a day or a month.
If a keyword has more than one, like a city and month, it’ll say so.
Another tab lets you paste your keyword list, and then paste or type in a custom list of terms you want to find – the examples here are often qualifying terms – so you can gather the most common themes of keywords to help you decide on an account structure (ad group themes etc.). As a bonus, you get a count of the number of words in each keyword phrase.
It is built for 1,000 keywords at a time right now but you can extend this by copying the formula down to the number of keywords you have. You can search for up to 6 terms.
One great scenario for this tool is to find irrelevant searches from search queries so you can exclude them – e.g. “second hand” and “used” if you sell only new items. But there are quite a few uses to which this tool can be put.
We hope these tools are of value to you and give you some of your precious time back!