Kicking Ass With API Cheat Sheet

Kicking ass with API webinar + cheat sheet

For your own API key, use our exclusive SEMrush API Account Promo for a free trial courtesy of SEMrush

Multi-page change monitor tool

Let’s take a look at the tool in the webinar!

Column B’s formula:

1. Initial formula for column B: =importdata(A2)

2. Adding concatenate: =concatenate(importdata(A2))

3. Removing line breaks: =substitute(concatenate(importdata(A2)), char(10), "")

4. Check for the URL: =if(A2="","", substitute(concatenate(importdata(A2)), char(10), ""))

Column D’s formula:

1. Initial formula for column D: =if(B2=C2, "Nope", "Yep!")

2. Add check for baseline data (column C) first: =if(C2="","", if(B2=C2,"Nope","Yep!"))

Bulk keyword position tool using SEMrush

Google Sheet

Column B’s formula:

1. Initial formula for column B: =importdata("http://us.api.semrush.com/?action=report&type=phrase_this&key=YOUR_API_KEY&display_limit=1&export=api&export_columns=Nq&phrase=" & A2)

2. Adding QUERY to remove the extra header line: =query(importdata("http://us.api.semrush.com/?action=report&type=phrase_this&key=YOUR_API_KEY&display_limit=1&export=api&export_columns=Nq&phrase=" & A2),"SELECT Col1 LABEL Col1 ''")

3. Use cell E2 to change country on the fly: =query(importdata("http://" & $E$2 & ".api.semrush.com/?action=report&type=phrase_this&key=YOUR_API_KEY&display_limit=1&export=api&export_columns=Nq&phrase=" & A2),"SELECT Col1 LABEL Col1 ''")

Meta and page title checker

Google Sheet

Column B’s formula (XPath code is in SWEET SWEET MAGENTA!):

1. The whole formula for column B: =substitute(importXML(A2, "//meta[@name='description']/@content"),char(10),"")

Column C’s formula:

1. The whole formula for column C: =importXML(A2, "//title")

Examples of finished tools

Find business address by domain

Give the tool a list of domains, and it tries to figure out the business addresses and phone numbers for each one by looking for the Google+ page and parsing it.

Unlinked brand mentions finder

A tool that opens ranking URL for a phrase, and parses that ranking page to check for a link back to the domain. Yep, the whole tool is on a single sheet!