Ad-set Report
1with
2 stats as (
3 select
4 *
5 from
6 {{raw.facebook_ads.ad_set_insight}}
7 )
8 , accounts as (
9 select
10 *
11 from
12 {{raw.facebook_ads.account}}
13 )
14 , campaigns as (
15 select
16 *
17 from
18 {{raw.facebook_ads.campaign}}
19 )
20 , ad_sets as (
21 select
22 *
23 from
24 {{raw.facebook_ads.ad_set}}
25 )
26 , ads as (
27 select
28 *
29 from
30 {{raw.facebook_ads.ad}}
31 )
32select
33 cast(stats.date as date) date_day
34 , accounts.id account_id
35 , accounts.name account_name
36 , campaigns.id campaign_id
37 , campaigns.name campaign_name
38 , ad_sets.id ad_set_id
39 , ad_sets.name ad_set_name
40 , ad_sets.start_time
41 , ad_sets.end_time
42 , ad_sets.bid_strategy
43 , ad_sets.daily_budget
44 , ad_sets.budget_remaining
45 , sum(spend) spend
46 , sum(reach) reach
47 , sum(impressions) impressions
48 , sum(clicks) clicks
49 , sum(safe_divide(clicks, spend)) as cpc
50 , sum(safe_divide(clicks, impressions)) as ctr
51 , sum(safe_divide(spend, impressions)) * 1000 as cpm
52 -- Additional pass-through columns should be manually specified if needed
53from
54 stats
55 left join accounts on stats.account_id = accounts.id
56 left join ad_sets on stats.adset_id = ad_sets.id
57 left join campaigns on ad_sets.campaign_id = campaigns.id
58group by
59 1
60 , 2
61 , 3
62 , 4
63 , 5
64 , 6
65 , 7
66 , 8
67 , 9
68 , 10
69 , 11
70 , 12 -- Adjust these numbers according to the selected columns+------------+------------+--------------+-------------+----------------+------------+----------------+---------------------+---------------------+---------------+--------------+-------------------+-------+-------+-------------+-------------+-------------+
| date_day | account_id | account_name | campaign_id | campaign_name | ad_set_id | ad_set_name | start_time | end_time | bid_strategy | daily_budget | budget_remaining | spend | reach | impressions | clicks | cpc |
+------------+------------+--------------+-------------+----------------+------------+----------------+---------------------+---------------------+---------------+--------------+-------------------+-------+-------+-------------+-------------+-------------+
| 2022-01-01 | 1234567890 | Example Ads | 9876543210 | Example Campaign | 1357924680 | Example Ad Set | 2022-01-01 08:00:00 | 2022-01-01 20:00:00 | Lowest_Cost | 1000 | 500 | 50 | 1000 | 2000 | 10 | 0.2 |
+------------+------------+--------------+-------------+----------------+------------+----------------+---------------------+---------------------+---------------+--------------+-------------------+-------+-------+-------------+-------------+-------------+Retrieves insights and performance metrics from Facebook Ads data. It integrates with the Facebook Ads platform and utilizes various tables such as ad_set_insight, account, campaign, ad_set, and ad. The SQL code begins by creating temporary tables (stats, accounts, campaigns, ad_sets, ads) that contain the selected data from the corresponding Facebook Ads tables. These temporary tables serve as the basis for generating the desired report. The SELECT statement then retrieves specific columns from the temporary tables, including the date, account ID and name, campaign ID and name, ad set ID and name, start and end times, bid strategy, daily budget, budget remaining, spend, reach, impressions, clicks, cost per click (CPC), click-through rate (CTR), and cost per thousand impressions (CPM). The SQL code also includes a left join operation to combine the data from the temporary tables based on specific column relationships. The GROUP BY clause is used to group the results by the selected columns. This SQL template is useful for generating ad-set reports that provide a comprehensive overview of performance metrics for different Facebook Ads campaigns. By analyzing the data obtained from this SQL query, advertisers can gain insights into the effectiveness of their ad sets, monitor spending, evaluate campaign performance, and optimize their advertising strategies.
