Universal Analytics Feature Turns Ugly Data into Usable Data
Alibris.com, a seller of both new, used and rare books, recently came to Blast Analytics & Marketing looking for a solution to a very specific Google Analytics reporting problem. Being a company with over ten thousand affiliate channels, a significant amount of traffic routinely gets tagged with an encoded source code to mask the affiliate’s real name.
This blog post shows how Google Analytics’ Dimension Widening paved the way to construction of a custom report where the analysts could see the actual name of the affiliate and not have to manually look up an encoded source code in a look-up table.
(Keep in mind, that this dimension widening solution can be applied to a wide variety of similar reporting challenges and is not just for this specific affiliate use case.)
Each data feed our client sends to their affiliates has a number of items for sale. Each item in that feed is available for sale on that individual affiliate’s site, and when selected for purchase takes the user to the Alibris website to complete the transaction. The links that take the user back to Alibris are embedded with additional parameters such as the following:
The bolded text above is the affiliate’s store codified by the affiliate management system.
The problem can really be seen when you look at an example of the Google Analytics Acquisition Channels report for all affiliates.
It’s great that we’re seeing good traffic to the affiliate GwEz7vxblVU… um, who is that again?
The Dimension Widening Solution
Our client stated, “What we need is a way to map the affiliate source used into something we recognize when looking at Google Analytics’ reports. Can we do that somehow?” Dimension Widening offers this capability, almost perfectly.
Here’s what we did to accomplish the objective, followed by more detail on each step:
- Define 2 custom dimensions in Google Analytics (user scope)
- Create custom tracking code to extract utm_source
- Prepare a CSV data mapping file
- Import the data set into Google Analytics
- Test a hypothetical campaign URL
- Build a custom report
Define 2 Custom Dimensions in Google Analytics
The Google Analytics support pages specifically state you cannot widen campaign dimensions, which becomes evident when you begin to use the data import in later steps. So for our project to succeed, we needed to capture the utm_source and store it away in a custom dimension, which we called Source-Encoded. We also created and reserve a second dimension for the Source-Decoded data. Both of these were defined as user-level dimensions.
We set the custom dimensions’ scope to ‘User’ because it is more closely aligned (but not perfectly) with how native ‘Source’ dimensions work. As an example, if a user comes in through an affiliate campaign link on Monday, but doesn’t convert on that visit, then later comes back to the site on Tuesday and converts, Google Analytics credits the original campaign (not as a ‘direct’). If we had selected a scope of ‘Session’ we would have seen issues. Since the purpose of this new decoded dimension is to understand affiliate performance, we felt ‘User’ best represented our objectives.
Create Custom Tracking Code to Extract utm_source
var dimValue = any defined utm_source from the campaign URL;
ga('set', 'dimension1', dimValue);
Preparing a CSV File
The client provided a 2-column Excel file with approximately 11,000 rows. Google Analytics asks for a CSV file under 1 GB with the first row being header detail, so the preparation needed was to make sure the header row contained the correct information. Google Analytics uses the header data to know which column is the affiliate code (Key) and which is the readable text (Widen-To Dimension).
To extract the data properly you have to use Google Analytics’ naming convention, putting in row 1, cell 1, the header “ga:dimension1”, and row 1, cell 2, the header “ga:dimension2”.
After this small change to our file, we exported it to CSV format.
3 Steps to Import Data into Google Analytics
The Google Analytics admin interface page shows a link called “Data Import” and requires 3 steps to complete the data import process.
Step 1, we select the type using the radio selection for “Generic Dimension Widening.”
Step 2 lets you name your data set and assign it to one or a number of views. Google Analytics strongly suggests you create a test view first and make sure the data widens as expected before using it on a production or default view (since you cannot remove the data once it is added).
On Step 3 we define the transformation we want to occur by selecting the appropriate “key” and “widen to” dimensions from the selection window. This is where we noted that you cannot simply select a campaign variable, but you can select from the custom dimensions we defined earlier.
After step 3, we have our data set defined and can now manage the file upload process. Once we select and upload a file, and Google Analytics validates that it is usable noting it as “Completed”, new custom dimensions are being created as data come in.
Testing a Hypothetical Campaign URL
Now that we have set up the rules for the source extraction from a URL string, set up the two custom dimensions, and imported the CSV file; we are ready to create a fictitious URL with a sample from one of the affiliate source codes. From earlier, we stated the URL would look like the following:
So all we need to do is paste this into a browser, perhaps monitor it with a proxy tool (like Charles Proxy) to see the details sent off to Google Analytics. Then wait until the following day to see the data in GA, and validate that it was widened.
Build a Custom Report
Since we have to wait to see the data, we can take a moment to build a custom report for ease of accessing the data. The standard Acquisition Channel report might get us within a couple of clicks but why not take advantage of the custom reporting feature, and make it a no-brainer to drill down to the needed affiliate data.
As you can see, the next day we see our custom report showing our “Source-Decoded” field front and center, and the decoded text is a URL (by design) of the affiliate company.
With Google Analytics’ dimension widening flexibility we were able to create an elegant solution for our client without a lot of fancy table manipulations or time-intensive manual processes. These newer Google Analytics features continue to address day-to-day processing needs associated with running an affiliate-oriented business.
Now that we have solved our client’s affiliate naming issue, perhaps next we will suggest tackling product-level dimension widening with external meta-data. I saw that option at Step 1 of the data set creation and couldn’t help but start to think of additional uses dimension widening could offer.
If you would like more high level information on Google Analytics Dimension Widening for importing data, check out this great post by Justin Cutroni.