Search API Connector Documentation

Print

Import Facebook Ad Data to Google Sheets

Last Updated On
byAna
< Knowledge Base

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

The easiest way to get started with the Facebook Ads API is through API Connector’s built-in integration.

  1. Select Facebook Ads from the drop-down listof applications
    facebookads-application
  2. Under Authorization, click Connect to Facebook Ads
    facebookads-authorization
  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 now active.
    facebookads-authorization-connected

Part 2: Pull Data from Facebook Ads to Sheets

Now that we’re connected, let’s pull some data into Sheets.

  1. Select the /act_{account_id}/insights endpoint, which allows us to retrieve Facebook ad insights data.
    facebookads-endpoints
  2. Under Path parameters, enter your account ID. Get your account ID as described here, or you can retrieve it by running a request to the /me/adaccounts endpoint.
    facebookads-parameters
  3. Optionally select any request parameters to customize your request. If you expect many records in the response, set the limit parameter to 5000 (more info below).
    facebookads-limit
  4. Pay attention to the level parameter as it needs to be set to the lowest granularity of your request. For example, if your fields parameter includes any campaign-level fields (e.g. campaign_name), set the level parameter to 'campaign', if it further breaks data down by ad-level fields, set the level parameter to 'ad'.
    facebookads-level
  5. Choose a destination sheet, name your request, and hit Run. A moment later you’ll see the response data in your sheet.
    facebookads-response

Part 3: Handle Filtering

Use the filtering parameter to filter for specific data points or conditions.
facebookads-filtering

When you add a filter, there are eight available operators: CONTAIN, EQUAL, GREATER_THAN, IN, LESS_THAN, NOT_CONTAIN, NOT_EQUAL, and NOT_IN.

  • CONTAIN, NOT CONTAIN: filter for text strings
  • EQUAL, NOT_EQUAL: filter for exact matches (strings or numbers)
  • GREATER_THAN, LESS_THAN: filter for numerical conditions
  • IN, NOT_IN: filter for values using array syntax. For example, if your request includes the actions field, you can include a filter for specific actions like this: action_type IN ["link_click","post_reaction"]
    facebookads-filters

Part 4: Handle Actions

"Actions" (aka results or conversions) may include purchases, leads, link clicks, outbound clicks, post engagement, video views, etc. These actions are retrieved by including fields=actions in your query.

The Facebook API response combines all actions into a single field, which is usually inconvenient for reporting. Therefore, API Connector automatically flattens each action into its own column using a configuration option located at Output options > More options > Flatten field to header
facebookads-flatten

By default, this setting will be automatically applied to all requests using the Facebook Ads OAuth connection. It can be manually selected for requests that connect with a token.

For more information on flattening fields, please see this article: Flatten Fields to Columns

Part 5: Create a Custom Request

Alternatively, you can run your own custom requests instead of using API Connector’s pre-built integration, using any of the endpoints and parameters shown in the Facebook API documentation. To create a custom request, add your complete URL into the Request URL field, and choose Facebook Ads from the OAuth menu (or connect with a token).

  • You can use this convenient request builder tool to easily construct custom API URLs.
  • To convert from a preset request to a custom API URL, tick the Add request URL box before running your preset request (under Output options). This will print out your complete API URL, which you can then copy/paste into the request URL field.

Part 6: 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, set the limit parameter to 5000.

  2. If you need more records after that, you can loop through them using pagination handling (paid feature).
    • Pagination type: cursor
    • Next token parameter: after
    • Next token path: paging.cursors.after
    • Run until: choose when to stop running the request

      facebook-pagination-cursor

Part 7: Error Messages

Please reduce the amount of data you’re asking for, then retry your request

This 500 error comes from Facebook's servers and is related to how much processing time Facebook requires to run the request. That means it’s related both to the amount of data requested, and the size of the underlying dataset that needs to be queried.

To avoid this error, try any or all of the following:

  • reduce the time range until the request succeeds
  • reduce the limit, e.g. to 300 instead of 5000
  • split up requests into smaller blocks, so that some metrics are requested in one request and some in another
  • if possible, fetch data at a higher level, e.g. by campaign instead of ad

