get_subproduct

Extracts a free-text sub-product identifier from the campaign name. This provides a more granular product classification beneath the main product level.

Signature

get_subproduct(campaign STRING, platform STRING) RETURNS STRING

Parameters

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

Return Value

Returns a STRING containing the sub-product name. Unlike other UDFs that return from a fixed set of values, this function returns free-text extracted from the campaign name. Common examples include specific solution names, use cases, or campaign themes.

Returns "None" if no sub-product is found in the campaign name.

How It Works

The sub-product is typically an optional field in the campaign naming convention, placed after the main product. Not all campaigns include a sub-product, so this field frequently returns "None".

The function is platform-aware because the sub-product sits at different positions within each platform's naming convention.

Example

SELECT
  CampaignName,
  customer_ads_suse.get_product(CampaignName, 'google') AS Product,
  customer_ads_suse.get_subproduct(CampaignName, 'google') AS SubProduct
FROM `paidteam-data-warehouse.customer_ads_suse.suse_google_adlevel_final_v2`
WHERE Date = '2026-02-01'
LIMIT 5;

Sample output:

CampaignName	Product	SubProduct
SUSE | SRCH | NA | T1 | EN | Linux | SLES | Brand	SLES	Brand
SUSE | SRCH | NA | T1 | EN | Linux | SLES | Migration	SLES	Migration
SUSE | DISP | EMEA | T1 | EN | Cloud Native | Rancher	Rancher	None
SUSE | PSOC | APAC | T1 | EN | AI | MLS | GenAI	MLS	GenAI
SUSE | SRCH | NA | T1 | EN | All | General	All	None

Filtering by Sub-Product

-- Find all campaigns with a specific sub-product
SELECT
  CampaignName,
  customer_ads_suse.get_subproduct(CampaignName, 'microsoft') AS SubProduct,
  SUM(Impressions) AS TotalImpressions,
  SUM(Clicks) AS TotalClicks
FROM `paidteam-data-warehouse.customer_ads_suse.suse_microsoft_adlevel_final_v2`
WHERE Date >= '2026-01-01'
GROUP BY CampaignName, SubProduct
HAVING SubProduct != 'None'
ORDER BY TotalImpressions DESC;

Product Hierarchy

The sub-product is the third level in the product hierarchy:

1. Product Line -- get_product_line() (e.g., Linux, Cloud Native, AI)
2. Product -- get_product() (e.g., SLES, Rancher, MLS)
3. Sub-Product -- get_subproduct() (e.g., Brand, Migration, GenAI)