unified_performance_v2

This view combines all 6 ad platforms into a single cross-platform reporting interface. It uses a force-swap approach to map each platform's naming convention column into a universal CampaignName, enabling all UDFs to operate on a single column without CASE logic.

View Details

Property	Value
Full path	paidteam-data-warehouse.customer_ads_suse.unified_performance_v2
Architecture	Two-layer: CTE (UNION ALL + JOINs) + enrichment wrapper
Total columns	35 (31 original + 4 status enrichment)
Total rows	~837K
Platforms	Google Ads, Microsoft Ads, LinkedIn Ads, Reddit Ads, 6sense, StackAdapt
Created	2026-02-08
Updated	2026-02-09 — Status enrichment (4 new columns)

Architecture

Layer 1 (CTE "base"):
  UNION ALL of 6 platform adlevel_final_v2 tables
  + LEFT JOIN creative_final_v2 for LinkedIn & Reddit
  + Force-swap: CampaignName = naming convention for ALL platforms
  + AdCreativeName and DestinationURL as separate columns

Layer 2 (SELECT from base):
  + 4 calculated rates (CTR, CPC, CPM, CPA)
  + 8 metadata UDFs on CampaignName (no CASE needed)
  + CampaignCode (1 CASE: URL vs creative name)
  + ContentName (1 CASE: URL extraction vs creative name parsing)
  + 3 status columns + IsActive boolean
  = 35 total columns → Looker Studio data source

Source Tables

#	Table	Rows	JOIN
1	suse_google_adlevel_final_v2	213K	None
2	suse_microsoft_adlevel_final_v2	145K	None
3	suse_linkedin_adlevel_final_v2	243K	LEFT JOIN suse_linkedin_creative_final_v2 ON CreativeID
4	suse_reddit_adlevel_final_v2	39K	LEFT JOIN suse_reddit_creative_final_v2 ON CampaignID+AdgroupID+AdID
5	suse_6sense_adlevel_final_v2	193K	None
6	suse_stackadapt_adlevel_final_v2	4K	None
7	suse_linkedin_creative_final_v2	1.7K	Joined into #3
8	suse_reddit_creative_final_v2	666	Joined into #4

Force-Swap Mapping

The core design principle: CampaignName always contains the pipe-delimited naming convention, regardless of which source column it actually comes from. The "other" hierarchy level is placed into AdGroupName.

Why Force-Swap?

All 8 metadata UDFs (get_channel, get_product_line, get_product, get_subproduct, get_geo, get_tier, get_language, get_audience) parse the pipe-delimited naming convention. By force-mapping this into a single CampaignName column for every platform, the enrichment layer calls all UDFs with b.CampaignName -- zero CASE logic needed.

Per-Platform Hierarchy Mapping

Unified Column	Google	Microsoft	LinkedIn	Reddit	6sense	StackAdapt
CampaignID	a.CampaignID	a.CampaignID	a.CampaignID	a.CampaignID	a.Campaign_ID	a.Campaign_ID
CampaignName	a.Campaign	a.CampaignName	a.CampaignName	a.Adgroupname	a.Campaign	a.Campaign
AdGroupID	a.AdgroupID	a.AdGroupID	a.CampaignGroupID	a.AdgroupID	a.AdGroup_ID	a.AdGroup_ID
AdGroupName	a.Adgroup	a.AdGroupName	a.CampaignGroupName	a.Campaignname	a.AdGroup	a.AdGroup
AdID	a.AdID	a.AdID	a.CreativeID	a.AdID	a.Ad_ID	a.Ad_ID
AdCreativeName	a.Adname	a.AdTitle	a.CreativeName	a.Adname	a.Ad	a.Ad
DestinationURL	a.CreativeFinalURL	a.FinalUrl	a.CreativeDestinationURL	c.AdClickURL	NULL	a.Click_URL

LinkedIn Force-Swap

