User-Defined Functions

The SUSE Paid Advertising Data Warehouse uses 11 SQL User-Defined Functions (UDFs) to parse campaign naming conventions and extract content metadata at query time. Rather than storing parsed metadata in separate columns, these functions extract structured fields directly from campaign names, ad names, and URLs on the fly.

This approach ensures that:

• Metadata stays in sync with the source campaign names
• No ETL step is needed to populate parsed fields
• Changes to parsing logic take effect immediately across all queries and views

All UDFs live in the customer_ads_suse dataset within the paidteam-data-warehouse project.

UDF Reference

Function	Signature	Return Type	Description
get_channel	get_channel(campaign STRING)	STRING	Extracts marketing channel from campaign abbreviation (e.g., SRCH, PSOC, DISP)
get_product_line	get_product_line(campaign STRING, platform STRING)	STRING	Determines product line (Linux, Cloud Native, AI, Edge, etc.)
get_product	get_product(campaign STRING, platform STRING)	STRING	Identifies specific product (SLES, Rancher, MLS, etc.)
get_subproduct	get_subproduct(campaign STRING, platform STRING)	STRING	Extracts free-text sub-product from campaign name
get_geo	get_geo(campaign STRING, platform STRING)	STRING	Determines geographic region (NA, EMEA, APAC, LATAM, Global)
get_tier	get_tier(campaign STRING, platform STRING)	STRING	Extracts market tier designation (T1, T2, T1+2, etc.)
get_language	get_language(campaign STRING, platform STRING)	STRING	Identifies language code (EN, DE, FR, ES, etc.)
get_audience	get_audience(campaign STRING)	STRING	Identifies audience targeting type (Prospecting, Retargeting, TOFU, etc.)
get_campaign_code	get_campaign_code(campaign STRING, ad_name STRING, platform STRING)	STRING	Extracts numeric campaign tracking code (5-12 digits)
get_content_name	get_content_name(creative_name STRING, campaign_name STRING)	STRING	Extracts human-readable content name from creative naming patterns
get_content_name_from_url	get_content_name_from_url(url STRING, platform STRING)	STRING	Extracts content name from utm_campaign URL parameter (Google/Microsoft)

Platform Awareness

Most UDFs accept a platform parameter because campaign naming conventions differ across advertising platforms. The platform value should match one of:

• google
• microsoft
• linkedin
• reddit
• 6sense
• stackadapt

Two UDFs are not platform-aware and do not require a platform parameter:

• get_channel -- channel abbreviations are consistent across all platforms
• get_audience -- audience keywords are consistent across all platforms

Note: get_content_name_from_url accepts a platform parameter but does not currently use it internally (reserved for future platform-specific extraction rules).

Design Principles

1. Deterministic -- Same input always produces the same output
2. NULL-safe -- Returns sensible defaults for NULL or empty inputs
3. Case-insensitive -- Normalizes input case internally before matching
4. Platform-aware -- Accepts a platform parameter when field positions vary by platform
5. Version tracked -- Changes are logged to the udf_metadata table

Usage in Views

UDFs are called extensively in the unified_performance_v2 view and the creative performance views to enrich raw ad data with parsed campaign metadata:

-- In unified_performance_v2, all 8 metadata UDFs operate on the
-- force-mapped CampaignName column (no CASE logic needed):
customer_ads_suse.get_channel(b.CampaignName) AS Channel,
customer_ads_suse.get_product_line(b.CampaignName, b.Platform) AS ProductLine,
customer_ads_suse.get_geo(b.CampaignName, b.Platform) AS GEO,

-- ContentName uses CASE routing to pick the right UDF:
CASE
  WHEN b.Platform IN ('Google Ads', 'Microsoft Ads')
  THEN customer_ads_suse.get_content_name_from_url(b.DestinationURL, b.Platform)
  ELSE customer_ads_suse.get_content_name(b.AdCreativeName, b.CampaignName)
END AS ContentName

Version Tracking

All UDF changes are logged in the udf_metadata table:

SELECT *
FROM `paidteam-data-warehouse.customer_ads_suse.udf_metadata`
ORDER BY updated_at DESC;