Weld logo
facebook-ads logo
Facebook ads

Ad Report

Integrates with Facebook Ads and retrieves data from various tables such as ad_insight, account, campaign, ad_set, and ad. It selects specific columns from these tables and performs calculations to generate aggregated metrics such as spend, reach, impressions, clicks, CPC, CTR, and CPM. The template then joins these tables based on specific conditions and groups the results by certain columns.
1with
2    stats as (
3        select
4            *
5        from
6            {{raw.facebook_ads.ad_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  , ads.id ad_id
41  , ads.name ad_name
42  , sum(spend) spend
43  , sum(reach) reach
44  , sum(impressions) impressions
45  , sum(clicks) clicks
46  , sum(safe_divide(clicks, spend)) as cpc
47  , sum(safe_divide(clicks, impressions)) as ctr
48  , sum(safe_divide(spend, impressions)) * 1000 as cpm
49    -- Additional columns should be manually specified if needed
50from
51    stats
52    left join accounts on stats.account_id = accounts.id
53    left join ads on stats.ad_id = ads.id
54    left join ad_sets on ads.adset_id = ad_sets.id
55    left join campaigns on ad_sets.campaign_id = campaigns.id
56group by
57    1
58  , 2
59  , 3
60  , 4
61  , 5
62  , 6
63  , 7
64  , 8
65  , 9
66    -- 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 | ad_id | ad_name | spend | reach | impressions | clicks | cpc  | ctr  | cpm  
------------+------------+--------------+-------------+---------------+-----------+-------------+-------+---------+-------+-------+-------------+--------+------+------+
2022-01-01 | 1234567890 | Facebook Ads | 9876543210  | Summer Sale   | 567890123 | Women       | 24680 | Ad A    | 500   | 1000  | 2000        | 100    | 5.00 | 0.05 | 250.00
2022-01-02 | 1234567890 | Facebook Ads | 9876543210  | Summer Sale   | 567890123 | Women       | 24680 | Ad A    | 600   | 1200  | 2400        | 120    | 5.00 | 0.05 | 250.00
2022-01-03 | 1234567890 | Facebook Ads | 9876543210  | Summer Sale   | 567890123 | Women       | 24680 | Ad A    | 700   | 1400  | 2800        | 140    | 5.00 | 0.05 | 250.00

Provide insights and analytics on Facebook Ads data. By integrating with the Facebook Ads platform, this SQL template retrieves data from various tables such as ad_insight, account, campaign, ad_set, and ad. The SQL code starts by creating temporary tables for each of these entities, allowing for easier querying and analysis. It then selects specific columns from these tables, including the date, account ID and name, campaign ID and name, ad set ID and name, ad ID and name, as well as various metrics such as spend, reach, impressions, clicks, CPC (cost per click), CTR (click-through rate), and CPM (cost per thousand impressions). The SQL template performs left joins between the temporary tables to link the data together based on the corresponding IDs. This enables the aggregation of metrics at different levels, such as by date, account, campaign, ad set, and ad. The resulting output provides a comprehensive overview of the performance of Facebook Ads campaigns, allowing marketers and advertisers to analyze key metrics and derive insights. For example, they can track the spend and reach of their ads, measure engagement through impressions and clicks, and calculate important performance indicators like CPC and CTR. By customizing the SQL template and selecting additional columns, users can further tailor the analysis to their specific needs and gain deeper insights into their Facebook Ads campaigns.

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.