LinkedIn has a 3-level hierarchy: CampaignGroup (top) > Campaign (mid) > Creative (ad). The naming convention lives in CampaignName (mid-level). So:

• CampaignName = LinkedIn's CampaignName (mid-level, where convention lives)
• AdGroupName = LinkedIn's CampaignGroupName (top-level short label, demoted)
• AdGroupID = LinkedIn's CampaignGroupID

Reddit Force-Swap

Reddit encodes the full naming convention in Adgroupname (not Campaignname). So:

• CampaignName = Reddit's Adgroupname (where convention lives)
• AdGroupName = Reddit's Campaignname (short label, demoted)

Column Groups

Group A: Date & Platform (2)

Column	Type	Description
Date	DATE	Reporting date (partition key in source tables)
Platform	STRING	Platform label: Google Ads, Microsoft Ads, LinkedIn Ads, Reddit Ads, 6sense Ads, StackAdapt Ads

Group B: Unified Hierarchy (6)

Column	Type	Description
CampaignID	STRING	Campaign identifier (CAST to STRING for cross-platform consistency)
CampaignName	STRING	Force-mapped naming convention. Always the pipe-delimited string. Feeds all 8 metadata UDFs.
AdGroupID	STRING	Ad group identifier (LinkedIn: CampaignGroupID; others: native)
AdGroupName	STRING	Ad group name (LinkedIn: CampaignGroupName; Reddit: Campaignname short label)
AdID	STRING	Ad/creative identifier (LinkedIn: CreativeID)
AdCreativeName	STRING	Actual creative/ad name (not force-mapped)

Group C: URL (1)

Column	Type	Description
DestinationURL	STRING	Landing page URL. NULL for 6sense. From creative table for LinkedIn/Reddit.

Group D: Platform Context (1)

Column	Type	Description
ChannelType	STRING	Platform-native channel type (SEARCH, DISPLAY, SPONSORED_STATUS_UPDATE, ABM Display, etc.)

Group E: Core Metrics (3)

Column	Type	Description
Impressions	INT64	Total impressions
Clicks	INT64	Total clicks
Cost	FLOAT64	Spend in USD (Google: CostUSD, Reddit: Spend, others: Cost)

Group F: Row-Level Rates (4)

These are per-row (Date + Ad) rates with a trailing underscore to avoid naming conflicts with Looker Studio calculated fields. In Looker Studio, create calculated fields named CTR, CPC, CPM, CPA that properly aggregate: e.g., CTR = SUM(Clicks) / SUM(Impressions).

Column	Type	Formula
CTR_	FLOAT64	SAFE_DIVIDE(Clicks, Impressions)
CPC_	FLOAT64	SAFE_DIVIDE(Cost, Clicks)
CPM_	FLOAT64	SAFE_DIVIDE(Cost * 1000, Impressions)
CPA_	FLOAT64	SAFE_DIVIDE(Cost, NULLIF(TotalConversions, 0))

Group G: Conversions (1)

Column	Type	Description
TotalConversions	FLOAT64	Composite per platform (see below)

Per-platform TotalConversions formula:

Platform	Formula
Google	a.Conversions
Microsoft	CAST(a.Conversions AS FLOAT64)
LinkedIn	OneClickLeads + DocumentCompletions + ExternalWebsiteConversions
Reddit	RedditLeads + ConversionLeadClicks + ConversionSignUpClicks
6sense	0.0 (not tracked)
StackAdapt	CAST(a.Conversions AS FLOAT64)

Group H: Engagement (2)

Column	Type	Description
Reach	INT64	Unique users/accounts reached. LinkedIn, Reddit, 6sense only. NULL for others.
Frequency_	FLOAT64	Avg impressions per user. LinkedIn and Reddit only. NULL for others. Trailing underscore for Looker Studio naming.

Group I: UDF Enrichment (10)

