Weld logo
facebook-ads logo
Facebook ads

Ad-set Report

Generates an ad-set report by integrating with Facebook Ads. It retrieves data from various tables such as ad_set_insight, account, campaign, ad_set, and ad. The template then selects specific columns and performs calculations to provide insights on metrics like spend, reach, impressions, clicks, CPC, CTR, and CPM. The final result is grouped by different dimensions such as date, account, campaign, and ad set.
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
Example of output from model:
+------------+------------+--------------+-------------+----------------+------------+----------------+---------------------+---------------------+---------------+--------------+-------------------+-------+-------+-------------+-------------+-------------+
| 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.

Ready to start modeling your own facebook-ads data?

Get started building your data warehouse with facebook-ads and 100+ more apps and databases available.

facebook-ads logo
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync and analyze your data with AI in minutes. Connect to 150+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.