suse_linkedin_creative_final_v2

Full reference: paidteam-data-warehouse.customer_ads_suse.suse_linkedin_creative_final_v2Type: Final | Rows: 1,667 | Size: 1.7 MB

LinkedIn creative metadata final table (V2). The authoritative lookup table for LinkedIn creative assets. Updated via MERGE from the staging table, preserving creatives that may have been removed from the Dataslayer export. Joined to the adlevel final table via CreativeID for creative performance analysis.

Schema

Column	Type	Nullable	Description
CampaignGroupID	STRING	Yes	LinkedIn campaign group ID
CampaignID	STRING	Yes	LinkedIn campaign ID
CreativeID	STRING	Yes	LinkedIn creative ID (primary key for MERGE)
CampaignGroupName	STRING	Yes	Campaign group name
CampaignName	STRING	Yes	Campaign name
CampaignType	STRING	Yes	Campaign type
CampaignObjectiveType	STRING	Yes	Campaign objective
CampaignTargetType	STRING	Yes	Targeting type
CreativeName	STRING	Yes	Creative name / internal label
CreativeType	STRING	Yes	Creative format (SingleImage, Video, Carousel, etc.)
CreativeDestinationURL	STRING	Yes	Click destination URL
CreativeStatus	STRING	Yes	Creative status (Active, Paused, etc.)
CreativeTitle	STRING	Yes	Headline text displayed in the ad
CreativeText	STRING	Yes	Body text / introductory text
CreativePostUrl	STRING	Yes	URL of the sponsored post
CreativeThumbnailURL	STRING	Yes	Thumbnail image URL
CreativeThumbnailImage	STRING	Yes	Thumbnail image asset reference
CreativeDownloadmediaURL	STRING	Yes	Media download URL
CreativeDownloaddocumentURL	STRING	Yes	Document download URL
CreativeReferenceID	STRING	Yes	Reference ID linking to LinkedIn's internal asset
_ingested_at	TIMESTAMP	Yes	Timestamp of last MERGE update

Partitioning & Clustering

• Partitioned by: None (metadata table, no Date column)
• Clustered by: CampaignID, CreativeID

Clustering on CampaignID and CreativeID optimizes JOIN operations with the adlevel table.

Refresh Logic

The Creative Refresh Script performs a MERGE operation:

MERGE INTO suse_linkedin_creative_final_v2 AS T
USING suse_linkedin_creative_staging_v2 AS S
ON T.CreativeID = S.CreativeID
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...

• MATCHED: All columns are updated with the latest values from staging; _ingested_at is set to CURRENT_TIMESTAMP()
• NOT MATCHED: New creatives are inserted with _ingested_at = CURRENT_TIMESTAMP()
• NOT MATCHED BY SOURCE: Rows are preserved (not deleted), keeping historical creatives

MERGE key: CreativeID only (globally unique on LinkedIn)

Results are logged to creative_refresh_log_v2.

Sample Query

-- Join creative metadata with adlevel performance
SELECT
  a.Date,
  c.CreativeTitle,
  c.CreativeType,
  c.CreativeThumbnailURL,
  SUM(a.Impressions) AS impressions,
  SUM(a.Clicks) AS clicks,
  SUM(a.Cost) AS cost
FROM `paidteam-data-warehouse.customer_ads_suse.suse_linkedin_adlevel_final_v2` a
JOIN `paidteam-data-warehouse.customer_ads_suse.suse_linkedin_creative_final_v2` c
  ON a.CreativeID = c.CreativeID
WHERE a.Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY a.Date, c.CreativeTitle, c.CreativeType, c.CreativeThumbnailURL
ORDER BY impressions DESC
LIMIT 100;

Notes

• The final table has 1,667 rows vs. 1,527 in staging. The difference (140 rows) represents historical creatives that are no longer in the Dataslayer export but have been preserved by the MERGE logic.
• CreativeID is the sole MERGE key because LinkedIn creative IDs are globally unique across all campaign groups and campaigns.
• The _ingested_at timestamp tracks when each creative was last updated by the MERGE script.
• This table is designed to be joined with the adlevel table via CreativeID in the linkedin_creative_performance_v2 view.