Do you want to be able to automatically pull all kinds of SEO and PPC data and avoid hours of data entry?
Of course, everyone would love to make this happen… but maybe you thought it required a developer?
While it never hurts to know a developer, there IS a spreadsheet-based way of working with the SEMrush API. Even if you’re not a coder, you can be automatically pulling marketing data at scale. Learn more from your competitors, publish industry research reports, analyze prospects on your email list, the possibilities are endless.
No coding with PHP or Python.
No hassling with Google Sheets.
I’ll try to keep this simple. If I find ways to explain it or illustrate it more simply, I’ll update this post. Feel free to contact me with any questions.
note – it’s generally not super cheap to work with SEO APIs, and this is no exception. (More on that below.) But for agencies and businesses, the costs associated with APIs can be far less than the labor and opportunity costs of manual methods.
also note – this post uses affiliate links whenever possible. It’s hard to see how this would change anything, since the content is straightforward documentation. If you do sign up for anything, it will fund our continued research and documentation.
What You Need
The first two items are specific to this method. The last two are needed to do anything with the SEMrush API.
- Microsoft Excel — hopefully you already have it
- SEOToolsForExcel — €99 per machine per year
- SEMrush Business plan — $399.95 per month
- SEMrush API credits — $1 per 20,000 units
Step 1: Get SEOToolsForExcel (Excel Add-In)
SEOToolsForExcel is one of the hidden gems of SEO tools. Besides SEMrush, it has over 100 connectors, including some with free data sources. You’ll likely keep finding uses for it.
- Make sure the SEMrush connector is loaded. Click “Manage” on the SEOTOOLS menu to get thsi popup. Click “SEO” on the left side, then click “Load More” in the list in the middle to see the SEMrush connector. Click the “Update” button on the far right if needed for the latest version. (You can click the version dropdown to see how often the connector is updated.
Step 2: Get SEMrush Account & API Credits
This is where things get a little more expensive. Hopefully this guide will help you minimize downtime and make the most of each month.
- Purchase (or upgrade to) SEMrush’s Business Plan.
- Go to My Profile > Subscription Info > API Units.
- Buy API units and get your API key. Keep the API number handy, you will need it soon.
Don’t worry if you don’t know exactly how many API units you need. You can always buy more later. I started with 2 million API units for $100, and it goes a long way… at least when pulling overview data for a lot of domains.
Step 3: Comply with SEMrush Terms-of-Use
When SEOToolsForExcel is installed, you will see a menu like this under the “SEOTOOLS” tab.
“User agrees to the following technical limits: (a) not more than ten (10) Calls per second, and (b) not more than two (2) Calls simultaneously.”
With that in mind, we head over to the Global HTTP Settings and adjust the delay between requests. I had a random delay between 1000 ms and 2000 ms (1 and 2 seconds) from another project, but you can adjust this as needed.
Step 4: Configuring The SEMrush Connector
The screenshot below illustrates a few of the steps at the same time (so don’t be worried if your screen doesn’t look like this at the beginning):
How to select the SEMrush API:
- Click the “SEO” tab illustrated above (not the one on the far left).
- Hover over “SEMrush” to get a sub-menu with options.
- In this case, I clicked the “Domain Overview” option. Doing so pops up the configuration panel on the left side.
How to configure the connector for the SEMrush API:
- On the left side menu, click the blue “Settings” link to enter your unique SEMrush API key. None of the sweet, sweet SEMrush data can be pulled without the API key.
- Enter a domain to query, along with any changes you want to make to the database, date or fields.
- Database – if you only deal with the US, change the database to google.com instead of all. Specifying only the data you need is easier to work with and saves API credits.
- Display Date – if this is too current, no data will display. For example, Domain Overview for the last few days. For consistency, I use the first day of each month.
- Select “Formula” instead of “Values”. This will give you something to work with, modifying and copy & pasting it as needed.
Step 5: How To Use The SEMrush API
After you select the connector settings in the last screenshot and click the “Insert” button, you’ll have a formula that you can work with to get more data.
Below is an example of a formula and its output:
In the screenshot below, we’ll make the formula more dynamic, pointing the domain parameter to another cell.
Example above has “semrush.com” after the first comma.
Example below has A3 after the first comma, referencing the cell with spyfu.com.
To pull the Domain Overview for many sites, simply paste your list of sites into Column A.
Then take a dynamic formula like the one above in B3 and copy it down. The cell references should adapt to the correct row number, and the SEMrush API data will start to pull.
You can work dynamically like this for any parameter, i.e. the same site over many months, or the same site with different databases.
Just copy & paste a handful of rows first to make sure everything works as expected, before using a bunch of API credits.
Conclusion (For Now…)
Seriously, I hope you have as much fun with this setup as I’ve had so far. There is so much to explore, far more than I can fit into the first draft of this post. But whenever I create a new report based on the SEMrush API that sparks joy, I’ll add it here or in a follow-up post.
If you want to get updates on new resources like this, join the happy newsletter subscribers.