When the Facebook Ads API returns an error 500, it is usually telling you that the combination of the number of metrics and rows of data being asked for is too large for them to process all at once. You may hit the limit around 1000 rows, or it could be higher or lower depending on the number of columns you are asking for.
Using a divide-and-conquer approach, you need to split up your query into smaller chunks of data and merge the results. Making a separate query for each campaign, ad set or ad usually works.
With the Analytics Edge connector for Facebook Ads, Analytics Edge provides the ability to get a listing of the Campaigns, Ad Sets and/or Ads for a specific Ad Account, Campaign or Ad Set. Using that listing as a reference, you can repeat your query for each campaign, ad set and/or ad, then merge the results together for your report.
The Analytics Edge Core Add-in for Excel can be used to automate this process, and the rest of this article describes the steps to take.
Macro 1 – Get a List to Process
Start by creating a worksheet to contain a list of the Campaigns, Ad Sets or Ads that you want to process for your report. Using the new Ads Listing wizard, you first select the Scope of your report:
- for all the ad accounts in a selected login, you would pick the Login and ‘-all-‘ for the Ad Account
- for all ads in a specific ad set, select the ad set in the Scope of Report listing
- for all the campaigns or ads in a selected ad account, you would pick the ad account in the Scope of Report listing
The select the Detail Level for your listing — if you want all the campaigns, pick Campaign. Run the report and save the results to a worksheet (Write to Current Cell). This is the list you will want to process.
In this simple example, I am listing all the campaigns in the ad account. Note that Facebook identifies all of your accounts, campaigns, ad sets, and ads by ID numbers, and there are columns in the listing for each of the ones you ask for. Since this report is listing at the ‘Campaign’ detail level, the column labeled ‘id’ is the campaign ID number. If you ran a report with a Detail Level of ‘ad’, the ‘id’ would be the ad ID number.
Note that in the query wizard, Analytics Edge shows these ID number prefixed with ‘act_’, ‘campaign_’, ‘adset_’, or ‘ad_’ so you can tell them apart (e.g. campaign_6048330043301). If you want to enter a value (or use a cell reference to a value) for the Scope selection, you would need to include the prefix. More about this later.
Macro 2 – Loop Through the List
Now that we have a list of campaign ID numbers in a worksheet. we can use a Repeat Macro to loop through the list. In this example, my listing has been written to a worksheet called ‘list’ and I want to write the results to a worksheet called ‘results’. On the first pass of the macro, I want to clear the results worksheet, so I pick those options in the wizard.
The function reads the first line of the worksheet and makes the values in each column available to the rest of the macro, but we need to look ahead a bit and understand what information is required and how it needs to be formatted.
In the query we will do, we want to get the metrics for all ads in a specific campaign, and we need to pass the campaign ID from our list to the query. This is done by referencing the column as a range name (placing the column name in square brackets, like ‘[id]’).
As mentioned previously, the query wizard requires that the ID numbers be prefixed (e.g. campaign_#######). Our current ‘id’ column does not have this prefix, so we need to use the Replace function to add it.
Now we can make our query and use the Append to Worksheet function to append the results of each query to our ‘results’ worksheet. When the macro runs, the first pass will clear the worksheet and each subsequent loop will append to the previous results. The final repeat macro looks like this:
Hopefully this delivers results below Facebook’s threshold for the acceptable ‘amount of data’. If it does not, then change your listing to get the Ad Sets in the account, and change the query to get the ads for the identified ad set (remember to change the prefix to ‘adset_’).
Now you can download all the detail you want, letting Analytics Edge do the grunt work of a divide-and-conquer solution.