Build Your Own Damn SEO Tools + Cheat Sheet


For a SEMrush API key, use promo code RANKTANK-AI5DGFUE for a free month courtesy of SEMrush


 Multi-Page Change Monitor Tool

Google Sheet

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, "Yep", "Nope")
  2. 2. Add check for baseline data (column C) first:     =if(C2="","", if(B2=C2,"Yep","Nope"))

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")

Sweet Examples of Finished Tools Using This Stuff

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.

2 thoughts on “Build Your Own Damn Tools Cheat Sheet

  1. Hi,
    =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 ””)
    is not working in my case? Get Error-Message.

    This is working fine: =IMPORTDATA(“http://api.semrush.com/?type=phrase_this&key=myAPI-KEY&export_columns=Nq&phrase=”&A2&”&database=de”)
    But how can I delet the column header?

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.