Blog

Like many SEOs, I spend a lot of time analyzing traffic data in Google Analytics. I also rely heavily on Excel, which goes hand-in-hand with Analytics.

Excel is a tool that is invaluable to SEOs, and I personally could not go a day without using it. Over the years I have come up with many ways to use functions or formulas to increase my keyword research efficiency; I have also relied heavily on other SEOs that share their insights and tips on Excel forums and SEO forums.

As someone who specializes in ecommerce SEO and PPC marketing, I often have to sort a lot of product data. Just recently, I had to identify the top performing product pages and category pages for an ecommerce store that had over 18,000 products and several hundred category pages. After I found the best pages, I had to rely heavily on my historical data and data from other sources, like Semrush, to come up with the best keyword strategy. There are some streamlined processes that I follow which allow me to do things effectively and efficiently. Here they are. 

Dissecting ecommerce data from Google Analytics

Assuming that your client has e-commerce revenue tracking enabled within Google Analytics, the first thing I would do is drill down on the landing pages report under “Behavior” (sub category “site content”) to see the pages that are bringing in the revenue and high-quality traffic. To do this, we will need to export the data from the landing pages report, which contains a lot of important data such as time on site, pages per visit, bounce rate, new/returning visits, ecommerce revenue and conversion rates associated with each landing page (and the keyword used to find that page if you apply “keyword” as a secondary dimension). An example of this report is shown here:

If you want to segment only U.S. traffic using your advanced segments, did you know Google provides a list of great, built-in advanced segments? I encourage everyone to use advanced segments (custom and built-in ones) to help you have more flexibility with your reports.

My most commonly used custom advanced segment strips out any visitors who used a search query containing the clients brand name or URL-based search queries. This is because those visits aren’t usually going to provide you with quality data, nor is it data related to your search engine marketing performance (unless you are running a brand campaign).

You might also want to use the quick filter under the linear graph to filter out any unwanted keyword data, like the dreaded (not set) and (not provided) keywords. This can easily be done by adding a Regex exclude filter to match ((not set)|(not provided)) as shown in the screenshot below. In my opinion, this way is pretty efficient.

Combining data from multiple reports

The next thing I would do is export this data into Excel so I could start to combine this data with data from other tools that provide search volume or rank like SEMrush and Google Webmaster Tools. I do this so that I can combine the metrics that are not provided in Analytics like search volume, click through rate and rank from those of competitors.

Typically, I will export the search queries report from Google Webmaster Tools and place it in a separate sheet to get CTR or AVG position data. I will also then take the Semrush keyword data from the client’s site (or competitor's) to find search volume and rank. With a few Vlookup functions you can combine the columns that you want to use from each report into one sheet like the one below. To learn more about Vlookup functions and how they help with keyword research, you may want to read my older blog post, “How to Use Semrush For Your Search Engine Marketing Services."

Organizing your data with a pivot table

Once you have all the metrics you want, you can create an easy-to-read report using pivot tables, which I explained in an older post. The concept is easy. Simply select all of the cells you want to add to the pivot table, then select the Row Labels and Values. Remember, when you create the Row Labels you are creating a hierarchy when you stack metrics on top of one another. I will select the Landing Page URL followed by the keyword in the row label field so that the data is organized by groups of keyword under relevant URLs. Selecting the values that you want to see can be done by dragging the icons down to the Value Field quadrant, as shown in the screenshot below.

You can segment out the data according to your most important metrics. For example, if you are not tracking ecommerce conversions, you could be tracking form submissions or events that could be applied to this report, as well as with the right use of Vlookup functions and Google Analytics. In this particular case, ecommerce conversion rate, search volume, rank and CTR were the most important to me, so I made sure to only keep the keywords with the highest stats by adding filters on each column.

The end product is a very easy-to-follow keyword strategy, and can even help you organize Adgroups very easily. Often times I will create a pivot table like the one below and share that with my team as a reference. The content writers can know which keywords to write about and which page to link back to very easily by referring to this pivot table. On the other hand, our Ad copy creators can also know which focus keywords to use in the Ad copy and which destination URL to use.

There are many other great formulas and tricks that I use for different scenarios. I will be blogging once per month on Semrush in the hopes I can help everyone become a little more efficient in their day-to-day SEO activities.

Until next time, I encourage comments and questions from everyone! For more great info on how to use Excel for SEO, feel free to check out my blog or just check back in once a month here on the Semrush Blog!

Innovative SEO services

SEO is a patience game; no secret there. We`ll work with you to develop a Search strategy focused on producing increased traffic rankings in as early as 3-months.

A proven Allinclusive. SEO services for measuring, executing, and optimizing for Search Engine success. We say what we do and do what we say.

Our company as Semrush Agency Partner has designed a search engine optimization service that is both ethical and result-driven. We use the latest tools, strategies, and trends to help you move up in the search engines for the right keywords to get noticed by the right audience.

Today, you can schedule a Discovery call with us about your company needs.

Source: