If you’re involved in doing keyword research, there may have been a time where you needed historical keyword data for your project. Seeing a trend that spans not the usual two years, but a grand total of ten gives a bit more insight. Enter Google Trends.
Google Trends Analysis
Google Trends is an extremely powerful research tool that lets you see how different parts of the search market have developed over the past ten years. A search for the keyword “real estate” for example shows a steady decline since January 2004.
A comparison with data available on Google Keyword Planner shows an entirely different picture, which even seems to suggest that this market is growing.
Immediately, you can see the value of using Google Trends. It’s even possible to do further calculations with the trends in order to estimate actual search volumes for these periods.
Google Trends Search Tool: An Issue and a Fix
Google Trends allows you to export a CSV containing the data you’ve researched. Based on the keyword popularity, the system decides on its own whether to export this data as monthly or yearly values, which has had me (nearly) pulling out my hair in the past. If you’re building a data set with this tool it can be very frustrating when one export gives you monthly data, whereas another gives it to you in years. Luckily for you, it was enough of a pain for me to develop a work-around. Please feel free to follow along as I apply this technique to the keyword “real estate”.
Pulling Monthly Data from Google Trends
In the browser of your choice (I’m using Chrome in this example) right-click anywhere in the page and choose “Inspect Element”. This will open the box shown in the screenshot, which many of you will be familiar with:
Select the magnifying glass and hover over one of the roll-over boxes that show index numbers. Make sure it’s highlighted and select it by clicking.
Now have a look at the code box. A few lines underneath your selected element, you will see a line containing script. Open it.
You should see the following.
Select the line starting with “var” and copy.
This is where the fun starts. Open up a new excel sheet and type anything in the first cell. Then select the entire column A, navigate to “Data” and choose “Text in Columns”. The icon looks like this:
You’re still following along? Good. Now select Row 2 in its entirety, move down to an empty part of the sheet and transpose the data (see screenshot).
Apply a filter to the top cell (var) and filter for “null,null”. You will see a list with values and months, which is perfect. It looks a bit like a jumble though, so we will clean it up with another “Text in Columns”. Select all relevant data, but this time split for “Comma”. The result:
So there it is! I understand this is a bit of a hack, and it may seem a bit complicated, but with a little practice you’ll be extracting data in no time. Please let me know if there’s any additional questions or better solutions for this issue. Cheers!