Google Looker studio calculated fields

Google Looker studio calculated fields

Below are listed some commonly used calculated fields that developers would use for Google Looker studio dashboards.

• Let’s start off with one of the most used google Looker studio formulas; you need to extract the branded keyword from non-branded keywords. You can do this by adding your data sources where you want to apply and creating custom dimensions with what information will be used for manipulation. Afterward click on “Add A Field” and copy/paste the following formula

CASE WHEN (REGEXP_MATCH(Query,".*brandname1.*|.*brandname2.*|.*brandname3.*")) THEN "Brand Keywords"
ELSE "Generic Keywords"
END 

Connector: Google Search Console

This unique and powerful algorithm can help you to separate branded from non-branded keywords, which is perfect for those who are looking into how they may be able to apply this on their own. Make sure to adjust your brand name when you copy/paste the formula so it can work well with your data. Field name is required and you can click the SAVE button. Now we can compare branded vs non-branded traffic distribution and add metrics to analyze it.

• To find out what the distribution of long-tail vs short-tail keywords is, without having too many false positives or negatives in your ranking data set-up is to use the following formula. For this formula you will need two different custom calculated fields – one to count words in the formula and another which will detect these two. The output should be more engaging.

The first formula will simply calculate the number of words within your keyword, I’ll call this field “number or Keyword Length.”

LENGTH(REGEXP_REPLACE(Query, "[^tnfr ]", "")) + 1

Connector: Google Search Console

The second formula will split these keywords into short tail and long-tail categories based on the number of words in a keyword. We’ll call this field Keyword Length Type, or how many letters are at either end with respect to being capitalized:

CASE WHEN REGEXP_MATCH(Keyword Length,"1|2|3") THEN "Short Tail Keyword"
Else "Long Tail Keyword" End

Connector: Google Search Console

By looking at how many people are searching for keywords that fall into different categories, you can easily identify which ones have a lot more potential and should be prioritized over others when building your SEO strategy!

• The keywords intent analysis is a formula that needs to be customized depending on the industry you’re in. The basic is to measure how people search for your product or service and use those terms when they find what they need online (and vice versa).

case
when REGEXP_CONTAINS(Query, "how|why|does|which|when|who|which|guide|tutorial|learn|examples|resource|ideas|tips") then "Informational Intent"
when regexp_contains(Query, "best|top|vs|review|cheap|comparison") then "Commercial Intent"
when REGEXP_CONTAINS(Query, "foot|romeonfoot") then "Navigational Intent"
when REGEXP_CONTAINS(Query, "Buy|price|cheap|expensive|recommendation|recommended|near me|firm|coupon|order|purchase|pricing") then "Transactional Intent"
ELSE "Informational Intent"
END

Connector: Google Search Console

This formula will detect the intent of the keywords and divide them into 4 sections: Informational, commercial, navigational and transactional keywords. The pipe (|) symbol is another regex function that means “or.” So, if you have different industries and different types of the keywords you can adjust the formula accordingly. For example, keywords containing questioning words or words such as “guide, ideas, tips” can be defined as informational: How to build digital marketing reports. Keywords containing Best, Review, Top, VS, Cheap are usually commercial keywords. Keywords containing buy, order, price, shop are transactional but again this depends on the industry and we recommend you adjust your list according to your specific industry. For local business transactional words will be “near me”, for banking industry transactional words will be “open” for apps will be “download”, etc. So, you can always add more keywords separated by the pipe symbol in the formula to reflect your particular business. Navigational intent are simply general keywords containing the brand name.

If you are a local business, you can add one more row in the formula to define Local keywords: 

case
when REGEXP_CONTAINS(Query, "how|why|does|which|when|who|which|guide|tutorial|learn|examples|resource|ideas|tips") then "Informational Intent"
when regexp_contains(Query, "best|top|vs|review|cheap|comparison") then "Commercial Intent"
when REGEXP_CONTAINS(Query, "foot|romeonfoot") then "Navigational Intent"
when REGEXP_CONTAINS(Query, "Buy|price|cheap|expensive|recommendation|recommended|near me|firm|coupon|order|purchase|pricing") then "Transactional Intent"
when REGEXP_CONTAINS(Query, "us|united states|new york") then "Local"
ELSE "Informational Intent"
END

Connector: Google Search Console

Again, depending on where your business is located, you can add and modify keywords for your local search.

• Custom channel grouping based on different sources and mediums is another helpful formula for all digital marketers.

CASE 
WHEN ((Source="direct" AND Medium="(not set)") OR Medium="(none)") THEN "Direct" 
WHEN Medium="organic" THEN "Organic Search" 
WHEN (Social Source Referral="Yes" OR REGEXP_MATCH(Medium,"^(social|social-network|social-media|sm|social network|social media)$")) THEN "Social" 
WHEN Medium="email" THEN "Email" 
WHEN Medium="affiliate" THEN "Affiliates" 
WHEN Medium="referral" THEN "Referral" 
WHEN (REGEXP_MATCH(Medium,"^(cpc|ppc|paidsearch)$") AND t0._ga:adDistributionNetwork_!="Content") THEN "Paid Search" 
WHEN REGEXP_MATCH(Medium," ^(cpv|cpa|cpp|content-text)$") THEN "Other Advertising" 
WHEN (REGEXP_MATCH(Medium,"^(display|cpm|banner)$") OR Ad Distribution Network="Content") THEN "Display" ELSE "(Other)" 
END

Connector: Google Analytics

This formula allows you to group your traffic sources into hats based on specific rules.

• You can also group your content for optimization, based on the type of the language:

CASE 
WHEN CONTAINS_TEXT(Landing Page,"/es/") THEN "Spanish"
WHEN CONTAINS_TEXT(Landing Page,"/fr/") THEN "French"
WHEN CONTAINS_TEXT(Landing Page,"/de/") THEN "German"
WHEN CONTAINS_TEXT(Landing Page,"/it/") THEN "Italian"
else "English" end 

Connector: Google Analytics

You can use sophisticated formulas and real calculations in the Custom fields to generate new numbers. You can find a complete list of Google Looker studio functions, which you can use in your Google Looker Studio Reports at this link.