Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Connector Improvement: Facebook Ad Account // Add Custom Conversions table

Answered
Pierre de Poulpiquet User

Hello,

In Facebook Ad, there is the possibility to create "Custom Conversions". See the technical details here: https://developers.facebook.com/docs/marketing-api/reference/custom-conversion/

Those conversions are referenced in the "Facebook Ad Insights" reports.

 

Ex.

From `facebook_actions` table, loaded with the prebuilt Fivetran "BASIC_ALL_LEVELS" report.

{
    "ad_id": "23845933263420180",
    "date": "2020-11-11",
    "index": "10",
    "_1_d_view": "4",
    "_28_d_click": null,
    "_fivetran_synced": "2021-01-06 08:45:26.400 UTC",
    "action_type": "offsite_conversion.custom.382628486247748",
    "value": "4.0"
  },
  {
    "ad_id": "23845933263420180",
    "date": "2020-11-11",
    "index": "11",
    "_1_d_view": "4",
    "_28_d_click": null,
    "_fivetran_synced": "2021-01-06 08:45:26.400 UTC",
    "action_type": "offsite_conversion.custom.981679075671491",
    "value": "4.0"
  },

As you can see, the end of the action_type value is an Id, this is the Id of a Custom Action.

 

In order to make great reporting, we would need to join those Ad Insights with the name of the Custom Conversion.

 

But the "Facebook Ad Account" connector doesn't seem to sync the Custom Conversion table/info.

 

Would it be possible to do so?

 

Best regards,

Please sign in to leave a comment.

Comments

6 comments

Date Votes
  • Official comment
    Lucas Alexander Fivetranner

    Hi all,

    Apologies for the long wait on this one. I'm doing a pass through the request portal to close out threads where there's already a working solution, and this is one of them.

    This is achievable using the data Fivetran already syncs. The custom conversion performance data lives in either basic_ad_actions (our prebuilt report) or a custom report's <report_name>_actions child table, depending on your setup. In both cases, custom conversions appear as rows where action_type matches offsite_conversion.custom.<id>.

    To get the human-readable conversion name, join to custom_conversion_history on that ID:

    SELECT
    a.date,
    a.ad_id,
    c.name AS custom_conversion_name,
    a.value AS conversions
FROM basic_ad_actions a
JOIN custom_conversion_history c
    ON SPLIT(a.action_type, '.')[OFFSET(2)] = CAST(c.id AS STRING)
WHERE a.action_type LIKE 'offsite_conversion.custom.%'

    (Adjust string-splitting syntax for your destination -- the above is BigQuery. Swap basic_ad_actions for your custom report's actions table if applicable.)

    For more on configuring action data, see our troubleshooting doc.

    Hope this helps anyone else who lands here. Let me know if you have questions or feel free to reach out to our support team for a fast response. 

    Cheers,
    Luke

  • Drew Fivetranner

    Hi Pierre, Drew from the Product Team here.

    We are looking into how to address this problem. I'll update here with any progress.

  • Matthew Clemente User

    Agreed - this would be really helpful for tracking the success of our campaigns too.

  • Paul Madani User

    Any update? Official answer was that you are looking into the problem 2 years ago. 

    Problem solved, expose data from this endpoint: https://developers.facebook.com/docs/marketing-api/reference/custom-conversion/

  • Giang Nguyen User

    @Matthew Clemente & @Paul Madani

    Did you figured out in the end? Or does anyone know whether performance data of custom conversions are available in Fivetran? I see that there is a a "custom_conversion_history" table but I have no idea where to find the corresponding performance data (e.g. for ad_id = "ABC", there were X amount of custom conversions on 1st of March 2023).

  • Matthew Clemente User

    @Giang Nguyen

    Never figured it out. As far as I know the data still isn't available.