Guide to Enhanced Ecommerce Product Data & Collection

Google Analytics Enhanced Ecommerce provides insight into Ecommerce site performance in detail that was previously impossible using standard Google Analytics methods.  In order to provide these insights, data  is collected in a new and unique way. It is important to understand the breadth of Enhanced Ecommerce data collection capabilities in order to fully reach the insights that the feature offers. This guide will take you from Ecommerce product page to Enhanced Ecommerce Product Data.

Enhanced Ecommerce Product Page
A pretty standard Ecommerce Product Page. Thanks to
Enhanced Ecommerce Reporting
Enhanced Ecommerce Product Reporting with Custom Dimensions and Metrics.

Product Data Problem and Solution

The new product schema was created to provide data in a way that answers questions that are specific to products rather than pages or events. Consider event hits; their schema uses a sentence-like structure to describe an action.  This does not map well to a product entity that is not  bound by specific instances in time. The product schema is more similar to pageview hits that collect attributes of a page entity over time. But still,  the data is collected on the page is in a different fashion.

For more, see Carmen Mardiros’ great conceptual explanation of Enhanced Ecommerce Data Layer and my slides on Enhanced Ecommerce schema.

Google Analytics Enhanced Ecommerce assigns a collection or properties to each product .  At least one property is mandatory (either name or id) and other properties are optional: brand, category (up to five tiers), variant, price, quantity, coupon, and position. These properties describe either an attribute of the product or the context in which the product is acted upon. These standard properties are great for providing a holistic understanding of how products are interacted with in the context of an Ecommerce store, but they don’t give the whole picture of how that product is interacted within thecontext of any specific business.

 Understand how a given product is interacted with based on qualitative dimensions like its availability, backorder date, release date or physical dimensions or appearance.

For instance, you may want to understand how a given product is interacted with based on qualitative dimensions like its availability, backorder date, release date or physical dimensions or appearance. Likewise, there is more to understand about products’ quantitative information such as cost of goods sold, previous price, discount or profit. This is where custom dimensions and metrics come in.

Product Data Collection

Data is collected about product entities in a way that very appropriately fits the concept of a product; each product is represented by Javascript Object. An Object is simply a collections of properties and values that describe those properties. For instance a product entity would be represented by an Object that has a name property of  “Clapper Light Switch” and a price property with a value of 30.00. To extent the ability for an Object to describe a product entity we can specify additional properties that further describe the product like “cost of goods sold”, or “back order date.” At the code level, this is done by simply adding one more property “key”:”value” pair to the product entity object. The only difference is that the property name will be a place holder such as “dimension9″ or “metric4″ to be assigned later within the Google Analytics interface. In Universal Analytics it would look like this:

ga("create", "UA-XXXXX-Y");
ga("require", "ec");
ga("ec:addProduct", {
   "id": "81301",
   "name": "Xantech AC1 Controlled AC Outlet",
   "price": "78.55",
   "brand": "Xantech",
   "category": "AC1 Controlled AC Outlet",
   "variant": "white",
   "dimension3": "In stock - Ships Today", // stock status custom dim.
   "metric2": 5,                           // rating stars custom metric
   "quantity": 1 });
ga("ec:setAction", "add");
ga("send", "event", "Product", "Add to Cart", "Xantech AC1 Controlled AC Outlet");

