How to Bring Google Analytics Back to Its Best with ExactTarget Data Import

With the rise of social media, it's easy to see email as an outdated element of digital marketing, so much of our time is spent on social media apps. The truth is,  email still outperforms social media in reach and ROI . If you have access to more than one Google Analytics, you have surely noticed that the email channel tends to convert better than the other channels and at a lower cost. So, if you use an “Enterprise” type marketing solution such as ExactTarget (renamed SalesForce Marketing Cloud in 2014) or Responsys (acquired by Oracle in 2013), it would be good to integrate your email campaign data with your marketing solutions. analytic.

You may be wondering, "why would I want to switch when my email tool already provides me with detailed performance reports?" The problem is that when your email data and web traffic data exist in silos, it's hard to get a 360-degree view of the customer journey. If you're doing A/B testing in the email subject line, don't you want to know which headline has the highest bounce rate on your landing page ? Or which generates the highest conversion rate? The best way to find answers to these questions is to import your “offline” email campaign data into your “online” analytics tool. 

Here is what you will discover in this article:

1. The process we use to import data from ExactTarget into Google Analytics 360 (formerly known as Google Analytics Premium), and the limitations of the tools used.
2. The type of reports and observations that can be generated through this integration. You can go directly to this section if you are not interested in the technical details.

It is important to note that we could not find an official connector for ExactTarget-to-GA. We had to create a tailor-made solution.

THE PROCESS

The diagram above summarizes the integration process. It starts with ExactTarget and ends with Data Studio. Let me explain each of the elements:

1. ExactTarget: The first step was to extract data from ExactTarget using both their APIs and emailing us reports. Getting all the data we wanted through ExactTarget's API was difficult to say the least, so we only used it when we had no other choice (for example when certain data could only be obtained with the API). 'API). Instead, we chose to extract most of the data by creating a custom report inside the ExactTarget interface and scheduling it to email this report to us on a daily basis. We then wrote a script that reads the data from the email report (csv document) and dumps it into Google Sheet. The data extracted through the API was also dumped into the same Google Sheet. Some data extracted in ExactTarget are of dimensions such as Source, Medium, Email Name, Email Subject, Job/Send ID, Email Language and other custom segments. On the metrics side, we had: #Sent, #Opened, #Clicks, #Delivered, #Unsubscribed, and a few others.
2. Google Sheet:  Once the data from ExactTarget was in Google Sheet, we had to do the following:

a) Create a unique key using the Job/Send ID field. It will be used later to link imported email data to Internet traffic data.

b) Prepare the dataset schema . This consists of assigning the import fields mentioned above to a single unique key. So each row of data will have a unique key. This is essential to successfully extend your campaigns in GA (by linking new categories and campaign variants). The unique key will actually be the utm_id parameter, which I'll cover in more detail a bit further down.   

c) Create an array with all dimensions and parameters imported from ExactTarget ready to store in BigQuery. The reason we have to store all the data in BigQuery is that Google Sheet has a 2 million cell limit and we were going to hit that limit in a few months or even weeks. So we chose to use BigQuery as the main database.

d) Write scripts that will automatically import campaign extension data into GA and send all dimensions and metrics to BigQuery for storage. These scripts have been scheduled to run on a daily basis.

1. URL Tagging:  This seemingly simple little gesture is probably the most important piece of the puzzle. Without the marking, everything crumbles. In this step, we need to make sure that every outgoing email sent by ExactTarget has been tagged with the “utm _id” parameter. This parameter must be added to each landing page URL, along with the other utm parameters (utm_campaign, utm_source, utm_medium, etc.). It is essential to ensure that the value transferred to this “utm _id” parameter does indeed exist. It must match one of the one created in point 2a above. This is why it is called Unique Key (its role is to link the imported data to specific Internet sessions/visits).
2. BigQuery:  The role of BigQuery is simple, it is to maintain all dimensions and metrics imported from ExactTarget as mentioned above. Another important reason for using BigQuery was to be able to persist metrics, since we couldn't import them efficiently into GA (I'll probably talk about that in another blog post).
3. Google Analytics:  Using the Universal Analytics data import feature, we have created a schema of the data set for extending campaigns. You need to do this step so that GA knows how to interpret the data imported from Google Sheet and keep it in the correct dimensions. Once the daily data import is done, GA will take the utm_id and link it to any web interactions generated by email campaign users. In order to be able to visualize everything, we had to create custom reports (see below). There may also be a delay (up to 24 hours) before any imported data is available for reporting. Plan for this when importing data into GA.
4. Data Studio:  This is where it all comes together. Since we couldn't import metrics from ExactTarget into GA, we had to find another way to visualize them. What we did was link both BigQuery and GA to DataStudio. BigQuery provided all ExactTarget data (dimensions and metrics) and GA provided all internet sessions with campaign extension applied on them (meaning the data import into GA had to be fully done before we could). can see GA data in DataStudio). Below you can see a screenshot that reveals some scorecards generated in DataStudio using data from BigQuery and GA.

REPORTS & OBSERVATIONS

After solving puzzles and pulling hairs out, finally comes the time to enjoy the fruits of our labor: reports and observations. I will now go through some of the reports and observations that you can get with this type of integration.

1. The following screenshot (from DataStudio) shows ExactTarget metrics on the first row. You find the regulars of an email campaign: sent, clicks, opened, delivered, unsubscribed, unsubscribe rate, bounces. On the second row, you find the GA metrics where the source = email: sessions, transactions, users, new user, revenue and average value per order. Having a similar dashboard with these scorecards can give email campaign managers insight into how email campaigns can directly influence web traffic and other KPIs (performance indicators).
2. You can create an advanced segment of a specific email subject (see image below) and apply it to any standard GA report. In this case, we applied it to the City report to see if some cities perform better than others. To make sure I haven't lost you along the way, the report below only shows you the users who clicked on an email with the following subject line: "we can't wait to share this you", and from what city ​​they come from (based on their IP address). This report can help you see that users in Chicago, for example, generate the best conversion rate. Thereby,
3. You can also create a custom report where "email subject" is the first dimension, and then compare the performance of each email subject line in terms of number of sessions, users, transactions, revenue or other KPIs. Since the email subject line tends to be what makes subscribers open or ignore newsletters, having this level of observation can drastically improve email conversion optimization efforts.
4. For those of you lucky enough to have Google Analytics 360 (formerly GA Premium), you can use one of my favorite features:  Custom Funnels. Let me explain the one I created below. Essentially, this tool allows you to see which users have already purchased, who have already visited a page on your site, who have visited a particular product and who have finally added the product to their cart. It also tells me how many users have abandoned the funnel. I could also have added the checkout and purchase confirmation steps if I wanted to. It's important to know that "Past Buyers" is a segment imported from ExactTarget (or CRM), and this conversion funnel sheds light on how these convert. Of course, you can create a similar funnel with other ExactTarget segments/fields and different stages. And that, my friends, is what I call analytical web marketing 🙂

That's it, you now know how to go about importing your ExactTarget data into Google Analytics and you now know what types of dashboards you can create in Data Studio, as well as custom reports and conversion funnels directly in GA.

And on your side? Have you worked on a similar integration? If so, have you found an easier way to do this? Share it with us in the comments below, so we can all learn from your experience. And if you have any questions or comments, please let us know in the comments as well.