Weld
Start free
LinkedIn Ads model

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.

See the SQLAll LinkedIn Ads models
Grain
day x creative
Built from
  1. 5
    Raw tables
  2. 1
    Staging
Reads from LinkedIn Ads
accountad_analytics_by_creativecampaigncampaign_groupcreative
Ships as
Weld and dbt SQL, open source on GitHub
staging.linkedin_ads.creative_report
1-- staging.linkedin_ads.creative_report
2-- Daily LinkedIn Ads delivery per creative, labelled with its campaign,
3-- campaign group and ad account. The LinkedIn input to the cross-channel
4-- marketing reports in google-ads/ and facebook-ads/.
5--
6-- Grain: day x creative. Money is in the ad account's currency
7-- (cost_in_local_currency, conversion_value_in_local_currency).
8-- Rates are worked out from the day's totals: ctr is clicks / impressions as a
9-- fraction, cpc is spend per click, cpm is spend per thousand impressions.
10-- conversions are LinkedIn's external website conversions (post-click plus
11-- post-view, as LinkedIn attributes them).
12--
13-- Several ad accounts: Weld lands every connected account in the same tables,
14-- so nothing extra is needed. For accounts synced by separate connections,
15-- union their tables into each CTE below.
16--
17-- Depends on: raw.linkedin_ads.ad_analytics_by_creative, creative, campaign,
18--             campaign_group, account
19--
20-- From the weld.app template library, rewritten and corrected. Not reconciled
21-- against LinkedIn Ads; check the output against the source before relying on it.
22
23WITH daily AS (
24    SELECT
25        CAST(date AS DATE)                                      AS date_day,
26        creative_id,
27        SUM(cost_in_local_currency)                             AS spend,
28        SUM(impressions)                                        AS impressions,
29        SUM(clicks)                                             AS clicks,
30        SUM(external_website_conversions)                       AS conversions,
31        SUM(conversion_value_in_local_currency)                 AS conversion_value
32    FROM {{raw.linkedin_ads.ad_analytics_by_creative}}
33    GROUP BY 1, 2
34),
35
36creatives AS (
37    SELECT
38        id                                                      AS creative_id,
39        title                                                   AS creative_name,
40        intended_status                                         AS creative_status,

Refs like {{raw.…}} resolve to your synced tables in Weld; the dbt view uses source() instead.

Notes

How it counts. And where it stops.

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.

Raw tables it reads

  • raw.linkedin_ads.account
  • raw.linkedin_ads.ad_analytics_by_creative
  • raw.linkedin_ads.campaign
  • raw.linkedin_ads.campaign_group
  • raw.linkedin_ads.creative

Put LinkedIn Ads in front
of your agents.

Connect LinkedIn Ads, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.

Start for freeTalk to us