If none of these suggestions help, an advanced but effective technique is to create a custom request that queries the /ads endpoint instead of the /insights endpoint, and then uses Facebook's "field expansion" functionality to fetch the associated insights data. Here's an example working request: https://graph.facebook.com/v15.0/act_11111111111111/ads?fields=name,id,campaign.fields(name),adset.fields(name),insights.fields(impressions,spend,reach,actions).date_preset(this_year).time_increment(1)&filtering=[{"field":"ad.effective_status","operator":"IN","value":['ACTIVE']}]

#100 Missing permissions, type: OAuthException

This error generally means that the Facebook Ads account ID has been entered incorrectly, or that you don't have access to it. Please double-check it against the account ID in Facebook Ads Manager. If you are entering a custom request URL, make sure that it is prefaced by act_, e.g. https://graph.facebook.com/v15.0/act_1111111/insights

Part 8: API Documentation & Resources

Appendix: Connect with a Facebook API User Access Token

This section is provided as a totally free 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 to choose an app type. Select Business and click Next.
    fb-ads-app-type
  4. 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
  5. 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
  6. On the left sidebar, you'll now see Marketing API listed under Products. Click Tools.
    fb-ads-img6
  7. 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
  8. 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
  9. Now that you have your token, you can use it by appending access_token=YOUR_ACCESS_TOKEN to any custom API URL, e.g. https://graph.facebook.com/v15.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 EventTemple Data to Google Sheets
