5 Ways to Connect Semrush With Data Studio - LAZARINA STOY (2023)

Frequently Asked Questions

Semrush is one of the best all-in-one platforms for digital marketing and analytics.

What can you do withSemrush?

• keyword research —including keyword suggestions, discovery, keyword sizing, as well as position tracking
• ranking research —find out the keywords, for which you rank, as well as find out how difficult it will be to rank for specific search phrases
• backlink research —number of backlinks and referring domains, anchor text, type of links (e.g. sponsored, follow, no follow, image, text, etc.), as well as identify backlink opportunities
• SEO auditing— audit the site for technical improvements, which when made can result in better search rankings
• content development— get help with content templates, backlink opportunities, the structure of your blog post, keywords, and readability

There are a lot of insights you can get via Semrush, but what are insights without visualization and understanding? Just a bunch of spreadsheets nobody’s ever going to see, that is what.

The first step to visualizing and organizing your data in Data Studio is to connect to the Semrush data source. There are five ways to do this.

The first way to connect Semrush with Data Studio is to do a manual export from the Semrush web dashboard.

Starting with the easiest method out there — simply exporting a spreadsheet via the Semrush dashboard. This can later be uploaded via the Google Sheets connector in Data Studio.

This is a beginner-friendly, usually cost-free approach to getting started, yet it is subject to the limitations of the free Semrushtrail account, which only allows you to pull a subset of the data.

Pros

This is a very straightforward setup. Literally, anyone with access to the Semrush dashboard can do this.

There are cool templates out there that can help you visualize these spreadsheets beautifully. As the fields are unlikely to change in structure, a plug-and-play framework is possible.

Another good thing is that this allows an archive of exports to be held, which when paired with data of content development efforts can enable a clear picture of their impact.

Owning the connection means the report is less likely to break due to server or connector malfunctions.

Cons

There is inefficiency in this approach, which adds to the indirect costs.

The inefficiency is due to the time and effort required to export the spreadsheets, upload them to Google Drive, convert them to spreadsheets, and connect them to the report.

Another limitation is that the data is not real-time, so any significant changes that happen between exports could be missed by the analyst. This could also lead to missed opportunities, where for instance the dashboard fails to alert analysts for sharp rises in search volumes and visibility for a given topic.

Getting Started

1. Go to the Semrush dashboard and export a CSV of the Organic Search Positions Report

• Organic Research
• Positions
• Export via CSV

2. Pull backlinks data from Semrush

• Go to the Semrush Backlinks Report
• Click on Backlink analytics, then backlinks and export as a CSV

3. Save the files in your Google Drive and convert them into Google Sheets.

4. Import it into the dashboard.

A great dashboard to get you started isJameSEO’s free positions tracking and backlinks analytics report.

This is a great starting point for visualizing this data, however, it’s best to add other visualizations and adapt them to serve the needs of your stakeholders.

5. To replace the existing files with new ones, open the Resource menu and select Manage added data sources.

• Identify the two data sources from Semrush and click on edit.
• Once opened, click on ‘Edit connection’
• Select the new files from your drive.
• The tables and charts will update automatically with the new data.

You can also connect the Semrush API to a Google Sheets or Excel file and use the command line to write formulas that interact with the API to get the same reports. To do this you will not need to create everything by hand. Instead, you can use templates with pre-written formulas.

This method is a step ahead of the manual export from the console, and a few behind the paid connector illustrated in the last section.

The friendly fellows atSEO Tools For Excel have created a Google Sheets template, which integrates with Semrush’s APIand pulls data directly in a spreadsheet.

This spreadsheet can then be integrated with Data Studio for reporting and visualization.

You can also check out my guide on How To Integrate Nightwatch API In Google Sheets And Data Studio Via Google Scripts, as the approach and script in that tutorial are also applicable to Semrush’s API.

Pros

You get to store archived versions of the data.

You get to bypass the limitations of data importing in Data Studio, which you might typically have when pulling data from a connector. Nonetheless, you might still have tier limitations.

You own the connection, which means there is a lower likelihood of the report malfunctioning.

You can easily disconnect the data source and shift between different accounts — something not so easy when using the Semrush Data Studio connectors. This allows using the same report template to visualize different client data, should you have the API keys to the different accounts you are managing.

Cons

You need the API key, which is provided only with certain tiers of the Semrush account (the same as for the Data Studio connectors).

