Views Overview

The customer_ads_suse dataset contains 5 views that provide ready-to-query interfaces for reporting and analysis. One is the primary V2 cross-platform unified view, two are V2 creative performance views, and two are legacy V1 cross-platform unions.

V2 Unified Cross-Platform View

The unified performance view is the primary data source for cross-platform Looker Studio dashboards. It combines all 6 ad platforms into a single 31-column interface with full UDF enrichment.

View	Description	Architecture
unified_performance_v2	Cross-platform view combining all 6 platforms with force-mapped naming conventions, creative JOINs for LinkedIn & Reddit, and 11 UDF-enriched fields	Two-layer: UNION ALL CTE + enrichment wrapper

How the Unified View Works

6 adlevel_final_v2 tables  ──┐
                              ├──  UNION ALL (force-mapped)  ──  UDF enrichment  ──  31-column output
2 creative_final_v2 tables ───┘    + creative LEFT JOINs         + calculated rates

Key design features:

1. Force-swap mapping: CampaignName always contains the pipe-delimited naming convention for every platform, enabling all 8 metadata UDFs to operate on a single column
2. Separate AdCreativeName + DestinationURL: V2 keeps creative names and landing page URLs as distinct columns
3. ContentName for 6/6 platforms: Uses get_content_name_from_url for Google/Microsoft URLs and get_content_name for other platforms' creative names

V2 Creative Performance Views

These views join creative metadata tables with adlevel performance tables, then enrich every row with UDF-parsed campaign metadata fields. They are the primary data sources for Looker Studio creative analysis dashboards.

View	Description	Join Strategy
linkedin_creative_performance_v2	Joins LinkedIn creative metadata with adlevel performance data, enriched with all 10 UDF fields	LEFT JOIN on CreativeID
reddit_creative_performance_v2	Joins Reddit creative metadata with adlevel performance data, enriched with all 10 UDF fields	LEFT JOIN on CampaignID + AdgroupID + AdID (composite key)

How Creative Performance Views Work

final_v2 (adlevel)  ──┐
                       ├──  LEFT JOIN  ──  UDF enrichment  ──  View output
creative_final_v2  ────┘

Each view:

1. Selects all date-level performance rows from the adlevel final table
2. Left-joins with the creative final table to attach ad copy, image URLs, and creative metadata
3. Applies all 10 UDFs to parse campaign naming conventions into structured fields (Channel, CampaignCode, ProductLine, Product, SubProduct, GEO, Tier, Language, Audience, ContentName)
4. Computes calculated metrics such as TotalConversions and CPA

V2 Budget Pacing Views

These views compare quarterly budgets (maintained in Google Sheets) against actual ad spend from the unified performance view. They calculate pacing ratios, projections, and remaining budget for Looker Studio dashboards.

View	Description	Budget Source
budget_pacing_platform_v2	Pacing by ad platform (Google Ads, LinkedIn Ads, etc.)	suse_budget_platform_final_v2
budget_pacing_region_v2	Pacing by geographic region (EMEA, NA, APAC, LATAM)	suse_budget_region_final_v2
budget_pacing_summary_v2	UNION ALL of platform + region for a single Looker Studio data source	Both

How Budget Pacing Works

Google Sheet (customer edits)
       |
  External Tables (federated)
       |
  Daily Refresh (UNPIVOT wide→long)
       |
  Final Budget Tables  ──┐
                          ├──  Pacing Views  ──  Looker Studio
  unified_performance_v2 ─┘    (JOIN + calculate ratios)

Key metric: PacingRatio = (% budget spent) / (% time elapsed). A value of 1.0 means perfectly on pace.

V1 Legacy Views

These views were built for the original pipeline and are deprecated. They remain available for backward compatibility with existing Looker Studio dashboards.

View	Description	Status
unified_ads_base	UNION ALL of all 6 platform final tables into a single cross-platform table with standardized column names	⚠️ Legacy
unified_ads_enriched	Selects from unified_ads_base and adds UDF-parsed metadata fields for dynamic reporting	⚠️ Legacy

Deprecated

The V1 legacy views (unified_ads_base and unified_ads_enriched) reference V1 tables that are no longer refreshed. Use the V2 unified performance view or creative performance views for all new work. The V2 replacement unified_performance_v2 is now live and replaces both V1 views.

Quick Reference

-- List all views in the dataset
SELECT table_name, view_definition
FROM `paidteam-data-warehouse.customer_ads_suse.INFORMATION_SCHEMA.VIEWS`
ORDER BY table_name;