An SEO's Guide to Looker Studio Calculated Fields

In preparation for the sunset of Universal Analytics (UA), I've been updating my SEO reports to pull data from Google Analytics 4. So I decided to share how to create calculated fields in Looker Studio (formerly Google Data Studio). If you are an SEO marketer looking to up the ante with your reporting, this post is for you.

If you're unfamiliar, Looker Studio is a data visualization platform that allows you to turn your data into compelling stories with visualizations. As an SEO, one of my major pain points is creating effective reporting that provides quantitative results for qualitative efforts. Calculated fields help me resolve that pain point.

While Looker Studio provides plenty of ways to build and share visualizations, creating new or custom fields allows SEOs (and anyone else) to provide better analysis. Let's explore ways to utilize calculated fields to analyze Google Search Console and Google Analytics 4 data in Looker Studio.

What are Calculated Fields?

In Looker Studio, a field is a dimension or metric used in your report. When you connect your data to Looker Studio, you'll see the various metrics or dimensions you can choose to build your visualizations.

To analyze your data the way you want, we use Calculated Fields to create new metrics or dimensions based on existing ones.

If you're unfamiliar with adding calculated fields, you can follow the tutorial from Google.

Calculated Fields for SEO Reporting

As an SEO, dealing with so much available data can sometimes be overwhelming. Calculated fields can help us filter and segment data to help us answer questions and draw insights.

It would be best if you created new custom fields when you want to answer questions you cannot answer with the current state of your data. That may mean creating a new metric or filtering or combining metrics to analyze your data differently. Let's examine some fields I've made to build better reporting dashboards.

Create clickable URLs

While SEOs deal with slugs all the time, the people interested in your reporting probably don't. Make it easy for them to click through to the landing pages you report on.

With UA, you may have had to combine dimensions to create clickable URLs in your reports. Your query may have looked something like this:

CONCAT(Hostname, Landing Page)

When you update your data sources to GA4, you can use the Full page URL dimension instead. This dimension is also helpful if your website has multiple subdomains, so you can create other fields to segment or filter your data however you need.

Remove trailing Slashes (“/”)

If your website has URLs mixed with trailing and non-trailing slashes, it can create issues with your data. Let's remove those pesky slashes so that we can consolidate data across URLs in our reports.

REGEXP_REPLACE(Full page URL,"/$","")

Remove URL query parameters

There are several reasons SEOs should exclude query parameters from URLs in reporting. Two main reasons we want to remove them are because:

1. URL parameters can split your data — If we want to analyze how many sessions a URL has, we don't want splintered data across multiple rows of data.
2. Personally Identifiable Information (PII) danger &mdash — Sometimes developers build sites or applications that store values in URL parameters that can personally identify someone. While that is a (major) security issue, as an SEO, you can make sure PII does not appear in your reporting.

REGEXP_REPLACE(Full page URL, '\\?.+', '')

Consistent casing

If you have mixed casing in your URLs, it can create duplicate content issues similar to trailing slashes. Since we don't want to split our data, we can fix it and make everything lowercase. For example, to remove query parameters from our URL and also make everything lowercase, you can update your calculated field formula as such:

LOWER(REGEXP_REPLACE(Full page URL, '\\?.+', ''))

Group landing pages

When reporting on organic landing page performance, it can be much more helpful to group your landing pages for analysis. The example statement below shows some pages you might find on your company or client's website.

CASE
  WHEN REGEXP_MATCH(Page path, "((?i).*^/$|^/\\?.*).*") THEN "Homepage"
  WHEN REGEXP_MATCH(Page path, "((?i).*.*/about.*).*") THEN "About"
  WHEN REGEXP_MATCH(Page path, "((?i).*^/product/.*).*") THEN "Product"
  WHEN REGEXP_MATCH(Page path, "((?i).*^/compare/.*).*") THEN "Compare"
  WHEN REGEXP_MATCH(Page path, "((?i).*^/solutions/.*).*") THEN "Solutions"
  WHEN REGEXP_MATCH(Page path, "((?i).*^/blog$).*") THEN "Blog"
  WHEN REGEXP_MATCH(Page path, "((?i).*.*/blog/.*).*") THEN "Blog Posts"
  WHEN REGEXP_MATCH(Page path, "((?i).*.*/contact$).*") THEN "Contact"
  ELSE "Other Pages"
END

Group default channels

While GA4 provides default channel groupings, you may want to regroup them to display them in a table or chart. For example, if you wanted to group all of the paid sources on your website, you could use write a CASE statement similar to this:

CASE
  WHEN Session default channel grouping = "Paid Search" THEN "Paid Media"
  WHEN Session default channel grouping = "Paid Social" THEN "Paid Media"
  WHEN Session default channel grouping = "Display" THEN "Paid Media"
  WHEN Session default channel grouping = "Paid Other" THEN "Paid Media"
  ELSE Session default channel grouping
END

Group keywords by rank

When it comes to keyword data, you can use Google Search Console, ahrefs, SEMrush, or any other keyword tracking tool.

CASE
  WHEN Average Position < 11 THEN "Page 1"
  WHEN Average Position < 21 THEN "Page 2"
  WHEN Average Position < 31 THEN "Page 3"
  WHEN Average Position > 30 THEN "Pages 4+"
  ELSE "Non-Ranking"
END

Branded vs. non-branded keywords

Branded keywords may seem obvious enough, but there are still a couple of tips I can share:

• Make sure you look in your keyword tracking tools to find brand misspellings.
• If a company figure is directly tied to your company, include it in branding terms.

CASE
  WHEN REGEXP_MATCH(Query, ".*ticketleap.*|.*ticket leap.*|.*ticketlesp.*") THEN "Brand"
  ELSE "Non-Brand"
END

Short-tail vs. long-tail keywords

First, create a Word Length field:

LENGTH(REGEXP_REPLACE(Query, "[^\t\n\f\r ]", "")) + 1

This function will count how many words are in your query. Next, create a field for Word Count (or whatever you need), similar to the one below:

CASE
  WHEN Word Length = 1 THEN "1 word"
  WHEN Word Length = 2 THEN "2 words"
  WHEN Word Length = 3 THEN "3 words"
  WHEN Word Length = 4 THEN "4 words"
  WHEN Word Length = 5 THEN "5 words"
  WHEN Word Length = 6 THEN "6 words"
  WHEN Word Length = 7 THEN "7 words"
  WHEN Word Length = 8 THEN "8 words"
  ELSE "8+ words"
END

Summary

• As an SEO, telling the story of your efforts through reporting can be difficult.
• Looker Studio provides a great way to connect to multiple data sources and build effective SEO reporting dashboards.
• Utilizing calculated fields empowers you to provide better analysis by creating custom metrics and dimensions from existing ones.
• Better analysis helps you become a better SEO, making data-driven decisions for optimizations.