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.

Google Trends for Keyword Real Estate

A comparison with data available on Google Keyword Planner shows an entirely different picture, which even seems to suggest that this market is growing.

Keyword Planner for keyword Real Estate

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

Google Trends’ chart is nicely coded with JavaScript, which lead me to suspect that the data, which is always shown as monthly values, might be hidden somewhere in the source code. It is, and here is how you access it.

Inspect Element

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:

Google Trends: Inspect Element

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.

Google Trends Inspect Element

Now have a look at the code box. A few lines underneath your selected element, you will see a line containing script. Open it.

Google Trends JavaScript

You should see the following.

Google Trends JavaScript values

Select the line starting with “var” and copy.

Excel Magic

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: Text in columns

In the wizard, choose “Separated” and proceed to the next screen. In there, we are going to check only the box that says “Space” and click on “Finish”. Next, select cell A1 once more and copy in your JavaScript. This should look like the following:

Google Trends values in Excel

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).

Transpose in Excel

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:

Google Trends filtered in Excel

Your data is in column D, starting at January 2004. Before you copy everything over into your data set, however, be sure to check the column for a “null” value. There should be one, with the correct value one or two columns over. Correct this, then copy over your data and be sure to get rid of the values that fall out of your date range, there seem to be a couple extra included in the JavaScript sometimes. Don’t worry though, if you start copying at the beginning, everything will be in the right order. Of course, it’s easy to cross-reference your results in Google Trends itself as well.

Finished!

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!