Using Google Sheet lookup tables

Identify brands and regions based on the campaign name

Sometimes you may have a situation when there are too many conditions to evaluate and creating IF-THEN rules becomes too complex.

Imagine that you create advertising campaigns for BMW, Audi and other car manufacturing brands in different geographical regions. You may add brand and region information to campaign names (_bmw, _audi, _eu, _africa and so on) as shown below:

Brands and regions in campaign names

If you then want to create new dimensions called “Brand” and “Region” based on the advertising campaign that you run, you can create lookup tables that Octoboard will use to identify brands and regions based on campaign names.

Google sheets lookup table

Based on the configuration above, campaign “SEO-FRM_audi_eu” will result in AUDI and EUROPE values populated in fields BRAND and REGION.

Once you set up the lookup tables, Octoboard will periodically read the Google Sheets documents and update the lookup values.

Lookup tables are re-read by Octoboard every 3 hours.

Over time, as you create more campaigns and introduce new brands or regions, you can simply modify the lookup tables and Octoboard will pick up the changes automatically for you.

Brands and regions in octoboard dashboards

Data Explorer widgets above show campaigns and key advertising data broken down by brands and regions. Brands and regions are retrieved from campaign names using the lookup tables above. As you can see, “AUDI” brand and “EUROPE” region were calculated for campaign “SEO-FRM_audi_eu”.

How to configure lookup-based dimensions

In this example, we are using the Combined DataSet. The new dimensions - BRAND and REGION - will be added to the combined dataset that will contain campaign names from all paid advertising networks. Go to “Data Streams” and click on the “Combined Data Analytics” dataset name to start adding new dimensions.

Combined data set in ppc data analytics

Use the “DataSet Dimension” button to add a new field:

Dataset dimension button

Once in the EDIT mode, enter the new dimension name (e.g. “BRAND”) and click the “Add New Rule” button.

Adding brand rule

When adding the new rule, make sure you select the “Calculation” rule type and “Lookup” field option.

Brand lookup option

When specifying lookup options, you will be prompted to select the Google Sheet document and lookup fields. We assume that the following lookup table is used:

Google sheets lookup table

The following image shows the configuration settings based on the tables above:

Brand lookup function
  • Default value - result value that will be used if campaign name value is not found in the lookup table
  • Lookup value - the dataset field that we will try to match (“Campaign” in our case)
  • Search In - name of the lookup table column (“Brand Suffix”) that we will try to match
  • Return - name of the lookup table column that Octoboard will return if a match is found
  • Function - search (lookup) function that Octoboard will use to match campaign names. In this case, we use the “Contains” function since we are trying to find a “Brand Suffix” in the name of the campaign. Other options are “Exact match” and “Starts with”

To configure the REGION dimension based on our lookup tables, use the following configuration setting:

Region lookup configuration