1with
2 creative as (
3 select
4 *
5 from
6 {{raw.linkedin_ads.creative}} -- 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 , campaign_group as (
17 select
18 *
19 from
20 {{raw.linkedin_ads.campaign_group}}
21 )
22 , account as (
23 select
24 *
25 from
26 {{raw.linkedin_ads.account}}
27 )
28 , report as (
29 select
30 *
31 from
32 {{raw.linkedin_ads.ad_analytics_by_creative}}
33 )
34select
35 cast(report.date as date) date_day
36 , cast(account.id as string) account_id
37 , account.name account_name
38 , creative.id creative_id
39 , campaign.id campaign_id
40 , campaign.name campaign_name
41 , campaign_group.id campaign_group_id
42 , campaign_group.name campaign_group_name
43 , campaign.status as campaign_status
44 , campaign_group.status as campaign_group_status
45 , account.currency
46 , creative.last_modified_at
47 , creative.created_at
48 , sum(cost_in_local_currency) spend
49 , sum(impressions) impressions
50 , sum(clicks) as clicks
51 , sum(safe_divide(cost_in_local_currency, clicks)) as cpc
52 , sum(safe_divide(clicks, impressions)) as ctr
53 , sum(safe_divide(cost_in_local_currency, impressions)) * 1000 as cpm
54 , sum(external_website_conversions) as conversions -- Additional columns should be manually specified if needed
55from
56 report
57 left join creative on report.creative_id = creative.id
58 left join campaign on creative.campaign_id = campaign.id
59 left join campaign_group on campaign.campaign_group_id = campaign_group.id
60 left join account on campaign.account_id = account.id
61group by
62 1
63 , 2
64 , 3
65 , 4
66 , 5
67 , 6
68 , 7
69 , 8
70 , 9
71 , 10
72 , 11
73 , 12
74 , 13 -- Adjust these numbers according to the selected columnsExample output
+------------+------------+-----------------+-------------+-------------+------------------+----------------------+--------------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+
| date_day | account_id | account_name | creative_id | campaign_id | campaign_name | campaign_group_id | campaign_group_name | campaign_status | campaign_group_status | currency | last_modified_at | created_at | spend | impressions | clicks | cpc | ctr | cpm | conversions |
+------------+------------+-----------------+-------------+-------------+------------------+----------------------+--------------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+
| 2022-01-01 | 1234567890 | Example Account | 987654321 | 567890123 | Example Campaign | 345678901 | Example Campaign Group | ACTIVE | ACTIVE | USD | 2022-01-01 12:34:56 | 2022-01-01 00:00:00 | 1000.00 | 5000 | 100 | 10.00 | 0.02 | 200.00 | 5 |
+------------+------------+-----------------+-------------+-------------+------------------+----------------------+--------------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+Designed to retrieve data from the LinkedIn Ads integration. It combines multiple tables, including "creative," "campaign," "campaign_group," "account," and "report," to generate insightful reports on advertising performance. The template starts by selecting all columns from the "creative" table, which can be joined with another LinkedIn Ads account if needed. It then proceeds to select all columns from the "campaign," "campaign_group," "account," and "report" tables. The final output includes various metrics such as the date, account ID and name, creative ID, campaign ID and name, campaign group ID and name, campaign status, campaign group status, account currency, creative modification dates, creative creation dates, spend, impressions, clicks, cost per click (CPC), click-through rate (CTR), cost per thousand impressions (CPM), and conversions. To generate the report, the template performs left joins between the tables based on their respective IDs. The results are then grouped by specific columns, which can be adjusted as needed. This SQL template is useful for analyzing and monitoring the performance of LinkedIn Ads campaigns. It provides valuable insights into key metrics such as spend, impressions, clicks, and conversions, allowing advertisers to measure the effectiveness of their advertising efforts and make data-driven decisions to optimize their campaigns.