Since gaining access to a paid Semrush account 11 months ago, it’s slowly become one of my favorite competitive insight tools. I use it to analyze everything from keyword rankings, international market share, to overall paid and organic visibility in Google for clients and competitors.
In this post, I’m going to show you how to use data from Semrush’s Chart tools and Google Sheet’s motion charts to help you tell a visually compelling story. Using these two tools, we’ll create a dynamic chart that will show Google organic traffic and number of total ranking keywords for multiple domains over time.
Getting Started with Semrush’s Chart Tools
You can find Tools > Charts in either the top or left sidebar menu from the homepage. Semrush’s Charts allows users to choose from a variety of paid (Advertising) and organic search performance filters. For organic, users can analyze up to five website’s Google organic traffic, total ranking keywords, traffic price, new keywords, lost keywords, improved keywords and declining keywords.
In the below screenshot, you’ll see I’ve added all four competitor’s domains, selected Organic > Traffic from the dropdown menu below the domain, and set the time range for two years. It’s important to note that the Charts feature only provides data for domains, not subdomains, subdirectories or specific pages.
Once I’ve exported the plotted chart report to Excel, it’s time to pull in another data set. For this analysis, I’m interested to see if there’s a correlation between the number of ranking keywords and Google organic search traffic, so I’ll next select Organic > Total Keywords from the drop down menu below domains, select Plot Chart and export to Excel.
Putting the Data Together in Google Sheets
Because we’re using a Google motion chart, we’ll want to create a new sheet in Google Drive and copy and paste in the export data from Semrush. Before copying the data into a new spreadsheet, I like to clean out any fields I won’t be using in the motion chart like type, database and summary.
The first column we’ll add into our spreadsheet will contain the website name (entity) that we’ll want to track followed by a column that includes date values (FYI, this chart supports multiple date formats). The rightmost columns will contain our Semrush data that includes Google organic search traffic and ranking keywords over the selected date range (two years for this analysis). Because entities, dates and data points are all stacked vertically using the motion chart format, using the transpose feature in paste will allow you to quickly move Semrush data into the required format in your Google Sheet.
Once you’ve compiled data from both Semrush reports, your new Google Sheet should look similar to the below screenshot:
Now that we’ve compiled the data into the proper format, creating a motion chart is as easy as selecting the data, inserting a motion chart (Google should detect that the data is formatted for a motion chart and suggest it) and voila, you now have a dynamic chart that shows estimated Google organic search traffic and number of ranking keywords over a selected date range!
For those that prefer a visual step-by-step tutorial, I’ve created a short video to guide you through the process.
Once the chart has loaded, we’ll select Organic Traffic as our Y axis and Time as our X axis. To help define our entities in the chart, we’ll select all domains and additionally select unique colors from the color menu. For size, select Ranking Keywords which will cause each domain to increase or decrease in size as they gain or lose visibility for keywords in Google search. I also like to uncheck the Trails option as I feel it’s distracting, but feel free to experiment and go with what works best for you or your client.
Analyzing the Data
While this analysis isn’t entirely apples to apples, it should provide you with the insight needed to create your own motion charts using Charts data from Semrush.
Other ways to use Semrush Charts.
There are plenty of other ways to use data from Charts to analyze organic and paid visibility including:
- Google algorithm updates — winners vs. losers
- Any major marketing campaigns, or link acquisition efforts — most backlink analysis tools allow you to analyze links acquired over time and look for any loose correlations between a spike in links acquired and organic search traffic
- Organic traffic and advertising paid traffic price — how much traffic a competitor is acquiring organically and how much they are paying for clicks
- Mergers and acquisitions
- Growth, or a decrease of organic visibility over time
Switching Between Chart Formats
If you prefer a simple line or column chart, switching chart formats in Google Sheets is as simple as choosing between your chart preferences for visually displaying the data.
Publish and Share Your Chart
Another awesome feature worth mentioning in Google Sheets is the ability to publish your chart, which creates a URL for easy sharing between co-workers and clients.
I’ve just scratched the surface on ways to use Semrush’s Chart Tool and would love to hear how you’re using the data for analysis or to guide strategy for paid or organic in the comments below.