Submit a post
Go to Blog

SEO Keyword Research Tips For Excel

Sergio Aicardi

In the world of SEO, there is nothing more important than keyword research. It is the core of search engine optimization, to which all other tasks and strategies revolve. If your keyword selection is poor, your SEO campaign is sure to fail.

It is also very important to have landing pages that are relevant to the keyword you are targeting as well as know which landing pages are driving traffic to your website. One of the best ways to select keywords you know will drive quality traffic is to use Google Analytics.

I will go through how to use Google Analytics in order to see your clients' complete search query history and what queries were used to find individual landing pages. Once that data is exported into Excel there are a few tricks to help make the data more organized and understandable.

After you have a nice set of keywords to go after you will need to pull search volumes, and SEMrush has a pretty nice keyword research tool you can use.

Landing page reports

First, I will discuss Google Analytics landing page reports and the use of secondary dimensions.

Once you have logged into your Analytics account, look to the left side navigation. Go to "Behavior" and click on "Site Content," then "Landing Pages." You will now be viewing all your (or your clients') landing pages.

Just under the left side of the graph, click on "Secondary dimension," scroll down to "Advertising" and again to "Keyword." We need to get rid of all the keywords that don't help us, such as (not set), (not provided) and brand name keywords. Also, to ensure all URL search queries are removed, remove www., and .com keywords. To do this, we need to use advanced segments. So, click on "advanced" under the right side of the graph. Select "keyword" as the dimension and, since we are trying to remove certain keywords, change "include" to "exclude."

Because we are removing several keywords at a time, the most efficient filter setting to use is "Matching RegExp." In the search bar we will enter the following keywords we want to exclude: (not set)|(not provided)|(clients brand name)|(www.)|(.com), and hit "Apply."

I encourage everyone to learn more about advanced segments if you have not already familiarized yourself with them. After applying these segments, you will be able to see each landing page and the keyword used to find each landing page. Below is an image showing the advanced segment screen. If you want to only show keywords or landing pages that you are working on promoting organically, you can create a user-defined function that will make things easier for you. I encourage you to read this “Using Excel for SEO” blog post, which shows you how to be more efficient when doing keyword research.


Understanding metrics and using them to your advantage

Now that we know the keywords being used to find each landing page, we need to identify which of those keywords brings quality traffic. We can do this by analyzing the provided metrics for each keyword. These metrics are broken up into three groups: Acquisition, Behavior and Conversions.

Acquisition gives you the number of visits, percent of new visits and new visits (people who have never visited your site before) that that keyword brought a particular landing page within the designated date ranges. Behavior shows you the bounce rate (the percentage of single-page visits/visits in which the person left your site from the entrance page without interacting with the page), Page/Visits (the average number of pages viewed during a visit to your site, repeated views of a single page are counted), and Average Visitor Duration (the average time duration of a session). Conversions provide you with your form Submission Conversion Rate (the percentage of visits that resulted in a conversion to the goal you created), Form Submission Completions (the number of conversions to the goal) and Form Submission Value (the monetary value of conversions to the goal).

Depending on how long the website has had an Analytics tracing code, it will determine the size on the data sample. In order to get the most unskewed data, you want to use the largest data sample possible (meaning, adjust the date range so it captures all recorded data). If it's a new website and has only had Analytics on it for a couple of months, the landing pages and keyword list will be short (small data sample), and it will be very easy for you to pick the highest performing keywords. But if it's a large data sample, the most time-efficient way to sort out the good from the bad is to export this data into Excel.

Below is a screenshot showing the metrics we will be analyzing.


Using pivot tables with Excel

At the top left of the page you will find an export button. Click it, and then click on Excel. Now that you have a spreadsheet full of data, we just need to organize it.

Currently the landing pages are in column A and the keywords are in column B. This makes the spreadsheet a lot larger than it needs to be and very difficult to read. The quickest way to organize this data is to insert a pivot table.

In order to do this, you need to highlight all the cells containing information and click on "insert" on the top tool bar. You will be shown the range of cell you have selected, if this is the correct range then click "OK." Now you can arrange the data anyway you want.

Look to the top right side of the screen and you will find your "PivotTable Field List" containing landing page, keyword and each metric. Underneath the PivotTable Field List, you will see four boxes: report filter, column labels, row labels and values. Click and drag "landing pages" into "row labels," then click and drag "keyword" underneath "landing pages." You can now see that the spreadsheet is organized with the landing page and all the keywords for that particular landing page listed underneath.

Next, click and drag all the metrics that would be useful in determining a quality keyword/landing page and place them in the "values" box. These metrics include: visits, % new visits, avg. visitor duration, bounce rate and page/visits.


Making the most of your data

Once these metrics have populated your spreadsheet, you will need to adjust their values. You can do this by clicking on each metric and then clicking on "value field settings." Each metric will need to be changed to "sum." Avg. visitor duration will then need to be formatted for "time" and bounce rate will need to be set to "percentage."

When you have this all setup, you will have a spreadsheet that is very useful and understandable. You can now use this sheet to pick the highest performing keywords, and even send it to your client to reinforce why you selected certain keywords (since every client seems to be an expert in this field). Of course, the next step would be to use a very reliable keyword research tool like SEMrush to find out the search volumes for these keywords.

When all said and done, this data can be used to do a number of things. It can help you identify which pages are driving higher quality traffic to your e-commerce shopping website, show you which pages are getting a lot of traffic but are not converting your visitors at a favorable rate, and much more. As a PPC management company owner, I personally use these methods when forming the initial keyword/page strategy for our clients. We leverage the same techniques for our clients' SEO campaigns as well.

Using Google Analytics, Excel and SEMrush together can be very advantageous when creating a keyword strategy. Once you become efficient with these tools, you will be able to quickly pull actionable data that will greatly increase the performance of your campaigns.

Author bio:

Sergio Aicardi is the owner of The Miami SEO Company and works with the No Risk SEO Team. His responsibilities include developing business relationships and optimizing internal processes to ensure the business is constantly growing, while constantly enhancing the quality of services rendered. He has been in the industry for over six years, and his core competency is in analyzing traffic data to enhance campaign performance.

Sergio’s last article for SEMrush was “PPC Management and 7 Tips for Your E-Commerce PPC Campaign.”

Like this post? Follow us on RSS and read more interesting posts:

Sergio Aicardi is the owner of The Miami SEO Company and lives in sunny South Florida. He offers consulting to many marketing companies in Miami and business owners alike. His responsibilities include developing business relationships and optimizing internal processes to ensure the business is constantly growing, while constantly enhancing the quality of services rendered.
Share this post


2000 symbols remain
Great information! Love the methodology behind this, very quantifiable Sergio
Sergio Aicardi
Thanks Scott! Glad you enjoyed reading!
Thank you so much. Really. Your post opened up many additional capabilities in Analytics for me. As a small business owner, every bit of information that I get from a pro like you not only helps me with my knowledge base and more importantly; increases revenue. Thanks again.
Sergio Aicardi
Andrew Buys
Andrew, I am really happy that you enjoyed my post. I will be creating more posts like these once a month. I also create many great posts on my blog as well which I encourage you to check out!
Thanks Sergio!!

FYI, there's a typo. There shouldn't be a space before "not provided", right?

(not set)|( not provided)|(clients brand name)|(www.)|(.com)
Kathleen Garvin
Ersan Seer
Just fixed it. Good catch :)
Sergio Aicardi
Ersan Seer
Thanks for catching that Ersan! I hope that you enjoy my next post around the same time next month! :)
Don't you just love Excel for data like this! Great post. If anyone wants to learn more about PivotTables I have a free tutorial here: http://www.myonlinetraininghub...
Sergio Aicardi
Mynda Treacy
Thanks Mynda! I am glad you enjoyed reading my post. :)

Send feedback

Your feedback must contain at least 3 words (10 characters).

We will only use this email to respond to you on your feedback. Privacy Policy

Thank you for your feedback!