Upcoming LunaMetrics Seminars
Los Angeles - Anaheim, Sep 8-12 Washington DC, Sep 22-26 Boston, Oct 6-10 Chicago, Oct 20-24

Automate GTM’s Lookup Table Macro from a Spreadsheet

Upside down house

Get it? It’s an upside-down house. She’s looking up at a table!

Earlier this year, Google Tag Manager added a great new feature called the Lookup Table Macro. It’s pretty simple to use. Populate the table with two columns of data. Feed a value into it using a macro and if it finds that value in the left column, it will return the value next to it. This useful macro isn’t meant to replace complicated Javascript switch statements or advanced regular expressions. But it can take a large list of data and save us lots of time!

There are a number of uses that spring to mind when working with this type of Macro, but most will depend on your individual site and how it’s laid out. If your site has a relatively small number of pages, consider using a Lookup Table to classify pages into categories using the page URL. Perhaps you have five different events and want to populate the value parameter with something different for each one. Or if you have a content site and want to pass in the month of a published article, you may need a table to convert numbers to the full month names, which is just one of the many examples in Simo Ahava’s blog post.

These tables are easy to set up for small batches, but can get tedious if you need to add 20, 50, or even hundreds of rows. So what can you do to speed this process up?
Gift Basket

1. Offer presents to the Google Tag Manager team in exchange for CSV import

I assume they like coffee and chocolates, though that might be a generalization.  A nice gift basket of popular books could do the trick. Seriously though, it makes sense that this feature will be implemented some day, and hopefully sooner rather than later! Until then, read on….

2. Use browser extensions/macros to automate the process

If you’re a frequent user of iMacros or some other tool, this may be the option for you!  Sometimes I find these useful, or sometimes the setup takes me longer than it would take just to get the project done. Phil Pearce has a comment here that may help you out.

3. Use a combination of Google Docs and Javascript!

Or, you can follow these easy steps  and let LunaMetrics do all the work for you with the help of Google Docs, the Developer Console, and a little Javascript magic!

Let's Get To Work!

1. Copy the Spreadsheet to your Drive

Open up our handy-dandy Lookup Table tool here. The doc is protected, so you won’t be able to make any changes. Never fear, you can either download it as an Excel file or go to File, Make a Copy to save the spreadsheet to your own Google Drive.

2. Fill in the sheet

There are instructions on the sheet, but you can either type your table into the spreadsheet, or copy and paste your information from another source (ex. Excel). You’ll notice as you do so that Column H will start filling in with green cells. This is a good thing!

3. Copy the goods

Once you’re finished inputting your data into the spreadsheet, you’ll need to select all of these fancy green cells and copy them.

4. Head into the Console

Now head over to GTM, where your shiny new lookup table is anxiously awaiting. Hit F12 to open the Developer’s Tools, and find the Console. This may vary by browser, but was tested and worked perfectly in Chrome, Firefox and even IE!

5. Paste in the Cells

Now that you’re in the Console, just hit paste, then execute the Javascript you just pasted in! It may take a second for it all to finish, depending on your browser, how many rows you have, etc…

6. Double check your Data

As with any quick fix, you’ll want to double check to make sure it works properly!

That’s it! Give it a shot and let me know how it goes!

Interested in getting the most out of Google Tag Manager? Check out our Google Tag Manager Workshop which provides hands-on training!
Jon Meck

About Jon Meck

Jon Meck is a Digital Analytics Engineer working with Google Analytics and Google Tag Manager. He is an Excel enthusiast and strong proponent of the "Work Smarter, Not Harder" mantra. He has a history of working with web technologies for companies large and small. Outside of work, Jon enjoys late nights working on pet projects, running races, and spending time with his family.

http://www.lunametrics.com/blog/2014/03/26/automate-gtm-lookup-table-macro/

3 Responses to “Automate GTM’s Lookup Table Macro from a Spreadsheet”

Les says:

NIce work Jon. I have been struggling with finding the best way to import +250 URLs for a macro I want to run…. And well… I am not struggling anymore!

Shuki Mann says:

Cool trick man!

Tnx for sharing

Leave a Reply