unified_ads_enriched (V1 Legacy)

Deprecated -- V1 Legacy

This view is part of the V1 pipeline and is no longer actively maintained. The underlying V1 tables are not refreshed by the current daily pipeline. Use V2 tables and views for all new reporting. A V2 replacement (unified_performance_v2) is planned on the roadmap.

Overview

unified_ads_enriched selects all columns from the unified_ads_base view and adds UDF-parsed metadata fields. By applying the dataset's user-defined functions to campaign and ad group names, it dynamically extracts structured reporting dimensions (Channel, Product, GEO, etc.) without requiring those fields to be stored in the source tables.

View Details

Property	Value
Full path	paidteam-data-warehouse.customer_ads_suse.unified_ads_enriched
Source view	unified_ads_base
Status	Deprecated (V1 Legacy)
Replacement	unified_performance_v2 (planned)

View Logic

CREATE VIEW `paidteam-data-warehouse.customer_ads_suse.unified_ads_enriched` AS
SELECT
  -- All base columns
  Date,
  Platform,
  CampaignID,
  Campaign,
  AdGroup,
  AdName,
  Impressions,
  Clicks,
  Cost,
  Conversions,

  -- UDF-enriched fields
  `customer_ads_suse.get_channel`(Campaign, Platform) AS Channel,
  `customer_ads_suse.get_campaign_code`(Campaign, Platform) AS CampaignCode,
  `customer_ads_suse.get_product_line`(Campaign, Platform) AS ProductLine,
  `customer_ads_suse.get_product`(Campaign, Platform) AS Product,
  `customer_ads_suse.get_subproduct`(Campaign, Platform) AS SubProduct,
  `customer_ads_suse.get_geo`(Campaign, Platform) AS GEO,
  `customer_ads_suse.get_tier`(Campaign, Platform) AS Tier,
  `customer_ads_suse.get_language`(Campaign, Platform) AS Language,
  `customer_ads_suse.get_audience`(Campaign, Platform) AS Audience

FROM `paidteam-data-warehouse.customer_ads_suse.unified_ads_base`;

Output Columns

Base Columns (from unified_ads_base)

Column	Type	Description
Date	DATE	The reporting date
Platform	STRING	Platform identifier (Google, Microsoft, LinkedIn, Reddit, 6sense, StackAdapt)
CampaignID	STRING	Campaign identifier
Campaign	STRING	Campaign name
AdGroup	STRING	Ad group name
AdName	STRING	Ad name
Impressions	INTEGER	Number of impressions
Clicks	INTEGER	Number of clicks
Cost	FLOAT64	Total spend
Conversions	INTEGER	Total conversions

UDF-Enriched Fields

Each of these fields is computed at query time by calling the corresponding UDF with the Campaign name and Platform as inputs.

Column	UDF	Description
Channel	get_channel(Campaign, Platform)	The advertising channel derived from the campaign naming convention (e.g., Paid Search, Paid Social, Display)
CampaignCode	get_campaign_code(Campaign, Platform)	The numeric campaign tracking code extracted from the campaign name (e.g., 0005547)
ProductLine	get_product_line(Campaign, Platform)	The product line (e.g., Business Critical Linux, SUSE Manager, Rancher)
Product	get_product(Campaign, Platform)	The specific product being advertised
SubProduct	get_subproduct(Campaign, Platform)	The sub-product or product variant
GEO	get_geo(Campaign, Platform)	The geographic targeting region (e.g., NA, EMEA, APJ, Global)
Tier	get_tier(Campaign, Platform)	The tier classification for targeting priority
Language	get_language(Campaign, Platform)	The language targeting (e.g., EN, DE, JA)
Audience	get_audience(Campaign, Platform)	The audience segment being targeted

How UDF Parsing Works

The SUSE campaign naming convention encodes metadata in a delimited string:

0005547 | paid_social | BCL | sle_server | na | t1 | en | itdm | gartner_webinar
  ^          ^          ^        ^          ^    ^    ^     ^          ^
  Code    Channel    ProdLine  Product    GEO  Tier Lang Audience  Content

Each UDF extracts its designated segment by splitting on the | delimiter, normalizing whitespace and case, and returning a cleaned value. UDFs are platform-aware: they accept the Platform parameter because field positions may vary across platforms.

Usage Notes

• This view applies UDFs on every query execution. For large date ranges, query performance depends on UDF computation time across all rows.
• UDFs handle NULL and empty campaign names gracefully, returning NULL or a sensible default.
• The Platform parameter passed to each UDF allows platform-specific parsing logic where naming conventions differ.
• Since this view reads from unified_ads_base, it inherits all the platform-specific mappings documented on the unified_ads_base page.

Example Queries

Spend by Product Line and GEO

-- Cross-platform product performance (V1 legacy)
SELECT
  ProductLine,
  GEO,
  Platform,
  SUM(Cost) AS TotalSpend,
  SUM(Clicks) AS TotalClicks,
  SUM(Conversions) AS TotalConversions,
  SAFE_DIVIDE(SUM(Cost), SUM(Conversions)) AS CPA
FROM `paidteam-data-warehouse.customer_ads_suse.unified_ads_enriched`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY ProductLine, GEO, Platform
ORDER BY TotalSpend DESC;

Channel Mix Over Time

-- Monthly channel distribution (V1 legacy)
SELECT
  DATE_TRUNC(Date, MONTH) AS Month,
  Channel,
  SUM(Cost) AS Spend,
  SUM(Impressions) AS Impressions,
  SUM(Conversions) AS Conversions
FROM `paidteam-data-warehouse.customer_ads_suse.unified_ads_enriched`
GROUP BY Month, Channel
ORDER BY Month DESC, Spend DESC;

Audience Performance Comparison

-- Audience segment analysis (V1 legacy)
SELECT
  Audience,
  Language,
  COUNT(DISTINCT CampaignID) AS Campaigns,
  SUM(Cost) AS TotalSpend,
  SUM(Clicks) AS TotalClicks,
  SAFE_DIVIDE(SUM(Clicks), SUM(Impressions)) AS AvgCTR,
  SAFE_DIVIDE(SUM(Cost), SUM(Conversions)) AS CPA
FROM `paidteam-data-warehouse.customer_ads_suse.unified_ads_enriched`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  AND Audience IS NOT NULL
GROUP BY Audience, Language
ORDER BY TotalSpend DESC;

Dependencies

• Views: unified_ads_base (V1 legacy)
• UDFs: All 9 metadata UDFs (get_channel, get_campaign_code, get_product_line, get_product, get_subproduct, get_geo, get_tier, get_language, get_audience)
• Consumers: Legacy Looker Studio cross-platform dashboards

Migration Path

When unified_performance_v2 is built, it will read from the V2 _final_v2 tables, include creative metadata, and apply the same UDF enrichment. Existing Looker Studio dashboards should be migrated to the new view at that time.