suse_linkedin_adlevel_staging_v2

Full reference: paidteam-data-warehouse.customer_ads_suse.suse_linkedin_adlevel_staging_v2Type: Staging | Rows: 8,543 | Size: 4.8 MB

LinkedIn Ads ad-level staging data (V2). Dataslayer writes a rolling window of ad-level performance data into this table daily. The scheduled refresh script reads from here to update the final table.

Schema

Column	Type	Nullable	Description
Date	DATE	No	Date of ad performance
CampaignGroupID	STRING	Yes	LinkedIn campaign group ID (top-level grouping)
CampaignID	STRING	Yes	LinkedIn campaign ID (equivalent to ad group on other platforms)
CreativeID	STRING	Yes	LinkedIn creative ID (equivalent to ad ID on other platforms)
CampaignGroupName	STRING	Yes	Campaign group name
CampaignName	STRING	Yes	Campaign name
CreativeName	STRING	Yes	Creative name
CreativeType	STRING	Yes	Creative type (SingleImage, Video, Carousel, etc.)
CreativeDestinationURL	STRING	Yes	Click destination URL
Impressions	INTEGER	Yes	Number of impressions
Clicks	INTEGER	Yes	Number of clicks
CTR	FLOAT	Yes	Click-through rate
CPC	FLOAT	Yes	Cost per click
CPM	FLOAT	Yes	Cost per thousand impressions
Cost	FLOAT	Yes	Total cost
Reach	INTEGER	Yes	Unique members reached
Frequency	FLOAT	Yes	Average frequency per member
LandingPageClicks	INTEGER	Yes	Clicks to landing page
CompanyPageClicks	INTEGER	Yes	Clicks to company page
OneClickLeads	INTEGER	Yes	Lead gen form submissions
OneClickLeadFormOpens	INTEGER	Yes	Lead gen form opens
LeadFormCompletionRate	FLOAT	Yes	Lead form completion rate
VideoViews	INTEGER	Yes	Number of video views
VideoCompletions	INTEGER	Yes	Number of video completions
DownloadClicks	INTEGER	Yes	Document download clicks
DocumentCompletions	INTEGER	Yes	Document ad completions
ExternalWebsiteConversions	INTEGER	Yes	Total external website conversions
ExternalWebsitePostClickConversions	INTEGER	Yes	Post-click website conversions
ExternalWebsitePostViewConversions	INTEGER	Yes	Post-view website conversions
ConversionRate	FLOAT	Yes	Conversion rate

Partitioning & Clustering

• Partitioned by: Date (DAY)
• Partitions: 15
• Clustering: None

Refresh Logic

This is a staging table. Dataslayer overwrites data here on a daily schedule. The refresh script reads from this table and upserts into suse_linkedin_adlevel_final_v2.

Primary keys used for refresh: Date, CampaignGroupID, CampaignID, CreativeID

Sample Query

SELECT
  Date,
  CampaignGroupName,
  CampaignName,
  CreativeName,
  Impressions,
  Clicks,
  Cost,
  OneClickLeads
FROM `paidteam-data-warehouse.customer_ads_suse.suse_linkedin_adlevel_staging_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY Date DESC, Cost DESC
LIMIT 100;

Notes

• LinkedIn uses a different hierarchy than other platforms: CampaignGroup (top) > Campaign (mid) > Creative (bottom). This is equivalent to Campaign > AdGroup > Ad on Google/Microsoft.
• LinkedIn does not have an "AdGroup" concept. The CampaignID in LinkedIn maps to what would be an ad group on other platforms.
• The staging table currently holds only 15 partitions (vs. 56 for Google), indicating a shorter data window in Dataslayer.
• LinkedIn-specific metrics include Reach, Frequency, LandingPageClicks, CompanyPageClicks, OneClickLeads, DownloadClicks, and DocumentCompletions.