Source Facebook Marketing: missing data in columns 'actions' and 'cost_per_action_type' at stream ads_insights

Connector Name

facebook-marketing

Connector Version

1.1.17

What step the error happened?

Other

Relevant information

I noticed some inconsistencies with Facebook Marketing data, specially in one column in the table ads_insights: the ‘actions’ table. I’m pretty familiar with the Facebook Ads Graph API, and our company uses it every single day so we are very used to it. While making some queries now in the data extracted using Airbyte we found that some actions are not being included in the array, actions with action_type “link_click” or “post_engagement” do return as expected and the values are ok too, but when it comes to action_types like “purchase” or “initiate_checkout” are not being returned. So you might be thinking now: maybe these are not supposed to appear inside the actions column, right? Wrong. When I make a request in the Graph API, to the exact same ad, in the same level (ad level) in the same time period, it returns these action_type inside the actions array.

While investigating the issue with my engineer team we ended up finding out what is causing this error for your Facebook Marketing Connector users. The error happens because data is requested from the Facebook API through Ad Report Run, which does not return detailed data when the time_increment field is equal to 1. Instead it just returns: “action_type”: “offsite_conversion.fb_pixel_custom ” which aggregates the value of all the pixel data, making it impossible to know what the value refers to. The solution would be to use the synchronous form of the request with the GET method on the same endpoint, instead of the POST and Ad Reports method.

We further realized there is a problem with the column 'cost_per_action_type' which is always null even though there were data in the 'actions' column. (By logic if one of them are not null the other should not be either).

Here is the data returned from Facebook Graph API:

}
    "ad_id": "23853379486750288",
    "ad_name": "[AD1] Um só lugar - ON",
    "actions": [
        {
            "action_type": "onsite_conversion.post_save",
            "value": "1"
        },
        {
            "action_type": "post",
            "value": "2"
        },
        {
            "action_type": "omni_initiated_checkout",
            "value": "1"
        },
        {
            "action_type": "page_engagement",
            "value": "27"
        },
        {
            "action_type": "purchase",
            "value": "1"
        },
        {
            "action_type": "onsite_web_app_purchase",
            "value": "1"
        },
        {
            "action_type": "post_engagement",
            "value": "27"
        },
        {
            "action_type": "onsite_web_purchase",
            "value": "1"
        },
        {
            "action_type": "landing_page_view",
            "value": "18"
        },
        {
            "action_type": "post_reaction",
            "value": "3"
        },
        {
            "action_type": "offsite_conversion.fb_pixel_custom",
            "value": "4"
        },
        {
            "action_type": "offsite_conversion.fb_pixel_initiate_checkout",
            "value": "1"
        },
        {
            "action_type": "offsite_conversion.fb_pixel_purchase",
            "value": "1"
        },
        {
            "action_type": "initiate_checkout",
            "value": "1"
        },
        {
            "action_type": "link_click",
            "value": "21"
        },
        {
            "action_type": "omni_purchase",
            "value": "1"
        }
    ],
    "cost_per_action_type": [
        {
            "action_type": "onsite_conversion.post_save",
            "value": "21.46"
        },
        {
            "action_type": "omni_purchase",
            "value": "21.46"
        },
        {
            "action_type": "initiate_checkout",
            "value": "21.46"
        },
        {
            "action_type": "offsite_conversion.fb_pixel_custom",
            "value": "5.365"
        },
        {
            "action_type": "post_engagement",
            "value": "0.794815"
        },
        {
            "action_type": "purchase",
            "value": "21.46"
        },
        {
            "action_type": "page_engagement",
            "value": "0.794815"
        },
        {
            "action_type": "onsite_web_app_purchase",
            "value": "21.46"
        },
        {
            "action_type": "link_click",
            "value": "1.021905"
        },
        {
            "action_type": "landing_page_view",
            "value": "1.192222"
        },
        {
            "action_type": "onsite_web_purchase",
            "value": "21.46"
        },
        {
            "action_type": "omni_initiated_checkout",
            "value": "21.46"
        }
    ],
    "date_start": "2023-09-17",
    "date_stop": "2023-09-17"
}

and here is the data returned from BigQuery (using Airbyte Facebook Marketing connector as its source):

SQL Query:

  SELECT
    ad_id,
    ad_name,
    actions,
    cost_per_action_type,
    date_start
  FROM
    metrics_testing_prod.FA_ads_insights
  WHERE
    ad_name = '[AD1] Um só lugar - ON'
    AND date_start = '2023-09-17'

All the requests are for the same period and level. (ad level and september 2023)

Relevant log output

No response

Contribute

[X] Yes, I want to contribute

airbytehq / airbyte