Shortcodes & Processes
Below is a list of quick processes or shortcodes I utilize to help with my day-to-day work in SEO, Data Analytics Reporting and Digital Consulting. I break them down by the different tools for easy sorting. My hope is that anyone can visit this page as a one-stop shop to learn how to speed up their work. If you have any suggestions for other processes please let me know!
Table of Contents
Vlookups & Excel
Countif – =SUM(COUNTIF(E2,”*”&list1&”*”)) – Count the amount of times based on certain criteria.
Count Duplicates – =COUNTIF(A:A, A2) – Find the number of duplicates.
Vlookup – =VLOOKUP(A2,’SHEET’!$A$1:$Z$9999,2,FALSE) – Look vertically through a list and pull result.
Hlookup – =HLOOKUP(A2,’SHEET’!$A$1:$Z$9999,2,FALSE) – Look horizontally through a list and pull result.
If Statement Mark – =IF((AND(C:C>=5,D:D>=70)), “Strike”, “Ignore”) – If cell meets certain mark it accordingly.
Lowercase Everything – =LOWER(text) – Lowercase all your text.
Proper Case – =PROPER(text) – Proper case all your text.
Character Count – =LEN(text) – Count the characters in each cell.
–> 11 Google Sheets formulas SEOs should know – Search Engine Land
–> Top 10 Formulas Every SEO Needs to Know – Conductor
Screaming Frog
Exclude page type – ^(?!.*?keyword).* – Exclude any page based on folder in URL.
Exclude URL Containing – \?price – Similar to above, exclude based on keyword.
Exclude ? in URL – ^(?!.*?\?).* – Exclude any page based on special character in URL.
Exclude File Type – jpg$ – Exclude any file type, in this case .jpg.
–> Screaming Frog Custom Extractions: A Guide to Extracting Crawl Data – SEO North
–> Screaming Frog Guide to Doing Almost Anything – SEER
–> ScreamingFrog Beginner’s Guide – Brainlabs Digital
Scraper
Links – //a/@href – Scrape all links from a webpage.
Google Results – //*[@id=”rso”]/div[*]/div//a/@href – Scrape all Google Results from the SERP.
People Also Ask – //g-accordion-expander – Scrape all PAA questions from a SERP.
Headers – //h3/text() – Scrape all headers from a webpage, in this case h3’s.
.Class – //*[@class=”.xyz”] – Scrape all content based on class.
#id – //*[@id=”xyz”] – Scrape all content based on an id.
Page Content – //p/text()|//a/text()|//h1/text()|//h2/text()|//h3/text()|//h4/text() – Scrape the entire page.
–> Scraper Chrome Extension – Super handy extension to scrape content on a page-by-page basis.
–> Tag Crowd – Easy tool to create word clouds based on content scraped.
–> Google Q&A (People also Ask) Research Tool – HanR – Awesome website that led me to this tool. Really handy process to see questions in the SERPs.
Regex
Google Search Console Questions – ^(who|what|where|when|why|how|is|are|does|can)[” “] – Find all questions users are searching for.
Match Everything enclosed – (?:xyz) – Find exact keywords or phrases.
And Statement Multiple Keywords – (\b(kwd1|kwd2).*){2} OR ((kwd1|kwd2).*){2} – And statement for multiple keywords in any order.
After Purchase – ^(clean|broken|wash off|shattered|polish|problem|treat|doesn’t work|replace|doesn’t start|scratch|repair|manual|fix|protect|renew|coverage|warranty)[” “] – Find all queries based on common after purchase terms.
–> Regex For SEO: A Guide To Regular Expressions (With Use Cases) – Search Engine Journal
–> Beginner Guide To Regex For SEO – JC Chouinard
–> 20 Google Search Console regex that give you filtering superpowers – Jason Wilson
Google Data Studio
GDS Custom Metric – ((Unique Pageviews-Old Pageviews)/Old Pageviews)*100
Brand Filter –
CASE
WHEN REGEXP_MATCH(Query, “(?i).*xyz.*”)THEN “Brand”
Else “Non-Brand”
END
Question Filter –
CASE
WHEN REGEXP_CONTAINS(Query, “^who|^what|^where|^why|^when|^how|^is|^are|^does”) then “Question”
ELSE “Not”
END
–> The Ultimate Guide to Google Data Studio in 2022 [+20 Expert Tips] – Hubspot
–> The Beginner’s Guide to Google Data Studio – Search Engine Journal
–> Category: Google Data Studio – Measure School
–> Category: Analytics Reporting – Mike Ginley
