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 money, but You can try to create your own solution with a very little costs. 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 {{credentials}}, {{name of spreadsheet}} and {{name of sheet}} replaced with your values:

import gspread
from gspread_pandas import Spread, Client, conf
from oauth2client.service_account import ServiceAccountCredentials

c = conf.get_config({{credentials}})

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 a very little costs, so I suggest 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:

Znalezione obrazy dla zapytania send python file on google 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! =)

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

  • Delightful. Really quite helpful. Gave me lots of ideas. Thank you very much for taking the time to write this. The illustrations are fantastic as well. I use a slightly different method. It’s very similar to yours. I wrote a module to automatically get a long lived token if it expires soon. This is only used internally so risk is minimal, otherwise I would recommend another method. Anyway, next I made an empty df. Then, for each account I retrieved ad level data (incl acct_id), pd.DF it, and if the len(df)>0 then concat to the main df, then save main df. We will probably have something sniff for new files and load it to a data warehouse.

    The issue I have is getting a date column for aggregated actions *for that specific day*. adinsights.field has created_time, date_start, and date_stop. They don’t help. created_time refers to when the ad is created, not when the action is recorded. date_start/stop just refers to the start and stop of the date range. Anything greater than a day will not generate dates between the start/stop. Must I send 365 queries for each day if I want a year of data by day?? and another year if I want to compare YoY?! Clearly there is a date column in the source schema. I don’t see why they would obfuscate it. Do you know of another way to get that column?

    thanks!

    jnault

  • This is remarkable! However, at step 2 I seem to run into the problem NameError: name ‘df’ is not defined.

    When commenting out display(df) the code runs fine.

    When adding the Gspread code, I run in the same error

    spread.df_to_sheet(df, index=False, sheet=’FB data’, start=’A1′, replace=True)
    NameError: name ‘df’ is not defined

    Any suggestions would be hugely appreciated!

  • Thanks! One Q, how do you plan on handling the FB access_token refreshes? Figure the token needs to be refreshed pretty often.

Leave a Reply

Your email address will not be published. Required fields are marked *