Often times SEO companies will target a list of keywords for optimization. Month over month you will get ranking reports showing how you are doing in the various search results pages (SERPs). Even when tied to overall traffic numbers, or branded vs. non-branded, these reports do not fully show you how the website is doing in terms of the target keywords driving traffic to the website.
Using Google Analytics and excel makes it a relatively simple task to evaluate over a defined period of time how many visits a specific keyword is sending to the site. At first glance you may say, but it is easy to simply search the analytics database for a specific keyword. I agree this is not a difficult task. However, searching 70 times may be a bit more challenging when done on a weekly basis. In addition to identifying exact search terms that are driving traffic, often times marketers will refer to long tail variations driving traffic.
There two things I plan to show you. The First is how to extract the data we need from google analytics. The second item is how to then take this data and identify which keywords are performing well and which ones are not. From a performance standpoint we can look at visits, goal completions, or even revenue. (note, this demo will not show you how to setup ecommerce or goals as that is a whole separate topic)
Step 1: Extracting the Needed Data from Google Analytics
The First step is to login to your Analytics account and select the profile you are looking to analyze. With the Profile selected, you are going to want to navigate to the Traffic Sources – Incoming Sources – Search – Organic Section. (see Figure 1)
Next you are going to need to export the data. With the new version of Google Analytics, by default you are limited to exporting 500 items at a time. If you are running this report on a moderate size website with a timeframe of 6 months to a year, it is highly plausible that you have more than 500 search terms driving traffic to the site.
There is a workaround for this problem. What you do is set the show rows to 500. (see Figure 2)
Then, you will notice the URL gets much longer. At the end of the URL find the text that says : table.rowCount%3D500/ (see Figure 3)
The 500 in “table.rowCount%3D500/” is the row count. If you replace this with a number larger than the total number of results you will be able to see all results on one screen, and as such your export will contain all results. In my example I set this to 5500. (see Figure 4)
Note: For those of you with 20,000+ results, I apologize but you can only export 20,000 at a time and you will need to either export in 20,000 result increments or reduce your timeframe such that under 20,0000 results appear.
With the full data set in view, you can now export your results. At the top of the page you will see an export option, select CSV for Excel. (see Figure 5)
With the Data Exported successfully, you have completed step 1!
Step 2: Doing an Analysis of the Data
With the data successfully exported, we now want to see how successful we are in driving traffic to the site for target keywords (and longtail variations of those keywords). The first step after opening your export results from Google is to paste your list of target keywords into a new tab. (see Figure 6)
You can see here that I have now opened the exported results and created a new tab. I have then taken my target keyword list and pasted it into the new tab:
(Note, the example site I am using is my sisters book blog. If you are interested in young Adult books, check out her site! ) (see Figure 7)
An important thing to note is the word “Keyword” in Cell A1. It is important to have the headings match in all keyword lists. The next step is to setup this keyword list to work with Excels Advanced Filter to extract both the Exact Match and Longtail variations of the keywords from our Google Analyics Data. To do this we will need to add some columns. The first column I am going to add is an *. This will be used for pulling out the long tail variations. The Second column is going to be an =. This is used in pulling out the Exact match keywords. To easily add these characters to each cell in the column, you can simply double click on the bottom right corner of the filled in cell. You will know you are click in the correct spot as the cursor will change. (see Figure 8 )
With theses columns in place I can setup my “Criteria Range” lists. For my Long Tail list I am going to use the concatenate function to append the * before and after my target keyword. For the Exact Match I am also using concatenate, however the setup is simple =Keyword. (see Figure 9)
The Formula for D2 is: =CONCATENATE(B2,A2,B2)
The formula for E2 is: =CONCATENATE(C2,A2)
Each fomula is then repeated down the line.
Note: The word Keyword at the top of the keyword lists, as mentioned previously, this is an important element to the success of using the advanced filter!
Now that the Criteria range are setup, you will want to make new tabs to place your data. I setup a Tab for Exact Match and a Tab for longtail. (see Figure 10)
Now it is time to take the data you have prepared and run the advanced filter. Depending on the version of excel you are running, the location of the advanced filter varies. I will leave it to you to find it.
With Excel open to your Longtail tab(sheet), select Advanced Filter. (see Figure 11)
There are 4 things you will have to fill in/select.
First, the radio buttons. Select “Copy to another location”
Second, List Range. This is going to be your Google Analytics Data. To Select this data, go to your Analytics Data Tab. Click on the “Keyword” Cell (A7 by default) and the while holding down Ctrl+Shift, hit the right arrow and then the down arrow. You should now have scuessfully selected all of your Google Analytics Data
Third, Criteria Range. This is your Target Keywords list. For Longtail we are going to be using the *Keyword* version. So, go to your Target keywords Tab and again, select the Cell “Keyword” that is located above the *Keyword* list. Then, holding Ctrl+Shift hit the down arrow. You have now selected your target keyword list with the Longtail search modifier! (see Figure 12)
Finally, the Copy to: This is going to be Cell A1 on the Longtail tab. (see Figure 13)
Success! I can now see that from my target keyword list of 7 keywords, I am driving traffic from 657 variations! (see Figure 14)
Now for Exact Match. Repeat the same Advanced Filter Process, substituting the Criteria range for the exact match variation of your target keyword list (=keyword). (see Figure 15)
I can now see that from my target keyword list of 7 keywords. Four exact match keywords are driving traffic. (see Figure 16)
If you are looking to tie the success of the keyword to revenue (Yay for ROI metrics!) make sure when you download the data from Google Analytics you are on the Ecommerce tab. (see Figure 17)
To see how well your target keywords are driving traffic (and revenue) to your site, you simply download the data from Google and then, using advanced filter, you filter your exported data using your target keyword list as a criteria range.