Kicking Ass with API Webinar + Cheat Sheet


For your own API key, use our exclusive RankTank SEMrush FREE 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!

Get this SEO tool free now!
Step 1:
Step 2:
Get the free SEO tool!
Click the button below to open Google Sheets and make a copy!
Column B’s formula:
  1. 1. Initial formula for column B:     =importdata(A2)
  2. 2. Adding concatenate:     =concatenate(importdata(A2))
  3. 3. Removing line breaks:     =substitute(concatenate(importdata(A2)), char(10), "")
  4. 4. Check for the URL:     =if(A2="","", substitute(concatenate(importdata(A2)), char(10), ""))
Column D’s formula:
  1. 1. Initial formula for column D:     =if(B2=C2, "Nope", "Yep!")
  2. 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. 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. 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. 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. 1. The whole formula for column B:     =substitute(importXML(A2, "//meta[@name='description']/@content"),char(10),"")
Column C’s formula:
  1. 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 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!

10 Million Marketers Can't Be Wrong!

When you join the RankTank mailing list, you'll be better than 9.9 million of them. You'll also get first access to cool new tools, tutorials and other neat stuff.

SPAM? Naw. Ain't nobody got time for that.

4 thoughts on “Kicking Ass with API Cheat Sheet!

  1. Hi

    Very informative webinar, enjoyed it. Is there a way of getting over the 50000 character cell limit in Google sheets. My website pages HTML are more than 50000 characters, if not the whole page then taking the first 25000 characters as I only need the top part of the page. I have tried many different things I can’t make it work.

    Thanks in advance.

    George

  2. This is probably old, but I guess it’s worth noting that you can use the index() formula instead of query() on Bulk Keyword Position Tool. I didnt’t try it, but I don’t see why it would work.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.