Get Facebook ads transformed data

We are using stitchdata to get data from Facebook ads data to Google BigQuery warehouse. We retrieved tables like ads, ads_insights, campaigns, adcreative etc. How can I get transformed data like ad, clicks, impressions, reach, spend amount, leads count etc? Which table & fieldset can provide me these details?

I searched for this and on that basis I have collected data like impression, click, reach, lead count but if compare that data with Facebook ad manager then there is a big difference. Can anyone help to get the correct data.

+1 We have the exact same challenge - we would like to get the data like “landing_page_views” count of “offsite_pixel_leads” etc

It seems possible using the ads_insights table and using the dimension of “actions.actions_type” and one of the “actions” metrics. Yet the numbers just don’t make any sense.

I am doing the filtering with data studio.

Interestingly enough, the spend, impressions counts are accurate

(I have already removed the attribution duplicate batches)

Hello, can you share how you are even getting the spend and impressions counts to match?

An update for others who may come across this issue. the “action.actions_xxx” fields are a nested field of “actions” column. When you select the “actions” column in “tables to replicate” these nested columns will show up. As such you need to filter your desired objective out based on the “actions.actions_type”. This however will give you an inaccurate cost per action as the filter applies to the total spend amount, etc

The best solution if you are using big query is to flatten the “actions” columns in a separate table and merge just actions you want back (both the dimensions and the metrics). that way you can have roll ups that make sense and get a more accurate cost per action numbers.

Hope that helps, or at least gives you the right search terms to look for in both stitch help and big query help.

1 Like