linkedin_creative_performance_v2

This view joins the LinkedIn adlevel final table with the LinkedIn creative final table, producing a single denormalized output with performance metrics, creative metadata, ad copy, media URLs, and UDF-enriched campaign fields.

View Details

Property	Value
Full path	`paidteam-data-warehouse.customer_ads_suse.linkedin_creative_performance_v2`
Source (adlevel)	`suse_linkedin_adlevel_final_v2`
Source (creative)	`suse_linkedin_creative_final_v2`
Join type	`LEFT JOIN`
Join key	`CreativeID`

Join Logic

sql

FROM `paidteam-data-warehouse.customer_ads_suse.suse_linkedin_adlevel_final_v2` a
LEFT JOIN `paidteam-data-warehouse.customer_ads_suse.suse_linkedin_creative_final_v2` c
  ON a.CreativeID = c.CreativeID

The LEFT JOIN ensures that all adlevel performance rows are preserved even if no matching creative metadata record exists. LinkedIn uses CreativeID as a globally unique identifier, so a single-column join is sufficient.

Column Groups

Date and IDs

Column	Type	Source	Description
`Date`	DATE	adlevel	The reporting date
`CampaignGroupID`	STRING	adlevel	LinkedIn campaign group identifier
`CampaignID`	STRING	adlevel	LinkedIn campaign identifier
`CreativeID`	STRING	adlevel	LinkedIn creative identifier (join key)

Names (with COALESCE)

Campaign and group names use COALESCE to prefer the adlevel value but fall back to the creative table value if the adlevel name is NULL.

Column	Type	Description
`CampaignGroupName`	STRING	`COALESCE(a.CampaignGroupName, c.CampaignGroupName)`
`CampaignName`	STRING	`COALESCE(a.CampaignName, c.CampaignName)`
`CreativeName`	STRING	Name of the creative from adlevel data

Campaign Metadata

Column	Type	Description
`CampaignType`	STRING	Type of campaign (e.g., Sponsored Content)
`CampaignObjective`	STRING	Campaign objective (e.g., Lead Generation)
`CostType`	STRING	Billing model (e.g., CPM, CPC)

Creative Metadata

Column	Type	Source	Description
`CreativeStatus`	STRING	creative	Current status of the creative (ACTIVE, PAUSED, etc.)
`CreativeType`	STRING	creative	Type of creative (e.g., SPONSORED_STATUS_UPDATE)
`IntendedStatus`	STRING	creative	Intended delivery status

Ad Copy

Column	Type	Source	Description
`AdHeadline`	STRING	creative	Headline text of the ad
`AdCommentary`	STRING	creative	Body/commentary text of the ad
`AdCallToAction`	STRING	creative	Call-to-action label (e.g., LEARN_MORE, SIGN_UP)

URLs

Column	Type	Source	Description
`DestinationURL`	STRING	creative	Click-through destination URL
`LandingPageURL`	STRING	creative	Final landing page URL

Media URLs

Column	Type	Source	Description
`ImageURL`	STRING	creative	URL to the ad image asset
`VideoURL`	STRING	creative	URL to the ad video asset (if video creative)
`ThumbnailURL`	STRING	creative	URL to the video thumbnail image

Core Metrics

Column	Type	Description
`Impressions`	INTEGER	Number of times the ad was displayed
`Clicks`	INTEGER	Number of clicks on the ad
`Cost`	FLOAT64	Total spend in account currency

Rate Metrics

Column	Type	Description
`CTR`	FLOAT64	Click-through rate (Clicks / Impressions)
`CPC`	FLOAT64	Average cost per click
`CPM`	FLOAT64	Cost per thousand impressions

Reach

Column	Type	Description
`Reach`	INTEGER	Unique users who saw the ad
`Frequency`	FLOAT64	Average number of times each user saw the ad

Engagement

Column	Type	Description
`Likes`	INTEGER	Number of likes/reactions
`Comments`	INTEGER	Number of comments
`Shares`	INTEGER	Number of shares
`Follows`	INTEGER	Number of follows generated
`TotalEngagements`	INTEGER	Sum of all engagement actions
`EngagementRate`	FLOAT64	Engagements as a percentage of impressions

Lead Generation

Column	Type	Description
`LeadFormOpens`	INTEGER	Number of times the lead form was opened
`LeadFormSubmissions`	INTEGER	Number of completed lead form submissions
`CostPerLead`	FLOAT64	Cost / LeadFormSubmissions

Video Metrics

Column	Type	Description
`VideoViews`	INTEGER	Number of video views
`VideoCompletions`	INTEGER	Number of times the video was watched to completion
`VideoCompletionRate`	FLOAT64	Completions as a percentage of views

Document Metrics