Next Import Facebook Leads to Google Sheets

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

  1. Is there a way how to get the daily breakout of performance and show the corresponding day for the data in each row? My request is working however, I am not able to pull in the specific date of each row (ad performance for a specific day within the time range), I only have date_start, date_stop (the first and last days of the whole time period).

    Thanks!

    Reply

  3. Great article! Would it be unwise to use this to pull in info from two different fb users? I'm thinking it might not be a good idea to show facebook a link between the two separate users/accounts so they don't suspect something fishy is going on? Could they know two APIs are linked to the same Google sheet??

    Reply

    • I don't really have any idea what Facebook is looking at, or if they even can tell that the API is being pulled from a Google Sheet, but I would be very surprised if this is an issue. Many agencies & companies work with multiple brands and accounts, so it's totally normal to bring data from separate FB accounts into the same sheet.

      Reply

  4. Ana I need your helpppp Please
    I want to have a sheet for every single campaign on fb ads, but i can't because it writes me all the campaigns in a single sheet. Secondly I really need the conversions number and conversion cost but i really can't add them. Thank you very very much for this work.

    Reply

    • Here I am! 😀 If you want to get campaign data into individual sheets, you should create multiple requests, each one filtering for the campaign ID. Like this:
      https://graph.facebook.com/v6.0/act_11111111111111/insights?date_preset=last_14d&time_increment=14&level=ad&fields=account_id,account_name,campaign_id,campaign_name,adset_id,adset_name,ad_id,ad_name,account_currency,actions&access_token=XXXXXXXX&filtering=[{field: "campaign.id",operator:"IN", value: ['23842750602790045']},{field: "action_type",operator:"IN", value: ['offsite_conversion.fb_pixel_purchase']}]
      You can get all your campaign IDs by first running the above request without the filtering parameter.
      The "actions" field shows all conversions. The example above shows the actions field filtered for just purchase conversions.

      Reply

  5. Hello dear Ana, Tahnk you so much of this Topic that helped me so much !
    I'm wondering if you have any idea about the API Url to use to generate a report that includes the spending per day per campaign?
    Also, I'd like to know if there is a way to automate for a daily update and keep the previous data stored (yesterday's for example)

    Thanks you in advance

    Reply

  6. Hi Ana and great site! I've been playing around the API Connector and it's been working great but when I tried to add any information about ad sets, ad ids, etc, it seems to completely ignore it. Much appreciated!
    https://graph.facebook.com/v7.0/act_id/insights?date_preset=lifetime&fields=reach,clicks,adset_id,adset_name,ad_id,ad_name,spend&time_increment=1&access_token=

    Reply

    • Thank you! Your request is missing the parameter &level=ad, so that's why it won't work. You'll need to include that parameter if you're pulling ad-level metrics like ad ID and name.

      Reply

  7. Hello Ana, I have joined the family for a week now, so far has been so great! Thanks!
    However, there's one thing about this Facebook api bugs me. The time zone is always in GT-8 time zone, and I'm in Auckland (GT+12), how can I convert the time(data » date_start) and date to my local time?

    Thanks!

    Reply

    • Hi James! I'm glad you like API Connector 🙂 It's rather hidden, but Facebook's documentation says "We report ad insights data in the ad account's timezone." Therefore, please check the timezone your ad account is set to. You should be able to view that at this URL: https://www.facebook.com/ads/manager/account_settings/information.
      The default is GMT-8 (Pacific time), so this is probably the reason you're seeing that time zone from the API. If you have admin access to the FB Ads account, you can change that value to your local time.

      Reply

  8. Hi Ana,

    Thank you so much for this documentation! It really helps.

    I just have one thing to raise: Is this realtime data that gets displayed on Sheets or is there an auto update? If so, how often does the update happen?

    Thank you so much again!

    Reply

    • Hi Alex! Thank you for the comment, I'm glad you like the article.
      The data is up to date at the moment you pull it, and you can run the request manually at any time to get newly refreshed data. Alternately you can set up scheduling (paid feature) to run the requests automatically on a schedule. Please let me know if that answers your question.

      Reply

  9. hi there, I followed your guide and the integration was super cool and worked fine for the last couple months. Today, however, I keep getting server error 400 when I send a new API. What could that be?

    Reply

    • Hi there, a 400 error generally means there's an error with how you've entered the request (info). Since the error just started, it's likely related to an expired token. If you click 'show response' in the footer of API Connector, you should be able to see the exact error message from Facebook.

      Reply

  10. Hi Anna,

    thanks so much for the detailed explanation. I have one issue, which I can't solve with google.
    The format of numbers is very weird.

    Example:
    CTR in FB-ads-manager is 5,76%. In Google Sheets it's 5.781.521
    Costs in FB-ads-manager is 78,37€. In Google Sheets it's 78.37

    Language of sheets is german.
    I already tried to change the format, but nothing helped.

    Do you know the solution to this?

    Thanks!
    Niklas

    Reply

  12. Hi Ana,

    I tried this 2 weeks ago and was getting each campaign in a separate row, however now it's not working and I am only getting all ads data in one single row which is no good.

    Thank you!

    Reply

    • Hey Amy, it sounds like you may have inadvertently switched your report style back to the default single-row style.
      Under Output Settings, please click Output options > Report style: compact to see if that resolves the issue.

      Reply

  13. Hi Ana,

    This is really a great article, which can make Facebook Ads numbers appear on Google Sheet, and it really helped me a lot in my work. I have a question about connecting accounts, can I extract campaign's data of all accounts in the my Facebook Business Manager through one API request? I hope that campaign spending of all accounts in one Business Manager can be presented on the same sheet. The request URL in this article seems to be designed for a single Ads account using. Was wondering whether you can help me to adjust the requested URL?

    Thank you very much!

    Reply

    • Thanks, Murphy, I appreciate the nice comment! Unfortunately you can't pull data from multiple accounts at the same time, as the Facebook API only allows for a single account ID. You can either duplicate your requests, substitute in different account IDs for each request, and then aggregate them together into a single sheet with a function like QUERY(), or use the multi-query function to list out all your URLs and run through them in one go.

      Reply

      • Hi Ana, thanks a lot for this great tutorial. In that particular case - pulling data from multiple ad accounts - will it be necessary to generate one access token for each request, every 2-3 months, or only one access token is needed for my App?

      • Hi Patrice, you only need one access token. It's tied to the user, so it will provide access to all the accounts that the user -- you -- has access to.

  14. Hi Ana,

    Thanks for the post. Really helpful.

    I am looking to pull inlcude paused campaigns in my data pull. Do you have an idea on how I can acheive this?

    Reply

    • Nice, I'm glad it's helpful 🙂 All campaign types including paused campaigns should be included by default. Are you seeing something different?

      Reply

  15. I've been looking for something like this product for *soooooooo long*
    I'm sooo happy. really.

    and this tutorial is amazing. and it's working.

    what can I say? You got yourself a loyal customer.
    keep rockin'

    Reply

    • Oh, this is a really nice comment, thank you! I'm interested to know whether you were looking for a tool to get FB data specifically, or just for an API connection tool in general.. sent you a message 🙂

      Reply

    • Conversion actions like installs are shown under the "actions" field, so you need to add "actions" to your list of fields and include a filter for the action you're interested in (if you're not sure of the action name, then first pull all the actions and add your filter after).

      Reply

  18. Nice Article, Ana!

    But i am getting the below error.

    {"error":{"message":"Invalid OAuth access token.","type":"OAuthException","code":190,"fbtrace_id":"AsnC2jHPNAav8JXp_7090vx"}}

    Can you pls help me in understanding this?

    Reply

  19. Hi Ana,

    Thank you for this insightful documentation! This really helped with my weekly reporting for the company i work at.

    However, i have trouble to get my conversion data into sheets. We have 3 different custom conversions. I used the template from this blog and tried to change the conversion type, but it doesnt show the right conversions.Right now it only shows purchases, but we don't use this goal.

    Could you please help me out? Every other metric comes out fine, i only have problems with the conversions. Thanks in advance and i hope to hear from you soon!

    -Jael

    Reply

    • Hi Jael, you should be able to substitute in the conversion you're interested in. The entire request would then be something like this:
      https://graph.facebook.com/v8.0/act_YOUR_ACCOUNT_ID/insights?date_preset=last_7d&fields=campaign_name,impressions,reach,clicks,spend,actions&level=campaign&filtering=[{field: "action_type",operator:"IN", value: ['offsite_conversion.YOUR_CONVERSION']}]&access_token=YOUR_ACCESS_TOKEN

      Reply

      • Hi Ana,

        Thanks for the fast and extensive response! The thing i dont understand is what to fill in at 'offsite_conversion.YOUR_CONVERSION. Should i replace 'YOUR_CONVERSION' with the custom conversion id? Or should it be the name of the conversion? For example the name of the conversion is 'Registration bootcamp'.

        Thanks again, i hope my explenation is clear!

        - Jael

      • You should replace it with your custom conversion ID. To find your ID, you can 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. So then you would substitute in that value, like offsite_conversion.custom.398028550584411

        If you don't have immediate access to FB Ads Manager, an alternate method is to run this request:
        https://graph.facebook.com/v8.0/act_1111111111111/insights?fields=actions&access_token=+++'Tokens'!B1+++
        It will return a list of all your conversions and custom conversion IDs, but it won't include their name. Still, it could work if you only have one or two custom conversions and are able to identify them.

      • Hi Ana,

        Thank you so much, it worked :D. This is amazing. I used the custom conversion id from FB Business manager.

        I have one more question, we have a total of 3 custom conversions, how should the request look like if i want to include all 3 of them?

        Thanks again!

        - Jael

      • woohoo, I'm glad it worked! So, you have two options here:
        1) Run them separately. I think this might be best because the output can get complicated when you're looking at multiple conversions in a single response.
        2) Run them together. In that case your query would look like this:
        &filtering=[{field: "action_type",operator:"IN", "value":["offsite_conversion.custom.398028550584411","offsite_conversion.custom.123456789","offsite_conversion.custom.987654321"]}]

        You can try the second one and see what you think, and if the output is too complicated try the first one 🙂

  20. When we create the token manually and copy it for use in the API Connector, where exactly do we put it in the API Connector? I've tried a few things and they haven't worked.

    Reply

    • Once you have your token, you can use it by appending it to any API URL, like https://graph.facebook.com/v8.0/act_123456789012345/insights?access_token=YOUR_ACCESS_TOKEN.

      Reply

    • Thanks, I'm glad you like the article. You would need to run your query twice, once with date_preset=last_3d and once with date_preset=last_7d. That will pull in the raw data to your sheet, and you can then reconfigure it however you like to make it match your desired dashboard output.

      Reply

  22. Hi Ana,

    Thank you very much for the article, but I stumble on a problem, can you please point me where i did wrong? By the way, I am not a coder, so it would be very helpful if you explain it in simple terms. Thank you so much 🙂

    I try to add "yesterday" date to the fields, and i use this URL https://graph.facebook.com/v8.0/act_xxxxxxxxxxxxxxxxx/insights?date_preset=yesterday&fields=date_presets{yesterday},spend,reach,impressions,click

    Reply

    • Hi Adya, thanks for the message. Date presets aren't added into the "fields" parameter. Please try the following instead:
      https://graph.facebook.com/v9.0/act_xxxxxxxxxxxxxxxx/insights?date_preset=yesterday&fields=spend,reach,impressions,clicks

      Reply

  23. Hi there,

    I'm trying to export the time of day (viewer's time zone) as a breakdown but without success. Can you please let me know how to do that?

    Reply

    • Hi Francesco, please try this:
      https://graph.facebook.com/v9.0/act_1111111111111/insights?breakdowns=["hourly_stats_aggregated_by_audience_time_zone"]

      Reply

  24. Hi Ana, I need to extract the spend from every day ever since the ad account has been created, and it should be updated once a day moving forward. And unfortunately, I am stuck.

    https://graph.facebook.com/v8.0/act_xxxxxxxxxxxxxxxx/insights?fields=spend/&time_increment=all_days

    Reply

    • You should be able to get historical data with a query like this:
      https://graph.facebook.com/v9.0/act_1111111111111111111/insights?date_preset=lifetime&fields=spend&time_increment=1&limit=5000
      Going forward you can just switch your date_preset value to 'yesterday' and run it each day.

      Reply

    • Hi Milica! It is possible, but since Facebook is giving you the "Please reduce the amount of data you're asking for" error message, you'll need to split up your request into smaller blocks, like with a date range filter for each request.
      Another method would be to set the limit to something smaller (e.g. limit=500) and then set up pagination handling as described in the article.
      Can you please try and see how it goes?

      Reply

    • You can add a filter parameter to your URL like this:
      &filtering=[{field: "campaign.name",operator:"CONTAIN", value: 'your_campaign'}]
      Substitute in your own campaign name where it says your_campaign.

      Reply

      • Their documentation is very unclear about this, but in my own tests I can only get the CONTAIN operator to work on campaigns. So I think you can only do this if you find a string that both of your campaigns contain.

  27. Hi, I'm Facebook Ads Marketer. I have a question:

    Can I get Data Bill Payment account or invoice payment?

    Because now I used "Sum amount spend" but it not good for charge fee with customer. I wanna get data payment in bill transaction to know exactly Facebook charge fee me and notify customers when the deposit is almost exhausted.

    Reply

    • Hi there, this page shows how to get invoices from the Facebook API. It says you would send a URL like this:
      https://graph.facebook.com/v10.0/Business_ID/business_invoices?start_date=2017-01-01&end_date=2017-04-01

      However it's not that easy, since the page also says your account needs a finance role and your connection requires business_management scope, which is not provided by API Connector's built-in Facebook Ads connection. So you would need to create your own app at https://developers.facebook.com/, and then add the permissions needed for the invoices endpoint. Hope that helps point you in the right direction.

      Reply

  28. Hi Ana,

    your API still uses version 8.0 as far as I know.
    I just received a mail from facebook that apps using version 8.0 are not supported after May 2021.
    Do you have any plans to upgrade to version 9.0 or 10.0?

    Thanks for the API! It helps me a lot 🙂

    Thanks,
    Niklas

    Reply

    • Yeah, if your client has provided your account with access to their FB Ads account, you can access their data via FB Ads Manager or the API.

      Reply

  30. 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

  31. 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

  32. 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

  33. 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?

    Reply

    • Hey Ken, thanks for the comment. Yeah, Facebook doesn't send back fields at all when their value is zero. Please click Edit Fields to map your fields to columns, that way it won't matter what gets sent back.

      Reply

  34. 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.

    Can you help me with that?

    Reply

    • 1) You can use the filtering parameter 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.

      Reply

  35. 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 likely means it's not recognizing your limit parameter. Please double-check that you've added it correctly.

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

  36. 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

  37. 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 !

  38. 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"}}

    Thank you!

    Reply

  39. 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. If you only want to display certain fields, you could either pull the fields you need into a second summary sheet, or use the field editor to filter all the extra fields out.
      -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

  40. 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

  41. Oh man, this might just be a lifesaver; I've been trying to build my own FB Ads connector in Data Studio and I think this covers all the pain points I was stuck on. Thank you, thank you, thank you!

    Reply

  42. Hi Ana,
    Yesterday I made my connection with Facebook and it worked. But today I see this error:

    {"error":{"message":"(#200) Provide valid app ID","type":"OAuthException","code":200,"fbtrace_id":"...

    What should I do?
    Thanks a lot

    Reply

  44. When I add the ad title

    1) 2: Completed with errors
    - We received an error from facebook.com (500) show response
    {"error":{"code":1,"message":"An unknown error occurred","error_subcode":99}}

    https://graph.facebook.com/v12.0/act_248762389092142/insights?fields=account_name,campaign_name,adset_name,ad_name,spend,impressions,clicks,ctr,actions,action_values,conversion_values,cost_per_inline_link_click,cpc,cpm,inline_link_click_ctr,inline_link_clicks,reach,purchase_roas,cost_per_action_type&limit=5000&time_increment=1&date_preset=yesterday&level=ad

    Reply

    • Facebook doesn’t clearly define this error message but it seems to be associated with requesting too much data at once. It also tends to be an intermittent error, so you may be able to resolve it just by waiting and trying again, otherwise you’ll need to request less data from their server.

      Reply

  45. is there a limit with the api connector results? im trying to do last_30d or last_7d but the no. of rows remain only 26 rows. it doesn't show me per day until today. how do i fix this limitation?

    Reply

  46. Thank you Ana. Appreciate your great work! One last thing, when I use last_3d or last_7d is there a tweak to include today's date as well? I noticed that it means 3/7days excluding today.

    Reply

    • You're welcome, thank you for reading 🙂 You can see all the time parameters Facebook allows here (check the section called date_preset). I see some presets that might work for you, e.g. this_week_sun_today. Alternatively, you can pull in exactly the time period you want by using time range parameters instead of date presets, e.g. &time_range={"since":"2022-02-14","until":"2022-02-16"}

      Reply

  47. Hi Ana, appreciate your help for the above. It is working great now. Thank you so much! One more favor, do you think you have a similar article for importing google adwords insights?

    Reply

    • Looks like you can do a request URL like https://graph.facebook.com/v13.0/12345/?fields=account_id,campaign,name,adset_schedule,budget_remaining, where you replace 12345 with your adset ID. Please check if that works for you.

      Reply

  50. Is it possible to get results for date_preset (e.g. last_7d) along with previous period data?
    maybe I have to add something to the following request in order to get the previous period results?

    https://graph.facebook.com/v13.0/act_123456789012345/insights?fields=reach,impressions,clicks,spend&date_preset=last_7d

    Reply

    • You can set your date_preset to last_14d, or run two requests where each one uses a different time_range parameter.

      Reply

    • Sorry, we don't support async requests, you need to receive the data when you make the request.
      If the issue is that your request is timing out, could you try reducing the time range or number of metrics?

      Reply

  52. Hi!

    is there a way to get only certain data? like on our ads, we name it if it is for "creative testing", or "audience testing", etc. We only want to get the creative testing ads, can you suggest a way to filter them out during the picking up of data?

    TYIA

    Reply

    • Yep, you can use the filtering parameter and enter your condition there. It sounds like you'll want to select something like ad.name CONTAIN testing.

      Reply

  53. Hi there,
    Is there a method by which to add the ad creative image URL? In supermetrics this is possible, so just want to get a better understanding how this can be achieved using the Facebook graph API?
    Many thanks,
    Francois

    Reply

    • Sure, you can access any of the parameters provided by the FB Ad Creatives API (reference). Something like this should work: https://graph.facebook.com/v14.0/act_1111111/adcreatives?fields=account_id,id, thumbnail_url, image_url,title,name&limit=500
      If you need to combine it with insights metrics you can include them like this: https://graph.facebook.com/v14.0/act_1111111/ads?fields=adcreatives{account_id,id, thumbnail_url, image_url,title,name},insights{spend,clicks,impressions,reach},id,name,campaign{id,name,account_id,objective},adset{id,name},created_time&limit=500

      Reply

  54. What can I do about this?

    -we received an error from facebook.com (400)

    {"error":{"message":"(#100) param filtering must be an array.","type":"OAuthException","code":100,"fbtrace_id":"AXSvNMncT8wqWwX7M0UEp_A"}}

    Reply

    • Facebook is saying that there's an error in the filtering parameter. Can you please share some info on how you filled it out or what you're filtering for? If you're using an IN operator, make sure the value is an array, e.g. ["link_click","post_reaction"]

      Reply

      • My Values are not correct. It is impossible to have a clickrate of 2.966.507 xD. What am i Doing wrong?

      • Sounds like a locale issue. Facebook sends back data in US format (comma separators) but your sheet may be set to a format with period separators. You can resolve it by changing your sheet to US locale or through a formula that swaps periods and commas, e.g. =substitute(substitute(substitute(A1;",";"#");".";",");"#";".")

  55. I just want to see clicks, thruPlays, impressions, clickrate, spend and cost per klick for each campagin. What ever i do is wrong 🙁

    Plsease Help 🙁

    Reply

    • You should be able to just select those fields, set a date preset, choose level= campaign, and hit Run. If you’d like more specific help please contact support with some details about your current setup.

      Reply

      • Open the fields parameter and select your fields (campaign_name, clicks, spend, impressions), open the level parameter and select campaign, and click Run.

  56. I am trying to pull the conversions and their values but when I do so in the fields it doesn't format properly. This is the code I have been using "act_1257024958085460/insights?date_preset=last_7d&fields=campaign_name,impressions,reach,clicks,spend,conversions". I need to find a way of flattening the nested results so they show up in my google sheet properly. Any suggestions?

    Reply

    • Can you please check the flatten fields function? Usually you can fix this kind of data structure issue through that function, otherwise JMESPath may be another option. I can't really say anything specific without knowing what kind of data response you're seeing, so please feel free to contact support with a sample of your JSON, then I can take a look and give you more specific suggestions.

      Reply

    • Yeah, I think you can get that with the adsets endpoint (info).
      So an example URL request would be https://graph.facebook.com/v14.0/act_111111111/adsets?fields=name,daily_budget,budget_remaining

      Reply

      • Another problem I have is I keep getting the 500 error and I believe it is due to having to large of a request. I have tried using the pagination "cursor" "after" "paging.cursors.after" "no data returned" but has not solved my issue. Is there something else I have missed or something you recommend trying?

      • Yep, Facebook returns a 500 error when the data set requested is too large for their servers to process at once. I don't know the exact limits on their side, but from what I've seen it's not just about the size of the set you request, but also the size of the underlying data set, presumably because extracting a small set of data from a large data table still requires more processing power than extracting a data set of the same size from a small data table.
        Applying pagination won't necessarily help with this error, instead you need to reduce the size of your request. Usually the best way to do this is to reduce your date range. Can you please try that?

      • This is very helpful thank you! It does work when I decrease the date range. Sadly I need a bigger date range than it allows. Thank you for all your help

  58. Hi Ana, API connector paid version is the best. Helps me daily. I just wanted your help with regards to pulling "results" (number of conversions). Not quite sure how to use the action field. Can you please modify my URL.

    https://graph.facebook.com/v13.0/act_428128674838566/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_range=%7B%22since%22%3A%222022-09-01%22%2C%22until%22%3A%222022-09-12%22%7D&level=ad&date_preset=last_3d&limit=5000

    Reply

    • Thank you Ana. Would do it that way then. Just additional requests though.

      Can you point me in the right direction to extract data in the following:

      1. extract results of clicks, impressions in daily basis
      2. is it possible to pull platform and placement data on either campaign or ad level?

      Reply

      • 1. extract results of clicks, impressions in daily basis
        You can set date_preset to yesterday, and fields to clicks and impressions. Run the request in Append mode to add the prior day's data to the end of the existing data set.

        2. is it possible to pull platform and placement data on either campaign or ad level?
        Sure, under fields you can pull in campaign_name or ad_name, and set the level to campaign or ad, depending on how you want to break it down.

  59. Hello, I have a problem , which is "offsite_conversion.custom" randamly showed column order.
    sometimes it shows columm C today and another day it shows in columm J.

    How can I fix this ?

    Reply

  60. Above one.

    https://graph.facebook.com/v11.0/act_923932687767438/insights?fields=campaign_name,actions,impressions,unique_clicks,cpm,cpc,conversions,spend&time_increment=1&date_preset=yesterday&limit=10000&filtering=[{field: "action_type",operator:"IN", value: ['offsite_conversion.custom.1108288433445008']}]&level=campaign

    Reply

  61. Hi Ana! Impressive stuff, and exactly what I've been looking for for our agency! We'll be adding this as a standard for every client, but can you answer three questions please?

    1) About the "purchase" Action --> The standard "action" field is great and all the data is correct. For example, I love to see the data.adset_name (so I know which Ad group it is), the data.action_values.1.action_type (so I know it was the purchase event) and the data.action_values.1.value (so I know what the euro amount of the purchase was. This is all perfect, but I would also need one more thing: To see parameters I send with the purchase event. E.G.: I send "content_name" with every purchase, so I can 100% identify each purchase. Is there a way to display the content_name parameter also?

    2) follow up on question 1): As I said, I see the data.action_values.1.action_type for every ad set, but it is always only offsite_conversion.fb_pixel_purchase (the only standard event I am using). Why are there no custom events displayed? How do I make them visible? And is it for them also possible to send the parameters (I again send a "content_name" with every custom event).

    3) Is there a way to automatically export all the data for every month ongoing? I would like to create an automatic, monthly data export of the whole sheet.

    Reply

    • What I forgot to say regarding question 1) was: is there also a way to look at single purchases? Because right now I only have the option to look at the summed up purchases. Is it possible to filter out and list every single purchase?

      Reply

      • As far as I know the Facebook Ads API only provides aggregate data, not data on single purchases. Normally I'd use a different tool like Google Analytics to get the line-item level reporting data.

    • These are great questions, but unfortunately I don't really have great answers:
      1) I don't believe Facebook returns custom parameters through this API (you can see the full list of available fields here). It seems possible they provide them through a different API, but I couldn't find anything definitive on this. I'll investigate this more and update if I find anything.
      2) Custom events should be automatically displayed through the actions field. You can see the full list of actions fields on this page, and it does include custom events. I wonder if you may have filtered them out, can you please click Edit Fields > Reset All?
      3) Sure, you can set your request to run on a schedule.

      Reply

      • Thanks for the reply Ana!
        Mhm yes it's very odd, I've seen it working in a Google sheet, but I just can't find a way to export single purchases with parameters automatically. Would be amazing, because you could match every single website sale (through the purchase number) to a certain Facebook ad set or even ad. Any other ideas how i could achieve that?

        We'll gonna be using the API anyway for different purposes 😉

        best,
        Sebastian

      • The closest I see is to add the parameter breakdown=product_id. I don't see anything about breaking insights data down by individual transactions, though, whether in the API or in the FB Ads Manager interface. So unless I'm missing something I don't think this is possible through Facebook. You can do this with web analytics tools like Google Analytics for sure though.

  62. Hi Ana,
    Thank you again for your thoughts and answers, it was already helpful! I'm a step closer now, maybe you also have some input on the following things. This is what I know about the script:

    A) Transaction data gets extracted every hour (I don't see that option in the API, is it possible?)
    B) Transaction data gets marked with a UNIX timestamp (time of purchase event) & the value of the purchase

    So my questions are:

    1) Is there a way to export data in shorter timeframes than one day? E.g. 1 hour or even less?
    2) Is there a way to include the UNIX timestamp of the time of the purchase to the export?
    3) Is there a script where I can tell the API to automatically export the data into a Google Sheet? (like every hour or so)

    Really any help to any of the questions would be much appreciated, since I'm going forward in small steps

    Reply

    • Hey Sebastian, please see below:
      1) Is there a way to export data in shorter timeframes than one day? E.g. 1 hour or even less?
      The time_range and date_preset parameters only accept inputs with granularity of 1 day at a minimum, so you always need to fetch at least a day's data. However, you can break that day down by hour with the breakdowns parameter.
      2) Is there a way to include the UNIX timestamp of the time of the purchase to the export?
      As far as I know, Facebook doesn't provide this type of granular data, they only return aggregated data that shows an overview of ad performance rather than individually timestamped actions.
      3) Is there a script where I can tell the API to automatically export the data into a Google Sheet? (like every hour or so)
      Sure, you can use scheduling to automatically refresh the data on a schedule.

      Reply

  63. Hi Ana,
    I am trying to get video views 50% and video views 100% for my ads but it doesn't appear as an available field on the FB ads Application. Is this something I need to do as a custom pull or am I missing something on the application?

    Reply

    • Facebook considers video views to be a type of action, so you should be able to get video views by selecting the "actions" field. Please check if that gets you what you're looking for.

      Reply

Leave a Reply to Ana Cancel reply

Jump To...