Facebook ads
Conversion Insights
This SQL template retrieves conversion insights data from Facebook Ads, including various metrics such as spend, reach, impressions, clicks, and more. It joins data from different tables like ad_insight, ad, and ad_roas_insight_conversion_insights to provide a comprehensive analysis of ad performance.
1with
2 stats as (
3 select
4 *
5 from
6 {{raw.facebook_ads.ad_insight}}
7 )
8 , ad as (
9 select
10 *
11 from
12 {{raw.facebook_ads.ad}}
13 )
14 , action_data as (
15 select
16 * except (
17 account_id
18 , account_name
19 , account_currency
20 , campaign_id
21 , adset_id
22 )
23 from
24 {{raw.facebook_ads.ad_roas_insight_conversion_insights}}
25 )
26select
27 cast(stats.date as date) date_day
28 , stats.account_id account_id
29 , stats.account_name account_name
30 , stats.campaign_id campaign_id
31 , stats.campaign_name campaign_name
32 , stats.adset_id ad_set_id
33 , stats.adset_name ad_set_name
34 , stats.ad_id ad_id
35 , stats.ad_name ad_name
36 , bid_amount
37 , bid_type
38 , configured_domain
39 , configured_status
40 , created_time
41 , effective_status
42 , last_updated_by_app_id
43 , status
44 , updated_time
45 , spend
46 , reach
47 , impressions
48 , clicks
49 , inline_link_clicks
50 , ctr
51 , cpm
52 , cpc
53 , action_data.* except (ad_id, date)
54from
55 stats
56 left join ad on stats.ad_id = ad.id
57 left join action_data on action_data.ad_id = stats.ad_id
58 and cast(action_data.date as date) = cast(stats.date as date)
59order by
60 created_time descExample of output from model:
+------------+------------+----------------+-------------+----------------+--------------+----------------+---------+----------------+--------------+----------------+--------------+----------------+--------+-------------+------------+
| date_day | account_id | account_name | campaign_id | campaign_name | ad_set_id | ad_set_name | ad_id | ad_name | spend | reach | impressions | clicks | purchases | leads | onsite_conversions |
+------------+------------+----------------+-------------+----------------+--------------+----------------+---------+----------------+--------------+----------------+--------------+----------------+--------------+-------------+------------+
| 2022-01-01 | 1234567890 | Example Account| 987654321 | Example Campaign| 567890123 | Example Ad Set | 4567890 | Example Ad | 34567890 | 1000| 5000 | 3500 | 2000 | 5| 15 |
+------------+------------+----------------+-------------+----------------+--------------+----------------+---------+----------------+--------------+----------------+--------------+----------------+--------------+-------------+------------+"Conversion Insights" is designed to provide valuable insights from Facebook Ads data integration. It retrieves various statistics related to ad performance, such as spend, reach, impressions, clicks, and more. By joining different tables, it allows for analyzing conversion insights by date, account, campaign, ad set, and ad levels. This SQL can be useful for tracking the effectiveness of Facebook ad campaigns, optimizing bidding strategies, and understanding user actions leading to conversions.
