How Can I Get the Number of Leads by Campaign Using the Facebook Ads Connector?

To calculate the number of leads by campaign with the Facebook Ads connector, do the following:

1. In Fivetran, set up a new custom report that includes the following:
   • Action breakdown: action_type
   • Fields: campaign_name, actions
2. When the report syncs, we create a table with the _ACTIONS suffix in your destination, such as FACEBOOK_ADS_ACTIONS. Filter this table to include the following lead-related action types:
   • lead
   • complete_registration
   • subscribe
   • submit_application
3. Run the following query to count leads by campaign, replacing the placeholder <schema_name>.<table_name> with the actual value:

   SELECT 
     campaign_name,
     COUNT(*) AS lead_count
   FROM 
     <schema_name>.<table_name>
   WHERE 
     action_type IN ('lead', 'complete_registration', 'subscribe', 'submit_application')
   GROUP BY 
     campaign_name;
   

This query returns each campaign name and the total number of leads for the specified action types.