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 free solution. 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-west1us-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:

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! =)

5 3 votes
Article Rating
Subscribe
Notify of
guest
31 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Radu
Radu
2 months ago

Hey man !
Nice Work, I’m stuck at the operation after the credentials.
I’m unfortunately stuck at some point.
Here is my script:

My Script
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 = “272078***4823699”
app_secret = “71402cdf509ca298f2*****c7ca7c58”
access_token = “EAAmqiqSkHhMBAE10jOuCJx23bzyTdkJtMaGg8KDRwDqUEK6j0Xkm1botH98cxpDKc2SycsaWoG9FD03VaUTVJ9*******N21DtUbZCsGRhOlg7ESoEjL0QudpzCyiS7K36nxtjwdejfw5sOT4wIpowuZBAZAUsZBCCVZAcyZBWSYWZAXIfXe0kAZD”
FacebookAdsApi.init(app_id, app_secret, access_token)
my_account = AdAccount(‘act_3197105***74689’)

campaign = Campaign(2384521**2270360)
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)

from gspread_pandas import Spread, Client, conf

c = conf.get_config(“/Users/***/Downloads/”, “credentials6.json”)
/Users/Radu/Downloads/credentials6.json
spread = Spread(“AR Digital – Facebook Ads Connector”, config=c)
spread.df_to_sheet(df, index=False, sheet=’Test’, start=’A1′, replace=True)

And when I run It: I get this:

Error Response
 File “<stdin>”, line 1
  /Users/Radu/Downloads/credentials6.json
  ^
SyntaxError: invalid syntax
>>> spread = Spread(“AR Digital – Facebook Ads Connector”, config=c)
Traceback (most recent call last):
 File “<stdin>”, line 1, in <module>
 File “/usr/local/lib/python3.7/site-packages/gspread_pandas/spread.py”, line 131, in __init__
  self.client = Client(user, config, scope, creds)
 File “/usr/local/lib/python3.7/site-packages/gspread_pandas/client.py”, line 91, in __init__
  credentials = get_creds(user, config, self.scope)
 File “/usr/local/lib/python3.7/site-packages/gspread_pandas/conf.py”, line 184, in get_creds
  reraise(ConfigException, *exc_info[1:])
 File “/usr/local/lib/python3.7/site-packages/future/utils/__init__.py”, line 440, in raise_
  raise exc.with_traceback(tb)
 File “/usr/local/lib/python3.7/site-packages/gspread_pandas/conf.py”, line 163, in get_creds
  config, scope, redirect_uri=”urn:ietf:wg:oauth:2.0:oob”
 File “/usr/local/lib/python3.7/site-packages/google_auth_oauthlib/flow.py”, line 159, in from_client_config
  ‘Client secrets must be for a web or installed app.’)
gspread_pandas.exceptions.ConfigException: Client secrets must be for a web or installed app.
>>> spread.df_to_sheet(df, index=False, sheet=’Test’, start=’A1′, replace=True)
Traceback (most recent call last):
 File “<stdin>”, line 1, in <module>
NameError: name ‘spread’ is not defined

Last edited 2 months ago by Radu
seoyeong
seoyeong
1 month ago

Thanks alot! So Delighful! It is very helpful.
However I have a error . Would you give me a hand?

this is what i wrote:

app_id = “344~~22”
app_secret = “79~~2”
access_token = “E~~~AlH” 
FacebookAdsApi.init(app_id, app_secret, access_token)
my_account = AdAccount(‘act_~~4663’)
#campaigns = my_account.get_campaigns()

campaign = Campaign(238~~40544)
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)

ERROR:
Traceback (most recent call last):
 File “<stdin>”, line 1, in <module>
NameError: name ‘df’ is not defined

Thanks alot

Kind Regards,
SeoYeong

jess nault
jess nault
9 months ago

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

jess nault
jess nault
Reply to  Serhii Puzyrov
9 months ago

thanks! confirmed. I’ve added the level into parameters and it works great. Data Studio looks interesting. I’ll tinker w/ it.
jnault

Wouter Schreurs
Wouter Schreurs
5 months ago

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!

Jason
Jason
4 months ago

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

Sanjeev
Sanjeev
3 months ago

Hi, Thanks for the post

I am trying to set up according to the tutorial but I am stuck on 1st step itself. I am getting this error below

Traceback (most recent call last):
File “test.py”, line 5, in
from facebook_business.api import FacebookAdsApi
ImportError: No module named facebook_business.api

My Code look this just like 1st step :
import sys
sys.path.append(‘/home/ec2-user/.local/lib/python3.7/site-packages/pip’) # Replace this with the place you installed facebookads using pip
sys.path.append(‘/home/ec2-user/.local/lib/python3.7/site-packages/pip/facebook_business-7.0.2.dist-info’) # same as above

from facebook_business.api import FacebookAdsApi
from facebook_business.adobjects.adaccount import AdAccount

