Taxonomy Updater

Taxonomy Updater

Generate campaign-level taxonomy entries for Google Ads accounts with mixed verticals.

Workflow

1. Identify campaigns needing taxonomy - Query campaign data to find campaigns missing from taxonomy or with incorrect vertical attribution
2. Check for vertical mixing - Determine if account has campaigns across multiple verticals (buy, rent, sell, new_construction, etc.)
3. Generate taxonomy CSV - Create properly formatted CSV with all required fields

Key Tables

-- Taxonomy reference
SELECT * FROM rdc_marketing.team_digital_marketing.taxonomy_hist;

-- Google campaign data
SELECT * FROM fivetran_martech.raw_google_campaign.campaign;

Detecting Mixed-Vertical Accounts

-- Check if account has multiple verticals
SELECT 
    CASE 
        WHEN LOWER(NAME) LIKE '%_buy_%' OR LOWER(NAME) LIKE '%for sale%' THEN 'buy'
        WHEN LOWER(NAME) LIKE '%rental%' THEN 'rent'
        WHEN LOWER(NAME) LIKE '%newcon%' THEN 'new_construction'
        WHEN LOWER(NAME) LIKE '%sell%' THEN 'sell'
        ELSE 'unknown'
    END as inferred_vertical,
    COUNT(DISTINCT ID) as campaign_count
FROM fivetran_martech.raw_google_campaign.campaign
WHERE CUSTOMER_ID = <account_id>
  AND DATE >= DATEADD(month, -3, CURRENT_DATE)
GROUP BY 1;

If multiple verticals exist, campaign-level taxonomy entries are required.

Getting Campaign Start Dates

SELECT 
    CAST(ID AS VARCHAR) as campaign_id,
    NAME,
    MIN(DATE) as first_active_date
FROM fivetran_martech.raw_google_campaign.campaign
WHERE CUSTOMER_ID = <account_id>
GROUP BY ID, NAME;

CSV Output Format

Generate CSV without headers. Fields in order:

Allowed Values Reference

See references/allowed-values.md for complete list of valid taxonomy field values.

Media Type Selection

• PMax campaigns: Use mixed
• Search/DSA campaigns: Use search
• Display campaigns: Use display_static or display_video

Common Budget Mappings