Linkedin ads
Ad stats by campaign_group monthly
Generates monthly ad statistics by campaign group for LinkedIn Ads, including cost, clicks, impressions, engagements, shares, CTR, CPM, CPC, leads, and actions. It pulls data from ad analytics, campaign, and campaign group tables and groups the results by month and campaign group name.
1select
2 date_trunc(cast(date as date), month) month
3 , g.name as campaign_group_name
4 , sum(cost_in_local_currency) cost
5 , sum(cost_in_usd) cost_usd
6 , sum(clicks) clicks
7 , sum(impressions) impressions
8 , sum(total_engagements) engagements
9 , sum(shares) shares
10 , sum(safe_divide(clicks, impressions)) as ctr
11 , sum(safe_divide(cost_in_local_currency, impressions)) * 1000 as cpm
12 , sum(safe_divide(cost_in_local_currency, clicks)) as cpc
13 , sum(one_click_leads) as leads
14 , sum(action_clicks) as actions
15from
16 {{raw.linkedin_ads.ad_analytics_by_campaign}} c
17 left join {{raw.linkedin_ads.campaign}} h on c.campaign_id = h.id left join{{raw.linkedin_ads.campaign_group}} g on h.campaign_group_id = g.id
18group by
19 1
20 , 2Example of output from model:
+------------+---------------------+---------+----------+--------+-------------+-------------+--------+------+-------+------+--------+
| month | campaign_group_name | cost | cost_usd | clicks | impressions | engagements | shares | ctr | cpm | cpc | leads |
+------------+---------------------+---------+----------+--------+-------------+-------------+--------+------+-------+------+--------+
| 2021-01-01 | LinkedIn Ads | 500.00 | 625.00 | 100 | 10,000 | 50 | 10 | 1.00 | 50.00| 5.00| 5 |
+------------+---------------------+---------+----------+--------+-------------+-------------+--------+------+-------+------+--------+By selecting data from the ad_analytics_by_campaign table and joining it with the campaign and campaign_group tables, this SQL generates a report that summarizes key metrics for each campaign group on a monthly basis. The report includes data on the cost of each campaign, the number of clicks and impressions, the engagement rate, and the cost per click. This information can be used to optimize ad campaigns, identify top-performing campaign groups, and make data-driven decisions about ad spend. The output of this SQL is a table that can be easily exported or visualized to help marketers and advertisers better understand the impact of their LinkedIn ad campaigns.
