Account Report
1with
2 account as (
3 select
4 *
5 from
6 {{raw.linkedin_ads.account}}
7 -- to join another linkedin ads account
8 -- union all
9 -- select * from {{}}
10 )
11 , campaign as (
12 select
13 *
14 from
15 {{raw.linkedin_ads.campaign}}
16 )
17 , report as (
18 select
19 *
20 from
21 {{raw.linkedin_ads.ad_analytics_by_campaign}}
22 )
23select
24 cast(report.date as date) date_day
25 , cast(account.id as string) account_id
26 , account.name account_name
27 , account.version_tag
28 , account.currency
29 , account.status
30 , account.type
31 , account.last_modified_time
32 , account.created_time
33 , sum(cost_in_local_currency) spend
34 , sum(impressions) impressions
35 , sum(clicks) as clicks
36 , sum(safe_divide(cost_in_local_currency, clicks)) as cpc
37 , sum(safe_divide(clicks, impressions)) as ctr
38 , sum(safe_divide(cost_in_local_currency, impressions)) * 1000 as cpm
39 , sum(external_website_conversions) as conversions
40 -- Additional columns should be manually specified if needed
41from
42 report
43 left join campaign on report.campaign_id = campaign.id
44 left join account on campaign.account_id = account.id
45group by
46 1
47 , 2
48 , 3
49 , 4
50 , 5
51 , 6
52 , 7
53 , 8
54 , 9
55 -- Adjust these numbers according to the selected columns+------------+------------+----------------+--------------+----------+--------+------+---------------------+---------------------+-------+-------------+-------------+-------------+-------------+-------------+
| date_day | account_id | account_name | version_tag | currency | status | type | last_modified_time | created_time | spend | impressions | clicks | cpc | ctr | cpm |
+------------+------------+----------------+--------------+----------+--------+------+---------------------+---------------------+-------+-------------+-------------+-------------+-------------+-------------+
| 2022-01-01 | 1234567890 | Example Account | v1.0 | USD | Active | Paid | 2022-01-01 12:00:00 | 2022-01-01 10:00:00 | 100 | 1000 | 50 | 2.00 | 0.05 | 100.00 |
| 2022-01-02 | 1234567890 | Example Account | v1.0 | USD | Active | Paid | 2022-01-01 12:00:00 | 2022-01-01 10:00:00 | 150 | 2000 | 75 | 2.00 | 0.0375 | 75.00 |
| 2022-01-03 | 1234567890 | Example Account | v1.0 | USD | Active | Paid | 2022-01-01 12:00:00 | 2022-01-01 10:00:00 | 200 | 3000 | 100 | 2.00 | 0.0333 | 66.67 |
+------------+------------+----------------+--------------+----------+--------+------+---------------------+---------------------+-------+-------------+-------------+-------------+-------------+-------------+Combines data from three tables: "account", "campaign", and "report". The "account" table retrieves all the available account information from the "linkedin_ads.account" table. The "campaign" table retrieves all the campaign information from the "linkedin_ads.campaign" table. Lastly, the "report" table retrieves all the ad analytics data by campaign from the "linkedin_ads.ad_analytics_by_campaign" table. The resulting report includes various columns such as the date, account ID, account name, version tag, currency, status, type, last modified time, created time, spend, impressions, clicks, CPC (cost per click), CTR (click-through rate), CPM (cost per thousand impressions), and conversions. By joining the "report" table with the "campaign" and "account" tables, this SQL template provides insights into the performance of LinkedIn Ads campaigns. It allows users to analyze metrics such as spend, impressions, clicks, CPC, CTR, CPM, and conversions for each account and campaign. Additional columns can be manually specified if needed. This SQL template is useful for generating account-level reports, analyzing campaign performance, and gaining insights into the effectiveness of LinkedIn Ads campaigns.
