How to automatically export Facebook post data to Google BigQuery every day with Google Apps Scripts and Supermetrics.
Playing with data can be fairly simple even if you have very basic technical skills. However It can also be intimidating if you don’t have any idea on how to interact with a database for example. This article aim to give you a quick introduction on how to easily interact with Google BigQuery. My idea behind this is that every marketer, social media manager and whatever growth hacker should have basics data knowledge. So here is three simple step to collect Facebook post data and send them to Google BigQuery.
To understand this short step-by-step guide you need to grasp the basics of Google Apps script (GAS). It’s easy to use and very useful in many situation. You can take a look at it here
This guide is divided in three simple steps :
- Collecting Facebook post data with Supermetrics in a Google Sheet.
- Cleaning and Formatting Facebook post data.
- Sending Facebook post data to Google BigQuery.
So let’s start.
- #1 Collect Facebook post data. In order to simplify this example and make it accessible to the most of us, I’ve decided to collect the facebook post data using Supermetrics. This tool is super easy to use. It will save us a lot of time so we don’t have to build our own Googe Apps Script to collect Facebook data. However, if you really want to collect everything all by yourself, you can always take a look at it here. So I’ve build my Supermetrics queries scheduled for a daily refresh. My data are simply organised in rows and columns :
- #2 Formatting the data. Here is the trickiest part. It’s very important to understand that data can’t flow from one place to another without being formatted properly. In our example, the columns Post Name and Post Message are causing trouble because they contain punctuation mark or other special characters. These characters break the exportation to BigQuery. So I decided to get rid of them by using the simple script available here. This script is taking each cell from the two columns and deleting all specific characters. I’ve scheduled the execution of this script one hours after refreshing the Facebook data with Supermetrics. Like this I had my data clean and ready to export automatically everyday.
- #3 Send data to bigQuery. Our Facebook data are clean, ready to be sent. We are going to use GAS with the following script available here. Once again I’ve scheduled the execution of this script everyday. To make sure that your data has been properly exported you can take a look at the preview section in BigQuery :