URL Report

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