Managing these spreadsheets can require more time in maintenance, as well as querying.

The data is not real-time as a query is needed to update the spreadsheet.

Getting Started

1. Get your Semrush API key.

To use the SEMrush Connector you need aSEMrush subscriptionand an API-Key.

2. Download the template and follow the tutorial.

There is an easy set-up process, illustrated in the following demonstration.

If stuck, follow the tutorial provided by thecreators of the tool.

3. Want to use Google Sheets instead of Excel? Follow these guides and connect effortlessly.

Semrush have also written a blog post about using their API in Sheets, should this be more convenient for you, which you cancheck out here.

If you are looking for a template with formulas in Google sheets, David Sottimano has created one, which works on the same principle as above.Follow his tutorial and download the free templatein order to query the Semrush database using the Google Sheet scripts he created.

An even more advanced step is getting this data via Python through the console. To do this, you will need access to the Semrush API, as well as to install the following libraries:

• Pandas
• URLlib
• Requests

Pros

Work with big data! You can pull a lot of data this way if you have the ability to do so from a price standpoint.

Working with Python directly enables you to perform more advanced analysis, using machine learning and specifically — natural language processing libraries.

Cons

This is a very advanced approach and requires at least rudimentary knowledge of Python, though tutorials are available to help you with every step of the way.

You will not have real-time data, connected in your report, so the limitations, highlighted with methods 1 and 3 will apply to this one, too.

Getting Started

1. Connect the Semrush API via Python and export your data.

Follow this amazing tutorial byDerek Hawkinsto connect to Semrush via Python and export your data.Need More SEO Data? Consider the SEMRush API in your Workflow (Updated!)I am a huge fan of SEMrush (and no I’m not getting paid by SEMrush to say that… not yet at least). I learned the basics…derek-m-hawk.medium.com

If you want to learn more about the different functions you can use with the Semrush API, refer to thedocumentation.

2. Analyze the data via machine learning and NLP Python libraries.

3. Export via Pandas as a csv

4. Connect through Google Sheets in Data Studio.

Now let me just specify quickly. The connector themselves are not paid but they are only available after you purchase a Guru ($229.95/mo) or Business tier ($449.95/mo) subscriptions.

So, a bit steep for solopreneurs, freelancers, or small marketing agencies with just a few accounts.

Pros

Semrush offersgreat templatesto start with if their connectors are used. This means all you need to do in order to replicate them is hit copy and connect your data via the process, illustrated below.

You get updates to your data in real-time, without the need to do anything else but refresh the report.

It is relatively easy to get started as there is no need for manual exporting, or getting the API key from the Semrush account.

Cons

The most obvious limitation of using the Semrush connectors is that they are paid. If you are already using the service at the aforementioned tiers, then the connectors can absolutely enhance your consultancy. If not, paying just for the connectors is likely not worth it.

Another, less apparent limitation of the connectors is that they are built on a very granular database schema. meaning that there are three connectors for three different types of reports: Site Audit, Positions Tracking, and Domain Analytics.

Each of the three connectors offers multiple choices per connection, which then makes data blending very difficult due toData Studio’s limitationof only being able to blend up to five sources.

The granular structure of the connectors means it will be timely to implement on multiple projects, while the multiple connections will likely result in unstable reports.

Getting Started

1. Authenticate in Data Studio.

2. Search for Semrush in the connector library and select a data source.

There are three Semrush Connectors, each pulling different data from the Semrush console.

Domain Analytics connector pulls the following reports:

• Domain Overview — this will pull an overview of the Domain’s search positions in the selected database.
• Organic Search Positions — this will pull information from an Organic Positions report about the domain’s top 100 keyword positions in organic search results.
• Paid Search Positions — this will pull information from an Advertising Positions report such as the top 100 keywords used to advertise PPC ads on Google Ads.
• Backlinks: Top Referring Domains — this will tell you the top domains that are sending backlinks to a domain.
• Backlinks: History — this can show you the trend of a domain’s backlink count over the past 12 months as well as the number of new and lost backlinks per month.
• Backlinks: Overview — this will give you an Overview table with the options for measuring various metrics likeAuthority Score, amount of backlinks, referring domains/IPs, and distribution of link types and attributes.

Position Tracking connector pulls the following reports:

