Weld logo
facebook-ads logo
Facebook ads

Campaign Report

Creates a campaign report by integrating with Facebook Ads. It retrieves data from three tables: campaign_insight, account, and campaign. The template calculates various metrics such as clicks, impressions, spend, CPC, and CTR, and groups the results by date, account, and campaign.
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
Example of output from model:
+------------+------------+----------------+-------------+-----------------+---------------------+--------+----------------+-------------------+---------------------+--------+-------------+--------+---------+---------+
| 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.

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.