The Data Wrangler: Tips for Easy Data Prep Part II

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.

Blend Google Analytics data with Your Marketing Data

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

Working With Disparate Data Fields

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.

Standardizing Your Data With Easyl

Here are the step-by-step instructions to edit your data values:

1. Go to the Data Set Details page for your Easyl data set. At the bottom, you’ll see the JDBC and ODBC Connection Strings for connecting directly to your data set using any SQL editor. Next to that, click the checkbox for Enable SQL Updates. Tip: If you want to automatically reapply this “fix” every time the data set reloads, select the SQL Playback on Reload checkbox.
2. Open your favorite SQL editor (such as dBVisualizer or Microsoft Query from Excel), and connect using the connection string along with your Easyl username and password.
3. Pass the SQL statement to the Easyl data set.

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.

Quickly Create Marketing Reports and Visualizations

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

Stay Tuned for More in this Series

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.

Share Your Comments

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.

About Rich Julius

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.