Unifying data from several sources is easy: here’s how.

Whether or not you’ve already got a dashboard in place, we’re going to talk here about how to unify data from various sources in order to take your performance monitoring further.

If you don’t have a dashboard yet, I encourage you to read my colleague Yann Kerveant’s article, Data analysis: Do you need a dashboard? At a glance, the answer may seem simple, but the most important thing is to know why you should build one, and how to make sure it’s useful.

Most of the time, teams will organize their own dashboards to highlight the performance of their initiatives. Where it starts to get interesting though, is when you’re able to take a step back and get a top-level view, and see how each action has repercussions that can be felt on multiple levels. That’s where multi-channel dashboards come in. You can even stretch your horizons to include offline activity. Follow along, and we’ll look at how you can accomplish all this with Google Data Studio.

Google Data Studio is a free visualization tool from Google. It allows you to bring together data from multiple sources, and easily share it.

To bring your data into Data Studio, the tool uses connectors, which are little integrated tools that allow you to connect to other platforms to pull the desired data.  

There are a number of available connectors; more than a dozen that connect to Google products, as well as a number of connectors that partners (like Supermetrics and Analytics Canvas) and the open source community have made available.

Benefits of unifying your data

The goal here is to go beyond displaying data from diverse sources separately, and look at the data as a single, unified picture. Seeing the information combined in a single graphic is far more useful, and allows you to visualize the relationships between data.

Let’s take the example of a company that sells bathing suits online and in stores.

When you look at the data separately (with sources like Google Analytics and an internal in-store purchase tracking tool), you get a siloed view of your performance: in-store sales, online sales, newsletter subscriptions.

By unifying the data, you can go a lot farther and get a combined view; in-store purchases by users who are subscribed to the newsletter, percentage of users who only buy online, etc.

Unifying the data allows you to get a 360 overview of your customers’ behaviour, and glean indispensable insights.

Let’s look at another example, with a site whose goal is to generate leads and then convert those leads into customers.

Unifying the online and offline data (using sources like Google Analytics and your CRM tool) will allow you to get information directly related to your goals; you’ll know exactly how many leads and how many customers a campaign generates.

Are you convinced?

How to unify your data: a few options

There are a number of solutions that can help make this happen. Honestly, there’s no “best” way of doing things, it really all depends on your situation and limitations. The most important thing to retain is that, to unify your data, you need to have a join key (one or more shared dimensions) to connect your sources.

The three data unification solutions we’ll talk about today are data blending, BigQuery and MySQL.

Data blending: nice and simple

Google Data Studio recently launched its highly anticipated data blending functionality. It allows you to blend multiple data sources within a single tool, by using a join key to unify your data.  

In a situation where the majority of your data is in Google Analytics or Google Ads (formerly AdWords), and you need to enrich this data with another data source, use this solution.

One very simple, very useful trick is applying targets to your metrics. In addition to adding context, this will allow you to measure your performance by comparing actual data with target data.

1. Take Google Analytics as your first source, using the metric of number of leads per day.
2. The second source would be a Google Sheet containing your targeted number of prospects per day.  
3. The join key would be the date.

Now you can see exactly how your metric compares to your target, what we refer to as a performance indicator. You’ll be able to see right away if things are going well or not.  

Think about the multiple connectors available in Data Studio that allow you to bring in data from different platforms to then unify them thanks to data blending.

For example, Supermetrics has a Facebook Ads connector. Integrate this data with your Google Analytics data in order to connect impressions, clicks and campaign costs, for example.

There’s also a Salesforce connector, and if you’ve already done the integration between Salesforce and Google Analytics 360, it will be even easier to connect the data from the two platforms in your dashboard.

BigQuery: Big data that walks the talk

BigQuery will be your best friend if you need to process a large volume of data.

If you’re part of the Google Analytics 360 family, you’ll find BigQuery very useful. GA data can be sent daily to BigQuery to apply more advanced queries to the raw data.

GA data aside, it’s also possible to enrich your data with external data imported into BigQuery; the unification of different sources is done within BigQuery through SQL queries. Always remember that you’ll need a join key to be able to unify any data.

Once the queries have been carried out, a connector exists to help you pull this data directly into Google Data Studio. What more could you ask for?

Another use case would be if you already use BigQuery for data warehousing. Here, again, unify your data with SQL queries and connect in Data Studio! Remember, the word “already” is key here. If you aren’t already using BigQuery, the cost and effort required to migrate your data won’t be worth it just to connect to Google Data Studio.

SQL: tables or queries

If the majority of your data is in MySQL or PostgreSQL databases, these Data Studio connectors are for you.

All you have to do is connect to your database in order to bring your data into Google Data Studio.

There are two ways to pull in the desired data:

• Create final tables in the DB so you can connect to them directly and ensure you’re only getting the necessary data;
• Create SQL queries directly in Data Studio to query the different tables and pull in the data.

The problem with the second option is that the Data Studio interface isn’t optimal and, depending on how heavy the queries are, it’s possible that the dashboard could be slow or that the query could return a time-out error.

The connector looks like this; you can see the two connection options, either by table or SQL query:

Your database might involve data from in-store purchases, calls made from a customer service centre or branch visits after an appointment has been made.

The combination of this data with user behaviour data from your site could provide you with invaluable information. And it’s entirely possible to combine data in this way thanks to data blending!

Now that we’ve given you a few ideas for how you can push your dashboards farther, all that’s left is for you to take action. Don’t hesitate to tell us about the challenges you face, or other solutions you use to unify your data.

And if you’re feeling brave and want to launch yourself into a deeper analysis of your data, I’d encourage you to read my colleague Ai’s article.