Column	Type	Description
`DocumentCompletions`	INTEGER	Number of times a document ad was fully viewed
`DocumentFirstQuartileCompletions`	INTEGER	Views past 25% of the document

Conversions

Column	Type	Description
`ExternalWebsiteConversions`	INTEGER	Conversion events from external website pixel
`ExternalWebsitePostClickConversions`	INTEGER	Post-click conversions
`ExternalWebsitePostViewConversions`	INTEGER	Post-view (view-through) conversions
`OneClickLeadFormOpens`	INTEGER	One-click lead form opens
`OneClickLeads`	INTEGER	One-click lead submissions

Calculated Fields

Column	Type	Description
`TotalConversions`	INTEGER	Sum of all conversion types
`CPA`	FLOAT64	Cost / TotalConversions (NULL-safe)

UDF-Enriched Fields

These fields are computed at query time by the dataset's UDFs, parsing the campaign naming convention.

Column	UDF	Description
`Channel`	`get_channel()`	Advertising channel (e.g., Paid Social)
`CampaignCode`	`get_campaign_code()`	Numeric campaign tracking code
`ProductLine`	`get_product_line()`	Product line (e.g., Business Critical Linux)
`Product`	`get_product()`	Specific product name
`SubProduct`	`get_subproduct()`	Sub-product or variant
`GEO`	`get_geo()`	Geographic targeting region
`Tier`	`get_tier()`	Tier classification
`Language`	`get_language()`	Language targeting
`Audience`	`get_audience()`	Audience segment
`ContentName`	`get_content_name()`	Human-readable content name from creative name

Audit Timestamps

Column	Type	Source	Description
`_ingested_at`	TIMESTAMP	adlevel	When the adlevel row was written to the final table
`creative_ingested_at`	TIMESTAMP	creative	When the creative record was last merged

Example Queries

Top Creatives by Spend (Last 30 Days)

sql

SELECT
  CreativeName,
  AdHeadline,
  ImageURL,
  SUM(Cost) AS TotalSpend,
  SUM(Impressions) AS TotalImpressions,
  SUM(Clicks) AS TotalClicks,
  SAFE_DIVIDE(SUM(Clicks), SUM(Impressions)) AS AvgCTR,
  SUM(TotalConversions) AS Conversions
FROM `paidteam-data-warehouse.customer_ads_suse.linkedin_creative_performance_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY CreativeName, AdHeadline, ImageURL
ORDER BY TotalSpend DESC
LIMIT 20;

Creative Fatigue Detection

sql

SELECT
  CreativeID,
  CreativeName,
  AdHeadline,
  DATE_TRUNC(Date, WEEK) AS Week,
  SUM(Impressions) AS WeeklyImpressions,
  SAFE_DIVIDE(SUM(Clicks), SUM(Impressions)) AS WeeklyCTR,
  SAFE_DIVIDE(SUM(Cost), SUM(Clicks)) AS WeeklyCPC
FROM `paidteam-data-warehouse.customer_ads_suse.linkedin_creative_performance_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY CreativeID, CreativeName, AdHeadline, Week
ORDER BY CreativeID, Week;

Performance by Product Line

sql

SELECT
  ProductLine,
  Product,
  COUNT(DISTINCT CreativeID) AS UniqueCreatives,
  SUM(Cost) AS TotalSpend,
  SUM(LeadFormSubmissions) AS TotalLeads,
  SAFE_DIVIDE(SUM(Cost), SUM(LeadFormSubmissions)) AS CostPerLead
FROM `paidteam-data-warehouse.customer_ads_suse.linkedin_creative_performance_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY ProductLine, Product
ORDER BY TotalSpend DESC;

Dependencies

Tables: suse_linkedin_adlevel_final_v2, suse_linkedin_creative_final_v2
UDFs: All 10 UDFs (get_channel, get_campaign_code, get_product_line, get_product, get_subproduct, get_geo, get_tier, get_language, get_audience, get_content_name)
Consumers: Looker Studio LinkedIn Creative Dashboard

linkedin_creative_performance_v2 ​

View Details ​

Join Logic ​

Column Groups ​

Date and IDs ​

Names (with COALESCE) ​

Campaign Metadata ​

Creative Metadata ​

Ad Copy ​

URLs ​

Media URLs ​

Core Metrics ​

Rate Metrics ​

Reach ​

Engagement ​

Lead Generation ​

Video Metrics ​

Document Metrics ​

Conversions ​

Calculated Fields ​

UDF-Enriched Fields ​

Audit Timestamps ​

Example Queries ​

Top Creatives by Spend (Last 30 Days) ​

Creative Fatigue Detection ​

Performance by Product Line ​

Dependencies ​