How to Make Google Analytics Great Again by importing ExactTarget Data?
With the rise of social media, it’s easy to dismiss Email as a has-been digital marketing medium because most of us spend so much time is spent on social apps. The reality is that Email still outperforms social media in terms of reach and ROI. If you have access to more than one Google Analytics, you have probably noticed that the email channel tends to convert better than other channels and at a cheaper cost. So if you use an enterprise marketing solution such as ExactTarget (renamed SalesForce Marketing Cloud in 2014) or Responsys (acquired by Oracle in 2013), it would be a good idea to integrate your Email campaigns data to your marketing analytics solution.
You might ask yourself: “Why would I want to do that when my Email tool already provides me with fancy performance reports?” Well, the problem is that when your Email data and Web traffic data live in silos, it’s kind of hard to have a 360 degree view of the customer journey. If you run A/B tests on Email subject lines, wouldn’t you want to know which one of those email subjects has a high bounce rate on your landing page? Or which one yields a higher conversion rate? Well, one of the best ways to find answers to these questions is to import your “offline” Email campaigns data into your “online” analytics tool.
Here is what you will learn in this article:
- The process we used to import ExactTarget data into Google Analytics 360 (previously known as Google Analytics Premium), and the limitation of the tools we used.
- The type of reports and insights that can be generated thanks to this integration. You can jump to this part if you are not interested in the technical details.
It’s important to note that we didn’t find any official ExactTarget-to-GA connector/plugin. We had to build our custom solution from scratch.
The diagram above summarizes our process with this integration. It starts with ExactTarget and ends with Data Studio. Let me break it down:
- ExactTarget: The first step was to extract data from ExactTarget by using both their API and sending ourselves email reports. Getting all the data we wanted through the ExactTarget API was a bit buggy to say the least so we only used it when we really didn’t have a choice (for example when some data could only be extracted through the API). Instead, we extracted most of the data by building a custom report inside the ExactTarget interface and scheduling it to be emailed to us on a daily basis. We then wrote a script that reads the data from the email report (a csv attachment) and dumps into a Google Sheet. The data extracted via the API was also dumped in the same Google Sheet. Some data exported from ExactTarget are primarily dimensions such as Source, Medium, Email Name, Email Subject, Job/Send ID, Email Language and other custom segments. On the metrics side, we had: #Sends, #Opens, #Clicks, #Deliveries, #Unsubscribes, and a few more.
- Google Sheet: Once the data from ExactTarget was in a Google Sheet, it was time process it as follow:a) Build a unique key using the Job/Send ID field. It will later be used to link imported Email data to Web traffic data.b) Getting the data set schema This consists of attributing the imported fields mentioned above to a unique or join key. So each row of data will have a join key. This is mandatory for a successful campaign widening in GA. The join key will actually be the utm_id which I’ll cover in more details below.c) Build a table with all dimensions and metrics imported from ExactTarget so they can be ready to be stored in BigQuery. The reason we had to store all data in Bigquery is that Google Sheet has a limit 2 million cells and we were going to reach that limit in a matter of months if not weeks, so we had to use BigQuery as our main database.
d) Write scripts that will automatically import campaign widening data into GA and also send all dimensions and metrics to BigQuery for storage. These scripts were scheduled to run daily.
- URL Tagging: This seemingly small step of the process is probably the most important piece of the puzzle. Without it, everything simply falls apart. Here we had to make sure that every outgoing email sent by ExactTarget was tagged with the parameter “utm_id”. This parameter must be added to every landing page URL along with other standard utm parameters (utm_campaign, utm_source, utm_medium…etc.). It’s crucial is to ensure that value passed to this “utm_id” does exist, basically it has to match one of those built under point 2a above. That’s why it’s called a Join Key (its purpose is to stitch imported data to specific web sessions/visits).
- BigQuery: The role is of BigQuery is simple, it stores all dimensions and metrics imported from ExactTarget as mentioned above. Another important reason we used BigQuery was to store metrics since we weren’t able to import them into GA successfully (I’ll probably go over this in another post).
- Google Analytics: Using the Data Import feature of Universal Analytics, we created a data set schema for the campaigns widening. You need to do this so GA knows how to interpret the imported data from the Google Sheet and stores it in the appropriate dimensions. Once the daily data import is successful, GA will then take the utm_id and link it to any interactions generated by Email campaigns users. In order to visualize all of this, we had to create custom reports (see some of them below). There could also be a delay (up to 24 hrs) before any imported data is available for reporting, make sure to plan this ahead of time whenever you import data into GA.
- Data Studio: This is where everything came together. Since we couldn’t import metrics from ExactTarget in GA, we had to find another way to visualize them. What we did was to link both BigQuery and GA to DataStudio. BigQuery provided all ExactTarget data (dimensions and metrics), and GA provided all Web sessions with campaign widening applied to them (this means that the data import in GA had to fully take place before we can visualize any GA data in DataStudio). Below you will find one screenshot that shows some scorecards generated in DataStudio using data from BigQuery and GA.
The Reports & Insights
After all the head-scratching and hair pulling finally came time to enjoy the fruits of our labor: the reports and insights. I’ll now go over a couple of reports and insights you can get from this type of integration.
- The following screen capture (from DataStudio) shows the metrics from ExactTarget on the first row. You have the usual suspects of an Email campaign: Sends, Clicks, Opens, Deliveries, Unsubscribes, Unsubscribe Rate, and Bounces. On the second row, you have the metrics from GA where the source = email: Sessions, Transactions, Users, New User, Revenue, and Average Order Value. Having a similar dashboard with these scorecards could give an Email Marketer a bird view of how Email Campaigns directly impacts Web traffic and other KPIs.
- You can create an advanced segment of a specific email subject (look at the image below) and apply it to any standard GA reports. In this case we applied it to the City report to see if some cities perform better than others. Just to make sure you haven’t lost me yet, the report below only shows you users who clicked on an email with the subject line: “we can’t wait to share this you”, and which city they are from (based on their IP address). This report could allow you to see that users from Chicago for example generate the best conversion rate, so you might want to take better care of them and send them specials, or simply get more email subscribers from Chicago.
- Or you can create a custom report where “Email Subject” is the first dimension and then compare the performance of each email subject line in terms of the number sessions, users, transactions, revenue, or any other KPI. Given the fact that Email subject lines tend to be what makes subscribers open or ignore newsletters, having this level of insights can drastically improve your Email channel conversion optimization efforts.
- For those of you lucky enough to have Google Analytics 360 (or GA Premium), you can use one of my favorite feature: Custom Funnels. Let me explain the one I created below. Basically it shows me users who are past buyers who visited any page of our site, then visited any product page, and finally added any product to the cart. It also shows me how many users drop out of the funnel. I could have also added a Checkout and Sales Confirmation steps if I wanted to. Here it’s important to note that “Past Buyers” is a segment imported from ExactTarget (or CRM), and this funnel sheds some light on how they convert. Of course you can create a similar funnel with other ExactTarget segments/fields and different funnel steps. And that, my friends, is what I call Web Marketing Analytics 🙂
Voilà, you now have some blueprints on how to import your ExactTarget data into Google Analytics, you are also aware of the type of dashboards you can create in Data Studio as well as the custom reports and funnels you can create in GA directly.
How about you? Have you worked on a similar integration? If yes, if you have found an easier way to do that please please please share it in the comments below so we can all learn from you. And should you have any questions or general comments, please add them in the comments as well.