• Organic positions — the domain’s positions in the organic search results of the target keywords
• Google Ads positions — the domain’s positions in the paid Google Ads search results of the target keywords
• Organic visibility — the domain’s visibility % among the organic search results of the target keywords
• Google Ads visibility — the domain’s visibility % among the paid Google Ads search results of the target keywords
• Organic overview — an overview of the domain’s rankings in the Google top 100. This includes new and lost keywords, search terms with improved or decreased rankings, and changes in its ranking over a selected period
• Rankings Evolution — this option allows you to measure the change in TOP rankings over a chosen period of time. The range of TOP positions you measure is configured by the “Position range” parameters directly below this field.

One very important thing to note for this connector is that all of these reports can be pulled for a single project on Semrush.

So, if you have a position tracking campaign on Semrush, tracking different keyword topic clusters for a big website, or you are an account holder with multiple accounts, you would need to replicate the process of pulling the data for all of the reports listed above for each of your projects.

Identifying the campaigns or projects is done via a project ID.

The Site Audit connector pulls data from the following five groups of errors:

• Crawlability
• Performance
• Linking (internal and external)
• International SEO (use of hreflang)
• HTTPS (security)

One thing to note is that in order to use this connector, you need to have a site audit project set up already in the Semrush console.

3. Authenticate with your username and password for Semrush.

You will be prompted to provide access to Semrush via Data Studio, then to insert your Semrush Username and Password. You will be given access to the connectors, only if you are paying the subscription tier needed to use this service.

4. Select the data you want to import in the Data Studio dashboard.

Choose the fields and dimensions you would like to import. A pro tip is to name the data sources with very specific, identifiable names, which will be descriptive enough for you to work with the search bar later on.

Your report can easily become cluttered and having ten different data sources, titled ‘Domain Analytics’ will not give you any clues as to what data you are working with.

A good naming schema for the Domain Analytics reports is to use the segment name (e.g. Domain Overview, Organic Search Positions, Backlinks History) and a summary of the settings (e.g. US db, Root / .com).

5. Click Create Report.

If you are stuck at any point, using theSemrush Data Studio connectors, refer to the documentation that they provide on their site.

Finally, you can also connect to Semrush via a paid, third-party connector provider, such as Supermetrics.

Supermetrics provides awide array of connectorsavailable for its paying users. This enables seamless connection to data that can be otherwise difficult or timely to visualize in a Data Studio report.

TheSupermetricsSemrush connectorsupports 5 main types of queries — Domain, Keyword, URLs, Display Ads, and Backlinks — for a total of 35 different query types.

The connector uses the same structure as the API Reports with the same names.

Pros

You can connect Semrush easily, as the Supermetrics interface clusters the different reports into one.

Suitable for agencies, which manage multiple client accounts, as it has features, such as auto-refresh for updating and sharing reports with clients in a way that requires no manual interference.

You can use the connector in Sheets, as well as in Data Studio.

Cons

Many solo users complain that the cost for them is on the steeper side, while customer support is limited.

You still need to pay for a Semrush subscription, as well as for a Supermetrics subscription, which can get significantly out of budget for solopreneurs.

Getting Started

1. Get your Semrush API key.
2. Create a Supermetrics account and pay for the service.
3. Follow their tutorial on using the Semrush connector in Sheets and Data Studio.

Supermetrics usually offeramazing guides for getting startedwith any of their connectors, and they have done the same with this one, too.

In addition, they havecreated or featured several Data Studio templates, which can help you kickstart the reporting of Semrush data, using the Supermetrics connector.

If you encounter any errors, visitthis page for troubleshooting common failuresand their respective solutions.

There are five ways to connect Semrush with Data Studio:

1. Via a Manual Export
2. Via an API, using Google Sheets or Excel
3. Via an API, using Python
4. Via the Semrush Official Data Studio Connectors
5. Via a third-party connector provider, such as Supermetrics.

The cheapest way is via a manual export, however, this leads to issues with data freshness, time cost, and accuracy.

The most expensive way is via a third-party connector provider, yet this allows bypassing of legacy issues, related to the database structure, as well as access to customer support.

The most advanced way is via Python, as this enables using the data to perform big data analytics and natural language processing, to extract greater insights from it.

Semrush connectors can lead to a highly cluttered report in terms of the number of data sources imported, which can lead to issues with the dashboard’s stability.

Exporting via Excel or Sheets can overcome stability, as well as authentication issues for multiple accounts, however, result in challenges with data freshness and a higher need for troubleshooting and error handling.

5 Ways to Connect Semrush With Data Studio