Weld logo
facebook-ads logo
Facebook ads

URL Report

Generates a URL report for Facebook Ads integration. It retrieves data from various tables such as ad_insight, creative, account, ad, ad_set, and campaign. The template selects specific columns like date, account ID and name, campaign ID and name, ad set ID and name, ad ID and name, creative ID and name, and extracts UTM parameters from the URL. It also calculates the sum of clicks, impressions, and spend, and groups the data accordingly.
1with
2    stats as (
3        select
4            *
5        from
6            {{raw.facebook_ads.ad_insight}}
7    )
8  , creatives as (
9        select
10            *
11        from
12            {{raw.facebook_ads.creative}}
13    )
14  , accounts as (
15        select
16            *
17        from
18            {{raw.facebook_ads.account}}
19    )
20  , ads as (
21        select
22            *
23        from
24            {{raw.facebook_ads.ad}}
25    )
26  , ad_sets as (
27        select
28            *
29        from
30            {{raw.facebook_ads.ad_set}}
31    )
32  , campaigns as (
33        select
34            *
35        from
36            {{raw.facebook_ads.campaign}}
37    )
38select
39    stats.date date_day
40  , accounts.id account_id
41  , accounts.name account_name
42  , campaigns.id campaign_id
43  , campaigns.name campaign_name
44  , ad_sets.id ad_set_id
45  , ad_sets.name ad_set_name
46  , ads.id ad_id
47  , ads.name ad_name
48  , creatives.id creative_id
49  , creatives.name creative_name
50  , coalesce(
51        regexp_extract(creatives.url_tags, r'[?&]utm_source=([^&]*)')
52      , 'facebook'
53    ) as utm_source
54  , coalesce(
55        regexp_extract(creatives.url_tags, r'[?&]utm_medium=([^&]*)')
56      , 'cpc'
57    ) as utm_medium
58  , coalesce(
59        regexp_extract(creatives.url_tags, r'[?&]utm_campaign=([^&]*)')
60      , campaigns.name
61    ) as utm_campaign
62  , coalesce(
63        regexp_extract(creatives.url_tags, r'[?&]utm_content=([^&]*)')
64      , ad_sets.name
65    ) as utm_content
66  , sum(stats.clicks) as clicks
67  , sum(stats.impressions) as impressions
68  , sum(stats.spend) as spend -- Additional pass-through columns should be manually specified if needed
69from
70    stats
71    left join ads on stats.ad_id = ads.id
72    left join creatives on ads.creative_id = creatives.id
73    left join ad_sets on ads.adset_id = ad_sets.id
74    left join campaigns on ads.campaign_id = campaigns.id
75    left join accounts on stats.account_id = accounts.id
76where
77    creatives.url_tags is not null
78group by
79    1
80  , 2
81  , 3
82  , 4
83  , 5
84  , 6
85  , 7
86  , 8
87  , 9
88  , 10
89  , 11
90  , 12
91  , 13
92  , 14
93  , 15
94    -- 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        | creative_id  | creative_name  | utm_source   | utm_medium     | utm_campaign | utm_content | clicks     |
+------------+------------+----------------+-------------+----------------+--------------+----------------+---------+----------------+--------------+----------------+--------------+----------------+--------------+-------------+------------+
| 2022-01-01 | 1234567890 | Example Account| 987654321   | Example Campaign| 567890123    | Example Ad Set | 4567890 | Example Ad     | 34567890     | Example Creative| facebook     | cpc            | Example Campaign | Example Ad Set| 100        |
+------------+------------+----------------+-------------+----------------+--------------+----------------+---------+----------------+--------------+----------------+--------------+----------------+--------------+-------------+------------+

Retrieves data from the Facebook Ads integration. It combines information from various tables including ad_insight, creative, account, ad, ad_set, and campaign. The SQL code selects specific columns such as date, account ID and name, campaign ID and name, ad set ID and name, ad ID and name, creative ID and name, as well as additional columns derived from the URL tags. The SQL code uses regular expressions to extract values from the URL tags, such as utm_source, utm_medium, utm_campaign, and utm_content. If these values are not present in the URL tags, default values are assigned. The code also calculates the sum of clicks, impressions, and spend. This SQL template is useful for generating reports that provide insights into the performance of Facebook Ads campaigns. By analyzing the data, marketers can gain valuable information about the effectiveness of different campaigns, ad sets, and ads. The URL tags allow for tracking the source, medium, campaign, and content of the traffic, providing insights into the success of various marketing efforts. The generated report can help optimize advertising strategies and allocate resources effectively.

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.