How To Automate Facebook Ads Reporting In Google Data Studio With Python And FB Marketing API

If You ever wanted to create a dashboard with Facebook Ads data – You probably had an issue with data connectors. The most popular like Supermetrics costs lot of money, although there are some cheap solutions like Pipelinica. But also You can try to create your own solution for free. There is a scheme of my solution:

As you can see on the scheme – there is a python script which is running on Google Virtual Machine, gets data from Facebook Ads through Facebook Marketing API, pastes it to Google Spreadsheets – and after that you can use a built in Data Studio Google Spreadsheet conector to use that data in your reports.

Step 1 – Create Your own FB app

Go to https://developers.facebook.com/apps and click “Add a New App”.
Give it a name (you can change it in future) and click “Create App ID”.

After that You will see a list of APIs and services, you can use in your FB app. For Facebook Ads we need only Marketing API – so find it and click “Set Up”.

Then navigate to “Tools” and generate an access token (ads_read for reporting purposes).

Save that token – You will need it later.
Next navigate to Settings – Basic, and find your App ID and App Secret:

Now You have all You need to make a first API call. Next install python package “facebook_business”:

pip install facebook_business

and try to run this code with your app_id, app_secret, access_token and fb_ads_account_id values:

from facebook_business.api import FacebookAdsApi
from facebook_business.adobjects.adaccount import AdAccount
app_id = {{your app id}}
app_secret = {{your app secret}}
access_token = {{your access token}}
FacebookAdsApi.init(app_id, app_secret, access_token)
my_account = AdAccount('act_{{fb_ads_account_id}}')
campaigns = my_account.get_campaigns()
print(campaigns)

You should see id`s of all your Facebook Ads campaigns printed.
Next try to get reporting data with the following code replacing {{id_of_any_of_your_campaign}} with your value:

from facebook_business.api import FacebookAdsApi
from facebook_business.adobjects.adaccount import AdAccount
from facebook_business.adobjects.campaign import Campaign
app_id = {{your app id}}
app_secret = {{your app secret}}
access_token = {{your access token}}
FacebookAdsApi.init(app_id, app_secret, access_token)
my_account = AdAccount('act_{{fb_ads_account_id}}')

campaign = Campaign({{id_of_any_of_your_campaign}})
params = {
    'date_preset': 'yesterday',
    'fields': ['campaign_name', 'ad_name', 'impressions', 'inline_link_clicks', 'spend']}
response = campaign.get_insights(params=params)
print(response)

More parameters for API call You can find here:
https://developers.facebook.com/docs/marketing-api/insights/parameters/v5.0

Step 2 – Transform response to dataframe and send it to Google Spreadsheets

When you have the response from FB Marketing API call – I suggest to transform it to pandas dataframe for easier use:

from facebook_business.api import FacebookAdsApi
from facebook_business.adobjects.adaccount import AdAccount
from facebook_business.adobjects.campaign import Campaign
import pandas as pd
app_id = {{your app id}}
app_secret = {{your app secret}}
access_token = {{your access token}}
FacebookAdsApi.init(app_id, app_secret, access_token)
my_account = AdAccount('act_{{fb_ads_account_id}}')

campaign = Campaign({{id_of_any_of_your_campaign}})
params = {
    'date_preset': 'yesterday',
    'fields': ['campaign_name', 'ad_name', 'impressions', 'inline_link_clicks', 'spend']}

response = campaign.get_insights(params=params)
for i in response:
    df = pd.DataFrame({})
    for col in i:
        df1 = pd.DataFrame({col:[i[col]]})
        df = pd.concat([df, df1], axis=1, sort=True)
display(df)

Next You should generate a Google Sheets credentials to get an access to It`s API, and turn on Sheets API in Google Cloud Platform.
Here you can create the credentials :
https://console.developers.google.com/apis/credentials
And there is a good instruction with all the details how you can do that:
https://docs.wso2.com/display/IntegrationCloud/Get+Credentials+for+Google+Spreadsheet
Next I would suggest You to use a great library “gspread”:

pip install gspread

It lets You to export your pandas dataframe to Google Sheets in a few lines of code. Try to run the code below, with {{path_to_credentials}}, {{credentials.json}}, {{name of spreadsheet}} and {{name of sheet}} replaced with your values:

from gspread_pandas import Spread, Client, conf

c = conf.get_config("{{path_to_credentials}}", "{{credentials.json}}")

spread = Spread("{{name of spreadsheet}}", config=c)
spread.df_to_sheet(df, index=False, sheet='{{name of sheet}}', start='A1', replace=True)

If the code is success – You should see your dataframe in the appropriate Google Spreadsheet.

Step 3 – Place Your python script on Google Virtual Machine

Next we should put our script on server and schedule it. We can do that with the help of Google VM (service of Google Cloud Platform). Go to GCP:
https://console.cloud.google.com, create a new project (if You doesn`t have one yet), and go to VM instances:

Click “Create” – here You can shoose the settings of your Virtual Machine instance:

f1-micro – is a simplest VM that will generate you any costs in these zones: us-west1, us-central1, or us-east1, so I suggest you to use it. Another important setting is “Allow HTTP traffic” – it should be turned on:

Other settings are not so important so You can leave them default. After You`ve created a VM – click SSH button:

It will open a VM console – now You are in Linux environment. Here You should install python and all python packages that You use in your script as normally You do on yourl local computer:

Now upload Your python file to VM:

Try to run your script with the command:

python3 my_script_name.py

If everything is OK – automate Your script with crontab in order to run it for example each day in the morning at 8 AM.
Call a crontab with a command:

crontab -e

And there – paste a line similar to this:

0 8 * * * python3 /home/serhii_puzyrov/my_script_name.py >> /home/serhii_puzyrov/my_script_name.log 2>&1

When You`ll save it – VM will run the script named my_script_name.py each day at 8 AM.

Step 4 – Create a data source in Google Data Studio

Now, when You have the FB Ads data in Spreadsheet and it refreshes automatically – it`s easy to use it in Google Data Studio. Create a new data source:

and just use the default Google Sheets connector:

That`s all! =)