Search API Connector Documentation

Print

Import Facebook Ad Data to Google Sheets

In this guide, we’ll walk through how to pull Facebook ad data from the Facebook Marketing Insights API directly into Google Sheets, using the API Connector add-on for Google Sheets.

There are 2 ways to connect to the Facebook Ads API:

  • Preset “Connect” button (OAuth) premium
  • Personal access token. Please check the appendix for detailed instructions to retrieve your token.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Connect to the Facebook Ads API

To begin, we’ll initiate the connection to the Facebook Ads API.

  1. Open up Google Sheets and click Add-ons > API Connector > Manage Connections.
  2. In the list of available connections, find Facebook Ads and click Connect.
    fb-ads-img13
  3. You will be directed to Facebook and asked to approve the connection. Click Done.
    fb-ads-img14
  4. You’ll then be returned to your Google Sheet, and can verify that your Facebook Ads connection is active in the Connections screen.

Part 2: Create a Facebook API Request URL

For our first request, let’s get some basic data about your Facebook ads account.

  • API root: https://graph.facebook.com
  • Endpoint: /v12.0/act_YOUR_ACCOUNT_ID/insights (Get your account ID as described here)

Putting it together, we get the full API Request URL:

https://graph.facebook.com/v12.0/act_123456789012345/insights

Just substitute in your own account ID. (If you use the preset Facebook Ads connection, API Connector will automatically route requests to the most recent version when the version changes.)

Part 3: Pull Facebook API Ad Data into Sheets

Now let’s enter that URL into API Connector and import our first set of data.

  1. Back in the Create Request interface, enter the Request URL we just created above.

    fb-ads-img16
  2. Under OAuth, choose Facebook Ads from the dropdown menu. You should see a badge saying “Connected”.
    fb-ads-img17
  3. We don’t need any headers so just leave that section blank.
  4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  5. Name your request and click Run. 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-img18

Part 4: More Example API URLs

Facebook’s API is large and complex, with many different endpoints. The cool thing about querying the API directly is that you can get ANY of the data they provide, but it can require a little digging to get exactly what you want. Here are some resources:

Resources

  • The example APIs below. Check out the examples first to get an idea how it works.
  • Mixed Analytics’ handy Facebook URL builder. This is the easiest way to create your request URLs.
  • While in the add-on, you can also query API Connector’s API Library (just search for “Facebook Ads”) to easily pull up many sample URLs.
  • If you want to get more technical, this blog article contains additional examples and information about the API.
  • And, of course, Facebook’s official documentation.

Examples

  • Key metrics (reach, impressions, clicks, CPC, and spend) for the past 30 days:
    https://graph.facebook.com/v12.0/act_YOUR_ACCOUNT_ID/insights?fields=reach,impressions,clicks,cpc,spend
    fb-ads-img19

  • Key metrics for the past 30 days, broken down by age and gender:
    https://graph.facebook.com/v12.0/act_YOUR_ACCOUNT_ID/insights?fields=reach,impressions,clicks,cpc,spend&breakdowns=["age","gender"]
    fb-ads-img26The documentation contains a list of available breakdown dimensions and breakdown combinations.
  • Key metrics for the past 30 days, for each day:
    https://graph.facebook.com/v12.0/act_YOUR_ACCOUNT_ID/insights?fields=reach,impressions,clicks,cpc,spend&time_increment=1
    fb-ads-img20The time_increment parameter lets you choose how to slice up the results. Options include time_increment=monthly, time_increment=all_days, or time_increment={any number from 1-90}.
  • Key metrics for a specific time period, for each day:
    https://graph.facebook.com/v12.0/act_YOUR_ACCOUNT_ID/insights?fields=reach,impressions,clicks,cpc,spend&time_increment=1&time_range={"since":"2020-08-01","until":"2020-08-31"}
    If you prefer to use a variable time period, you can either use Facebook’s date_preset parameter, or reference cells containing dynamic dates in your API request URL.
  • Key metrics for a specific time period, for each day and campaign name:
     https://graph.facebook.com/v12.0/act_YOUR_ACCOUNT_ID/insights?fields=campaign_name,reach,impressions,clicks,cpc,spend&time_increment=1&time_range={"since":"2020-08-01","until":"2020-08-31"}&level=campaign
    fb-ads-img22

  • Key metrics for the past 7 days, for each day and by account name, campaign ID, campaign name, adset ID, adset name, ad ID, ad name, and account currency:
    https://graph.facebook.com/v12.0/act_YOUR_ACCOUNT_ID/insights?fields=account_id,account_name,campaign_id,campaign_name,adset_id,adset_name,ad_id,ad_name,account_currency,reach,impressions,clicks,cpc,spend&time_increment=1&level=ad&date_preset=last_7d
    fb-ads-img23
    The date_preset parameter lets you choose your reporting period. Options include date_preset=today, yesterday, this_month, last_month, etc. A full list is available in the documentation. It will be ignored if the time_range parameter is used.

  • Key metrics for the past 30 days, including conversions/actions (see more about getting conversion data in part 5). May 2021 update: the &use_unified_attribution_setting=true parameter has been added to account for iOS14-related attribution changes (info).
    https://graph.facebook.com/v12.0/act_YOUR_ACCOUNT_ID/insights?fields=actions,reach,impressions,clicks,cpc,spend&use_unified_attribution_setting=true

    fb-ads-img24
    Note: this request was run in “compact” mode to make it easier to read the list of actions. The “single row” report style would display each action in its own column.

  • Ad status (active, paused, deleted, etc):
     https://graph.facebook.com/v12.0/act_YOUR_ACCOUNT_ID/ads?fields=name,id,ad_review_feedback,effective_status,updated_time&limit=1000
    Note: this final “Ad status” request pulls from the FB Ads API rather than the FB Insights API. The FB Ads API uses a different URL structure, and requires UNIX timestamp filters, e.g. https://graph.facebook.com/v12.0/act_YOUR_ACCOUNT_ID/ads?fields=name,id,ad_review_feedback,effective_status,updated_time&limit=1000&filtering=[{'field':'updated_time','operator':'GREATER_THAN','value':'1616025600'}, {'field':'updated_time','operator':'LESS_THAN','value':'1629590400'}] or https://graph.facebook.com/v12.0/act_YOUR_ACCOUNT_ID/ads?fields=name,id,effective_status&filtering=[{field: "effective_status",operator: "IN",value: ['ACTIVE']}]&limit=1000

