Prepared for NADA DM01

Objective: Learn how to build a custom field in Looker Studio using a CASE statement to categorize branded and non-branded queries from Google Search Console data.

Introduction

Looker Studio (formerly Google Data Studio) allows you to transform and manipulate your data using custom fields. One powerful feature is the CASE statement, which enables you to create new dimensions or metrics based on conditional logic.

In this lesson, we’ll walk through the steps to create a custom field that identifies whether a search query is branded (contains your brand name) or non-branded.

What is a Branded Query?

A branded query is a search term that includes your company’s brand name or variations of it. Analyzing branded vs. non-branded queries helps you understand brand awareness and the effectiveness of your marketing efforts.

Steps to Create a Custom Field Using a CASE Statement

1. Open Your Looker Studio Report

  • Navigate to your Looker Studio dashboard.
  • Ensure you have connected your Google Search Console data to the report.

2. Add a Chart or Use an Existing One

  • You can apply the custom field to any chart or table that includes the Query dimension.
  • For this lesson, we’ll use a table for simplicity.

3. Create a Custom Field

  • In the Data panel of your chart, look for the Dimensions or Metrics section.
  • Click on Add Dimension (or Add Metric if appropriate), then select + Create Field.

4. Write the CASE Statement

In the Formula editor, input your CASE statement. Here’s how to construct it:

CASE
  WHEN REGEXP_MATCH(Query, '(?i).*yourbrand.*') THEN 'Branded'
  ELSE 'Non-Branded'
END

Explanation:

  • REGEXP_MATCH(Query, '(?i).*yourbrand.*'): Checks if the Query field contains the word “yourbrand” (replace with your actual brand name). The (?i) makes the match case-insensitive.
  • THEN 'Branded': If the condition is true, label it as Branded.
  • ELSE 'Non-Branded': If not, label it as Non-Branded.

Example with a Brand Name:

If your brand is “Dodd Ford”, the CASE statement becomes:

CASE
  WHEN REGEXP_MATCH(Query, '(?i).*dodd.*') THEN 'Branded'
  ELSE 'Non-Branded'
END

5. Name and Save the Custom Field

  • In the Field Name box, enter a descriptive name, such as “Branded vs. Non-Branded”.
  • Click Apply to save the custom field.

6. Use the Custom Field in Your Chart

  • The new field now appears in your list of available dimensions or metrics.
  • Add it to your chart to categorize queries accordingly.

7. Customize Further if Needed

You can extend the CASE statement to include multiple brand-related terms:

CASE
  WHEN REGEXP_MATCH(Query, '(?i).*(acme|acme corp|acme corporation).*') THEN 'Branded'
  ELSE 'Non-Branded'
END

Testing and Validation

  • Preview the Data: Check a sample of the data to ensure queries are correctly categorized.
  • Filter the Table: Apply filters to display only Branded or Non-Branded queries to validate the logic.
  • Adjust as Necessary: If you find mismatches, refine your regular expression in the CASE statement.

Conclusion

By creating a custom field with a CASE statement in Looker Studio, you can effectively categorize and analyze your Search Console queries based on custom logic. This empowers you to gain deeper insights into user behavior and the impact of your branding efforts.

Next Steps

  • Explore Advanced CASE Statements: Incorporate more complex conditions or multiple categories.
  • Visualize the Data: Use charts to visualize the performance of branded vs. non-branded queries over time.
  • Share Insights: Leverage these insights to inform marketing strategies and report to stakeholders.

Remember: Regular expressions in Looker Studio use RE2 syntax, and the (?i) flag makes the pattern case-insensitive. Always test your custom fields to ensure they work as expected.