Changelog

All notable changes to the SUSE Paid Advertising Data Warehouse V2 are documented here. Entries are listed in reverse chronological order.

2026-02-09

UDF Dimension Normalization — Phase 1-3 Deployed

Deployed normalization fixes to all three dimension UDFs (get_product_line, get_product, get_subproduct). Unknown spend reduced from ~$316K to ~$81K (74% reduction). Catalog combinations reduced from 52 to 44.

Changes:

• get_subproduct: Added normalization layer (CASE LOWER(TRIM(raw))) — the last UDF to get this pattern. Fixes: Always on → Always On, VW ABM → Volkswagen ABM, MLS Umbrella → Umbrella. Also fixed the AI| regex bug (missing space before pipe in Google SRCH campaigns).
• get_product: Added 5 new normalization entries (ECM + Security → ECM, Kubenetes → Kubernetes, Horizontal → Edge, CentOS → MLS, SAP → SLES). Expanded regex allowlists across all branches.
• All 3 UDFs: Regex tolerance improvements — compound quarters (Q2+3), missing spaces around pipes, YTB YouTube channel support. Unified product token lists across all platform branches.

See Dimension Normalization Rules for full details.

Customer Input Needed Page

Created Customer Input Needed page with 8 business decisions requiring customer input, backed by real campaign name examples from ad platforms.

Campaign Dimension Catalog View

Created campaign_dimension_catalog_v2 -- a new view that surfaces all unique ProductLine x Product x Campaign combinations from unified_performance_v2. This is Phase 1 of the Campaign Dimension Mapping System.

New view: campaign_dimension_catalog_v2 (44 unique combinations after normalization fixes)

Column	Type	Description
ProductLine	STRING	Top-level product category
Product	STRING	Specific product
Campaign	STRING	Campaign-level detail (sub-product)
PlatformCount	INT64	Number of platforms running this combination
Platforms	STRING	Comma-separated platform list
TotalSpend	FLOAT64	Total spend in USD
TotalImpressions	INT64	Total impressions
FirstSeen / LastSeen	DATE	Date range for this combination
TotalRows	INT64	Total adlevel rows

Purpose: Enables discovery of all dimension combinations, prioritized by spend, for seeding a BusinessUnit and GlobalCampaign mapping sheet (Phase 2). The mapping will eventually enrich unified_performance_v2 with two new columns (Phase 3).

Status Enrichment for unified_performance_v2

Enriched the unified performance view with campaign, ad group, and ad status columns from 5 status tables. The view grows from 31 to 35 columns.

New columns:

Column	Type	Description
CampaignStatus	STRING	Normalized: ACTIVE, PAUSED, REMOVED, COMPLETED
AdGroupStatus	STRING	Same normalized values
AdStatus	STRING	Same normalized values
IsActive	BOOLEAN	TRUE only when all 3 are ACTIVE

New UDF: normalize_status — maps platform-specific status values (Google ENABLED, Microsoft Active, etc.) to a unified UPPER CASE standard.

Key design decisions:

• Status JOINs happen inside each platform CTE (Layer 1) for clean separation
• Missing status rows default to PAUSED (conservative safeguard — no NULLs)
• LinkedIn status follows the force-swap: CampaignStatus ← LinkedIn mid-level, AdGroupStatus ← LinkedIn top-level CampaignGroupStatus
• StackAdapt has no status table — all rows default to PAUSED

Status tables joined:

• suse_google_status_final_v2 (2,689 rows)
• suse_microsoft_status_final_v2 (1,136 rows)
• suse_linkedin_status_final_v2 (1,571 rows)
• suse_reddit_status_final_v2 (248 rows)
• suse_6sense_status_final_v2 (1,018 rows)

LinkedIn and Reddit Status Tables

Added two new status tables to BigQuery:

• suse_linkedin_status_final_v2 (1,571 rows) — 3-level hierarchy: CampaignGroupStatus, CampaignStatus, CreativeStatus. Values: ACTIVE, COMPLETED, PAUSED.
• suse_reddit_status_final_v2 (248 rows) — Campaigneffectivestatus, EffectiveStatus, AdEffectiveStatus. Currently all ACTIVE.

6sense Campaign Status Table

