From Analytics to Action: How we Personalize Outreach With AI

Analyze the text from 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 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:

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 JSON array for each 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:

• 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