get_campaign_code

Extracts the numeric campaign tracking code from campaign names or ad names. This code is a 5-12 digit identifier used to link advertising campaigns back to internal campaign management systems.

Signature

get_campaign_code(campaign STRING, ad_name STRING, platform STRING) RETURNS STRING

Parameters

Parameter	Type	Description
campaign	STRING	The full campaign name string
ad_name	STRING	The ad or creative name string
platform	STRING	The advertising platform (google, microsoft, linkedin, reddit, 6sense, stackadapt)

Return Value

Returns a STRING containing the numeric campaign code (5-12 digits). Returns "Unknown" if no valid code is found.

Even though the code is numeric, it is returned as a STRING to preserve leading zeros and maintain consistency with the project convention of using STRING for all identifiers.

Platform-Specific Extraction

The campaign code is embedded in different locations depending on the platform:

Platform	Extraction Source	Method
LinkedIn	ad_name	Extracted from the start of the ad name
6sense	ad_name	Extracted from the start of the ad name
StackAdapt	ad_name	Extracted from the start of the ad name
Microsoft	campaign	Extracted from UTM parameters within the campaign name
Google	campaign or UTM	Extracted from UTM parameters or campaign name
Reddit	ad_name	Extracted from the start of the ad name

Example

SELECT
  CampaignName,
  AdName,
  customer_ads_suse.get_campaign_code(CampaignName, AdName, 'linkedin') AS CampaignCode
FROM `paidteam-data-warehouse.customer_ads_suse.suse_linkedin_adlevel_final_v2`
WHERE Date = '2026-02-01'
LIMIT 5;

Sample output:

CampaignName	AdName	CampaignCode
SUSE | PSOC | NA | T1 | EN | Cloud Native | Rancher	0005547 | gartner_webinar | 1200x628 | v2	0005547
SUSE | PSOC | EMEA | T1 | EN | Linux | SLES	0013663 | SLES_Migration_1200x1200_V1	0013663
SUSE | PSOC | APAC | T1 | EN | AI | MLS	0012312 | How_to_Operationalize_AI | v1	0012312

Cross-Platform Code Lookup

-- Find all ads associated with a specific campaign code
SELECT
  'LinkedIn' AS Platform,
  CampaignName,
  AdName,
  SUM(Impressions) AS TotalImpressions
FROM `paidteam-data-warehouse.customer_ads_suse.suse_linkedin_adlevel_final_v2`
WHERE customer_ads_suse.get_campaign_code(CampaignName, AdName, 'linkedin') = '0005547'
  AND Date >= '2026-01-01'
GROUP BY CampaignName, AdName

UNION ALL

SELECT
  'Reddit' AS Platform,
  CampaignName,
  AdName,
  SUM(Impressions) AS TotalImpressions
FROM `paidteam-data-warehouse.customer_ads_suse.suse_reddit_adlevel_final_v2`
WHERE customer_ads_suse.get_campaign_code(CampaignName, AdName, 'reddit') = '0005547'
  AND Date >= '2026-01-01'
GROUP BY CampaignName, AdName
ORDER BY Platform, TotalImpressions DESC;

Notes

• The campaign code is the primary linkage between advertising data and internal campaign management
• Codes typically start with leading zeros (e.g., 0005547), which is why the return type is STRING
• For platforms that extract from ad_name, the code is expected at the very beginning of the string, before the first delimiter
• For Microsoft, the code is embedded within UTM tracking parameters in the campaign name