Campaign Report

1with
2    campaign as (
3        select
4            *
5        from
6            {{raw.linkedin_ads.campaign}} --   to join another linkedin ads account
7            --   union all
8            --   select * from {{}}
9    )
10  , campaign_group as (
11        select
12            *
13        from
14            {{raw.linkedin_ads.campaign_group}}
15    )
16  , account as (
17        select
18            *
19        from
20            {{raw.linkedin_ads.account}}
21    )
22  , report as (
23        select
24            *
25        from
26            {{raw.linkedin_ads.ad_analytics_by_campaign}}
27    )
28select
29    cast(report.date as date) date_day
30  , cast(account.id as string) as account_id
31  , account.name as account_name
32  , campaign.name campaign_name
33  , cast(campaign.id as string) campaign_id
34  , campaign.version_tag
35  , campaign_group.id campaign_group_id
36  , campaign_group.name campaign_group_name
37  , campaign.status as campaign_status
38  , campaign_group.status as campaign_group_status
39  , campaign.locale_country
40  , campaign.objective_type
41  , campaign.optimization_target_type
42  , campaign.audience_expansion_enabled
43  , campaign.offsite_delivery_enabled
44  , campaign.run_schedule_start
45  , campaign.run_schedule_end
46  , campaign.last_modified_time
47  , campaign.created_time
48  , campaign.type
49  , campaign.cost_type
50  , campaign.creative_selection
51  , account.currency
52  , campaign.format
53  , sum(cost_in_local_currency) spend
54  , sum(impressions) impressions
55  , sum(clicks) as clicks
56  , sum(safe_divide(cost_in_local_currency, clicks)) as cpc
57  , sum(safe_divide(clicks, impressions)) as ctr
58  , sum(safe_divide(cost_in_local_currency, impressions)) * 1000 as cpm
59  , sum(external_website_conversions) as conversions -- Additional pass-through columns should be manually specified if needed
60from
61    report
62    left join campaign on report.campaign_id = campaign.id
63    left join campaign_group on campaign.campaign_group_id = campaign_group.id
64    left join account on campaign.account_id = account.id
65group by
66    1
67  , 2
68  , 3
69  , 4
70  , 5
71  , 6
72  , 7
73  , 8
74  , 9
75  , 10
76  , 11
77  , 12
78  , 13
79  , 14
80  , 15
81  , 16
82  , 17
83  , 18
84  , 19
85  , 20
86  , 21
87  , 22
88  , 23
89  , 24
90    -- Adjust these numbers according to the selected columns