Build Your Own Damn Tools Cheat Sheet

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. 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, "Yep", "Nope")

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

Sweet Examples of Finished Tools Using This Stuff

Bulk Keyword Position Checker – Finds keyword rank and ranking URLs for a domain by parsing Google SERPs in crazy ways, going down into 100’s of positions.

Bulk Keyword Search Volume – Slicing & Dicing the SEMrush API

Unlinked Mentions Finder – A full on crawler built with a Google Doc that parses Google SERPs, then opens each ranking URL and parses the ranking page to check for a link back to the domain. Yep. This one was fun to make!