and using the Google Tag Manager data layer it would look like this:

   "event": "addToCart",
   "ecommerce": { 
      "currencyCode": "USD",
      "add": {
         "products": [{
            "id": "81301",
            "name": "Xantech AC1 Controlled AC Outlet",    
            "price": "78.55", "brand": "Xantech",            
            "category": "AC1 Controlled AC Outlet", 
            "variant": "white", 
            "dimension3": "In stock - Ships Today",  // stock status
            "metric2": 5,                            // review stars
            "quantity": 1 

For a great working examples of this, see:

Setting Custom Dimension & Metric Names

The first thing to note is, if you are using the Google Tag Manager data layer to collect product data, make sure you have checked the “Enable Enhanced Ecommerce Features” and “Use data layer” boxes shown below. Google Tag Manager Add to Cart Event No matter if you are using the data layer or standard Universal Analytics collection code, you will have to do two things:

  1. Ecommerce and Enhanced Ecommerce Reports must be enabled.  Just go into your Admin section > choose a View > Ecommerce Settings  and toggle  Enable Enhanced Ecommerce Reporting to “ON”.
  2.  Custom Dimension and Metric must be activated, named, and configured as you want them to appear in Google Analytics reports. This is also done within the Admin interface. Go to Admin > choose a Property > Custom Definitions and click Custom Dimensions or Custom Metrics. Set the name, the scope to Product, and the state to On. For Custom Metrics, set the appropriate formatting type.

Enhanced Ecommerce Product Custom Metric Setup Note that these hits must be set at the product hit level. Otherwise, the data will not be collected as expected, if at all.

Enhanced Ecommerce Product Data Reports

Great, everything went well up to this point an you are ready to appreciate all your shiny new insights. To find these reports within the Reporting interface go to Conversions > Ecommerce >  select a Product or Sales report .

Enhanced Ecommerce Reporting

From there you can see all your products and sort and filter them by any given property that have been collected. Metrics can also be added to Custom Reports to provide aggregate insights.

Inspecting, Debugging and Perfecting Product Data Collection

You may see something funny in your reports at this point or nothing at. In my experience, Enhanced Ecommerce data is reported as soon as the hit that it was sent with is reported. This is usually relatively quick. (Under 10 minutes) If something looks amiss, don’t worry, there are usually a few simple fixes that can be made to make sure the data is being collected and reported correctly. Assuming you have done everything correctly up to this point, there is may be a few things you need to check and fix. Heres a list:

Debugging Enhanced Ecommerce Hits
This is what you are looking for in the Google Analytics Debugger.
  • Make sure the hit is being sent to right property.
  • Use mandatory product  fields (name or id) as report dimensions.  This is helpful when starting out. If you are looking at a  report with a primary dimension of Product List, but are not yet collecting product list data the report will appear to be empty.
  • Make sure Ecommerce data is sent with a standard Google Analytics Hit. Enhanced Ecommerce data is buffered on the page until a stand Google Analytics hit is sent. Then the Ecommerce data is collected with that hit.
  • Make sure Javascript Object data structure is correct and without errors. Use my data layer debugger bookmarklet to make sure the data is in the data layer. Also, keep an eye on the Javascript console and use to make sure there are not errors and everything is formatted correctly.
  • Try this cool method of inspecting each object that is pushed to the data layer is  by using  JSON.stringify to view Object in data layer. Just type the following command into your Javascript console and view the object in JSON (JavaScript Object Notation).
// where 0 is the index of the first object in the data layer array

Accessing Nested Values with Google Tag Manager Variables

In the case that you are sending an event hit to Google Analytics that is carrying Enhanced Ecommerce info, you many want to use a product’s attributes as the values for the event’s Category, Action, or Label or even Custom Dimension or Metric. Similarly, product data can be applied as a custom dimension on the pageview hit that, for example, is sent to on a product page to carry product detail view Enhanced Ecommerce information. In these case, if you are using Google Tag Manager, you can access the values of the product or actionField data using a Data Layer v2 Variable. These GTM Varialbes allow you to access property values that are nested within Objects or Arrays in the data layer. For instance, if you wanted to access the name of a product that was just added to a shopping cart (as shown above), you would use the following format without the quotes: “”. Note that the 0 is specifying the index (zero based count) of the Object noted in the array that is enclosed in [brackets].

Thanks Simo for getting me back on track with this.

A Note on Product Hit Scope Dimensions and Metrics

Custom Dimensions and Metrics are product hit level and wont be applied to an event or page on which the happen or to a user that interacts with them. This is done by setting the dimension or metric at the hit level. Just make sure to configure the hit Dimension or Metric accordingly.  Check out this old but good explanation of  hit scope by Justin Cutroni.

Start Collecting and Start Optimizing!

This may seem complicated but the power that it provides is well worth time spent in a detailed implementation. Please leave a comment if you have any questions. Or call us at SwellPath and let’s get this started!

Thank you to for the pretend data. I want everything in your store.

Agile Strategy for Data Collection and Analytics

This is one of my posts from the Swellpath blog. My original post can be found here.

If you are like most people doing business online, it seems like there is always a long list of digital to-dos that are somewhere between “that will happen in Q4” and “that should have happened by Q4 last year.” Aside from the constant stream of daily hiccups that arise due to the asynchronous nature of our medium, if you are like most others managing a website, you face broader development challenges of slow servers, uncooperative CMS’s, or lame mobile experiences impacting your online success.

This is not failure that you have to accept! Let me introduce you to a little thing that has been bouncing around in the software/web development community that will make your online business operations feel less like swimming in peanut butter. It’s called Agile Development and it’s sexy. It’s fast and sexy like a cheetah wearing high heels.

We can apply these principles of Agile Development to data collection, analytics and optimization to provide two exceptional benefits: rapid access to data and insight, and safeguards against constantly changing web properties.

For data-collection, analytics and optimization:

  • An Agile approach provides action before traditional methods provides insight
  • An Agile approach safeguards against the constant variability of the web medium
Analytics plan and strategy
“If you fail to plan, you are planning to fail!” — Ben Franklin

Learning from Feature Driven Development

The Agile Development concept covers an array of development methodologies and practices, but I would like drill into one especially coherent and efficient method of Agile called Feature Driven Development.

Feature-Driven Development essentially works like this: an overall project is planned as a whole then it is separated into discrete pieces and each of these pieces is designed and developed as a component and added to the whole. This way, instead of having many semi-functional components, the project’s most valuable components are complete and fully functioning.

Phased Implementation (Not Iteration)

Because you might have already heard something about Agile Development, it is important, at this time to dispel the notion that Agile development is defined by iterations upon products. In a sense that is true but mostly it is the complete opposite of the Agile approach. The only iterations that happen are the planning, implementation and completion of a new feature. This is not the same as adding layers upon existing features (more on this with the Definition of Done). The difference here is planning and the ability to see the project and business objectives as a whole.

feature driven development for analytics

Step 1: Develop an Overall Model

You must plan! Planning in an organization can be hard to motivate and difficult to initiate, but these planning steps will actually provide you with better, more actionable data sooner than not.

Understand the system. This is digital. There are a lot of moving parts. It is very important to really know how your digital presence affects your physical business and your overall business strategy and vice versa. Additionally there are likely many components within your business that are (or could be) affected by the data that can be collected. This leads to my next suggestions.

Ask questions and seek multiple perspective. This is time to confront your assumptions about your business, your pain points and your data needs. It is important to really know the processes and decisions that are taking place and how they are (or are not) or could be affected by data. Communicating with those who interact with and make decisions on the data at any level will be extremely insightful.

Be strategic. Look at the big picture of the future, define your goal and work backwards. Agility does not come by luck but rather by being aware of and prepared for all foreseeable possibilities. Consider how things will change and what parts of your digital presence are shifting. How will redesigns, platform changes, and code freezes affect your strategy? This is generally the best way to face an analytics problem so this step applies very well to analytics. Agile was created to solve the problems of being short-sighted and reactive.

Step 2: Define the Parts of Your Plan

This is where the fun starts. There are multiple ways an analytics strategy can be divided and further subdivided into parts. When considering how to divide the project into parts, the goal should be to get to define parts at their most discrete, independent or atomic level. This will be helpful in prioritizing the parts into steps. Ultimately,  these parts can be regrouped based on similarity and development implementation process.

By Web Property and Section

An organization’s web presence is often not limited to a single site or app. There may be different properties or sections of web properties with different intents. Inevitably, some of these properties or sections will have a bigger impact on your organization’s goals and thus would be prioritized differently.

By Data Scope (User, Page/Screen, Event)

Each web properties has layers of data that can be gathered from it. Data about the user in the app or website’s database, information about the content of the page, and information about how the user interacts with the app or website can all be thought of discretely. These differ in terms of intelligence and the actual development work that is required to collect the data.

By Data Use

Another way to divide up the data-collection needs is by end use. For instance, you may be an ecommerce store that has different people or teams who are merchandising, planning and creating content, managing email, social campaigns, or paid media campaigns and/or optimizing the application and user experience. The data needs for each initiative will often overlap with other initiatives but sometimes data needs will be very different from others. These different data needs can be thought of as different parts of your strategy.

By Data Depth

Think 80/20 rule in terms of granularity. Some data is instantly useful. For instance you may not be tracking clicks on your main call-to-actions or “Buy” buttons. These clicks are likely key micro-conversions and having this interaction insight can literally change your strategy overnight. Another layer of depth would be knowing what product was added to the cart as part of that event. A further layer would be configuring Google Analytics’ Enhanced Ecommerce to understand how that specific product flows from the product page to the checkout. Each of these examples provide varying depths of data but also require varying amounts of development time.

Other features like Google Adwords Dynamic Remarketing and Google Analytics Content Groupings can be thought of similarly as they need more information to associate with the user or page.

Step 3: Prioritize

This is the most important step. This is where the unique value of the Agile approach really shines. This can drastically lower the cost and shorten the time to data-driven action. All the planning and foresight that took place before can be leveraged to make the right decisions for the most success.

Consider Goals

Duh. The whole reason you are gathering data is to be data-driven. The parts of your plan that most directly affect your top-line goals should be at the top of the list. Think about every time you have said or heard “If we only knew abc we could achieve xyz.” Now depending on the value of xyz, prioritize data collection.

Consider Time

This is what Agile is all about! With goal impact in mind communicate with relevant parties and your development team or partners to understand how long it will take to implement the code to start gathering data. Sometimes the value of data will scale to the development time, other times it may be as simple as using a Google Tag Manager click listener on calls-to-actions to send events to Google Analytics within a few minutes. Overall, its good to have some data to orient your decisions right away so go for the quick wins first and work with that as code is being implemented to get the real data gold.

Consider Cost

Unfortunately, bottom lines still exist and often development resource cost will have to be justified in implementing code to gather data. Some data collection might be cost prohibitive but it is possible that, by gathering data that is easier to gather, such as standard Ecommerce implementation will give you the rationalization to, in time, get more in depth data. Overall, get the valuable data that comes cheap, squeeze the life out of it until you need more depth.

Step 4: Implementation Cycle (Plan, Implement, QA)

Now, for the moment we’ve all been waiting for, let the collection begin! This is the step that most people think of when they think of Agile development; sprinting to complete a feature and then releasing it.  For Agile analytics, this works the same way. Now that there is a list of analytics “features” or streams of data that have been prioritized each step should be planned, implemented and tested successively.


This is a more detailed plan than the overall model. This plan defines how the data will be collected. For example this is when Google Analytics Event or Custom Dimension naming conventions would be defined and documented. Be explicit. This will really improve the efficiency of the process.


Buy your development partner beer and pizza and pass your documentation on to them. Keep them happy and maintain a good relationship. There will be more implementations in the future. Hopefully your documentation is clear but be open and responsive to questions; this is all about speed and accuracy.

Quality Assurance

This should happen in your development environment so that when the code is implemented on the site, the data that is reported is clear and accurate. Be thorough as this implementation should stay this way well into the future. If changes are to be made, be discreet, just as in implementation.

These three steps can happen simultaneously, for example planning can happen on a future part as implementation and QA is happening on the present part.

Start Optimizing!

Agile is not simple but its also not magic. Speeding up the time to data-driven action is made possible by the planning that happens up front. Being proactive is not only a practice of Agile but also general best practice in analytics. It is the planning that makes agile efficiency possible. It may seem difficult, but putting in the effort to plan will put you in a position to act proactively agilely into the future.  Happy optimizing!

Bulk Hide, Show or Delete Multiple Sheets in Google Spreadsheets

Too many sheets! I came across the problem of having a large number of visible or unused sheets in cluttering up my Google Spreadsheets  often enough that I decided to solve it. This problem most often comes up when using Google Apps Scripts that auto-generate multiple sheets. If you have ever used the Google Analytics Magic Script, you know what I mean.

Too many sheets for one Google Spreadsheets

Using the Script:

Copy the code below into a script file in for the Google Spreadsheet you want to use it on, save the script file and reload the spreadsheet. A new menu will appear called “Spreadsheet Cleanup.” This menu has three options:

  • Hide Sheets
  • Show Sheets
  • Delete Sheets

Select an option and and the spreadsheet will prompt you to enter a word or phrase to match the names of the spreadsheets you would like to take action on against. In other words… enter part of the name of the sheets that you would like to take action on into the box and then it will do the job pretty quickly.  Note that you can only use letters in the name. Number will not work. Feel free to edit the code though.  (At first I was using the Javascript match method to search for sheet names but the regex would not allow for the prompt box.) Anyway, that’s all you have to do.

Hope you can find it useful too!

You can also make a copy of the script here:

The Google App Script:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetsCount = ss.getNumSheets();
var sheets = ss.getSheets();

function onOpen() { 
 // Try New Google Sheets method
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Spreadsheet Cleanup')
  .addItem('Hide Sheets', 'hideSheets')
  .addItem('Delete Sheets', 'deleteSheets')
// Log the error
 catch (e){Logger.log(e)}
// Use old Google Spreadsheet method
  var items = [
  {name: 'Hide Sheets', functionName: 'hideSheets'},
  {name: 'Show Sheets', functionName: 'showSheets'},
  {name: 'Delete Sheets', functionName: 'deleteSheets'}
 ss.addMenu('Spreadsheet Cleanup', items);
function deleteSheets() {
 var deleteSheetsContaining = Browser.inputBox("Delete sheets with names containing:"); 
 for (var i = 0; i < sheetsCount; i++){
  var sheet = sheets[i]; 
  var sheetName = sheet.getName();
  if (!sheetName.indexOf(deleteSheetsContaining)){
function hideSheets() {
 var hideSheetsContaining = Browser.inputBox("Hide sheets with names containing:"); 
 for (var i = 0; i < sheetsCount; i++){
  var sheet = sheets[i]; 
  var sheetName = sheet.getName();
  if (!sheetName.indexOf(hideSheetsContaining)){
function showSheets() {
 var showSheetsContaining = Browser.inputBox("Show sheets with names containing:"); 
 for (var i = 0; i < sheetsCount; i++){
  var sheet = sheets[i]; 
  var sheetName = sheet.getName();
  if (!sheetName.indexOf(showSheetsContaining)){

Inspecting and Debugging the Google Tag Manager dataLayer

the datalayer in action
ah data jokes…

The Data Layer Inspector and Debugger

This project began as a solution to a problem that I often faced setting up Google Tag Manager and specifically the data layer. The solution that I ended making was dataLayer logger, inspector, and debugger that offered the same features as the Google Analytics Debugger but for the data that was being held in or pushed to the dataLayer. It pretty-logs all the new contents of the dataLayer as it is pushed no matter if the dataLayer.push() happens on the page, in a script or in a custom HTML tag within GTM. Its all there as soon as it happens and it saves a fair bit of time.

datalayer debugger
Each object that is pushed to the dataLayer is logged like this.

One interesting difference between what I ended up with is that unlike the Chrome extension, this works across browsers (as far as I cared to test) and can be run as a standalone script, a custome HTML tag in GTM or even as bookmarklet. (Bare in mind I started this project about a month ago and, if you don’t already know, you will see why declaring that now makes sense shortly.) Also, Simo, if you ever read this, please integrate this into your Chrome extension.

The Google Tag Manager Debugging Problem

If you have ever worked with Google Tag Manager, you recognize that one of the most important, if not the most important feature of GTM is the dataLayer. This was a very conscious choice by the makers of GTM and other tag management companies to solve a problem that has arisen as websites have invariably become web apps with multiple players playing roles on a page, consuming and passing in data. That the data layer offered is a central stage for all the actors in the data game to meet and access the data that they require. It was an elegant solution to eliminated a lot of redundancy and has truly made data access and use a lot more agile.

The problem was that the Google Tag Manager interface, in its infancy was a bit less transparent than was ideal. GTM initially made claims that it would not require IT but that, for all but the most trivial cases was flat out untrue. In the beginning and still at the time that I write this, the process involves a fair bit of Javascript coding and hence, quality analysis and debugging that comes along with it. So if you have pushed to or pulled from the GTM dataLayer, you have entered this into your console more than once … many times more than one.

datalayer array
The tedious way to inspect the dataLayer.

You have inspected the dataLayer array and to see if that event was fired at the appropriate time or if all customer or event attributes made it through accurately. I did, quite often and that why I said, “These nine keystrokes, they can be automated.”

The Rabbit Hole of Problem Solving

Initially I thought it would be cool just to copy the style of the Google Analytics Chrome extension and just show every new push. But two things changed that initially course, this is not the same problem as that and I was getting really interested in the jQuery source. (Which is amazing if you every want to learn some really killer idioms.)

I learned about the Chrome Console API which as luck has it means I learned about the other cool Webkit browser, Firefox’s Console API. After that I realized I was half way there so I learned about decent but not quite as advanced Safari and IE console APIs. (Can you believe there are even cross browser considerations for the Javascript console!?)

So the main feature is just a nifty combo of the method and string styling in the better two browsers to display the pushed object. This all goes on because the main change that the script makes is that it redefines the dataLayer.push method to wrap around the original GTM version of the dataLayer.push method. Once I got that far I had to ask myself why don’t I do this, why don’t I do that and so on until I ended up with a console API of sorts specifically for one, in my opinion conceptually significant, Javascript array. If you want to learn more about all the properties and methods of the Data Layer Debugger I will write a bit of an API reference for kick later. Otherwise the comments in the code do a decent job explaining the what everything does.

The Bookmarklet

If you want to try it out click the bookmarklet below and open up your Javascript console and click around a bit. You’ll see it working, hopefully as expected. If you want yours to keep, drag the bookmarklet into your bookmark bar and click it on any page with a dataLayer and watch the interaction take place.


The Custom HTML Script

Here is the GTM debugging script on GitHub. Its kind of useless now that the good folks at Google Tag Manager have baked this functionality in to the GTM interface. ( I like to think they saw my code on github and said, “that guy Trevor, he’s on to something…” Try it out, maybe you will find it useful.

Automate Mobile Preferred Ad Creation with Adwords Scripts

Scripts Save Lives

I love algorithms and you should too. They provide simple consistency to life like no calendar, routine or habit ever could.  They do exactly what you tell them to do and they do it fast, and most beautiful of all, they make life automatic. (sigh of contentment)

I also love javascript but sadly, there are not a lot of places where javascript and algorithms intersect. Scripts for Adwords is one of those happy unions and this delicious union of medium and method has a little cherry on top called automation. Ain’t it sweet!

Adwords Scripts are great for automating reporting, pulling info from other API’s into Adwords and routine account maintenance. The idea for this one was brought up to me by my Google Adwords Rep. Scott, who it turns out, is rad. He called me up one day mentioning how we could optimize one of my accounts. (To be read: “make more money selling ads for Google.”) He suggested that we add mobile preferred ads to all my adgroups with a click-to-call extension, suggesting that this could potentially increase conversion rates from mobile users who are better at calling then poking their screen.

“Lets try it!” I said then thought, “How can I automate this?”

As it turns out, its not fully automatable, as the Adwords Script API does not allow for extensions to be added to ads in the ad creation process. But we can automate our work most of the way there:

How it Works

This script mirrors the process that I went through with my Adwords rep:

  • Select the ad with highest click through rate in its adgroup
  • Copy the headline and URL’s for the selected ad
  • Make a copy with a historically successful call-to-action
  • Make a copy with a mobile optimized call-to-action
  • Set this ad to be mobile-preferred.

But instead of doing this one by one or in a spreadsheet, the script iterates through all adgroups in a the account, and makes the changes with one click. (Now you get why I love algorithms?)

There is one last manual step though. As I mentioned before, Scripts won’t allow for adding extensions or labels in the ad creation process. This means that you will have to go into the Adwords interface, create your click-to-call ad extension and apply it to all your ad groups. To be safe make sure that the call-to-click extension applies only to mobile ad extensions.

So here is the script the script can also be found on my github.

function main() {
  //set the following variables to customize the script to your account 
  var IMPRESSIONS_THRESHOLD = 100;       // min. impressions an ad needs to have to be considered
  var DATE_RANGE = "LAST_30_DAYS";       // time frame for impression threshold 
  var ADGROUP_NAME_CONTAINS = 'you specify';   // word or phrase that eligible adgroup name must contain
  var DESC_LINE_1 = "Free Shipping and Free Returns!";   // description line 1 (end with punctuation)
  var CTA_CONTROL = "Shop Latest Cool Styles Today";      // a historically succesful CTA for line 2
  var CTA_MOBILE = "Call Now To Place Your Order";       // new mobilized CTA to test for line 2
  // select adgroups that meet criteria defined above
  var adGroupSelector = AdWordsApp
  .withCondition("Status = ENABLED")
  .withCondition("CampaignName CONTAINS_IGNORE_CASE " + "'" + ADGROUP_NAME_CONTAINS + "'")
  var adGroupIterator = adGroupSelector.get(); 
  // iterate through all selected adgroups
  while (adGroupIterator.hasNext()) {
    var adGroup =;
    var headline;
    var displayURL;
    var destURL;
    var optArgs = {
        isMobilePreferred: true
    // select enabled ads that meet predifined criteria
    // ad with best ctr for each group will be copied
    var adSelector =
    .withCondition("Status = ENABLED")
    .withCondition("Impressions > " + IMPRESSIONS_THRESHOLD)
    .orderBy("Ctr DESC");    
    // this iterator does not really iterate, instead
    // it orders by CTR and selects ad with highest CTR
    var adIterator = adSelector.get();  
    while (adIterator.hasNext()) {
      var ad =;
      var stats= ad.getStatsFor(DATE_RANGE); 
      // headline and URLS are selected from ad
      headline = ad.getHeadline();     
      displayUrl = ad.getDisplayUrl();
      destinationUrl = ad.getDestinationUrl();
      Logger.log(headline +": " + stats.getCtr())
    // and are copied along with pre-defined description line
    // to create to versions of the mobile-prefered ad

Automate MailChimp Reporting with Google Spreadsheets

I made it! I am finally a nerd. At the top of the MailChimp API there is a clear disclaimer: “Woah, Nerds Only!” Undeterred, I scrolled on to test my nerdy medal. The following is my account of navigating the well-charted waters of the MailChimp API and a tutorial on how to automate a “BARF” report using Google Apps Scripts and Google Spreadsheets.

First, let me tell you how this started. I love automating reports. Ok done explaining.

The MailChimp API is a Friendly API

MailChimp’s API is nice and clean with great documentation. It has well defined endpoints and returns JSON objects. Google Apps Scripts is great for parsing JSON objects thanks to the JSON class made specifically for that.

The problem as I defined it was as follows:

  1. MailChimp API has the data I want.
  2. I have to make a call to API using Apps Script’s UrlFetchApp
  3.  The call has to be authenticated by passing along API key.
  4. First I have to make a call to get all the campaigns/list endpoint to get a list of campaigns’ name/id info.
  5. Then I have to iterate through all the selected campaigns and pass their id to the reports/[whichever-stat-I-want] endpoints to get back each campaign’s stats.
  6. Format that data into an array and write it to the spreadsheet.

The Google Apps Script

Yup, so that’s exactly what I did. The interesting part for me was getting a better understanding of the UrlFetchApp class. I had used it before for getting data from APIs or webpages but only the simple way:


But Mailchimp’s API asks for more. It asks that all requests be made as POST requests and it asks that the “apikey” and additional parameters be passed as JSON objects rather than a query string like:

This meant using the additional “param” parameter in the UrlFetchApp.fetch() method. I am happy that I got it to work. It’s clear in the code how it is used but I still need to know exactly why and how it works because it’s pretty cool. Expect a blog post on that.

Beyond that, the code is pretty self-explanatory. I commented parts that would be dodgy for codeacademy level folks. (I know. I was there not too long ago.)

Writing the report to a Google Spreadsheet

Check it out. It is explained in the comments.

// campaigns list api endpoint docs:
// reports api endpoint:
// GAS docs:,Object)
// mailchimp open tracking calculations:
// your api key can be found at:
// standard "24 hour format in GMT, eg "2013-12-30 20:30:00" - if this is invalid the whole call fails"
// add formated values for start and end date like: var REPORT_START_DATE = "2013-12-30 20:30:00"

function chimpReport() {
  var API_KEY = 'yourapikeygoeshere';
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();  
  var dc = API_KEY.slice(-3);
  var api = 'https://'+ dc +'';
  var campaignList = '/campaigns/list.json' 
  var reports = '/reports/summary.json' 
  // MC api-specific parameters
  var payload = {
    "apikey": API_KEY,
    "sendtime_start": REPORT_START_DATE,
    "sendtime_end": REPORT_END_DATE
  // GAS specific parameters: 
  var params = {
    "method": "POST",
    "muteHttpExceptions": true,
    "payload": payload
  var apiCall = function(endpoint,cid){
      payload.cid = cid
    var apiResponse = UrlFetchApp.fetch(api+endpoint, params);
    var json = JSON.parse(apiResponse);
    return json
  var campaigns = apiCall(campaignList);
  var total =;
  var campaignData =;
  for (var i=0; i< campaignData.length; i++){
    var c = campaignData[i];
    var cid =;
    var title = c.title;
    var subject = c.subject;
    var send_time = c.send_time;
    // send_time values are only present for campaigns that have been sent. otherwise set to null.
    // this if statement will only call for report data and write to the spreadsheet, data from sent campaigns.
    if (send_time){
      var r = apiCall(reports,cid);
      var emails_sent = r.emails_sent;
      var opens = r.opens;
      var unique_opens = r.unique_opens;
      var clicks = r.clicks;
      var unique_clicks = r.unique_clicks;
      var open_rate = (unique_opens / emails_sent).toFixed(4);
      var click_rate = (unique_clicks / emails_sent).toFixed(4);
      // the report array is how each row will appear on the spreadsheet
      var report = [send_time, subject, emails_sent, opens, unique_opens, clicks, unique_clicks, open_rate, click_rate];
      // note that this method will append to the bottom of the spread sheet wherever that is.
      // to overwrite a specific range use setValues()

Google Result Date Bookmarklet Using “&as_qdr=y15″

I have been goofing around a lot with JavaScript lately. It’s fun to explore because every time I find and open a new door of understanding, there are two more doors to open and explore. I am currently in the room of browser functionality and thats where I found my bookmarklet.

Making a bookmarklet was an easy way to put a bit of code into action without building anything too big. As it turns out, it’s surprisingly useful. If you have ever wondered when a web page was published, it will tell you, (fairly accurately) just that.

A bookmarklet is essentially a bookmark that holds a bit of JavaScript code that a web browser, like Chrome or Firefox, will execute. Just like any bookmark it is a bit of information held within a link. A link is which is just a reference), but instead of referring to a web page, it refers to some instructions coded in JavaScript. The browser knows the code lingo and does exactly what it is instructed to do.

The instructions look like this (Javascript and HTML translated to plain English):


<a href='javascript:

“Hey browser, this looks like its a link, but no, here comes some JavaScript instructions.”

(function(){if(location.hostname === "")

“This is the beginning of the Javascript function: IF the user is on…”


“THEN: take the page the user is on and change it (make it EQUAL to) the page they are on ADD ON ‘as_qdr=y15′.” (That is the instruction for Google to tell you the dates of all the pages on the results page.)

else{alert("This only works on Google Searches")}

“If the person is not on, ELSE pop-up an alert window and tell them the bad news.”

})()'>Google Date Finder</a>

“Finally, end the Javascript code. Specify the text that anchors the link. End the link code.”

When its all done it looks like this:  

<a href=’javascript:(function(){if(location.hostname === “”){window.location.href=document.URL+”&as_qdr=y15″}else{alert(“This only works on Google Searches”)}})()’>Google Date Finder</a>

And amounts to this with the super-sexy CSS styled version below:

Google Date FinderGoogle Date Finder

Just drag one of the links into your bookmark bar, make a Google search, click the “Google Date Finder” bookmarklet and Whammy! each one of the links has a date on it.

Event Tracking on Squarespace Using Google Tag Manager

I am in “like” with
Google Tag Manager and Squarespace
for the same reason: they simplify and
streamline the processes website design and Google Analytics implementation
while maintaining power and functionality of the product.  For the old sliced bread analogy, they are
the greatest things to come along since the knife.

Squarespace is a
great plug-and-play, drag-and-drop Content Management System (CMS). It allows
users to easily add all kinds of content in a simple non-technical way. For the
average user, Squarespace has all but rendered WordPress, the old standard CMS,
obsolete.  But that argument is a
different conversation. Google it later.

Google Tag Manager is
a Tag Management System for Google Analytics
. (You could say TMS but nobody
does.) It allows for on-page tagging and most other advanced analytics
capabilities of Google Analytics. It greatly minimizes technical implementation
and actual code that must be added to a website in order to track user
interaction. Its semi-technical, in fact, its still pretty technical but once
the code is implemented by your friendly webmaster or neighborhood hacker, the
backend is pretty accessible to fairly tech savvy person.  I recommend checking out this short Google
Tag Manager video
 to get a basic understanding of its features and
capabilities. When I watched it, it blew my mind, but then again, I love

“Stepping Into”
Google Tag Manager

That clever heading is a JavaScript joke. Don’t worry if you
don’t get, I wouldn’t have either until this year.  If you do, then you are in pretty good shape
to follow this guide.  To get you up to
speed, here are a few great references
to learn what’s going on with the technical side of the following

If you were to do this from scratch, you would need a basic
understanding of JavasScript, HTML, CSS and JQuery. Actually, since Squarespace
uses YUI instead of JQuery you will have to also understand YUI. I’ll explain later. You can learn to code at They offer a great step-by-step method that makes learning to
code fun and fulfilling. I really did enjoy it.

For the YUI to JQuery translation, use
It does exactly what it sounds like: lists translations of all the
functionalities of JQuery into YUI.

For help references use Google Tag Manager Help, Squarespace Developer Center and the Holy Grail: if you ever get
hung up.

Step 1: Get Your Google Tag Manager Account and Container

Now that you are a technically proficient hack, let’s dive

This has been covered many times so I will let Google take
it from here. Go to Google
Tag Manager’s website
and follow steps 1-3. Do not follow step 4! Do it
yourself or hire me instead!

Also check out Justin Cutroni’s extremely helpful guide  for setting up your account.

2:  Insert the Container
and Initialize the Data Layer


This is where the fun starts; the beginning of analytics
tracking glory!

Inserting the

Google Tag Manager documentation recommends that you place
your container just below the opening body tag. One way to do this is by adding
the container into a code block at the top of each page, but that is just a lot
of work. Instead, the functional alternative, which works just as well, is
“injecting” the container in the header section.

Initializing the Data

The container is the brain that runs Google Tag Manager on
your site but it doesn’t really do anything too special without the data layer.

You will also have to insert a bit of code above the
container to initialize the data layer. Technically, this bit of code creates
an empty data layer object, which will be filled as user interaction push
information to the data layer object. The code looks like this:

    dataLayer = [];

<!-- Google Tag Manager -->

 **Make sure that you place the data later snippet above the container!**

Step 3: Insert
Code to Push Events to the Data Layer


method of event tracking offers the agility that differentiates GTM from
previous methods of Google Analytics event tracking. It is truly where the magic happens.

Inject this snippet of javascript into the footer. This way
loading the script will not slow down loading the visual elements of the page.

This snippet of code says, “every time a user clicks a link
to a social media site, I will push the corresponding key/value pair to the
data layer.  Most commonly this is done
using JQuery, but Squarespace uses YUI so you have to play the game between the


 YUI().use('event', 'node', function(Y) {
   // (1) These variables reference the 'class' link attribute. 
   // Each social media site link has its own 'class' attribute.
   // Note that by using an element class rather than element id,
   // any button that shares that class will fire the GTM rule 
   // triggerd by that class.    
   var twitterLink = Y.all('');
   var gPlusLink = Y.all('');
   var linkedInLink = Y.all('');
   // (2) The 'socialView' function tracks a button click event (2a) 
   // then pushes a corresponding data layer variable to the data layer. (2b)
   var socialView= function(socialLink,socialClick){
     socialLink.on("click", function(e){                //(2a)          
       dataLayer.push({'event': socialClick});          //(2b)
       //These are logs I used for debugging:
       console.log(socialLink + " has been clicked");    
       console.log(socialClick + " was pushed to the datalayer");
   // (3) Finally, 'socialView' (2) is called for each link class variable (1).
   // This pushes a data layer value (2b) into the "key : value: pair below.
  socialView(twitterLink, "twitterClick");
  socialView(gPlusLink, "gPlusClick");
  socialView(linkedInLink, "linkedInClick"); 

Step 4: Setup Tag
Manager Rules and Tags

The Google Tag Manager interface is not immediately
intuitive so give yourself some time to play around with it. Once you get lost
in it, you will be amazed by the power it provides.

For this example, we will go through event tracking on the
Google+ button on the footer of each page. This will also track any button
click for any other button with a designated div “class” attribute of  “social-google.” I use the “class” attribute
because my intent is to measure navigation to my social media profiles rather
than on-page UX button testing. This can be modified pretty easily to track
other events. Let me know if you have questions.

Part 1: Setup the Tag

This tag measures both a Virtual Pageview and a Custom Variable. This way its possible to track
navigation to my Google+ page in aggregate and also at the visitor level.

Part 2: Setup the
Rule to fire the Tag

We are tracking a JavaScript event, not to be confused with
a Google Analytics event so we use the {{event}} macro to trigger the rule.
When the  {{event}} matches the string
assigned as the data layer variable,

Note: The {{event}} macro must exactly match thedata layer and sure that the match is
case sensitive!

In the case of the Google+ button, the data layer variable
is “gPlusClick.”

Part 3: Create a Container Version 

Create a version of your container. Name your container
version with a specific and consistent naming convention. This will really help
in keeping track of progress and contained tags.

Step 5: Debugging and Google Analytics Real-Time Feature

Good job so far!

Now, to makes sure this is all working properly, preview the
new container version. There are few ways to check if your tags are working.
First preview your container in “Preview and Debug” mode. If the tags are
firing on the target user interaction publish the new container and use the
Real-Time feature in Google Analytics to make sure that the virtual pageviews
and/or events are being reported properly.

If you followed this whole process or you managed to
customize your own event tracking, give yourself a pat on the back! If you have
any questions, please comment below or reach me on Google+.


Hopped Up on the Hillsboro Hops

I am so glad that baseball is coming back to this neck of the woods! I can’t tell you how bitter I was when I found out that major league soccer would be playing in the old PGE Park. The place was old but well worn  like my high school baseball hat. The place still keeps the ghosts of a venue where people would dress in suits to go to the game, pitchers threw high and tight, and the only performance enhancers were that extra cup of coffee and topical horse anti-inflammatory.

I miss the majesty of that old ballpark, but as hard as it is for me to admit, its time had passed. I stopped wearing that old baseball hat too.

The Hops hats aren’t available yet, but you can bet as soon as I can get my hands on one, I will be showing my Hillsboro Hops love.. Hillsboro is the perfect place for a Northwest League, Short Season Single A Baseball team. Hillsboro is the just edges out Beaverton as being Oregon’s 5th largest city with nearly 90,000 residents. The market is perfect. Its a young and expanding suburbia full of kids who will attend their first baseball game wondering what the heck a Hop is and why the mascot looks like a cross between Oscar the Grouch and Big Bird. To this day I remember my first single A game. I saw the Eugene Ems and my friend tried to convince me that a grand slam was a ball that was hit out of the stadium and I asked for the bat boys autograph after the game.

I can already see it, Kids standing up, shakin’ and dancin’ to “Danny and the Jurors’ “At the Hop” as the mascot bounces his big grinning hop bud body. And this is how families fall in love. Mom is happy that the kids are happy. Dad is happy to be sharing America’s pastime with kids and maybe even prompting a game of catch. They’re both happy because they can sit back and enjoy a beer as the kids bounce around the, the inflatable, you guessed it, “Hoppy Castle.”

Did someone mention beer? I’m counting the days until Thirsty Third Thursday. A day that will pull yuppies and hipsters on MAX trains to Hillsboro from the Pearl to Alberta and as far as Gresham. Local music plays before the game and a lively social beer deck doesn’t watch the game. And as for the beer batter, are you kidding me!? I can already hear the music playing after the second strike. Fans ask each other what the featured beer is this week. 1st strikeout, one dollar off; second strike out, two dollars off, and the magical hat trick makes beer half priced!

The Hillsboro Hops was a great idea. I can’t wait to see what life it takes on. Please subscribe to my RSS feed to keep up on latest news, commentary and profiles from the team.

Heres to the Hops!


On the Web… on the Web