Ad stats by campaign monthly
1select
2 date_trunc(cast(date as date), month) month
3 , h.name campaign_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 sum(action_clicks) as actions
14from
15 {{raw.linkedin_ads.ad_analytics_by_campaign}} c
16 left join {{raw.linkedin_ads.campaign}} h on c.campaign_id = h.id
17group by
18 1
19 , 2+------------+----------------+--------+----------+--------+-------------+-------------+--------+-------+-------+-------+--------+
| month | campaign_name | cost | cost_usd | clicks | impressions | engagements | shares | ctr | cpm | cpc | leads |
+------------+----------------+--------+----------+--------+-------------+-------------+--------+-------+-------+-------+--------+
| 2021-01-01 | Advertise Now | 100000 | 1250.00 | 5000 | 100000 | 7500 | 1000 | 0.050 | 1000.0| 20.000| 100 |
+------------+----------------+--------+----------+--------+-------------+-------------+--------+-------+-------+-------+--------+This SQL model allows you to extract valuable insights on the performance of your campaigns by providing a detailed breakdown of key metrics such as cost, clicks, impressions, engagements, shares, and more. By grouping the data by month and campaign name, you can easily identify trends and patterns in your advertising performance over time. Additionally, the SQL template includes calculations for important metrics such as click-through rate (CTR), cost per thousand impressions (CPM), and cost per click (CPC), which can help you optimize your campaigns for maximum ROI. Whether you're a marketer looking to improve your LinkedIn advertising strategy or a business owner looking to track the success of your campaigns, the Ad stats by campaign monthly SQL template is an essential tool for gaining valuable insights into your advertising performance.