Created suse_6sense_status_final_v2 -- a new status lookup table for 6sense campaigns, ad groups, and ads. This is the third status table in the warehouse, joining Google and Microsoft.

New table: suse_6sense_status_final_v2 (1,018 rows)

Column	Type	Description
Campaign_ID	STRING	6sense campaign ID
AdGroup_ID	STRING	6sense ad group ID
Ad_ID	STRING	6sense ad ID
Campaign	STRING	Campaign name
AdGroup	STRING	Ad group name
Ad	STRING	Ad name
CampaignStatus	STRING	Active, Completed, Paused
AdGroupStatus	STRING	Ad group status
AdStatus	STRING	Ad status

Apps Script changes: Status extraction is integrated as a step within the existing 6sense processing workflows (both manual sheet-based and V3 Drive processing). New functions: format6senseStatusData(), v3_format6senseStatusInMemory(), upload6SenseStatusToBigQuery().

Status breakdown: 920 Active, 98 Completed.

Getting Started Documentation

Added a Getting Started page to the documentation site, providing a simple overview of data sources, ingestion methods (automated vs manual), and how to troubleshoot common issues. Clarifies that 6sense and StackAdapt require manual uploads via Google Sheets.

Status Tables Documentation

Added a Campaign Status Tables page documenting all three status tables (Google, Microsoft, 6sense) with schemas, join examples, and notes on status value formats.

---

2026-02-08

Budget Pacing System Deployed

Built a complete budget pacing pipeline: Google Sheets → BigQuery external tables → daily refresh → pacing views.

New tables:

• suse_budget_platform_staging_v2 — External table reading Platform_Budget sheet tab
• suse_budget_region_staging_v2 — External table reading Region_Budget sheet tab
• suse_budget_config_staging_v2 — External table reading Quarter_Config sheet tab
• suse_budget_platform_final_v2 — 24 rows (6 platforms × 4 quarters, long format)
• suse_budget_region_final_v2 — 8 rows (4 regions × 2 active quarters)
• suse_budget_config_v2 — SUSE fiscal quarter date ranges (FY starts Nov 1)
• suse_budget_refresh_log_v2 — Refresh operation tracking

New views:

• budget_pacing_platform_v2 — Budget vs actual spend by platform with pacing metrics
• budget_pacing_region_v2 — Budget vs actual spend by region (GEO)
• budget_pacing_summary_v2 — UNION ALL of platform + region for Looker Studio

Pacing metrics calculated: QTD/MTD Spend, BudgetRemaining, PctBudgetConsumed, PctTimeElapsed, PacingRatio (1.0 = on pace), ProjectedSpend, ProjectedVariance.

Phase 2 (future): Product Line pacing, Campaign Objective pacing, get_campaign_objective() UDF.

unified_performance_v2 View Deployed

Created the cross-platform unified performance view combining all 6 ad platforms into a single 31-column interface. Key features:

• Force-swap mapping: CampaignName always contains the pipe-delimited naming convention for every platform (LinkedIn CampaignName stays; Reddit Adgroupname is swapped to CampaignName)
• Separate columns: AdCreativeName (actual name) + DestinationURL (landing page URL) replace the V1 single AdName column
• Creative JOINs: LEFT JOIN for LinkedIn (on CreativeID) and Reddit (on composite key)
• Full UDF enrichment: 8 metadata UDFs + CampaignCode + ContentName = 10 enriched columns
• ContentName coverage: 6/6 platforms (was 4/6 in V1)
• ~837K rows across all platforms