my_app_id = ‘appid’
my_app_secret = ‘my app secret’
my_access_token = ‘my token’
FacebookAdsApi.init(my_app_id, my_app_secret, my_access_token)
my_account = AdAccount(‘act_’)
campaigns = my_account.get_campaigns()
print(campaigns)

Wilo A
Wilo A
3 months ago

Could you explain more this part:

import gspread —————– #is wrong becouse is — import gspread_pandas
from gspread_pandas import Spread, Client, conf
from oauth2client.service_account import ServiceAccountCredentials

c = conf.get_config({{credentials}}) —————– #What credentials? Client ID or Secret ID or Authorization code or Access token, if are diferent credential how are they replaced ?

spread = Spread(“{{name of spreadsheet}}”, config=c) #What is name of spreadsheet?
spread.df_to_sheet(df, index=False, sheet='{{name of sheet}}’, start=’A1′, replace=True)

Wilo A
Wilo A
Reply to  Serhii Puzyrov
3 months ago

Ty 🙂

Gabriel
Gabriel
3 months ago

Hi, thanks for the post, great info.
Any idea of the following error? thanks

Traceback (most recent call last):
File “script.py”, line 6, in
FacebookAdsApi.init(app_id, app_secret, access_token)
File “/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/facebook_business/api.py”, line 199, in init
session = FacebookSession(app_id, app_secret, access_token, proxies,
File “/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/facebook_business/session.py”, line 70, in __init__
params[‘appsecret_proof’] = self._gen_appsecret_proof()
File “/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/facebook_business/session.py”, line 78, in _gen_appsecret_proof
self.app_secret.encode(‘utf-8’),
AttributeError: ‘set’ object has no attribute ‘encode’

Gabriel
Gabriel
3 months ago

Hi, thanks for the post, great info.
Any idea of the following error? thanks

Traceback (most recent call last):
File “script.py”, line 6, in
FacebookAdsApi.init(app_id, app_secret, access_token)
File “/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/facebook_business/api.py”, line 199, in init
session = FacebookSession(app_id, app_secret, access_token, proxies,
File “/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/facebook_business/session.py”, line 70, in __init__
params[‘appsecret_proof’] = self._gen_appsecret_proof()
File “/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/facebook_business/session.py”, line 78, in _gen_appsecret_proof
self.app_secret.encode(‘utf-8’),
AttributeError: ‘set’ object has no attribute ‘encode’

Jack
Jack
3 months ago

Hi, Thanks for great tutorials, but when printing the campaign it went like this:
Traceback (most recent call last):
File “report.py”, line 3, in
app_id = {{3163026307050929}}
TypeError: unhashable type: ‘set’

Do you have any ideas about this? Thanks

Jack
Jack
Reply to  Serhii Puzyrov
3 months ago

Thank you for your reply.
Another question is when I ran the file it informed like this:
Status: 400
Response:
{
“error”: {
“message”: “Unsupported get request. Object with ID ‘1510427702573105’ does not exist, cannot be loaded d
ue to missing permissions, or does not support this operation. Please read the Graph API documentation at https:/
/developers.facebook.com/docs/graph-api”,
“type”: “GraphMethodException”,
“code”: 100,
“error_subcode”: 33,
“fbtrace_id”: “A4tHtAoVqBbB60tPQQO8Wty”
}
}

Is this relevant to app permission or something like this? Thanks again

Jack
Jack
Reply to  Serhii Puzyrov
3 months ago

Yes this is the AdAccount ID. Here is my code:

AdAccountID = ‘act_1510427702573105’
FacebookAdsApi.init(app_id, app_secret, access_token)
my_account = AdAccount(AdAccountID).get_insights()
campaigns = my_account.get_campaigns()

Wilo A
Wilo A
3 months ago

how i get a correct dataframe

params = {
‘date_preset’: ‘yesterday’,
‘fields’: [‘campaign_name’, ‘ad_name’, ‘impressions’, ‘inline_link_clicks’, ”actions, ‘spend’]}

response = campaign.get_insights(params=params)

the column actions have all fields with lists, i would like that this column dont be one column but make diferent columns example:

action_type | value
video_view | 45

Camilo
Camilo
3 months ago

How i will save it in VM ?

Ann
Ann
2 months ago

Thanks! It is useful for me! How can I renew API key automatically?

Sachin More
2 months ago
FacebookRequestError: 

  Message: Call was not successful
  Method:  GET
  Path:    https://graph.facebook.com/v7.0/act_{{https://www.facebook.com/adsmanager/manage/ads?act=2370387293224587}}/campaigns
  Params:  {'summary': 'true'}

  Status:  400
  Response:
    {
      "error": {
        "message": "Invalid OAuth access token.",
        "type": "OAuthException",
        "code": 190,
        "fbtrace_id": "AUX9-hLLClNJ9P745n9-7LH"
      }
    }

I am getting this error ……………..pls guide me how to sort this???

Gary
Gary
2 days ago

How do i get the report day by day not a period?