Weld logo
linkedin-ads logo
Linkedin ads

Account Report

Generates an account report for the LinkedIn Ads integration. It retrieves data from the linkedin_ads.account, linkedin_ads.campaign, and linkedin_ads.ad_analytics_by_campaign tables. The report includes various metrics such as spend, impressions, clicks, CPC, CTR, CPM, and conversions, grouped by date, account, and other specified columns.
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
Example of output from model:
+------------+------------+----------------+--------------+----------+--------+------+---------------------+---------------------+-------+-------------+-------------+-------------+-------------+-------------+
| 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.

Ready to start modeling your own linkedin-ads data?

Get started building your data warehouse with linkedin-ads and connect all your apps and databases.

linkedin-ads logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.