Column	UDF	Input	Description
Channel	get_channel	CampaignName	Marketing channel (Search, Paid Social, Display, etc.)
ProductLine	get_product_line	CampaignName, Platform	Product line (Linux, Cloud Native, AI, Edge)
Product	get_product	CampaignName, Platform	Specific product (SLES, Rancher, MLS, etc.)
Campaign	get_subproduct	CampaignName, Platform	Campaign-level detail (was SubProduct). Hierarchy: ProductLine > Product > Campaign
GEO	get_geo	CampaignName, Platform	Geographic region (NA, EMEA, APAC, LATAM, Global)
Tier	get_tier	CampaignName, Platform	Market tier (T1, T2, T1+2, etc.)
Language	get_language	CampaignName, Platform	Language code (EN, DE, FR, etc.)
Audience	get_audience	CampaignName	Audience segment (Prospecting, Retargeting, TOFU, etc.)
CampaignCode	get_campaign_code	see below	Numeric campaign tracking code
ContentName	see below	see below	Human-readable content name

CampaignCode routing (1 CASE):

get_campaign_code(
  b.CampaignName,
  CASE WHEN b.Platform IN ('Google Ads', 'Microsoft Ads')
       THEN b.DestinationURL       -- URL with utm_campaign
       ELSE b.AdCreativeName       -- creative name with code prefix
  END,
  b.Platform
)

ContentName routing (1 CASE):

CASE
  WHEN b.Platform IN ('Google Ads', 'Microsoft Ads')
  THEN get_content_name_from_url(b.DestinationURL, b.Platform)  -- extract from URL
  ELSE get_content_name(b.AdCreativeName, b.CampaignName)       -- parse creative name
END

ContentName coverage: 6/6 platforms (was 4/6 in V1).

Group J: Audit (1)

Column	Type	Description
_ingested_at	TIMESTAMP	Source table ingestion timestamp

Group K: Status Enrichment (4)

Column	Type	Description
CampaignStatus	STRING	Normalized: ACTIVE, PAUSED, REMOVED, COMPLETED. Default PAUSED if not found.
AdGroupStatus	STRING	Same normalized values as CampaignStatus.
AdStatus	STRING	Same normalized values as CampaignStatus.
IsActive	BOOLEAN	TRUE only when all 3 statuses are ACTIVE.

Status Normalization

All raw platform status values are normalized via the normalize_status UDF. Google's ENABLED becomes ACTIVE, Microsoft/6sense Title Case becomes UPPER CASE. If an adlevel row has no matching status table row, all 3 statuses default to PAUSED.

LinkedIn Status Mapping

LinkedIn status columns follow the force-swap hierarchy:

• CampaignStatus ← LinkedIn's CampaignStatus (mid-level, where naming convention lives)
• AdGroupStatus ← LinkedIn's CampaignGroupStatus (top-level, swapped!)
• AdStatus ← LinkedIn's CreativeStatus (bottom-level)

Status source tables:

Platform	Status Table	JOIN Keys
Google	suse_google_status_final_v2	CampaignID + AdgroupID + AdID
Microsoft	suse_microsoft_status_final_v2	CampaignID + AdGroupID + AdID
LinkedIn	suse_linkedin_status_final_v2	CampaignGroupID + CampaignID + CreativeID
Reddit	suse_reddit_status_final_v2	CampaignID + AdgroupID + AdID
6sense	suse_6sense_status_final_v2	Campaign_ID + AdGroup_ID + Ad_ID
StackAdapt	(none — hardcoded PAUSED)	—

Example Queries

Cross-Platform Spend by Product Line (Last 30 Days)

SELECT
  Platform,
  ProductLine,
  Product,
  SUM(Cost) AS TotalSpend,
  SUM(Impressions) AS TotalImpressions,
  SUM(Clicks) AS TotalClicks,
  SAFE_DIVIDE(SUM(Clicks), SUM(Impressions)) AS AvgCTR,
  SAFE_DIVIDE(SUM(Cost), SUM(Clicks)) AS AvgCPC
