Import Facebook Ad Data to Google Sheets

Import Facebook Ad Data to Google Sheets

Last Updated On October 11, 2019

Facebook has a popular advertising platform, and an extensive API to extract data on ad performance. In this guide, we’ll walk through how to pull Facebook ad data from the Facebook API directly into Google Sheets, using the free API Connector add-on.

The process for getting Facebook access tokens is somewhat convoluted as there are several kinds of tokens, which are valid for different lengths of time, and have different purposes. In addition, the API itself is very complex, with options for all different kinds of metrics and use cases. There are multiple methods to hook into the Facebook advertising API, but the method described below is relatively straightforward and should ensure you don’t get lost along the way.

PART 1: GET YOUR FACEBOOK API USER ACCESS TOKEN

  1. Begin by navigating to https://developers.facebook.com/, and click Create App.
    fb-img1
  2. Fill out your details and click Create App ID. You can call your app anything, but in this example we’ll call it Google Ads for Sheets.
    fb-ads-img2
  3. You’ll be directed into your Developer dashboard. You don’t need to do anything in this dashboard. Instead, open up the Facebook Graph API Explorer
  4. Click Get User Access Token from the drop-down menu
  5. fb-ads-img3

  6. A modal will appear prompting you to select the permissions you wish to access. For this example, make sure you’ve at least selected “ads_read”. Click Get Access Token.
    fb-ads-img4

  7. You’ll be prompted to log in.
    fb-img5
  8. Click through, ignoring any prompts to “Submit for Login Review”. We don’t need to worry about that for now.
    fb-ads-img6
  9. You should now see your first access token. However it only lasts for 1 hour. We can extend it by clicking the small blue icon next to the short-lived access token.
    fb-ads-img7
  10. Next, click Open in Access Token Tool (link).
    fb-ads-img8
  11. At the bottom of this page, click Extend Access Token.
    fb-ads-img9
  12. This will produce a long-lived token. Click Debug
    fb-ads-img10
  13. You’ll see a new screen that displays the whole string. Copy it to your clipboard, we’ll need it in a moment.
    fb-ads-img11

PART 2: CREATE YOUR API REQUEST URL

We’ll follow the Facebook documentation to access key summary data for your account.

  • API root: https://graph.facebook.com/v4.0
  • Endpoint: /act_{account ID}/insights (Get your account ID from Ads Manager)
    Endpoint Example: /act_161974980891886/insights
  • Query Strings: ?access_token={Page Access Token}
    Query Strings Example: ?access_token=EAAGMSKNpLKwBAPKo3s4b7Q1nGMjXoZCROUyl…

Putting it together, we get the full API Request URL:
https://graph.facebook.com/v4.0/act_161974980891886/insights?access_token=EAAGMSKNpLKwBAPKo3s4b7Q1nGMjXoZCROUyl…
Don’t forget to substitute in your own account ID and access token.

PART 3: ENTER YOUR VALUES INTO API CONNECTOR

We can now enter all our values into API Connector and import Facebook Ad data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
  2. In the Create Request interface, enter the Request URL we just created.
    fb-ads-img12
  3. Leave the Headers section empty as we don’t need any headers for this request
  4. Create a new tab. You can call it whatever you like, but here we’ll call it ‘FB_insights’. While still in that tab, click ‘Set’ to use that tab as your data destination.
  5. Name your request. Again we’ll call it ‘FB_insights’
  6. Click Run and a moment later you should see summary ad stats populate your Google Sheet. By default, without additional parameters the FB Advertising API will return impression and spend data for the last 30 days.
    fb-ads-img13

PART 4: NOTES AND EXPANSIONS

  1. Experiment with endpoints and query strings as described in the documentation to see other types of ad data. For example, you can access key ad metrics for the last 7 days by changing the API URL to https://graph.facebook.com/v4.0/act_{account ID}/insights?date_preset=last_7d&fields=actions,reach,impressions,spend&access_token={access token}. Again, remember to substitute in your actual account ID and access token where it reads {account ID} and {access token}.
    fb-ads-img14
  2. The ‘actions’ parameter will return a long list of fields. The ‘purchases’ metric from the Ads Manager account overview dashboard is shown in the ‘offsite_conversion.fb_pixel_purchase’ field.
    fb-ads-img15
    If you want to see all the available fields in rows instead of columns, switch the report style to ‘compact’.
  3. If you want to filter for specific actions, e.g. purchases, you can do so by appending &filtering=[{field: “action_type”,operator:”IN”, value: [‘offsite_conversion.fb_pixel_purchase’]}] to the end of your API URL. Due to the special characters, you’ll first need to encode that piece of the URL. You can do so by placing it in a cell (e.g. cell A1), using the =ENCODEURL(A1) function in Sheets, and then referencing that cell as part of your request URL.
    fb-ads-img16

Comments:0

Leave a Reply

Your email address will not be published.