1. Test a string using REGEXMATCH
Welcome back! In this lesson, we will build on your regular expressions knowledge by discussing one of Google Sheet's predefined functions, REGEXMATCH.
2. Recall the campaigns of interest...
Here, we are interested in campaigns that are specific to users.
3. Recall the campaigns of interest...
Rather than setting up a regular expression to match R users and Python users, we want to set one up that matches any campaign that has users in the name. If spreadsheet users is eventually added as a campaign, the regular expression would not need to be rewritten and would already filter for that campaign.
Previously, we studied impressions and click metrics. Let's discuss cost and cost-per-click or CPC metrics.
Here, cost is the total amount spent by the campaign's ad group and CPC is the average cost each time someone clicks on one of your ads.
Digital marketers generally want to see high cost and low CPC, but this is not always the case.
4. REGEXMATCH()
To create this regular expression, we first need to know if the campaign name contains the word users in the string. The best way to do this is using REGEXMATCH, which returns a boolean, true or false.
REGEXMATCH is often used to filter tables by using REGEXMATCH as the condition in a filter function.
It is also very useful when aggregating specific categories from a large data set or table. For our table, we would use sum to calculate total cost, and average to calculate the average cost-per-click.
5. Filter with REGEXMATCH()
The REGEXMATCH function takes two arguments. The first argument is the cell, or range of cells, in which you want to test and see if the regular expression matches. This regular expression is the second argument, which will be written as a string, or simply put, wrapped in quotes.
The filter function then filters the range for the cells that match the REGEXMATCH function.
Here, we can see how to filter the table for campaigns specific to users.
6. Filter with REGEXMATCH()
In the previous example, the regex was written directly in the REGEXMATCH function, but you can also write regex in a separate cell that you reference.
Writing the regular expressions in a different cell allows you to see exactly what your regex looks like without having to change it in the formula bar, where you risk making an error to the functions or the entire formula.
A great way to become more comfortable with regular expressions is through trial and error, which is easier when you can see the regex in a cell, rather than in the formula bar.
7. Aggregate with regular expression
REGEXMATCH is also very useful when aggregating data. To get the total ad spend or total cost, filter the range you would like to sum, in this case, the cost column, then set up the REGEXMATCH to only include rows that match the regular expression and wrap that in the sum function.
To get the average cost per click, follow the same structure with filter and REGEXMATCH but wrap that in the average function.
8. Let's get to work!
Now that we have some ways to apply our knowledge of regular expressions, lets take a look at how to filter and aggregate tables using REGEXMATCH! Let's get to work!