Weld logo
linkedin-ads logo
Linkedin ads

Campaign Report

Generates a campaign report by joining data from the LinkedIn Ads integration. It retrieves information such as the date, account details, campaign name, campaign status, impressions, clicks, spend, and various performance metrics like CPC, CTR, and CPM. The data is grouped by different columns and can be adjusted based on the selected columns.
1with
2    campaign as (
3        select
4            *
5        from
6            {{raw.linkedin_ads.campaign}} --   to join another linkedin ads account
7            --   union all
8            --   select * from {{}}
9    )
10  , campaign_group as (
11        select
12            *
13        from
14            {{raw.linkedin_ads.campaign_group}}
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  , cast(account.id as string) as account_id
31  , account.name as account_name
32  , campaign.name campaign_name
33  , cast(campaign.id as string) campaign_id
34  , campaign.version_tag
35  , campaign_group.id campaign_group_id
36  , campaign_group.name campaign_group_name
37  , campaign.status as campaign_status
38  , campaign_group.status as campaign_group_status
39  , campaign.locale_country
40  , campaign.objective_type
41  , campaign.optimization_target_type
42  , campaign.audience_expansion_enabled
43  , campaign.offsite_delivery_enabled
44  , campaign.run_schedule_start
45  , campaign.run_schedule_end
46  , campaign.last_modified_time
47  , campaign.created_time
48  , campaign.type
49  , campaign.cost_type
50  , campaign.creative_selection
51  , account.currency
52  , campaign.format
53  , sum(cost_in_local_currency) spend
54  , sum(impressions) impressions
55  , sum(clicks) as clicks
56  , sum(safe_divide(cost_in_local_currency, clicks)) as cpc
57  , sum(safe_divide(clicks, impressions)) as ctr
58  , sum(safe_divide(cost_in_local_currency, impressions)) * 1000 as cpm
59  , sum(external_website_conversions) as conversions -- Additional pass-through columns should be manually specified if needed
60from
61    report
62    left join campaign on report.campaign_id = campaign.id
63    left join campaign_group on campaign.campaign_group_id = campaign_group.id
64    left join account on campaign.account_id = account.id
65group by
66    1
67  , 2
68  , 3
69  , 4
70  , 5
71  , 6
72  , 7
73  , 8
74  , 9
75  , 10
76  , 11
77  , 12
78  , 13
79  , 14
80  , 15
81  , 16
82  , 17
83  , 18
84  , 19
85  , 20
86  , 21
87  , 22
88  , 23
89  , 24
90    -- Adjust these numbers according to the selected columns
Example of output from model:
DATE_DAY  ACCOUNT_ID  ACCOUNT_NAME  CAMPAIGN_NAME  CAMPAIGN_ID  CAMPAIGN_VERSION_TAG  CAMPAIGN_GROUP_ID  CAMPAIGN_GROUP_NAME  CAMPAIGN_STATUS  CAMPAIGN_GROUP_STATUS  CAMPAIGN_LOCALE_COUNTRY  CAMPAIGN_OBJECTIVE_TYPE  CAMPAIGN_OPTIMIZATION_TARGET_TYPE  CAMPAIGN_AUDIENCE_EXPANSION_ENABLED  CAMPAIGN_OFFSITE_DELIVERY_ENABLED  CAMPAIGN_RUN_SCHEDULE_START  CAMPAIGN_RUN_SCHEDULE_END  CAMPAIGN_LAST_MODIFIED_TIME  CAMPAIGN_CREATED_TIME  CAMPAIGN_TYPE  CAMPAIGN_COST_TYPE  CAMPAIGN_CREATIVE_SELECTION  ACCOUNT_CURRENCY  CAMPAIGN_FORMAT  SPEND  IMPRESSIONS  CLICKS  CPC  CTR  CPM  CONVERSIONS
2022-01-01  1234567890  Example Account  Example Campaign  9876543210  Version 1.0  1111111111  Example Campaign Group  Active  Active  US  Awareness  Clicks  Enabled  Enabled  2022-01-01 00:00:00  2022-01-31 23:59:59  2022-01-31 23:59:59  2022-01-01 00:00:00  Standard  CPM  1000.00  1000000  1000  1.00  0.001  1.00  10

Combines data from multiple tables, including "campaign", "campaign_group", "account", and "report". The SQL code retrieves various fields such as the date, account ID and name, campaign name and ID, campaign group ID and name, campaign status, campaign group status, locale country, objective type, optimization target type, audience expansion enabled, offsite delivery enabled, run schedule start and end, last modified time, created time, campaign type, cost type, creative selection, account currency, spend, impressions, clicks, CPC (cost per click), CTR (click-through rate), CPM (cost per thousand impressions), and conversions. By joining the relevant tables and aggregating the data, this SQL template provides insights into campaign performance metrics such as spend, impressions, clicks, and conversions. It allows users to analyze the effectiveness of their LinkedIn Ads campaigns, monitor key metrics, and make data-driven decisions to optimize their advertising strategies.

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.