normalize_status

Normalizes platform-specific status values to a unified standard set of UPPER CASE values.

Signature

normalize_status(raw_status STRING) RETURNS STRING

Parameters

Parameter	Type	Description
raw_status	STRING	Raw status value from any platform's status table

Return Values

Normalized Value	Description
ACTIVE	Campaign/ad group/ad is currently active and serving
PAUSED	Manually paused by the advertiser
REMOVED	Permanently removed (Google only)
COMPLETED	Campaign finished its scheduled run (LinkedIn, 6sense)
NULL	Unknown or unrecognized status value

Normalization Mapping

Platform	Raw Value	Normalized
Google	ENABLED	ACTIVE
Google	PAUSED	PAUSED
Google	REMOVED	REMOVED
Microsoft	Active	ACTIVE
Microsoft	Paused	PAUSED
LinkedIn	ACTIVE	ACTIVE
LinkedIn	PAUSED	PAUSED
LinkedIn	COMPLETED	COMPLETED
Reddit	ACTIVE	ACTIVE
6sense	Active	ACTIVE
6sense	Completed	COMPLETED

Usage

-- Direct usage
SELECT `customer_ads_suse.normalize_status`('ENABLED');  -- 'ACTIVE'
SELECT `customer_ads_suse.normalize_status`('Active');   -- 'ACTIVE'
SELECT `customer_ads_suse.normalize_status`(NULL);       -- NULL

-- In unified_performance_v2 (with PAUSED default for missing rows)
COALESCE(`customer_ads_suse.normalize_status`(s.CampaignStatus), 'PAUSED') AS CampaignStatus

Design Notes

• Case-insensitive: Uses UPPER() internally, so Active, ACTIVE, and active all normalize the same way
• NULL-safe: Returns NULL for NULL or empty input (the COALESCE wrapper in the view handles the default)
• Deterministic: Same input always produces the same output
• Used by: unified_performance_v2 view (5 status table JOINs)

Source

sql/customer_ads_suse/routines/normalize_status.sql