Marketing Automation with Google Sheets and Apps Script

Posted: June 23, 2021

Updated: July. 22, 2022


Google Sheets and apps scripts use cases for digital marketing projects.


Separate URLs into their Components

If you're working in SEO, at some point you will have a need to separate URLs into their components, such as for analyzing a sitemap or site structure. Go to the data menu in google sheets → split text to columns, and then choose "/" as the separator. Or use the split formula: =SPLIT(A1,“/”,true,true)


Check Your Users' Browser Window Widths

This is useful when creating a new landing page. Get the browser size (both width and height) in Google Analytics, and then export that data to Google Sheets to see the distribution of browser window widths. I first learned about this in a blog post from 2018, and have used it regularly. It's a bit old but still works the same: How to make a chart of your users' window sizes.


Ad Spend Tracker

When managing PPC campaigns, I usually setup a quick Google Sheet to track the monthly ad spend and make sure that I'm spending the budget evenly.

=DAY(TODAY())/DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0)) 

If I have enough accumulated data, I also create a few simple formulas to project the total monthly conversions, to make sure I'm on track to meet my goals.


This is also possible with with Google Data Studio, as explained in this Medium article. However, Google Data Studio has been a bit finicky for me these days especially if you bring in blended data, so I prefer keeping my analysis in sheets if possible.


Organic Traffic Forecasting

As a prerequisite to performing this function, you'll need to prepare a competitive research of your site and competitors. Include basic metrics such as target keywords, domain rating, Google ranking, and site traffic. Then, use a multi factor forecasting method to create a formula for predicting site traffic based on domain rating and Google ranking.


Google Ads add-on

There is a built-in Google Ads add-on in Google Sheets that can pull campaign data. I've found this tool to be a bit buggy so I don't use it to automate regular reporting, and my view is that you should be checking your Google Ads account itself anyway. But the add on is useful for quickly pulling data for one-time analyses, especially for larger accounts.


Google Analytics add-on

Similarly, there is also a Google Analytics add-on in Google Sheets. I recently started using it after talking to colleague, and had an overall better experience than with the Google Ads add-on. You can automatically populate Google Sheets with analytics data, modify it where needed (fix historical data, make any customizations, etc.) and use that to import into Data Studio.


Identify SEO Issues

If you're an SEO professional, you are probably already using tools like ahrefs or SEMrush. But in an emergency, you can also quickly identify SEO issues with Google Sheets by importing the html of pages in your site, then checking for things like missing h1, title, meta tags, image alts, etc.


Use the IMPORTXML function to scrape structured data from webpages, then use XPath query to specify which part of the XML to extract.


Some simple XPath queries:

  • //h1 - to scrape h1s

  • //title - to scrape titles

  • //@href - to scrape links

Other common XPath queries for SEO:

  • //meta[@name='description']/@content - to scrape meta description

  • //link[@rel=’canonical’]/@href - to scrape canonicals

For JavaScript elements, something like this works.

  • //script[contains(.,'gtm.js')] - to scrape (check for) GTM code

RegEx

RegEx (regular expression) is a sequence of characters to define a search pattern, often used for cleaning and manipulating data. It is available in Google Sheets find and replace, as well as many programming languages.


For example, Google BigQuery is based on SQL but supports RegEx as well, allowing users to write cleaner code and avoid complicated subqueries.


In standard SQL, the query below extracts the substring between two parentheses.

substr (column, instr (column, '(')+1,instr (column, ')') instr(column '(')-1)

In ReGex, you can rewrite it as follows.

regex_extract (column, r'/((.*)')---

---


The next few examples use Google Apps Script, which mostly runs on JavaScript 1.6, with some additions from 1.7 and 1.8. So you’ll need some JavaScript knowledge to edit the script and link it to Google properties, but not to the level of app or web development.


My GitHub repository for Google Apps Scripts for SEO includes scripts for retrieving html server response status codes, and destination URLs for redirected pages. I also have a separate general repository for Google Apps Scripts for automating everyday workflows such as email alerts and calendar events.


Confirmation Emails

Although Google Forms already has an option to send a standard confirmation email, Google Apps Script allows for further customization - send different confirmation emails depending on the user's responses, add external links, etc. This post by Google Workspace for Developers that explains in depth how to do this.


Site Migration

Use Google Apps Script to make sure all pages are accounted for properly during a site migration. You can write a script to keep an eye on the status code of all pages before and after the migration, or determine the redirects chain if any. Another use case is checking which pages have been indexed.


Scrape SERPs

Tracking your own site is not enough; you need to be aware of competitors to succeed in SEO. Fortunately, you can write a script to retrieve Google search results and capture data that interests you: url, title, meta description, ranking position, etc., all according to pre-selected criteria such as language and keyword.


For more resources, keywords in sheets has free scripts for SEO. There are different templates for tasks such as getting old webpages from wayback machine, SEO forecasting and clustering.