Campaign Group Report
1with
2 campaign_group as (
3 select
4 *
5 from
6 {{raw.linkedin_ads.campaign_group}} -- to join another linkedin ads account
7 -- union all
8 -- select * from {{}}
9 )
10 , campaign as (
11 select
12 *
13 from
14 {{raw.linkedin_ads.campaign}}
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 , account.id as account_id
31 , account.name as account_name
32 , campaign_group.id campaign_group_id
33 , campaign_group.name campaign_group_name
34 , account.id account_id
35 , account.name account_name
36 , campaign_group.status
37 , account.currency
38 , campaign_group.backfilled
39 , campaign_group.run_schedule_start
40 , campaign_group.run_schedule_end
41 , campaign_group.last_modified_time
42 , campaign_group.created_time
43 , sum(cost_in_local_currency) spend
44 , sum(impressions) impressions
45 , sum(clicks) as clicks
46 , sum(safe_divide(cost_in_local_currency, clicks)) as cpc
47 , sum(safe_divide(clicks, impressions)) as ctr
48 , sum(safe_divide(cost_in_local_currency, impressions)) * 1000 as cpm
49 , sum(external_website_conversions) as conversions -- Additional columns should be manually specified if needed
50from
51 report
52 left join campaign on report.campaign_id = campaign.id
53 left join campaign_group on campaign.campaign_group_id = campaign_group.id
54 left join account on campaign.account_id = account.id
55group by
56 1
57 , 2
58 , 3
59 , 4
60 , 5
61 , 6
62 , 7
63 , 8
64 , 9
65 , 10
66 , 11
67 , 12
68 , 13
69 , 14 -- Adjust these numbers according to the selected columns+------------+------------+----------------+----------------------+------------------------+------------+----------------+--------+----------+--------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+
| date_day | account_id | account_name | campaign_group_id | campaign_group_name | account_id | account_name | status | currency | backfilled | run_schedule_start | run_schedule_end | last_modified_time | created_time | spend | impressions | clicks | cpc | ctr | cpm | conversions |
+------------+------------+----------------+----------------------+------------------------+------------+----------------+--------+----------+--------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+
| 2022-01-01 | 1234567890 | Example Account | 9876543210 | Example Campaign Group | 1234567890 | Example Account | Active | USD | Not Backfilled | 2022-01-01 00:00:00 | 2022-01-31 23:59:59 | 2022-02-01 12:34:56 | 2022-01-01 12:34:56 | 1000.00 | 50000 | 1000 | 1.00 | 0.02 | 20.00 | 50 |
| 2022-01-02 | 1234567890 | Example Account | 9876543210 | Example Campaign Group | 1234567890 | Example Account | Active | USD | Not Backfilled | 2022-01-01 00:00:00 | 2022-01-31 23:59:59 | 2022-02-01 12:34:56 | 2022-01-01 12:34:56 | 1500.00 | 75000 | 1500 | 1.00 | 0.02 | 20.00 | 75 |
| 2022-01-03 | 1234567890 | Example Account | 9876543210 | Example Campaign Group | 1234567890 | Example Account | Active | USD | Not Backfilled | 2022-01-01 00:00:00 | 2022-01-31 23:59:59 | 2022-02-01 12:34:56 | 2022-01-01 12:34:56 | 2000.00 | 100000 | 2000 | 1.00 | 0.02 | 20.00 | 100 |
+------------+------------+----------------+----------------------+------------------------+------------+----------------+--------+----------+--------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+Combines data from multiple tables, including campaign_group, campaign, account, and report, to provide insights into campaign performance. The SQL code starts by creating temporary tables for campaign_group, campaign, account, and report, which are populated with data from the corresponding tables in the LinkedIn Ads integration. These tables are then joined together using left joins based on specific column relationships. The resulting output includes various columns such as date_day, account_id, account_name, campaign_group_id, campaign_group_name, status, currency, backfilled, run_schedule_start, run_schedule_end, last_modified_time, created_time, spend, impressions, clicks, cpc, ctr, cpm, and conversions. These columns represent different metrics and attributes related to the campaigns and accounts. By running this SQL template, you can obtain valuable insights such as the total spend, impressions, clicks, cost per click (CPC), click-through rate (CTR), cost per thousand impressions (CPM), and conversions for each campaign group. These metrics can help analyze the performance of advertising campaigns on LinkedIn and make data-driven decisions to optimize marketing strategies. Please note that additional columns can be manually specified if needed, by adjusting the group by clause accordingly.
