This tool helps us track keywords and organic traffic throughout SEO campaigns – we use it as a part of our monthly reports for clients.
We hacked up a Google Sheets file to pull API data from Google Analytics and SEMrush and generate a ton of helpful reports.
USE THE TOOL
The outputs of the file:
Month over month and year over year changes in organic traffic for “target” sections of your website.
Crosswalks organic traffic data with KW data. This helps you understand the impact of your “rankings” on organic traffic at a page level.
Improvements, losses and overall campaign progress of keyword rankings across your website.
The keywords that are decreasing in rankings, by search volume and CPC.
The keywords that are improving, by search volume and CPC.
USE THE TOOL
Or, create a FREEmium account here.
How does the tool work?
We built a simple to use interface that walks you through the details of how to run the report.
- Enter your information, connect your data sources.
- The report requires inputs from SEM Rush and Google Analytics.
- Instead of manually pulling that data, a Sheets plugin called Supermetrics connects via API to grab it all for you.
- Follow step by step prompts to populate the report.
- Along with a step by step instructional video, we also built out a text version of the file.
- This easy to use formula allows us to push our reporting to our offshore team to increase local productivity.
- Using powerful scripts and formula, it crunches the data.
- Sheets shows it’s true power not with pulling data, but aggregating and formatting.
- A few clicks runs powerful data crosswalks between Google Analytics and SEM Rush.
- Track keyword progress over periods of time.
- The template scrapes ranking data from SEM Rush’s index and calculates progress using a number of KPIs, including keyword movements and progress of top landing pages on your site.
- Crosswalk KW rankings and organic traffic numbers.
- The template allows you to enter a set of “target” pages, aka the pages on your site that mean the most to your top line.
- Using advanced lookups, the template calculates organic traffic changes (Google Analytics data) and keyword movements (SEM Rush) in an easy to understand set of reports.
The tool is available for FREE in our training platform, along with step by step video guideance on how to set it up.
Create a FREEmium account here.
1
Awesome post!