Using Google Sheet lookup tables
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:
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.
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.
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.
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”.
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.
Use the “DataSet Dimension” button to add a new field:
Once in the EDIT mode, enter the new dimension name (e.g. “BRAND”) and click the “Add New Rule” button.
When adding the new rule, make sure you select the “Calculation” rule type and “Lookup” field 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:
The following image shows the configuration settings based on the tables above:
- 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:
