Mashing Up Data From Facebook and Google Analytics

Jiri Tobolka

If you’ve read the article about how we are extracting data from Google BigQuery and uploading it to the GoodData Platform, you already understand how GoodData can serve as a powerful tool for visualizing and finding patterns in your data. GoodData offers data insights through report mash-ups of various data sources. It lets you see for yourself how different communication channels influence one another.

In the following example, we’ll look at how you can use GoodData to see how visits to your website correlate with your Facebook followers’ activities. By the end of this post, you’ll be able to create a report like the one displayed below.

preview

The chart above displays both website traffic (in visits) as well as the number of storytellers, both sliced by date. By storytellers, we mean people who actively share or comment your Facebook page content. The chart provides useful information that could allow us to draw inferences about how website traffic and facebook activity may be interrelated.

Hint

Learn more about CloudConnect and try it yourself.

So how can you recreate this report for your own use? First, you’ll need data from Facebook and Google Analytics. Here’s where GoodData CloudConnect comes in handy. CloudConnect is a tool that allows you to upload data from these two sources to the GoodData platform. From there you’ll be able to create simple metrics based on your source data and visualize data in a chart report.

First, you’ll want to create two datasets in your data model - the first one for Facebook data, and the second one for Google Analytics data. You’ll need facts for computing numbers and some attributes for analyzing the data. We’re interested in looking at how the data changes across time, so you’ll need to establish a Date dimension as well. See the sample data model below:

social data model

Now that you have your data model created in a GoodData project, the next step is to get data from the source to the destination. This is what we call the ETL process. See the complete process below and learn more about how to create it.

etl

The first part of the ETL process relates to retrieving data from Google Analytics. This process is straightforward, and simply involves using a properly configured Google Analytics reader.

The second part dictates how data will be extracted from Facebook, to be mashed up in GoodData. There’s a key difference here that separates this step from the previous one: each metric must be downloaded separately in the Facebook reader. That means that there are three parts within the Facebook graph - the first one extracts the number of Storytellers (People Talking About This - PTAT), the second one extracts the number of Facebook Page Fans added for every day, and the third one passes the date for each row (since we don’t have date directly extracted from the Facebook reader).

Together, these three parts form the resulting dataset. As a final step, connect these columns together and upload the results to GoodData. You should see something like this:

Storytellers, Fans, Date
10,12,2012-01-01
…,...,...

After the whole process finishes, you will be able to see the data in the GoodData Platform, where you can define your metrics. Start with simple ‘Select SUM(PTAT)’ for the storytellers and ‘Select SUM(Visits)’ for your website visits.

Now create the report: choose those two metrics in the WHAT section of the Report Editor and slice them by the Date attribute in the HOW section. Select the line chart and use the chart configurations options to set any desired secondary axes, colors, axis ranges, etc.

Once you’ve added your new report to your dashboard, a nice finishing touch is to create two headline widgets for those two metrics. Add a dashboard date filter so you will be able to easily zero in on certain time periods of interest. And there you have it!

social dashboard

Dev's Newsletter

Subscribe Now