Need to make a big lookup table? Wish lookup tables supported regex? We built a Google Sheet that will save you a ton of time. Grab the template or read on to learn more.
GTM Lookup Tables Are Awesome
Google Tag Manager's 'lookup table' Variable type is awesome because it:
- allows for great flexibility without requiring custom JavaScript or tons of Tags
- is very fast, regardless of table size (i.e. the number of possible matches)
If you're not familiar with lookup tables, I recommend checking out some use cases and learning about how flexible they can be. (When checking out those links, remember that "macros" = "variables" in the world of GTM.)
The Problems
Despite being an extremely useful feature in Google Tag Manager, the lookup table Variable can be tedious to configure and its exact-match nature is limiting.
Creating Large Lookup Tables Is A Data Entry Job
A lookup table Variable is simple to create by hand when it looks like this:
But what if there are a ton of key/value pairs? Maybe you have a large number of domains, each to be mapped to a tracking id. Or you want to map system ids to human-readable labels (e.g. YouTube video ids to video titles). Whatever the case, it's not uncommon to need a massive lookup table. Yet GTM provides no simple mechanism for importing key/value pairs or editing them in bulk, so we're faced with creating lookup table Variables that look like this …
… by hand.
Even if you have the time (or some unlucky intern), this manual task is prone to human error. (And you'd better save often, lest some crash or mis-click causes you to lose your progress on the Edit Variable page!)
Lookup Tables Are Exact-Match Only
The GTM lookup table Variable can only perform exact-match (a.k.a. "is equal to") lookups. So it cannot be used in cases where an indeterminate number of input values must be matched. In cases where the input values are known, but frequently change or are simply too numerous to manage, using a lookup table is possible but not practical.
For example, say my website deals with state-specific content. Say my CMS uses URLs with full state names in some cases and state abbreviations in others, sometimes upper-, sometimes lower-, sometimes capital- case. I want to track all of these variations consistently.
Until Variable casing modifiers are added to Tag Manager, the only way to handle inconsistent casing in the input of a lookup table Variable (without using custom JavaScript) is to create a key-value pair for every casing variation of the input variable. So for each state, I'd need six key/value pairs ("Pennsylvania", "PENNSYLVANIA", "pennsylvania", "PA, "Pa", "pa"). So, a relatively small number of inputs has multiplied and brought us back to the data entry issue.
Let's say I also want to match user search inputs. To account for all possible case variations of names of American states and territories, I'd need to match 328,228 inputs. If I had to match state names as a term within an arbitrary search query, I'd have infinite possible inputs.
Cases like this warrant more flexible matching.
The Solution
This tool was built to simplify the act of building large lookup tables, by allowing them to be created using a spreadsheet. It also adds options for more flexible matching, going beyond strict exact-match lookup functionality to offer substring matching ("Contains") as well as regex matching and extraction. (This is a Google Sheet, but it could be adapted for Excel.)
Instructions
Make a copy of the template (you have to be logged in to a Google account) and follow the steps below to use the GTM Match Table Variable Generator. You can create up to 10 new variables at a time using this tool.
- Enter the name for the new Variable.
- Enter the input Variable name (you can select a built-in Variable from the dropdown, or type a custom Variable name). You can leave this this blank, but you'll need to set it in GTM before you can use the Variable.
- Optional - Enter the default value (if you want to reference another Variable, use GTM's
Variable reference notation).
- Select the lookup type. "Exact Match" will yield a standard GTM lookup table. The rest are custom types, which are detailed in the Flexible Match Tables section below.
- Enter the key/value pairs.
- Optional - Repeat steps 1-5 for up to a total of 10 new Variables.
- Open the custom menu to the right of 'Help', and click 'Download'.
7a. The first time you use this spreadsheet, you'll get an “Authorization Required” dialog. This authorization is required only to automatically download a JSON file. If you prefer not to grant any scripted access, jump down to 7b for the manual alternative. Otherwise, click “Continue.”
You'll see the authorization prompt, which indicates that the script will only be able to access the current spreadsheet. Review the permissions (as you always should :), and click 'Allow'.
Then, the Container export file will download.
If this scripted method fails at any step, try the manual method described in 7b.
7b. As an alternative to downloading the export file from the Google Sheet, you can create your Container export file manually. To do so, highlight cell B1, and press Ctrl+C (or Cmd+C on a Mac) to copy the cell's value. Paste into a plain text file, and save with the extension ".json".
- In your GTM Container, go to Admin > Import Container. Select the file created in the previous step.
- IMPORTANT: Select “Merge.”
Then click "Continue."
That's it! Your Container now has the the Variable(s) that you defined in the spreadsheet, ready for use in your measurement implementation.
Flexible Match Tables
The flexible match options are (each with a case-sensitive and case-insensitive version):
- Contains
- Regex Match
- Regex Extract (dynamic output based on inputs)
These match options produce "flexible match tables," which are implemented as custom JavaScript Variables (based on modified versions of Simo Ahava's Simple RegEx Table). Once generated, these custom tables can be edited by anyone who is even vaguely familiar with JavaScript.
By default, these flexible match tables will avoid the unexpected consequences of having an undefined input value (which is treated as the string "undefined"). This protection can be turned off using the hidden configuration tab.
Note that with flexible matching, multiple keys might match an input. This implementation returns only the first match.
Performance
It's important to know that these flexible match tables are inherently different structures than normal lookup tables (as Simo details in his post). The key difference is performance. Exact-match lookup tables are much faster than any flexible match type. The difference is negligible for small tables, but the more key/value pairs added, the more chance there is of causing noticeable slowdown with flexible match types. The moral: Don't use a flexible lookup type if an exact-match lookup type can reasonably be used for the same thing.
Before you use a flexible match type, ask yourself:
- Can I make a finite list of input values (even if it's obscenely long)?
- If so, will that list stay the same over time, or is it feasible to update tracking whenever the list changes?
If the answer is "yes" to both of these, you should consider using a regular exact-match lookup table. It may take a bit of extra work, but the the performance benefit is well worth it. Besides, wouldn't you rather avoid custom Javascript in your Container if you can?
Regex Extract
The 'regex extract' match option performs a standard regex match, but allows the returned value string to pull in parts or all of the matched string. The standard JavaScript notation for referencing capture groups may be used, including: $# to reference capture groups (e.g. $1), as well as $& or $0 to reference the entire matched section of the input string.
In the example above, an input of "info.site.com" would match the key "(.+)\.site\.com$", and the returned Variable value would be "Subdomain: info".
Limitations
Due to character limits in Google Sheets, the number of key/value pairs this tool can handle is limited. The actual number depends on the sizes of the values and keys. With an average key size of three characters and average value size of ten characters, the formulas can handle a maximum of 399 key/value pairs for a single Variable. Note, the maximums apply across all Variables, so if multiple large lookups are needed, you may need to download them one at a time.
Issues?
- When importing, if you get the error "Failed to Create Container Draft", then you need to fix errors in your Container (in GTM, not in this tool) before importing.
- You can reference Variables in this tool that are not actual Variables in your Container, and still successfully import. However, GTM will not allow you to publish or preview, and will point you to the invalid Variable reference(s).
- The spreadsheet has some validation to catch issues before trying to import. Errors are indicated by a little red triangle at the top-right of the cell; click the cell to see what the issue is.
- If you get the error: "File format is invalid" when importing, then it may be the result of a bug in this tool. Once you confirm there are no errors in the sheet (see previous bullet), please email me with a link to your spreadsheet: stephenh (at) seerinteractive.com.
Feel free to share any other feedback on Twitter @smhmic or in the comments below!