Part 5: Understanding Conversion / Action Data

Facebook’s API treats conversions as a type of “action”, and you retrieve them by including fields=actions in your query, as shown in the conversions URL example above. Actions will automatically be returned with their action count (the number of times the action occurred). If you would like to also retrieve the value of an action, e.g. a purchase value, you need to also request the action_values field like this: fields=actions,action_values.

The response isn’t that convenient as Facebook will return ALL of the actions they’ve tracked on your site or app, along with the action count. This is an abbreviated list of the actions tracked by Facebook (Source: https://developers.facebook.com/docs/marketing-api/reference/ads-action-stats/).

  • offsite_conversion.custom.: Custom Conversions defined by the advertiser
  • offsite_conversion.fb_pixel_add_payment_info: Adds Payment Info
  • offsite_conversion.fb_pixel_add_to_cart: Adds To Cart
  • offsite_conversion.fb_pixel_add_to_wishlist: Adds To Wishlist
  • offsite_conversion.fb_pixel_complete_registration: Completed Registration
  • offsite_conversion.fb_pixel_custom: Custom pixel events defined by the advertiser
  • offsite_conversion.fb_pixel_initiate_checkout: Initiates Checkout
  • offsite_conversion.fb_pixel_lead: Leads
  • offsite_conversion.fb_pixel_purchase: Purchases
  • offsite_conversion.fb_pixel_search: Searches
  • offsite_conversion.fb_pixel_view_content: Views Content
  • onsite_conversion.flow_complete: On-Facebook Workflow Completions
  • onsite_conversion.messaging_block: Blocked Messaging Conversations
  • onsite_conversion.messaging_conversation_started_7d: Messaging Conversations Started
  • onsite_conversion.messaging_first_reply: New Messaging Conversations
  • onsite_conversion.post_save: Post Saves
  • onsite_conversion.purchase: On-Facebook Purchases
  • outbound_click: Outbound Clicks
  • photo_view: Page Photo Views
  • post: Post Shares
  • post_reaction: Post Reactions
  • rsvp: Event Responses
  • video_view: 3-Second Video Views

This means that when you run your “fields=actions” request, the Facebook API may return a lot of data about conversions you’re not interested in. Therefore, you will usually want to include some type of filtering. The next section has information on that.

Part 6: Handle Filtering

If you want to filter for specific actions, e.g. purchases, you can do so with the ‘filtering’ parameter, like this:

https://graph.facebook.com/v12.0/act_YOUR_ACCOUNT_ID/insights?fields=actions,reach,impressions,clicks,cpc,spend&filtering=[{field: "action_type",operator:"IN", value: ['offsite_conversion.fb_pixel_purchase']}]

If you’re using the IN operator, you can include multiple conditions like this:

&filtering=[{field: "action_type",operator:"IN", "value":["offsite_conversion.fb_pixel_purchase","offsite_conversion.fb_pixel_add_to_cart"]}]

To combine multiple filters for different fields, e.g. both a specific campaign and a specific action type, include them in the “filtering” array as a comma separated list. Facebook’s documentation isn’t very clear on this point, but it seems campaign names and ID filters need to be evaluated as a string rather than in an array, like this:

&filtering=[{field: "campaign.id",operator:"CONTAIN", value: '123456789'},{field: "action_type",operator:"IN", value: ['offsite_conversion.fb_pixel_purchase']}]

To filter for custom conversions, open up FB Business Manager, click Events Manager, then click Custom Conversions in the lefthand sidebar. You’ll see a list of custom conversions and their IDs. Substitute in that ID, like offsite_conversion.custom.398028550584411 instead of offsite_conversion.fb_pixel_purchase.

Part 7: Sort Data

Facebook allows sorting of data using the sort parameter. You enter the field name you’d like to sort on, and then append “_ascending” or “_descending” to specify the sort order (the default sort order is ascending). For example, to sort data in descending order of spend amount, you would append the following to your URL:

&sort=spend_descending

If you’re sorting on actions, you would sort by action type using the syntax sort=["actions:<action_type>"]. For example, to sort data in ascending order of cart adds, you would append the following to your URL:

&sort=["actions:offsite_conversion.fb_pixel_add_to_cart_ascending"]

Part 8: Set Column Order

The Facebook API doesn’t return fields at all when they don’t contain any data. So to make sure your columns line up between pulls, please open the Field Editor and select the fields you’d like to show in your sheet. This will ensure that your data stays fixed in place.

For actions, it’s a bit more complicated as Facebook’s API lists each conversion under the “actions” field. Therefore, if you’re requesting multiple conversions at once, you’ll probably need to do one of the following:

  • split actions into separate requests, or
  • use Sheets functions like VLOOKUP and QUERY to extract just the actions you want, or
  • restructure the data with a snippet of JMESPath (paid feature). The following JMESPath would create a report with the following fields fixed in place: campaign_id, campaign_name, unique_clicks, spend, cart_adds, purchases, date_start, date_stop
data[*]. {campaign_id:campaign_id, campaign_name:campaign_name, unique_clicks:unique_clicks, spend:spend, cart_adds:actions[?action_type=='offsite_conversion.fb_pixel_add_to_cart'].value|[0], purchases:actions[?action_type=='offsite_conversion.fb_pixel_purchase'].value|[0], date_start:date_start, date_stop:date_stop}

Part 9: Handle Pagination

  1. By default Facebook limits the number of results in a single response (usually to 25 records) as described here. To get more records, you can add the ‘limit’ parameter to the end of your URL, like this: https://graph.facebook.com/v12.0/act_YOUR_ACCOUNT_ID/insights?fields=reach,impressions,clicks,cpc,spend&limit=1000

  2. There seems to be an undocumented limit of 5000, so if you need more records after that, you can loop through them using pagination handling (paid feature).
    • API URL: enter your request URL as usual, making sure to include limit=5000
    • Pagination type: cursor
    • Next token parameter: after
    • Next token field: paging.cursors.after
    • Number of pages: enter the number of pages you’d like to fetch

      fb-ads-img21

Part 10: API Request URL Builder (NEW)

This is a convenient tool for easily building Facebook API request URLs as shown below. Just select your dates and parameters.

Once you’ve constructed your URL, you can copy and paste it into API Connector’s request URL input field. Here’s the link: Facebook API Request Builder

Part 11 [Business Plan]: Get the Facebook Ads Report Pack

Note: this report pack makes use of JMESPath filters and API Connector’s Facebook Ads OAuth2 connection, so you’ll need a paid Business account with API Connector for it to work (or install API Connector for a free trial).

The Facebook Ads report pack is a pack of pre-built report templates to get you started. All you need to do is enter your own Facebook account ID to populate the reports with your own data. The report pack includes the following:

  • Overview: A summary overview of key metrics
    fb-ads-img28
  • Campaign Data: A report showing all your campaigns, with key metrics including impressions, frequency, clicks, spend, purchases, etc.
    fb-ads-img29
  • Monthly Data: A report showing a summary of key metrics broken out by month
  • Device Data: clicks and impressions by device
  • Demographic: clicks and impressions by age and gender

Here is the link (click File > Make a Copy to get your own copy).

Appendix: Connect with a Facebook API User Access Token

This section is provided as an alternative to the method described above. Instead of clicking Facebook Ads in the Connections manager, you will retrieve and refresh your token yourself every 2 months.
  1. Begin by navigating to https://developers.facebook.com/, and click My Apps.
    fb-ads-img1
  2. Click Add a New App.
    fb-ads-img2
  3. Facebook will ask you who is using your app. Choose “Manage integrations for your business” and click Next.
    fb-page-app-audience
  4. Now choose an app type of Business and click Next.
    fb-page-img3
  5. Finally, enter your app details. You can name your app anything as long as it doesn’t include terms related to Facebook. Click Create App.
    fb-ads-app-details
  6. You’ll be directed into your Developer dashboard. Under the heading “Add Products to Your App”, scroll down to Marketing API and click Set Up.fb-ads-img5
  7. On the left sidebar, you’ll now see Marketing API listed under Products. Click Tools.
    fb-ads-img6
  8. This will produce a screen where you can select your token permissions. All the example API requests in this article use the “ads_read” permission, which allows you to read ads reports from the Ads Insights API for ad accounts you own or have been granted access to by the ad account owner. Click Get Token.fb-ads-img7
  9. You’ll now see your access token. Copy it down and keep it safe as they won’t show it again. Congrats, you can now access the Facebook Advertising API!
    fb-ads-img8
  10. Now that you have your token, you can use it by appending access_token=YOUR_ACCESS_TOKEN to any API URL, e.g. https://graph.facebook.com/v12.0/act_123456789012345/insights?fields=clicks,cpc,spend&access_token=YOUR_ACCESS_TOKEN. Since you’re manually including a token, leave OAuth2 authentication set to None.
This access token will expire in 2 months. Once it expires, go back to your Developer Dashboard, select your app, click Tools, and get a new token. Repeat this process each time your token expires. To view the status and expiration date of a token, you can paste it into Facebook’s Access Token Debugger tool.

Previous Import Etsy Data to Google Sheets
Next Import Facebook Leads to Google Sheets

209 thoughts on “Import Facebook Ad Data to Google Sheets”

  1. hey there

    Very good article, thank you for making it. I am trying to create an API URL to read all the data from form id, but I am getting no response.
    Kindly suggest how can I fetch all the columns and data.

    Response
    {
    "data": []
    }

    URL: https://graph.facebook.com/v10.0/{form-id}/leads?access_token=

    Reply
  2. Hello!

    I am in love with the API Connector, however I am trying to do one thing that I find impossible to do. Could you give me a hand?

    I would like to create a column to show an action, for example lead or like.

    For example: when I use https://graph.facebook.com/v9.0/+xxxxxx/insights?fields=account_name,campaign_name,actions,action_values&time_range={"since":"2020-01-01","until ":"2025-03-31"}&level=campaign&limit=50&filtering=[{field:"action_type", operator: "IN", value: ['like', 'comment', 'lead']}], I believe 6 Columns disordered according to action, one column is called data »actions» 1 »action_type with the value lead and another data» actions »1» value with the value number of like.

    Could you create a column called “lead” which contains the lead number?

    I hope I have explained myself 🙂

    Thanks!

    Reply
    • Hey Daniel, thanks for the comment, I’m glad you like API Connector 🙂
      If I’ve understood correctly, this is the issue discussed under the section called Set Column Order. When you select multiple actions, Facebook will send that data back in all different orders. The best way to deal with this is use a JMESPath expression, for example this one would create separate, fixed columns for likes, comments, and leads:
      data[*].{account_name:account_name,campaign_name:campaign_name,likes:actions[*] | [?action_type=='like'] | [0].value,comments:actions[*] |[?action_type=='comment'] | [0].value,leads:actions[*] |[?action_type=='lead'] | [0].value,date_start:date_start,date_stop:date_stop}

      Otherwise you can try pulling one action at a time, or switching to compact or grid style to make it a bit easier to read. Hope that helps, just let me know if you still have questions.

      Reply
      • Yes, it’s what i need! Thank you!

        On the other hand, when I use date_start and date_stop, I get the dates that I use to filter, I mean: time_range = {"since": "2020-01-01", "until": "2025-03-31"}.

        I would like to indicate the exact day when the campaign started and when it ended, but in “date_start” I see 2020-01-01 and “date_stop” the date of today.

        I have tried https://graph.facebook.com/v9.0/xxxx+/campaigns?fields=name,objective,status,start_time,stop_time and it works correctly as I need. But, when using two different queries (/ insights and / campaigns), everything appears in different lines.

        Could you help me? 🙂

        I hope I have explained myself correctly.

        All the best.

      • Hey Daniel, that’s how Facebook returns date data so we can’t do anything about that. However you can create your own summary dashboard with a VLOOKUP (or similar) function to match the 2 data sets together and pull in campaign start/stop dates into the same sheet as your insights data. Just let me know if you need further clarification.

  3. Hi Ana.
    Please help me!

    I get an error and cannot get the data from late April.
    error message;
    Completed with errors
    – Server responded with an error (500) show response{“error”:{“code”:1,”message”:”Please reduce the amount of data you’re asking for, then retry your request”}}

    I use;
    https://graph.facebook.com/v10.0/act_+++account!C2+++/insights?time_range={"since":"+++account!B6+++","until":"+++account!B8+++"}&level=ad&fields=account_id,account_name,campaign_id,campaign_name,adset_id,adset_name,ad_id,ad_name,impressions,inline_link_clicks,inline_link_click_ctr,cost_per_inline_link_click,spend,actions&filtering=[{field: "action_type",operator:"IN", value: ['offsite_conversion.fb_pixel_complete_registration','offsite_conversion.fb_pixel_lead','onsite_conversion.lead_grouped']}]&access_token=+++account!E2+++&time_increment=1&limit=500

    Thank you!

    Reply
    • Hi Yoshi! 🙂 I think the error message says it all: you need to reduce the amount of data you’re asking for and then try again.
      The simplest way is probably to just reduce your date range. You could break it into a couple different requests and then merge the data back together at the end. Alternatively, you could pull fewer metrics, or pull one action at a time, or perhaps choose level=campaign instead of level=ad if you don’t need that level of detail. Basically Facebook doesn’t like it when we try to extract too much data at once…

      Reply
  4. Hi Ana.
    Please help me!

    Is it possible to pull data for FB Ad Accounts Failed Payments? I’m new to API and don’t know how to set up this.

    Thanks in advance,
    Mye

    Reply
    • Hi Mye, this article is about getting advertising metrics, but it sounds like you’re looking for data about your account, right? So I am not really sure, but I just searched online and didn’t find anything information about that. If there is any relevant endpoint I suppose it would be in Facebook’s Business Manager API, so you can poke around there and see if there are any relevant data points I missed. Hope that helps!

      Reply
      • Hi Ana,

        Thank you so much for your reply, yes me too can’t find information about that. Will definitely check the Business Mgr API.

        All the Best!

  5. Hi Ana,

    First of all congratulations on such a great product, I’ve been looking for a while for something just like this and I’m thrilled of all the possibilities I have thanks to this API Connector.

    I’ve successfully been able to get all FB Ads information on a daily basis and going through the results, I’ve found out that the total actions for the events on the action_type field are not the same totals found on FB Ads Manager directly. Doing some research, I think this is due to new iOS 14 policies and I’m wondering if there’s a way in which I can get the total amount of actions for the conversions through the API Connector.

    What I’ve understand so far is that it can be possible but I’m not sure how to get iOS conversion actions information through the API Connector.

    Thanks in advance,
    Santiago

    Reply
    • Hey there, thank you for the nice comment!
      Yeah, at the end of April, Facebook changed its attribution changes to comply with iOS 14.
      Based on their docs, I think you now need to add &use_unified_attribution_setting=true to the end of your API request URL. This will force the data to use the attribution setting of the ad being queried, and should result in data matching between Ads Manager and the API response. Please try and let me know if it works for you.

      Reply
  6. Hi There

    I got the message: “Completed with errors – Server responded with an error (400)” When trying to connect to pull Facebook Ads marketing data… Any idea how to fix this?

    Reply
    • The detailed error is here: “{“error”:{“message”:”Unsupported get request. Object with ID ‘act_123456789012345’ does not exist, cannot be loaded due 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”:”AHzSs70GnGnPvk7oXCBssF-“}}”

      Reply
  7. Hi Ana,

    This is really helpful and thank you for the great explanation.

    I have one issue with filtering, it seems on certain dates where we have 0 value for metrics like ‘comment’, the API will not show that metric on google sheet, so it breaks the report format.

    How do we add more filtering like the value should be >= 0 or make sure FB show all metrics although it’s 0?

    I have below for the filtering
    filtering=[{'field': 'campaign.name','operator':'CONTAIN', value: 'XXX'},{'field':'action_type','operator':'IN','value':['spend','like','comment','post_reaction','post','onsite_conversion.post_save','video_view','photo_view','page_engagement','post_engagement','app_install','landing_page_view','link_click','landing_page_view','offsite_conversion.fb_pixel_add_to_cart','impressions','reach','video_play_actions','video_30_sec_watched_actions','video_avg_time_watched_actions','video_p100_watched_actions','video_p25_watched_actions','video_p50_watched_actions','video_p75_watched_actions','video_p95_watched_actions']}]&limit=1000`

    Reply
  8. Hi, i have some questions about your url builder.

    1) Can i choose data only for one campaign or it is only possible for all of them at the time?
    2) When i choose conversion (purchase) and try to get cost per action, action value and roas this data arent showing in my google sheet.
    3) I got the this error several times in a row and exactly dont know how to solve this because my limit is now 500 {“error”:{“code”:1,”message”:”Please reduce the amount of data you’re asking for, then retry your request”}}

    Can you help me with that?

    Reply
    • 1) You can add in filters for your campaigns. Please see here, the final example shows how to filter for a specific campaign.
      2) If those values don’t show up, it means those values don’t exist for the selected Action. You can test some of the other similar Actions (e.g. “Grouped Action: Purchases”) or make an API request to https://graph.facebook.com/v10.0/act_YOUR_ACCOUNT_ID/insights?fields=cost_per_action_type,action_values,purchase_roas to see what Actions are available for each of those metrics.
      3) That error message comes from Facebook, so unfortunately I can’t really say much more than they did: you’ll need to reduce the amount of data you’re requesting at once. You might need to reduce your date range or number of parameters, or try an even lower limit like 100. I would experiment with reducing your request until you do get data back, and then add complexity back to your query to see where it fails.

      Reply
      • Thank you. I also have problem like this

        Server responded with an error (400) show response{“error”:{“message”:”(#2635) You are calling a deprecated version of the Ads API. Please update to the latest version: v11.0.”,”type”:”OAuthException”,”code”:2635,”fbtrace_id”:”Ai6GRn7bfD1C4uqWoi-OFAN”}}

        But dont understand what should i update. Can you help me please?

      • Looks like Facebook updated its API version today (their schedule). Are you connecting through an API token or through OAuth2? If OAuth2, we automatically handle the version number so you shouldn’t have this issue. Otherwise, you can just change your base URL from graph.facebook.com/v10.0/ to graph.facebook.com/v11.0/.

  9. Also, can you write me an email how much it will be cost to install it for me? I study it for 2 days but i have some problems i cannt really solve

    Reply
    • How about trying a different tool like Supermetrics that doesn’t require working with the API directly? Big platforms like Facebook have plenty of specialized connectors (just click Add-ons > Get Add-ons and search for “Facebook”). Those connectors don’t require creating your own API URL, so they will be simpler to use.

      Reply
  10. Hello, i’m having trouble using the pagination.
    Ive used the excat same set-up described in the article but it does not work. can you help?
    – added the &limit=5000 in the API request
    – pagination : cursor
    – Next token parameter : after
    – Next token field : paging.cursors.after

    Reply
      • If it’s only showing the first 25 requests, that means it’s not recognizing your limit parameter. Please double-check that you’ve constructed the URL correctly (make sure you have no extra spaces, and that parameters start with a ? mark, etc).
        If you can copy and paste in your entire URL I can take a look.

      • Sorry, I was using the wrong API call it’s me/adaccounts and not me?fields=adaccounts 🙂 every thing is fine now
        thanks again

  11. My comment did not paste correctly. This is the URL I’m using https://graph.facebook.com/v10.0/me?fields=adaccounts&limit=5000&access_token=XXX
    – added the &limit=5000 in the API request
    – pagination : cursor
    – Next token parameter : after
    – Next token field : paging.cursors.after (also tried adaccounts.paging.cursors.after) but did not work either.
    either I have only 25 results, or I keep having the same results pasted over and over again.

    Thanks a lot!

    Reply
    • Not too sure since that’s not a Facebook API I’ve worked with, it might have a different limit. How about trying limit=100, or turning off pagination handling? You don’t really need it if you’re pulling so many records at once anyway.

      Reply
  12. Hi!

    Our data in Ads Manager only pulls the results that come as browser events. We have the server side data coming into our FB account via a server side CAPI (conversions API for FB).

    Unfortunately, all of the lead info that has been added to FB through server events does NOT show up in the spreadsheet that we have connected to mixed analytics API Connector.

    It does show up in Ads Manager.

    I did try your suggestion to others to add &use_unified_attribution_setting=true to the end of the API url, just in case, but that only makes the action (lead) data column pull blank.

    So it’s not an attribution issue.

    It appears to strictly be from when we set up and turned on server events. Any thoughts on how to pull that data also? Since it’s not technically a pixel event?

    Thank you.

    Reply
    • Hi Vicki! Good question. I’m not that familiar with the Conversions API but I think the issue might be related to the name of the action you’ve chosen. If you look at Facebook’s list of actions here, you can see that actions prepended by offsite_conversion come from the Facebook Pixel. So if you were using, say, offsite_conversion.fb_pixel_lead, that would pull only data tracked through the Facebook pixel, rather than data tracked through the server.

      So here are my suggestions:
      1) replace offsite_conversion.fb_pixel_lead with simply the word lead, in both your URL and JMESPath.
      This might resolve the issue, but if not it means that these actions aren’t tracked there either. In that case…
      2) open up FB Business Manager, click Events Manager, then click Custom Conversions in the lefthand sidebar. See if you can find your server events there, and, if so, note the custom ID number.
      3) run an API request to https://graph.facebook.com/v11.0/act_YOUR_ACCOUNT_ID/insights?fields=actions. Use compact mode to see a list of all the actions tracked by your account. See if you can find your leads data there.

      Sorry I can’t be more specific since I haven’t pulled conversions API data into Sheets before, and all the official documentation only refers to POSTing data to Facebook, rather than the other way around. Feel free to message me via support if you’d like to work on this together.

      Reply
  13. Hi guys! Thanks for the tutorial, I followed all the steps but I get this error:

    1) Completed with errors
    – Server responded with an error (400) show response
    {“error”:{“message”:”Unsupported get request. Object with ID ‘act_XXXXXXXXXXXXX’ does not exist, cannot be loaded due 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”:”AIn_XwAfTDga0rujxxxxxxxxx”}}

    Reply
  14. Hi Ana, thank you for a great blog post
    I have a couple of doubts:

    How can I include a column with the number of leads my campaigns are generating?
    I have try different way, just including “lead”, “action_type=lead” and also by filtering. But I think I have been typing something wrong. Could you tell me how to include it here:

    https://graph.facebook.com/v11.0/act_ACCOUNT/insights?fields=account_id,account_name,campaign_name,adset_name,ad_name,objective,reach,inline_link_clicks,inline_link_click_ctr,cost_per_inline_link_click,inline_post_engagement,spend&level=campaign&level=adset&level=ad&time_increment=1&time&limit=5000&date_preset=this_year&time_range={"since":"2021-01-01","until":"2021-01-31"}

    My second doubt, I created my app and token, now what? I past the token as de “API URL path:” in the API Connector but is not working.

    Thank you so much for your help

    Reply
    • Hi! I have managed to extract the leads.

      Regarding the token, it’s still not working. I get this error message:

      1) Completed with errors
      – Server responded with an error (400) show response
      {“error”:{“message”:”(#2635) You are calling a deprecated version of the Ads API. Please update to the latest version: v11.0.”,”type”:”OAuthException”,”code”:2635,”fbtrace_id”:”AiQ7t_Vl8E-wjXx6hcPBLQG”}}

      Reply
      • Great, I’m glad you’re able to access leads now.
        As for your error message, Facebook updates its API version regularly. It looks like you’re calling one of its deprecated versions, so please update your API request URL such that it begins with https://graph.facebook.com/v11.0/

  15. I’m trying to pull these metrics from my FB Ads account:

    https://graph.facebook.com/v10.0/act_XXXXXXXXXXXXXXXXX/insights?level=ad&filtering=[{field: 'action_type',operator:'IN', value: ['landing_page_view','onsite_conversion','link_click','like','photo_view','video_view','comment','post_reaction','post','post_engagement','page_engagement','lead']}]&fields=account_currency,campaign_name,account_name,adset_name,ad_name,reach,impressions,frequency,spend,cpm,inline_link_clicks,cost_per_inline_link_click,inline_link_click_ctr,clicks,cost_per_unique_click,cost_per_action_type,actions,conversion_values,cpc,ctr&time_range={"since":"2021-05-22","until":"2021-05-22"}&time_increment=1&limit=10000

    But even when I’m trying to pull for one single day, I am getting an error saying: “Please reduce the amount of data you’re asking for, then retry your request.”
    If I reduce the no. of metrics to just 1 or 2, I can see there are ~400 rows for the day.

    What am I doing wrong?

    Reply
    • Hey Walter, Facebook produces this error when you request too much data at once. I’m not sure exactly where their limits lie, but you’re currently requesting 12 different actions at once, 20+ different metrics, and 10,000 rows, so it’s a huge request. I would test pulling just a couple actions at once and reducing your limit to 1000, then you can increase the limit and add back complexity as you see where their limits kick in.

      Reply
      • Hi Ana,

        Thank you for the quick response. The response on this (if I keep only a couple of metrics) actually has only 400 rows. I think the problem is probably with the structure of the request?

        Per your advice, I set the limit to 10. And still got the “Please reduce the amount of data you’re asking for, then retry your request” message back.

      • It’s not (just) about the number of rows, it’s about how much their servers need to process on the fly and the total number of fields. Facebook’s documentation doesn’t contain any information about what exactly triggers this error but if you Google for your error message you can find many people discussing this. Since reducing the limit didn’t help, I suspect it’s related to the combination of all those actions and metrics. What happens when you remove all those actions except one or two? Or reduce your metrics?

    • For some reason, I can’t reply to your final answer, so just posting as a reply to my original question: If I keep only two metrics, I can pull the data. I think it is the “actions” that is breaking it—but those are link clicks etc. which are pretty important metrics to pull.

      Does the structure of the query itself appear to be ok?

      Reply
      • The structure of the query is totally fine. I agree it’s probably the total number of actions/conversions. As far as I’ve seen, people don’t usually track more than 2-3 of those at once. By default, Facebook Ads Manager itself only shows 2-3 actions, dependent on what column template you choose (their templates include “Performance”, “Setup”, “Delivery”, etc). How about duplicating those reports you see in Facebook Ads instead of pulling in everything at once? Or just copy/paste some of the example URLs in this article and start from there. I think you can also optimize your query, e.g. there’s no reason to have an action of “link click” AND a metric of “clicks”.

  16. Hi,

    I wanted to give this a shot but I’m stuck at the very first step. Whenever I try to connect the add on to facebook, I get an error message on facebook “Could not link API Connector for Ads to Facebook, You may not be connected to the network or we could not establish a connection with our server. Check your connection and try again later.”

    Any idea why that is ?

    Thanks

    Reply
    • Hey Julien, I haven’t seen this before but I just Googled the error message and found some people saying it’s related to your browser settings. Could you please try with a different browser? Also I read that it can happen if you just created your Facebook account within the past hour, any chance it’s a new account?

      Reply
      • Hi Ana, thanks for the very quick reply. I won’t get too much into boring details but indeed, I had to create a new facebook account for this. Went to get some lunch, came back, and it works now. Thanks again !

  17. Hi!

    I want to pull the number of times a video was played at 75%. Buy if I include an “actions&filtering” I don’t wet this KPI. Look, this is my URL:

    https://graph.facebook.com/v11.0/act_531771314822499/insights?fields=account_id,account_name,campaign_name,adset_name,ad_name,objective,reach,inline_link_clicks,inline_link_click_ctr,cost_per_inline_link_click,inline_post_engagement,video_p75_watched_actions,cost_per_action_type,conversions,spend,actions&filtering=[{field: 'action_type',operator:'IN', value:['lead', 'like', 'onsite_conversion.messaging_conversation_started_7d']}]&level=campaign&level=adset&level=ad&time_increment=1&time&limit=5000&date_preset=this_year&&date_preset=yesterday

    I don’t get any error, but either the 75% video plyaed

    Reply
    • video_p75_watched_actions is an action-based metric so you’ll need to include the corresponding action in your filter (where you’ve put ‘lead’, ‘like’, etc.). The action name is video_view.

      I also see a few issues with the structure of the URL. You should only set a single level (to tell FB which level you want to report on), a single date_preset, and remove extra parameters like &time&.

      Reply
      • Thank you a lot!

        Does this look fine to you?

        https://graph.facebook.com/v11.0/act_531771314822499/insights?fields=account_id,account_name,campaign_name,adset_name,ad_name,objective,reach,inline_link_clicks,inline_link_click_ctr,cost_per_inline_link_click,inline_post_engagement,video_p75_watched_actions,spend,actions&filtering=[{field: 'action_type',operator:'IN', value:['lead', 'like', 'video_view', 'onsite_conversion.messaging_conversation_started_7d']}]&level=adset&time_increment=1&time&limit=5000&date_preset=yesterday

        I still can’t pull the number of times a video was played at 75%

      • That looks right to me so I wonder if you just didn’t have any data yesterday. I would try simplifying your query to just the 75% video view elements and increasing the date range to see if they show up then, e.g.
        https://graph.facebook.com/v11.0/act_531771314822499/insights?fields=account_id,video_p75_watched_actions&filtering=[{field: 'action_type',operator:'IN', value:['video_view']}]&level=account&time_increment=1&limit=5000&date_preset=this_year

  18. Hi Ana!
    Im not sure how to use this URL to get the leads from a lead ads campaign:

    https://www.facebook.com/ads/lead_gen/export_csv/?id=&type=form&from_date=1482698431&to_date=1482784831

    Could you elaborate it?

    Reply
    • Hi Nazarrii, instead of directly adding your other data to your FB API output sheet, I suggest doing all your transformations and data merging in a summary sheet. The summary sheet can pull in your API data with a formula like =query(Sheet1!A:H), so it pulls in fresh data without overwriting your other data.
      Update: You can now set a starting cell to avoid overwriting your saved formulas.

      Reply
  19. Hi,

    I’m trying to pull ad set level data for the last 7 days using this query: https://graph.facebook.com/v11.0/act_XXXXXXXXX/insights?fields=campaign_id,campaign_name,adset_id,adset_name,impressions,clicks,spend&time_increment=1&level=adset&date_preset=last_7d

    I’m only getting the first 3 of the previous 7 days though, do you know why this is happening?

    On a seperate note, do you have anything similar to this for Microsoft Ads?

    Reply
  20. Hi Ana.
    Please help me!
    I get an error and cannot get the data today.
    error message;
    {“error”:{“message”:”(#100) Missing permissions”,”type”:”OAuthException”,”code”:100,”fbtrace_id”:”A7Whs5ymYcefTVWxLamrwLv”}}

    I use;
    https://graph.facebook.com/v12.0/act_+++account!C2+++/insights?time_range={"since":"+++account!B32+++","until":"+++account!B32+++"}&level=ad&fields=account_id,account_name,campaign_id,campaign_name,adset_id,adset_name,ad_id,ad_name,impressions,inline_link_clicks,inline_link_click_ctr,cost_per_inline_link_click,spend,actions&filtering=[{field: "action_type",operator:"IN", value: ['offsite_conversion.fb_pixel_complete_registration','offsite_conversion.fb_pixel_lead','onsite_conversion.lead_grouped']}]&access_token=+++account!E2+++&time_increment=1&limit=100&use_unified_attribution_setting=true

    Thank you!

    Reply
  21. Hi Ana,
    I have a question I was hoping you could help me with. I manage ad placements for 100+ facebook/instagram accounts & I’d like to have their monthly spends populate automatically for me in one sheet instead of me manually going in each day and keying it in. I’m getting tripped up a little bit though with crafting my API URL path. I would like it to always pull in the current month without me having to go in and key in the new month each month, if that makes sense? So I need something like [current month] where google sheets knows what month we’re in instead of me saying “{"since":"2021-10-01","until":"2021-10-31"}“. I also noticed that when I put “{"since":"2021-10-01","until":"2021-10-31"}” in my URL, that also populates in the cell. I want that to qualify the data I want, but don’t want to the date ranges pulling thru in the actual sheet…way to hide? Also, how do I get the API URL to add up the spends in the current month (assuming I have multiple campaigns running in a given month) instead of spitting out each campaigns data spend?

    Essentially need to know:
    -How can I roll up all campaign spends in a given month to give me one lump sum number
    -How to tell the URL I was the [current month] without actually qualifying what the current month is
    -How to ONLY have the lump sum spend populate in my sheet
    -How often does the data reload? Does it refresh automatically or do I have to run it daily?

    this is url I was working with if this helps: https://graph.facebook.com/v12.0/act_794801067272984/insights?fields=spend&time_increment=1&time_range={"since":"2021-10-01","until":"2021-10-31"}

    Reply
    • Hi Paige!
      -How can I roll up all campaign spends in a given month to give me one lump sum number
      https://graph.facebook.com/v12.0/act_1111111111/insights?fields=account_id,account_name,spend&date_preset=last_month
      -How to tell the URL I was the [current month] without actually qualifying what the current month is
      You can either use date presets like this_month, last_month, etc., or you can set up your dates with functions in cells, and then use those cells in your requests, e.g. https://graph.facebook.com/v12.0/act_88976240/insights?fields=account_id,account_name,spend&time_range={"since":"+++Sheet1!A1+++","until":"+++Sheet1!A2+++"}
      -How to ONLY have the lump sum spend populate in my sheet
      By default, API Connector will return whatever data comes back from the API, and Facebook sends back some “extra” fields like dates. If you only want to display certain fields, you could either pull the fields you need into a second summary sheet, or use a JMESPath expression to filter all the extra fields out. If you need help constructing that expression, feel free to send a message to support.
      -How often does the data reload? Does it refresh automatically or do I have to run it daily?
      You can refresh it manually by clicking Run or turn on scheduling to refresh automatically.
      Hope that helps clarify, just let me know if you have any other questions.

      Reply
    • I’m not really familiar with dark posts and couldn’t find any way to test (found some guides online but none of them matched the current interface / options in FB Ads Manager. Are dark posts still available?) Anyway assuming they’re a type of page post, I think you’d pull stats for them using the Facebook Page API.

      Reply
  22. Hi there! Is there also a way to pull the data from facebook lead ads directly in the sheet (name, email address, phone number,….)

    Looking forward to your reply!

    Reply
  23. Hi Ana,

    I’m looking to pull campaign data from ALL of my manages FB ad accounts. Is there a way to generate a single API URL path that would allow this? I have about 250 accounts I manage and would like reports generated for all of them in one place.

    Reply
    • Sorry, unfortunately Facebook’s API requires the account ID in the URL path, so it needs a separate URL for each report. You can probably find some tools specialized for Facebook that automate this for you though, I imagine other agencies also need bulk reporting.

      Reply

Leave a Comment

Table of Contents