FROM `paidteam-data-warehouse.customer_ads_suse.unified_performance_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY Platform, ProductLine, Product
ORDER BY TotalSpend DESC
LIMIT 20;

Content Performance Across All Platforms

SELECT
  ContentName,
  Platform,
  CampaignCode,
  SUM(Cost) AS TotalSpend,
  SUM(Impressions) AS TotalImpressions,
  SUM(Clicks) AS TotalClicks,
  SAFE_DIVIDE(SUM(Cost), NULLIF(SUM(TotalConversions), 0)) AS AvgCPA
FROM `paidteam-data-warehouse.customer_ads_suse.unified_performance_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND ContentName != 'Unknown'
GROUP BY ContentName, Platform, CampaignCode
ORDER BY TotalSpend DESC
LIMIT 30;

GEO Performance Breakdown

SELECT
  GEO,
  Channel,
  COUNT(DISTINCT Platform) AS PlatformCount,
  SUM(Cost) AS TotalSpend,
  SUM(Clicks) AS TotalClicks,
  SAFE_DIVIDE(SUM(Clicks), SUM(Impressions)) AS AvgCTR,
  SAFE_DIVIDE(SUM(Cost), NULLIF(SUM(TotalConversions), 0)) AS AvgCPA
FROM `paidteam-data-warehouse.customer_ads_suse.unified_performance_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY GEO, Channel
ORDER BY TotalSpend DESC;

Platform Comparison by Month

SELECT
  DATE_TRUNC(Date, MONTH) AS Month,
  Platform,
  SUM(Cost) AS MonthlySpend,
  SUM(Impressions) AS MonthlyImpressions,
  SUM(Clicks) AS MonthlyClicks,
  SUM(TotalConversions) AS MonthlyConversions,
  SAFE_DIVIDE(SUM(Cost), NULLIF(SUM(TotalConversions), 0)) AS MonthlyCPA
FROM `paidteam-data-warehouse.customer_ads_suse.unified_performance_v2`
GROUP BY Month, Platform
ORDER BY Month DESC, MonthlySpend DESC;

Design Decisions

Separate AdCreativeName + DestinationURL

Unlike V1 which collapsed creative names and URLs into a single AdName column, V2 keeps them separate:

• AdCreativeName = the actual creative/ad name (useful for LinkedIn, Reddit, 6sense, StackAdapt)
• DestinationURL = the landing page URL (useful for Google, Microsoft campaign code extraction)

This enables the enrichment layer to use CASE logic to pick the right column for get_campaign_code and get_content_name without losing information.

Creative JOINs in Layer 1

LinkedIn and Reddit benefit from creative table JOINs to get richer name and URL data. The LEFT JOIN ensures all adlevel rows are preserved even without a creative match. COALESCE prefers creative table values but falls back to adlevel values.

ContentName from URLs

The new get_content_name_from_url UDF extracts content names from utm_campaign URL parameters for Google and Microsoft, solving the V1 limitation where these platforms returned "Unknown" for ContentName.

Extensibility

The two-layer architecture supports easy extension:

Change	How
New platform	Add one SELECT block to the UNION ALL in Layer 1
New creative JOIN	Add LEFT JOIN in the platform's block in Layer 1
New UDF column	Add one line in Layer 2
New data source JOIN	Add LEFT JOIN in Layer 2 against the base CTE

Dependencies

• Tables: All 6 adlevel_final_v2 tables + 2 creative_final_v2 tables (LinkedIn, Reddit) + 5 status_final_v2 tables (Google, Microsoft, LinkedIn, Reddit, 6sense)
• UDFs: All 12 UDFs (get_channel, get_product_line, get_product, get_subproduct, get_geo, get_tier, get_language, get_audience, get_campaign_code, get_content_name, get_content_name_from_url, normalize_status)
• Consumers: Looker Studio cross-platform dashboards
• Spec document: UNIFIED_VIEW_SPEC.md
• Mapping reference: UDF_MAPPING_REPORT.md