Adept’s Excel Nerd takes on the challenge of creating useful pivot tables from Google Analytics and SEMrush data. Learn how to do the same with this step-by-step guide.
SEMrush regularly introduces beta tools that make my life easier, and the more I use it, the more I love it. SEMrush has recently launched their Organic Traffic Insights that is helping solve the not provided problem. Their new tool will give you similar data, but there's no harm in knowing how to manually dig deeper into your data.
In this post I’ll cover how to combine SEMrush and Google Analytics data in order to map keyword opportunities with top performing pages for SEO. In the end, you’ll have a pivot table that summarizes each landing page’s average rank, number of keywords, total search volume, sessions, and goal completions. You can customize this with whatever key metrics are KPIs for your business.
You can do a lot with this data, such as:
- Identify low hanging keyword opportunity
- Identify keyword types and their intent that could be driving traffic to your site
- Identify pages with a lot of keywords that don’t convert or drive traffic
- Create an action plan with prioritized content opportunities—with data to back up your decision
This process doesn’t just apply to this scenario; it provides other key takeaways including:
- How to combine multiple data sources
- How pivot tables can summarize overwhelming data into comprehensive summaries
Follow these steps, and you’ll have a series of useful tables in no time!
1. Get ranking data from SEMrush for your First Data Source
- Enter your domain into SEMrush
- Go to Organic Traffic
- Export the result. This will be “Data Source 1.”
- Save the export file as a new name and ensure it’s an Excel workbook and not a CSV.
Tip: If you only want certain pages or keywords, you can filter the results before exporting or once it’s in Excel.
2. Use Screaming Frog to get your Second Data Source
- Open Screaming Frog and change it to List Mode:
- Connect the Google Analytics API for the domain and select the metrics you want:
Tip: You can use this same process to get Search Console data as well
- Copy all the ranking URLs from the SEMrush export (column G on Data Source 1)
- Paste the copied URLs into the tool, let it run and export the results
- Open the export and copy the entire tab to your other worksheet
Tip: If you don’t have Screaming Frog (but really, you should get it), you can go into GA and export the top organic landing pages for the same GA data. Either way, the goal is to get two data sources with a common data point of the URLs.
You should now have a worksheet with both data sources in their own tabs.
3. Start a new tab with the SEMrush data and rename “Combined Data”
I do this to keep at least one clean version of the data in case I need to reference back for some reason. After that, I remove columns I don’t care about right now – like previous position, number of results, etc.
This new combined data tab will be where we start working off of from now on. (You could have easily have done the same with the Screaming Frog data and reversed the rest of the steps).
4. Use Index Match to Combine GA Data
Next, we are going to combine the GA data from the Screaming Frog export onto the new SEMrush Tab, using Index Match. Learn more about index match here if you aren’t familiar.
- Identify the common data point between your two steps of data. URLs in this example: Data Source 1 has URLs in column F, Data Source 2 in column A.
- Identify what data you want to bring from Data Source 2. In this example, I want sessions and goal completions in columns AI and AP.
Tip: You may want different data than I did. This goes back to the original API crawl setup and deciding which metrics you want to include.
- Create the function to pull the data you want from data source 2 into data source 1. For the first function, I want to pull Data Source 2 column AI over into Data Source 1. Here’s what that looks like:
- For the second function I want column AP. I keep everything the same, but change the starting index column:
5. Your Combined Spreadsheet
Once you have pulled all the data over and added headers, you should have a spreadsheet that looks like this:
You have successfully combined two data sources!
6. Insert Pivot Table
Click in the table. Go to Insert > Pivot Table. Make sure the pop-up has the right data and keep New Worksheet selected.
- The Pivot Table builder will pop up. Don’t be afraid of it!
- The two pieces that matter the most right now are the “rows” and the “values." Rows will be the data points that end up in the left of the pivot table. Values will be the data that is summarized for the rows.
- A simple table would be URLS in rows and count of keywords in values. This will tell you the number of keywords for each URL. But that data doesn’t tell you much right now. We need to build off that to get more actionable data.
- Keep in mind that you can change the function for the value summaries. Click on the i next to the value to change the way it’s calculated. We will be using this to customize our data to average, sum, or count later.
- To create the example pivot table, put URL in the Rows first, then Keyword beneath
- Values will have more. Drag keyword, position, search volume, sessions, and avg. session duration into the values area. The pivot table will default to sum or count summaries, but we will want to change these to match:
- Keyword — Count
- Position — Average
- Search Volume — Sum
- Sessions — Average
- Goal Completions — Average
Tip: You decide what to use (avg, sum, count) based on what type of summary data you want from the data.
- I choose average of position instead of sum so we don’t sum up all of the positions and instead get an average of the position.
- Sessions and goal completions are averages as well because every keyword that ranks for the same URL will have that URL’s sessions and goal completions. Summing this data up will give a wrong data summary at the URL level. Averaging will give the average of all the data, which is just the URL data.
- Your pivot table should now look like the one below:
7. Cleaning Up the Pivot Table
You made it to the final step! Here, we’ll clean up the pivot table by changing the colors, collapsing all fields, and sorting by a KPI to prioritize. Now it’s ready to play around with and find actionable insights!
When you’re using this brand new knowledge to go off and try it out on your own, you may run into questions. If you do, or would just like to leave us a comment, tweet at us or leave it below!