Weld logo
facebook-ads logo
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 desc
Example 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.

Ready to start modeling your own facebook-ads data?

Get started building your data warehouse with facebook-ads and connect all your apps and databases.

facebook-ads logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.