Posted: June 23, 2021
Updated: July 22, 2022
1. Separate URLs into their Components
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. Alternatively, use the split formula.
=SPLIT(A1,“/”,true,true)2. 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.
3. 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)) Regression analysis is a common technique for understanding how a dependent variable such as organic traffic, changes in relation to independent variables such as search volume. Start with the built-in forecasting tools such as XLMiner Analysis ToolPak for Google Sheets, and then experiment with other statistical models such as multiple linear regression to see which one fits best. If you have trouble formulating a statistically significant model, try adding other important factors such as the page's publish date or number of high-quality backlinks.
5. Google Ads and Google Analytics add-ons
Google Sheets has a built-in Google Ads add-on that can pull campaign data. It's a bit buggy but good enough for ad hoc analysis. Similarly, there is also a Google Analytics add-on that you can use to pull data, modify as needed (such as fixing historical data) and export to Data Studio.
6. Identify SEO Issues
Import the html of pages in your site and 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 basic 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
.
An example for JavaScript elements:
//script[contains(.,'gtm.js')] - to scrape (check for) GTM code
.
7. Find & Replace with 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, BigQuery uses SQL and also supports RegEx as well, allowing users to write simpler 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, it can be rewritten as follows.
regex_extract (column, r'/((.*)')---－－－
The remaining examples use 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.
8. 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 offers an in-depth explanation.
Ensure all pages on your site are accounted for with the following script.
// Get destination urls including redirects
function getRedirects(url) {
var params = {
'followRedirects': false,
'muteHttpExceptions': true
};
var followedUrls = [url];
while (true) {
var res = UrlFetchApp.fetch(url, params);
if (res.getResponseCode() < 300 || res.getResponseCode() > 399) {
return followedUrls;
}
var url = res.getHeaders()['Location'];
followedUrls.push(url);
}
}
function getDestination(url) {
return getRedirects(url).pop();
}10. http Server Status Code
Track the status code of all pages on your site with the following script.
// Get http server response status code
function getStatusCode(url){
var options = {
'muteHttpExceptions': true,
'followRedirects': false
};
var statusCode ;
try {
statusCode = UrlFetchApp .fetch(url) .getResponseCode() .toString() ;
}
catch( error ) {
statusCode = error .toString() .match( / returned code (\d\d\d)\./ )[1] ;
}
finally {
return statusCode ;
}
}
// Exceed importxml limit
function importRegex(url, regexInput) {
var output = '';
var fetchedUrl = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
if (fetchedUrl) {
var html = fetchedUrl.getContentText();
if (html.length && regexInput.length) {
output = html.match(new RegExp(regexInput, 'i'))[1];
}
}
Utilities.sleep(1000);
return unescapeHTML(output);
} 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.
Comments