Google Analytics & Excel Series – Keyword Research
As an SEO, you are going to have some kind of understanding and regular use of Excel. Whether its exporting keyword data or compiling stats for a client report. However most SEOs (including myself until recently) don’t understand the potential Excel has to make our lives so much easier. It can be integrated with a number of SEO/PPC tools and make sense of normally complicated data. So it can be utilized to plan future campaigns, review existing ones and visualise data for your boss or client.
I wanted to follow in the footsteps of Richard Baxter and Will Critchlow who first sparked my interest in Excel as an SEO tool back at the SEO Pro Training Seminar. So I’ve decided to do a series of blog posts showing how I use Excel in my SEO work. How often these series will be I can’t say as I don’t blog enough due to lack of time already. But I’ll do my best!
Using Google Analytics and Excel to Plan Keyword Targeting
Objective – See which pages to target with your keywords
First in this series of blog posts, I want to talk about exporting keyword data from Google Analytics and using it to plan future SEO campaigns. You can export keyword data and sort it so that you can see which keywords sent the most traffic to which pages. This data tells you exactly which pages are already ranking for your keywords, therefore you know which pages Google see as most relevant.
I actually came across this method whilst doing some research for a client, I wanted to know which keywords sent them the most traffic last summer. Some of their product lines are very seasonal and we saw a large increase over the summer, so I wanted to be prepared for this year. I figured the best place to start was to improve rankings for keywords they were already getting some traffic for.
After I’d exported their top referring keywords, I started to map these keywords to the pages I thought were most relevant. Then I realised – Analytics could tell me which pages had actually received traffic for these keywords. This data would make it much easier for me to plan my SEO campaign.
Note – Data is from my own blog
1 – Go to Traffic Sources > Search Engines
2. Click on Google followed by non-paid traffic to see the following screen (with keywords included!)
3. From the drop down menu select “landing page”
4. You should see something like the following
5. Go to the bottom of the page and select how many keywords you want to look at, I usually choose 500
6. Export your data into Excel
Now you have several approaches you can take at this point, you can sort by keyword, landing page or visitors. It took a bit of playing around but I decided to sort by landing page followed by visitors. This gave me a sorted list of which pages had received the most traffic and which keywords sent that traffic.
My next step was to use some internal processes and documents to map the keywords to landing pages and integrate this into the SEO strategy for the client. Its worth noting that its possible for keywords to send traffic to more than one page, commonly this will be your homepage and an internal page. Here you need to make a judgement call but I’d nearly always opt to optimise the internal page.
I’d start by going through my on-site SEO checklist followed by coming up with some link building strategies that can incorporate these keywords and landing pages.
Thats it! Nice simple way to get data into Excel, re-arrange it and use that data to plan your SEO campaign.
Paddy Moogan
Paddy is an SEO Consultant working for Distilled in the London office.
21 
124 
10
April 30th, 2012 at 9:25 pm
Check out http://megalytic.com/google-analytics. Uses the API to download multiple Google Analytics accounts into a single Excel Workbook. Handles Facebook, Twitter, Omniture, and others as well.