Test a string using REGEXMATCH

Marketing Analytics in Google Sheets

Luke Pajer

Digital Marketing Specialist

Recall the campaigns of interest...

Digital Marketing Table

Marketing Analytics in Google Sheets

Recall the campaigns of interest...

Digital Marketing Table 2

  • Cost: total amount spent by the campaign's ad group
  • CPC: the average cost each time someone clicks on an ad
Marketing Analytics in Google Sheets

REGEXMATCH()

=REGEXMATCH(string to test, regular expression)
  • Great for testing existence of a string within a string
    • Returns a boolean (True or False)
  • Often used for filtering tables
    • Creating subtables using FILTER()
  • Useful when aggregating specific categories
    • SUM()
    • AVERAGE()
Marketing Analytics in Google Sheets

Filter with REGEXMATCH()

=FILTER(range, REGEXMATCH(string to test, regular expression))

Filter with REGEXMATCH

Marketing Analytics in Google Sheets

Filter with REGEXMATCH()

=FILTER(range, REGEXMATCH(string to test, regular expression))

Marketing Analytics in Google Sheets

Aggregate with regular expression

To get a sum of the total ad spend or total cost:

=SUM(FILTER(range to sum, REGEXMATCH(string to test, regular expression)))

To get the average CPC or Cost-per-Click:

=AVERAGE(FILTER(range to average, REGEXMATCH(string to test, regular expression)))
Marketing Analytics in Google Sheets

Let's get to work!

Marketing Analytics in Google Sheets

Preparing Video For Download...