Preparing the data

Marketing Analytics in Google Sheets

Luke Pajer

Digital Marketing Specialist

Digital marketing data set

Example Data Set

Marketing Analytics in Google Sheets

Add a source selection dropdown

Source can be either Google or Facebook

Data validation type: List of items

  • Works well for a small number of items
  • Best choice if the list remains constant
  • Provides an easy-to-use dropdown menu

source selection

Marketing Analytics in Google Sheets

Add another source selection dropdown

Source can be either Google or Facebook

Data validation type: List from range

  • Works well for a large number of items
  • Best choice if the list changes often
  • Provides an easy-to-use dropdown menu

Campaign Selection Dropdown

Marketing Analytics in Google Sheets

Filter data based on source selection

Filter the data based on the value in the source dropdown

=IF(len(A1) > 1, 
    FILTER(A5:E10, REGEXMATCH(A5:A10, A1)), 
    A5:E10)

1. Determine that the cell A1 has a value

2. FILTER() original table range A5:E10

3. Use REGEXMATCH() to match the Source

4. IF() no value, return original table

Filter Data Action

Marketing Analytics in Google Sheets

Building a regex table

Filter tables

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

Aggregate by categories

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

Remember that we will be writing regular expressions in separate cells for this table

Marketing Analytics in Google Sheets

Let's prep the data!

Marketing Analytics in Google Sheets

Preparing Video For Download...