How To Find Keywords That Generated Organic Traffic But Don’t Anymore

• Buffer
• Tweet
• 

When we talk about SEO measurement strategies we talk alot about keyword diversity as a Primary KPI. Ensuring that you are on a consistent trend of increasing the number of keywords or keyphrases that are sending traffic to your website from organic search is a great indicator of the level of value Google and other search engines are giving to your website and its content.

We often get asked questions such as “What was I ranking for last month compared to this month?”, “Which keywords are sending organic traffic this month that were not last month?”, and even “How do I know if I was hit by a recent Google update?”. This how-to guide will help you find the answers to those very questions by using Google Analytics Data and every SEOs second favorite tool, Microsoft Excel to trend keywords sending traffic.

Phase 1: Export The Data From Google Analytics

What data will you need?
For the first steps in the report we will need to get only the organic traffic to the website so that we don’t pollute the data with PPC or other referring data.

1. Click on Traffic Sources in the left hand nav inside Google Analytics.
2. Click on overview.
3. If you are running any other campaign such as PPC you will need to then click on the Search Tab that shows up under the traffic section, and then on Organic Traffic. This will filter out any PPC or other traffic sources (if your GA is set up properly).

Getting the Total Keywords Sending Traffic and Outputting More Than 500 Rows.

1. Once you are in the overview of your organic traffic, scroll down, and find the “view full report link” under your top keywords overview. (click it)



2. >By clicking this link it will take you to the full report view.
3. First step is scrolling to the bottom of the report and find the drop down menu for “Show Rows”. Click on it and change it to 25. You will notice the page refreshes and you now have 25 keywords showing.
4. If you scroll to the bottom of the page you will see next to the “Show Rows” numbers that say 1 of X,XXX. Note the X,XXX number as we will need it in the next step.

   

How to export more than 500 rows of keywords from Google Analytics?

1. Once you have completed the above steps you will need to scroll back to the top of the page and in your browser bar find the part of the URL that says rowCount%3D25/ (assuming you changed the show rows number to 25).



2. Once finding this refer back to the note you took in step 3 above that showed your total number of keywords (1 of X,XXX). Replace the 25 with your total number of keywords and hit ENTER. The page will refresh again.
3. You will now notice when you scroll down that all your keywords that have send at least 1 visit from Google over the specified time period are on one page.
4. The next step is to export this list to EXCEL.
   1. Scroll back to the top of the page, if you are not already there and click on Export.
   2. Choose CSV

   

   3. Save file to computer

Phase 2: Excel: How to Compare One Column of Keywords To Another

Now that you understand how to get the data that we will need to compare keywords it’s time to utilize Microsoft Excel to compare two date ranges to determine which keywords stopped sending traffic or which keyword are new to the list of organic referring keywords.

1. Export the two time periods from Google Analytics that you want to compare following the steps shown above.
2. Place one row of keywords in Column A, and the other row of keywords in Column B.



3. Past the formula below in column C.
   1. This is done by clicking on the first cell in column C.
   2. Then go to the formula bar and type the following formula.

   

   3. Hit enter
4. Once you hit Enter you should see a “Yes” or “No” in column C.
5. If you see the “Yes” or “No”, that means you have done it correctly.
6. You can now proceed to copy the formula into each cell in column C.
   1. This can be done by clicking on the first cell in Column C.
   2. You should see it highlighted and in the lower right hand corner of that cell you will see a little blue box.
   3. If you double click on that box it should copy the formula all the way down column C.
   4. If this is done correctly you will see “Yes” and “No” all the way down that column.
7. What this formula does, is check column B for the value in Column A. If it’s there, then it will output the “Yes”, if it is in column A but not in Column B (meaning you no longer are receiving traffic for that keyword) it will output a “No”.

What This Shows

You can then use this data to determine if your website has lost value in the search results across certain types of keywords or keyword sets. If you have this could be an indication of a loss in value due to a recent Google update such as Panda or Penguin.

Bill is the Lead Linchpin at the company and focuses on SEO and content strategy. Bill’s ten-year background in digital marketing, SEO, and building online companies has helped him rise to strategic lead for some of the largest online brands and content websites in the world. Bill believes that at the core of a website's success sits the value that website provides it users.

Click here to learn more about LinchpinSEO's Lead Linchpin.