For the second installment in the Data Wrangler series (see my previous post), I wanted to share with you how to easily blend data from Google Analytics with marketing campaign data from systems like Eloqua, Marketo and Salesforce. For example, let’s say you want to correlate the landing pages from an email campaign with the analytics for those pages so you can discover more about what happened when email recipients clicked through to landing pages.
To blend Google Analytics data with data from a marketing automation system, you need a “key” that connects the two systems. One common key for email campaign reporting is a landing page. For example, as you can see in the following screenshot, I used Progress® Easyl® to bring in email campaign data from Eloqua and then enhanced that data set with relevant Page Tracking data from Google Analytics.
Fig. 1: Blending data in Progress Easyl
Easyl enables you to create data set tables for reporting that are optimized from your campaign data. In this case, we created one for Eloqua’s Activity_EmailClickThrough data and one for Google’s Page Tracking. Then you define joins between the matching fields, which in this case is the Landing Page field.
As soon as you try to do this, you’ll notice right away that there is a problem, which is that most marketing campaign systems store the landing page URLs with the protocol prefix: http://. Google, however, shows the landing page URL as www.progress.com, without the http://. So how do you match these keys?
You need to edit the values for all the Google Analytics landing pages at once, appending http:// to all the landing page URLs so that the key pairs now match.
Here are the step-by-step instructions to edit your data values:
In this example, the following command is used to concatenate the http:// string with the existing landing page URL as shown here:
UPDATE "Pageviews" SET "Landing Page" = CONCAT('http://', "Landing Page");
Now your Google landing page values will have the http:// prefix so you can easily blend your data to create reports, as shown in the following screenshot.
Fig. 2: Standardizing your data with Progress Easyl
Is this report going to be refreshed daily or weekly? If so, you can click the SQL Playback on Reload checkbox and your SQL statement will be recorded and automatically applied every time the data set is refreshed.
Now with matching landing page values, it’s easy to run a report as you can see in the image below that shows the correlations between the campaign landing results and Google metrics, such as the average time on a page or the number of page views.
Fig. 3: Correlated data from Eloqua and Google Analytics in one data set for creating reports
And remember, Easyl is integrated with tools like Excel and Tableau, so you can also create awesome visualizations, as shown below.
Fig. 4: Adding visualizations with Tableau
More to come in this series! So you’ll want to stay tuned to this blog for future installments from my colleagues and me. You’ll get insider tips on how to do data prep, data cleansing and data analysis easier and faster. If you haven't already, make sure you read Part I and Part III as well.
I’d love to hear from you about Easyl data blending tips that you’ve discovered! And if you haven’t tried it yet, get started now with a free trial download of Progress Easyl.
Rich is the Principal Product Manager for Progress Easyl, a cloud-based data integration and blending tool that enables you to create reporting data sets and data marts “on-the-fly” quickly and easily. (Learn more about Easyl now.) Rich has spent two decades working with data-driven applications and marketing automation, and served in senior management roles at a number of Silicon Valley companies before settling in North Carolina.
Rich has years of experience leading consulting engagements for Fortune 1000 clients that include Cisco, Microsoft, Seagate, and McKesson, and is an alum of PeopleSoft, Oracle, and Informix. Most recently, President Barack Obama nominated Rich to serve as a senior advisor with the Department of Treasury, as a member of the IRS Oversight Board.
Rich is the Principal Product Manager for Progress Easyl, a cloud-based data integration and blending tool that enables you to create reporting data sets and data marts “on-the-fly” quickly and easily. Rich has spent two decades working with data-driven applications and marketing automation, and served in senior management roles at a number of Silicon Valley companies before settling in North Carolina. Rich has led consulting engagements for Fortune 1000 clients including Cisco, Microsoft, Seagate and McKesson, and is an alum of PeopleSoft, Oracle, and Informix. Rich was nominated in 2015 by President Barack Obama to serve on the Internal Revenue Service Oversight Board.
Copyright © 2018 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.
Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.