From Analytics to Action: How we Personalize Outreach With AI

Analyze the text from { { record['PAGE_URL'] } } and categorize it by selecting the most relevant category based on the presence of specific keywords. Return only the category name without any extra text or explanations.

Steps:

1. Review the { { record['PAGE_URL'] } } text thoroughly.
2. Match the content to one of the categories below, based on the presence of keywords.

Categories and Keywords:

• Operational Analytics Awareness: [“operational-analytics”]
• CDP Essentials Campaign: [“cdp”, “customer-360”, “data-warehouse”]
• Data Warehouse Activation: [“data-warehouse”, “snowflake”, “google-sheets”]
• Mastering Reverse ETL: [“reverse-etl”, “etl”, “sync”]
• Customer Success Stories: [“customer-stories”]
• Machine Learning Lead Scoring: [“machine-learning”, “lead-scoring”]
• Data Engineering Lifecycle: [“data-engineering”]
• Data Enrichment Awareness: [“data-enrichment”]
• Composable CDP Promotion: [“composable-cdp”]
• Entity Resolution Best Practices: [“entity-resolution”]
• SQL Tutorial Series: [“sql”, “tutorial”, “aggregate”]
• Tech Interview Improvement: [“technical-interviews”]
• Data Activation with dbt: [“dbt”]
• Boosting E-commerce Targeting: [“ecommerce”, “ad-targeting”]

If none of the keywords match, return General Campaign.

Example Input:

PAGE_URL: { { record['PAGE_URL'] } }

Expected Output:

Only return the most relevant category name, such as CDP Essentials Campaign.

WITH source AS (
    SELECT 
        pv.anonymous_id,
        pv.happened_at,
        pv.id,
        pv.page_url,
        pv.user_id,
        categories.census_third_party_result_column AS categories
    FROM 
        census_on_census.production.census_page_views AS pv
    LEFT JOIN 
        census.census.dataset_column_gpt_fe4891a7_5a86_4494_b89b_3ff82cbf3e05 AS categories 
    ON 
        categories.census_third_party_unique_id_column = pv.page_url
    WHERE 
        pv.user_id BETWEEN 1 AND 500
)

SELECT 
    user_id,
    ARRAY_AGG(
        OBJECT_CONSTRUCT(
            'page_url', page_url, 
            'categories', categories, 
            'happened_at', happened_at
        )
    ) AS page_views
FROM 
    source
GROUP BY 
    user_id

Analyze the { { record['PAGE_VIEWS'] } } JSON array for each { { record['USER_ID'] } } to uncover the single most relevant overarching theme of interest, inferred from the page_url data. Use category information as supporting context to refine your understanding but prioritize identifying the primary theme that reflects the account’s broader interests.

Instructions:

1. Analyze the URLs and associated categories to infer a single, overarching theme that represents the account’s dominant area of interest or concern.
2. Return only the top theme in a concise and descriptive manner, avoiding duplication or overly narrow categorizations.

Input:

• { { record['PAGE_VIEWS'] } } JSON array containing page_url and category data for each account ID.

Output Format:

For each account ID, provide the top theme in plain text format:

Output Example:

Data Activation with dbt