Weld logo
linkedin-ads logo
Linkedin ads

Creative Report

Generate a report on LinkedIn Ads performance. It combines data from various tables such as creative, campaign, campaign_group, account, and report to provide insights on metrics like spend, impressions, clicks, CPC, CTR, and conversions. The template uses left joins to connect the tables based on their respective IDs and groups the data by specific columns for aggregation.
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 columns
Example of output from model:
+------------+------------+-----------------+-------------+-------------+------------------+----------------------+--------------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+
| 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.

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.