Marketing Automation with Google Sheets and Apps Script

Posted: June 23, 2021

Updated: Dec. 8, 2021


Google Sheets is a collaborative, user-friendly software that lets you easily manipulate data and share reports, making it an important tool for digital marketing. Knowing how to get the maximum utility out of Google Sheets will surely boost your performance and help you work smarter, not longer.


In this post, I will list up some interesting and effective uses of Google Sheets that I've actually been using for my 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. You can do this by going to the data menu in google sheets → split text to columns, and then choose "/" as the separator. Or, you can use the split formula: =SPLIT(A1,“/”,true,true)


Check Your Users' Browser Window Widths

This is useful when creating a new landing page. You can 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

You can use simple subtraction, division, and date formulas to pace the monthly ad spend. When managing PPC campaigns, I usually setup a quick Google Sheet to track the monthly ad spend compared to days elapsed in the month to 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.


You can also do this 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.


Predict Organic Traffic

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. You can do this with a programming language, but a simple Google Sheet works fine too.


Google Ads add-on

There is a built-in Google Ads add-on in Google Sheets that you can use to 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, you can 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

---


The next few examples use Google Apps Script, which is a great way to automate data movement between Google properties and save yourself time from repetitive tasks. It 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 website 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 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 use Google Apps Script for this too. You can create 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.