Weld logo
Linkedin ads

Ad stats by campaign monthly

Generates monthly ad statistics by campaign for LinkedIn Ads, including cost, clicks, impressions, engagements, shares, CTR, CPM, CPC, leads, and actions. It uses data from the ad_analytics_by_campaign and campaign tables and groups the results by month and campaign name.
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
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
Example of output from model:
|   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.

Ready to start modeling your own linkedin-ads data?

Get started building your data warehouse with linkedin-ads and 100+ more apps and databases available.

Weld logo
Sign up for free
Weld logo

Tired of scattered data? Build your data warehouse with Weld in minutes with our powerful ELT, SQL Transformations, Reverse-ETL and AI Assistant - connected to 100+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
© 2023 Weld. All rights reserved.