Campaign Report
1with
2 stats as (
3 select
4 *
5 from
6 {{raw.facebook_ads.campaign_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 )
20select
21 cast(stats.date as date) date_day
22 , accounts.id account_id
23 , accounts.name account_name
24 , campaigns.id campaign_id
25 , campaigns.name campaign_name
26 , campaigns.start_time
27 , campaigns.status
28 , campaigns.daily_budget
29 , campaigns.lifetime_budget
30 , campaigns.budget_remaining
31 , sum(stats.clicks) as clicks
32 , sum(stats.impressions) as impressions
33 , sum(stats.spend) as spend
34 , sum(safe_divide(clicks, spend)) as cpc
35 , sum(safe_divide(clicks, impressions)) as ctr
36 , sum(safe_divide(spend, impressions)) * 1000 as cpm -- Additional pass-through columns should be manually specified if needed
37from
38 stats
39 left join accounts on stats.account_id = accounts.id
40 left join campaigns on stats.campaign_id = campaigns.id
41group by
42 1
43 , 2
44 , 3
45 , 4
46 , 5
47 , 6
48 , 7
49 , 8
50 , 9
51 , 10 -- Adjust these numbers according to the selected columns+------------+------------+----------------+-------------+-----------------+---------------------+--------+----------------+-------------------+---------------------+--------+-------------+--------+---------+---------+
| date_day | account_id | account_name | campaign_id | campaign_name | start_time | status | daily_budget | lifetime_budget | budget_remaining | clicks | impressions | spend | cpc | ctr |
+------------+------------+----------------+-------------+-----------------+---------------------+--------+----------------+-------------------+---------------------+--------+-------------+--------+---------+---------+
| 2022-01-01 | 1234567890 | Facebook Ads | 9876543210 | Campaign A | 2022-01-01 08:00:00 | Active | 100.00 | 1000.00 | 500.00 | 100 | 1000 | 50.00 | 0.50 | 0.10 |
| 2022-01-02 | 1234567890 | Facebook Ads | 9876543210 | Campaign A | 2022-01-01 08:00:00 | Active | 100.00 | 1000.00 | 500.00 | 150 | 1500 | 75.00 | 0.50 | 0.10 |
| 2022-01-03 | 1234567890 | Facebook Ads | 9876543210 | Campaign A | 2022-01-01 08:00:00 | Active | 100.00 | 1000.00 | 500.00 | 200 | 2000 | 100.00 | 0.50 | 0.10 |
+------------+------------+----------------+-------------+-----------------+---------------------+--------+----------------+-------------------+---------------------+--------+-------------+--------+---------+---------+Generate a report on Facebook Ads campaigns. It integrates with the Facebook Ads platform and utilizes three main tables: `campaign_insight`, `account`, and `campaign`. The SQL code begins by creating temporary tables `stats`, `accounts`, and `campaigns`, which contain the data from the respective tables in the Facebook Ads integration. The final select statement retrieves the desired columns from these temporary tables, including the date, account ID and name, campaign ID and name, campaign start time, status, budget details, and various aggregated metrics such as clicks, impressions, spend, CPC (cost per click), CTR (click-through rate), and CPM (cost per thousand impressions). The SQL code then performs left joins on the temporary tables to associate the relevant data. The group by clause groups the results by the selected columns. This SQL template is useful for generating campaign reports that provide insights into the performance of Facebook Ads campaigns. It allows users to analyze key metrics such as clicks, impressions, spend, and engagement rates (CPC, CTR, CPM) at a granular level, broken down by date, account, and campaign. The report can help advertisers optimize their campaigns, track budget utilization, and make data-driven decisions to improve their advertising strategies.