-- The view is now available for cross-platform queries:
SELECT Platform, ProductLine, SUM(Cost) AS Spend
FROM `paidteam-data-warehouse.customer_ads_suse.unified_performance_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY Platform, ProductLine
ORDER BY Spend DESC;

get_content_name_from_url UDF v1.0.0

Created new UDF to extract human-readable content names from utm_campaign URL parameters. Used by unified_performance_v2 for Google Ads and Microsoft Ads, where creative names are unavailable.

• 9-step regex pipeline: extract utm_campaign, strip suffixes (mediaplan, language, funnel, geo), strip campaign code prefix, remove platform tokens, clean up
• ~80% clean extraction rate for standard campaign URL formats

-- Example: Google URL → content name
SELECT `customer_ads_suse.get_content_name_from_url`(
  'https://www.suse.com/lp?utm_campaign=5_0003991_Gartner_Edge_Google_Search_TOFU_RG_GLB_mp_12345_en',
  'Google Ads'
);
-- Result: "gartner edge"

Initial V2 Documentation Site

Created the VitePress documentation site for the data warehouse, covering architecture, UDFs, workflows, data dictionary, and platform references.

get_content_name UDF v1.2.0

Added Thought Leadership special case handling to the get_content_name UDF. When a campaign name contains "Thought Leadership" and the creative name is NULL or empty, the function now returns "sponsored thought leadership post" instead of an empty or unknown value.

-- Before v1.2.0: returned empty string or NULL
-- After v1.2.0: returns "sponsored thought leadership post"
SELECT customer_ads_suse.get_content_name(NULL, 'SUSE | PSOC | NA | T1 | EN | Thought Leadership');
-- Result: "sponsored thought leadership post"

Google VideoViewsrate Schema Fix

Changed the VideoViewsrate column type from INTEGER to FLOAT64 in both the Google adlevel staging and final tables. Video view rates are decimal percentages and were being truncated to whole numbers under the previous type.

ALTER TABLE `paidteam-data-warehouse.customer_ads_suse.suse_google_adlevel_staging_v2`
ALTER COLUMN VideoViewsrate SET DATA TYPE FLOAT64;

ALTER TABLE `paidteam-data-warehouse.customer_ads_suse.suse_google_adlevel_final_v2`
ALTER COLUMN VideoViewsrate SET DATA TYPE FLOAT64;

udf_metadata Table Recreated

The udf_metadata table was recreated to track UDF versions and changes. This table stores the name, version, description, update timestamp, and author for each UDF modification.

---

2026-02-07

Reddit Creative Table and Performance View

Created the Reddit creative pipeline:

• suse_reddit_creative_staging_v2 -- staging table for Reddit creative metadata
• suse_reddit_creative_final_v2 -- final table with _ingested_at audit column
• reddit_creative_performance_v2 -- view joining Reddit creative and adlevel data with UDF-derived metadata

Reddit uses a composite primary key (CampaignID + AdGroupID + AdID) for creative record matching in the MERGE operation.

LinkedIn Creative Table and Performance View

Created the LinkedIn creative pipeline:

• suse_linkedin_creative_staging_v2 -- staging table for LinkedIn creative metadata
• suse_linkedin_creative_final_v2 -- final table with _ingested_at audit column
• linkedin_creative_performance_v2 -- view joining LinkedIn creative and adlevel data with UDF-derived metadata

LinkedIn uses a single primary key (CreativeID) for creative record matching in the MERGE operation.

V2 Refresh Scripts Deployed

Deployed the two main refresh scripts as scheduled queries in BigQuery:

• Creative Refresh Script -- runs at 07:30 UTC daily, performs MERGE operations for each platform's creative tables
• Adlevel V2 Refresh Script -- runs at 08:00 UTC daily, performs DELETE + INSERT for each platform's adlevel tables

Both scripts use BEGIN...EXCEPTION...END blocks for platform isolation and log all results to refresh_log_v2 and creative_refresh_log_v2 respectively.

---

2026-01-26

Initial V2 Migration

Created all V2 adlevel tables for the six advertising platforms:

Staging tables:

• suse_google_adlevel_staging_v2
• suse_microsoft_adlevel_staging_v2
• suse_linkedin_adlevel_staging_v2
• suse_reddit_adlevel_staging_v2
• suse_6sense_adlevel_staging_v2
• suse_stackadapt_adlevel_staging_v2

Final tables (partitioned by Date, clustered by IDs):

• suse_google_adlevel_final_v2
• suse_microsoft_adlevel_final_v2
• suse_linkedin_adlevel_final_v2
• suse_reddit_adlevel_final_v2
• suse_6sense_adlevel_final_v2
• suse_stackadapt_adlevel_final_v2

Supporting tables:

• refresh_log_v2 -- adlevel refresh execution log
• creative_refresh_log_v2 -- creative refresh execution log

All 11 UDFs were created or migrated to the V2 schema:

• 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 (added 2026-02-08)