suse_linkedin_adlevel_final_v2

Full reference: paidteam-data-warehouse.customer_ads_suse.suse_linkedin_adlevel_final_v2Type: Final | Rows: 243,168 | Size: 140.1 MB

LinkedIn Ads ad-level final data (V2). Contains the complete historical record of LinkedIn Ads performance, refreshed daily from staging via a rolling 60-day DELETE+INSERT process.

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)
CreativeID	STRING	Yes	LinkedIn creative ID (equivalent to ad ID)
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
_ingested_at	TIMESTAMP	Yes	Timestamp when the row was inserted by the refresh script

Partitioning & Clustering

• Partitioned by: Date (DAY)
• Partitions: 450
• Clustered by: CampaignName, CampaignGroupName

Refresh Logic

The scheduled refresh script performs the following for LinkedIn:

1. Read all rows from suse_linkedin_adlevel_staging_v2
2. Identify the MIN(Date) and MAX(Date) range in staging
3. DELETE rows in this final table matching that date range AND the same CampaignGroupID, CampaignID, CreativeID combinations
4. INSERT all staging rows with _ingested_at = CURRENT_TIMESTAMP()
5. Log the operation to refresh_log_v2

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

Sample Query

SELECT
  Date,
  CampaignGroupName,
  CampaignName,
  CreativeType,
  SUM(Impressions) AS total_impressions,
  SUM(Clicks) AS total_clicks,
  SUM(Cost) AS total_cost,
  SUM(OneClickLeads) AS total_leads
FROM `paidteam-data-warehouse.customer_ads_suse.suse_linkedin_adlevel_final_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY Date, CampaignGroupName, CampaignName, CreativeType
ORDER BY Date DESC
LIMIT 100;

Notes

• LinkedIn uses CampaignGroupID + CampaignID + CreativeID as its composite key. This differs from Google/Microsoft which use CampaignID + AdGroupID + AdID.
• With 243,168 rows and 450 partitions, this is the largest table by row count in the V2 warehouse.
• Clustering is on CampaignName and CampaignGroupName to support dashboard filtering.
• The CreativeID on LinkedIn is globally unique, but the refresh script uses the full composite key for correctness.