delivery_queries.py

import re


def _quote_identifier(identifier: str) -> str:
    """Quote SQL identifiers that contain special characters."""

    def quote_part(part: str) -> str:
        if re.match(r"^[A-Za-z_][A-Za-z0-9_]*$", part):
            return part
        return f'"{part}"'

    return ".".join(quote_part(p) for p in identifier.split("."))


def get_main_query(
    table,
    start_datetime,
    end_datetime,
    message_filter,
    campaign_id,
    integration_filter=None,
    ad_format_filter=None,
):
    """Returns the main impression count query filtered by integration and ad format."""

    table = _quote_identifier(table)

    # Build optional filters to apply after the CTE union.
    # Filtering on the calculated aliases (Integration/Ad_Format)
    # in the CTE `WHERE` clause would force Snowflake to interpret
    # those names as existing columns and attempt type coercion,
    # which triggered errors like:
    #   Numeric value 'bciq1rts' is not recognized
    # Instead we apply the filters on the outer SELECT where the
    # aliases are available.
    post_union_filter = ""
    if integration_filter:
        post_union_filter += f" AND Integration = '{integration_filter}'"
    if ad_format_filter:
        post_union_filter += f" AND Ad_Format = '{ad_format_filter}'"

    return f"""
    WITH today AS (
      SELECT
        to_date(convert_timezone('UTC','America/New_York',datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC','America/New_York',datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC','America/New_York',datetime)) AS EST_MINUTE,
        CASE
            WHEN body[0]:yieldGroupIds[0]::varchar IN ('397722') THEN 'HBT_OB'
            WHEN body[0]:campaignId::varchar = '2204701358' THEN 'House'
            WHEN b.name LIKE '%Prebid%' THEN 'Prebid'
            WHEN b.name LIKE '%TAM%' OR b.name LIKE '%Amazon%' THEN 'TAM'
            WHEN b.name LIKE '%AdX%' THEN 'AdX'
            WHEN len(body[0]:lineItemId::varchar) > 10 THEN 'AdX'
            WHEN c.name LIKE '%TAM%' OR c.name LIKE '%Amazon%' THEN 'TAM'
            WHEN c.name LIKE '%AdX%' THEN 'AdX'
            WHEN c.name LIKE '%CS%' OR c.name LIKE '%S2S%' THEN 'Prebid'
            WHEN b.name LIKE '39_%_%' THEN 'Direct'
            WHEN b.name LIKE '38_%_%' THEN 'Direct'
            WHEN b.name LIKE '8_%_%_%' AND b.name LIKE '%IX%' THEN 'Prebid'
            WHEN b.name LIKE '8_%_%_%' THEN 'Ignore - House, Test, Pub Deal'
            WHEN b.name LIKE '7_%_%_%' THEN 'PG'
            WHEN b.name LIKE '5_%_%_%' THEN 'PG'
            WHEN LEFT(b.name,1) = '4'
              AND RIGHT(LEFT(b.name,2),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,3),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,4),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,5),1) = '_' THEN 'Affiliate'
            WHEN b.name LIKE '0_%_%_%' THEN 'Ignore'
            WHEN (body[0]:campaignId IS NULL
                  AND body[0]:slotTargeting:hb_bidder[0]::varchar IS NOT NULL)
              THEN 'Prebid'
            WHEN body[0]:companyIds IS NOT NULL THEN 'OB'
            WHEN c.id IS NOT NULL THEN 'Prebid'
            ELSE 'OB'
        END AS Integration,
        CASE
            WHEN split(body[0]['adUnitPath'],'/')[2]::varchar LIKE '%Outstream%' THEN 'Display'
            WHEN split(body[0]['adUnitPath'],'/')[2]::varchar LIKE '%Video%' THEN 'Video'
            ELSE 'Display'
        END AS Ad_Format,
        COUNT(*) AS CNT,
        'Today' AS timeframe
      FROM {table} a
      LEFT JOIN ANALYTICS.GAM360.ORDERS b
        ON a.body[0]:campaignId::VARCHAR = b.ID::VARCHAR
      LEFT JOIN ANALYTICS.GAM360.LINEITEM c
        ON c.id::VARCHAR = a.body[0]:lineItemId::VARCHAR
      WHERE convert_timezone('UTC','America/New_York',datetime)
        BETWEEN '{start_datetime}' AND '{end_datetime}'
        AND message = 'SlotRenderEnded::adImpression'
      GROUP BY ALL
    ),
    prev1 AS (
      -- 1 Week Ago
      SELECT
        to_date(convert_timezone('UTC','America/New_York',datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC','America/New_York',datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC','America/New_York',datetime)) AS EST_MINUTE,
        CASE
            WHEN body[0]:yieldGroupIds[0]::varchar IN ('397722') THEN 'HBT_OB'
            WHEN body[0]:campaignId::varchar = '2204701358' THEN 'House'
            WHEN b.name LIKE '%Prebid%' THEN 'Prebid'
            WHEN b.name LIKE '%TAM%' OR b.name LIKE '%Amazon%' THEN 'TAM'
            WHEN b.name LIKE '%AdX%' THEN 'AdX'
            WHEN len(body[0]:lineItemId::varchar) > 10 THEN 'AdX'
            WHEN c.name LIKE '%TAM%' OR c.name LIKE '%Amazon%' THEN 'TAM'
            WHEN c.name LIKE '%AdX%' THEN 'AdX'
            WHEN c.name LIKE '%CS%' OR c.name LIKE '%S2S%' THEN 'Prebid'
            WHEN b.name LIKE '39_%_%' THEN 'Direct'
            WHEN b.name LIKE '38_%_%' THEN 'Direct'
            WHEN b.name LIKE '8_%_%_%' AND b.name LIKE '%IX%' THEN 'Prebid'
            WHEN b.name LIKE '8_%_%_%' THEN 'Ignore - House, Test, Pub Deal'
            WHEN b.name LIKE '7_%_%_%' THEN 'PG'
            WHEN b.name LIKE '5_%_%_%' THEN 'PG'
            WHEN LEFT(b.name,1) = '4'
              AND RIGHT(LEFT(b.name,2),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,3),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,4),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,5),1) = '_' THEN 'Affiliate'
            WHEN b.name LIKE '0_%_%_%' THEN 'Ignore'
            WHEN (body[0]:campaignId IS NULL
                  AND body[0]:slotTargeting:hb_bidder[0]::varchar IS NOT NULL)
              THEN 'Prebid'
            WHEN body[0]:companyIds IS NOT NULL THEN 'OB'
            WHEN c.id IS NOT NULL THEN 'Prebid'
            ELSE 'OB'
        END AS Integration,
        CASE
            WHEN split(body[0]['adUnitPath'],'/')[2]::varchar LIKE '%Outstream%' THEN 'Display'
            WHEN split(body[0]['adUnitPath'],'/')[2]::varchar LIKE '%Video%' THEN 'Video'
            ELSE 'Display'
        END AS Ad_Format,
        COUNT(*) AS CNT,
        '1 Week Ago' AS timeframe
      FROM {table} a
      LEFT JOIN ANALYTICS.GAM360.ORDERS b
        ON a.body[0]:campaignId::VARCHAR = b.ID::VARCHAR
      LEFT JOIN ANALYTICS.GAM360.LINEITEM c
        ON c.id::VARCHAR = a.body[0]:lineItemId::VARCHAR
      WHERE convert_timezone('UTC','America/New_York',datetime)
        BETWEEN dateadd(DAY,-7,'{start_datetime}') AND dateadd(DAY,-7,'{end_datetime}')
        AND message = 'SlotRenderEnded::adImpression'
      GROUP BY ALL
    ),
    prev2 AS (
      -- 2 Weeks Ago
      SELECT
        to_date(convert_timezone('UTC','America/New_York',datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC','America/New_York',datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC','America/New_York',datetime)) AS EST_MINUTE,
        CASE
            WHEN body[0]:yieldGroupIds[0]::varchar IN ('397722') THEN 'HBT_OB'
            WHEN body[0]:campaignId::varchar = '2204701358' THEN 'House'
            WHEN b.name LIKE '%Prebid%' THEN 'Prebid'
            WHEN b.name LIKE '%TAM%' OR b.name LIKE '%Amazon%' THEN 'TAM'
            WHEN b.name LIKE '%AdX%' THEN 'AdX'
            WHEN len(body[0]:lineItemId::varchar) > 10 THEN 'AdX'
            WHEN c.name LIKE '%TAM%' OR c.name LIKE '%Amazon%' THEN 'TAM'
            WHEN c.name LIKE '%AdX%' THEN 'AdX'
            WHEN c.name LIKE '%CS%' OR c.name LIKE '%S2S%' THEN 'Prebid'
            WHEN b.name LIKE '39_%_%' THEN 'Direct'
            WHEN b.name LIKE '38_%_%' THEN 'Direct'
            WHEN b.name LIKE '8_%_%_%' AND b.name LIKE '%IX%' THEN 'Prebid'
            WHEN b.name LIKE '8_%_%_%' THEN 'Ignore - House, Test, Pub Deal'
            WHEN b.name LIKE '7_%_%_%' THEN 'PG'
            WHEN b.name LIKE '5_%_%_%' THEN 'PG'
            WHEN LEFT(b.name,1) = '4'
              AND RIGHT(LEFT(b.name,2),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,3),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,4),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,5),1) = '_' THEN 'Affiliate'
            WHEN b.name LIKE '0_%_%_%' THEN 'Ignore'
            WHEN (body[0]:campaignId IS NULL
                  AND body[0]:slotTargeting:hb_bidder[0]::varchar IS NOT NULL)
              THEN 'Prebid'
            WHEN body[0]:companyIds IS NOT NULL THEN 'OB'
            WHEN c.id IS NOT NULL THEN 'Prebid'
            ELSE 'OB'
        END AS Integration,
        CASE
            WHEN split(body[0]['adUnitPath'],'/')[2]::varchar LIKE '%Outstream%' THEN 'Display'
            WHEN split(body[0]['adUnitPath'],'/')[2]::varchar LIKE '%Video%' THEN 'Video'
            ELSE 'Display'
        END AS Ad_Format,
        COUNT(*) AS CNT,
        '2 Weeks Ago' AS timeframe
      FROM {table} a
      LEFT JOIN ANALYTICS.GAM360.ORDERS b
        ON a.body[0]:campaignId::VARCHAR = b.ID::VARCHAR
      LEFT JOIN ANALYTICS.GAM360.LINEITEM c
        ON c.id::VARCHAR = a.body[0]:lineItemId::VARCHAR
      WHERE convert_timezone('UTC','America/New_York',datetime)
        BETWEEN dateadd(DAY,-14,'{start_datetime}')
            AND dateadd(DAY,-14,'{end_datetime}')
        AND message = 'SlotRenderEnded::adImpression'
      GROUP BY ALL
    ),
    prev3 AS (
      -- 3 Weeks Ago
      SELECT
        to_date(convert_timezone('UTC','America/New_York',datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC','America/New_York',datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC','America/New_York',datetime)) AS EST_MINUTE,
        CASE
            WHEN body[0]:yieldGroupIds[0]::varchar IN ('397722') THEN 'HBT_OB'
            WHEN body[0]:campaignId::varchar = '2204701358' THEN 'House'
            WHEN b.name LIKE '%Prebid%' THEN 'Prebid'
            WHEN b.name LIKE '%TAM%' OR b.name LIKE '%Amazon%' THEN 'TAM'
            WHEN b.name LIKE '%AdX%' THEN 'AdX'
            WHEN len(body[0]:lineItemId::varchar) > 10 THEN 'AdX'
            WHEN c.name LIKE '%TAM%' OR c.name LIKE '%Amazon%' THEN 'TAM'
            WHEN c.name LIKE '%AdX%' THEN 'AdX'
            WHEN c.name LIKE '%CS%' OR c.name LIKE '%S2S%' THEN 'Prebid'
            WHEN b.name LIKE '39_%_%' THEN 'Direct'
            WHEN b.name LIKE '38_%_%' THEN 'Direct'
            WHEN b.name LIKE '8_%_%_%' AND b.name LIKE '%IX%' THEN 'Prebid'
            WHEN b.name LIKE '8_%_%_%' THEN 'Ignore - House, Test, Pub Deal'
            WHEN b.name LIKE '7_%_%_%' THEN 'PG'
            WHEN b.name LIKE '5_%_%_%' THEN 'PG'
            WHEN LEFT(b.name,1) = '4'
              AND RIGHT(LEFT(b.name,2),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,3),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,4),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,5),1) = '_' THEN 'Affiliate'
            WHEN b.name LIKE '0_%_%_%' THEN 'Ignore'
            WHEN (body[0]:campaignId IS NULL
                  AND body[0]:slotTargeting:hb_bidder[0]::varchar IS NOT NULL)
              THEN 'Prebid'
            WHEN body[0]:companyIds IS NOT NULL THEN 'OB'
            WHEN c.id IS NOT NULL THEN 'Prebid'
            ELSE 'OB'
        END AS Integration,
        CASE
            WHEN split(body[0]['adUnitPath'],'/')[2]::varchar LIKE '%Outstream%' THEN 'Display'
            WHEN split(body[0]['adUnitPath'],'/')[2]::varchar LIKE '%Video%' THEN 'Video'
            ELSE 'Display'
        END AS Ad_Format,
        COUNT(*) AS CNT,
        '3 Weeks Ago' AS timeframe
      FROM {table} a
      LEFT JOIN ANALYTICS.GAM360.ORDERS b
        ON a.body[0]:campaignId::VARCHAR = b.ID::VARCHAR
      LEFT JOIN ANALYTICS.GAM360.LINEITEM c
        ON c.id::VARCHAR = a.body[0]:lineItemId::VARCHAR
      WHERE convert_timezone('UTC','America/New_York',datetime)
        BETWEEN dateadd(DAY,-21,'{start_datetime}')
            AND dateadd(DAY,-21,'{end_datetime}')
        AND message = 'SlotRenderEnded::adImpression'
      GROUP BY ALL
    )
    SELECT * FROM (
        SELECT * FROM today
        UNION ALL SELECT * FROM prev1
        UNION ALL SELECT * FROM prev2
        UNION ALL SELECT * FROM prev3
    )
    WHERE 1=1 {post_union_filter}
    """


def get_bidder_query(
    table,
    start_datetime,
    end_datetime,
    message_filter,
    campaign_id,
    integration_filter=None,
    ad_format_filter=None,
):
    base = get_main_query(
        table,
        start_datetime,
        end_datetime,
        message_filter,
        campaign_id,
        integration_filter,
        ad_format_filter,
    )
    # inject hb_bidder field
    return base.replace(
        "COUNT(*) AS CNT",
        "COUNT(*) AS CNT, body[0]:slotTargeting:hb_bidder[0]::varchar AS hb_bidder",
    )


def get_flex_bucket_query(
    table,
    start_datetime,
    end_datetime,
    message_filter,
    campaign_id,
    integration_filter=None,
    ad_format_filter=None,
):
    base = get_main_query(
        table,
        start_datetime,
        end_datetime,
        message_filter,
        campaign_id,
        integration_filter,
        ad_format_filter,
    )
    return base.replace("COUNT(*) AS CNT", "COUNT(*) AS CNT, bucket")


def get_device_query(
    table,
    start_datetime,
    end_datetime,
    message_filter,
    campaign_id,
    integration_filter=None,
    ad_format_filter=None,
):
    base = get_main_query(
        table,
        start_datetime,
        end_datetime,
        message_filter,
        campaign_id,
        integration_filter,
        ad_format_filter,
    )
    # inject device case
    device_case = (
        "CASE "
        "WHEN useragent LIKE '%iPad%' OR useragent LIKE '%Tablet%' THEN 'tablet' "
        "WHEN useragent LIKE '%Windows%' OR useragent LIKE '%Macintosh%' THEN 'desktop' "
        "WHEN useragent LIKE '%Android%' OR useragent LIKE '%iPhone%' OR useragent LIKE '%Mobi%' THEN 'phone' "
        "ELSE 'other' END AS device"
    )
    return base.replace("COUNT(*) AS CNT", f"COUNT(*) AS CNT, {device_case}")


def get_ad_unit_query(
    table,
    start_datetime,
    end_datetime,
    message_filter,
    campaign_id,
    integration_filter=None,
    ad_format_filter=None,
):
    base = get_main_query(
        table,
        start_datetime,
        end_datetime,
        message_filter,
        campaign_id,
        integration_filter,
        ad_format_filter,
    )
    ad_unit_case = (
        "CASE "
        "WHEN body[0]:slotElementId::varchar LIKE '%Content%' THEN 'Content' "
        "WHEN body[0]:slotElementId::varchar LIKE '%Footer%' THEN 'Footer' "
        "WHEN body[0]:slotElementId::varchar LIKE '%Recipe%' THEN 'Recipe' "
        "WHEN body[0]:slotElementId::varchar LIKE '%Sidebar%' THEN 'Sidebar' "
        "WHEN body[0]:slotElementId::varchar LIKE '%Header%' THEN 'Header' "
        "WHEN body[0]:slotElementId::varchar LIKE '%Below_Post%' THEN 'Below_Post' "
        "WHEN body[0]:slotElementId::varchar LIKE '%Outstream%' THEN 'Sticky Outstream' "
        "WHEN body[0]:slotElementId::varchar LIKE '%Video%' THEN 'Video' "
        "ELSE 'Other' END AS ad_unit_group"
    )
    return base.replace("COUNT(*) AS CNT", f"COUNT(*) AS CNT, {ad_unit_case}")


def get_refresh_query(
    table,
    start_datetime,
    end_datetime,
    message_filter,
    campaign_id,
    integration_filter=None,
    ad_format_filter=None,
):
    base = get_main_query(
        table,
        start_datetime,
        end_datetime,
        message_filter,
        campaign_id,
        integration_filter,
        ad_format_filter,
    )
    refresh_field = "body[0]:slotTargeting:refresh[0]::varchar AS Refresh"
    return base.replace("COUNT(*) AS CNT", f"COUNT(*) AS CNT, {refresh_field}")


def get_main_int_sov_query(
    table,
    start_datetime,
    end_datetime,
    message_filter,
    campaign_id,
    # integration_filter no longer used for SOV
    ad_format_filter=None,
):
    """
    Returns the share-of-voice query filtered only by ad format.
    """
    table = _quote_identifier(table)

    # Only apply Ad_Format filtering after the CTE so that the alias
    # can be referenced safely.
    post_union_filter = ""
    if ad_format_filter:
        post_union_filter = f" AND Ad_Format = '{ad_format_filter}'"

    return f"""
    WITH today AS (
      SELECT
        to_date(convert_timezone('UTC','America/New_York',datetime)) AS EST_DATE,
        extract(hour FROM convert_timezone('UTC','America/New_York',datetime)) AS EST_HOUR,
        extract(minute FROM convert_timezone('UTC','America/New_York',datetime)) AS EST_MINUTE,
        CASE
            WHEN body[0]:yieldGroupIds[0]::varchar IN ('397722') THEN 'HBT_OB'
            WHEN body[0]:campaignId::varchar = '2204701358' THEN 'House'
            WHEN b.name LIKE '%Prebid%' THEN 'Prebid'
            WHEN b.name LIKE '%TAM%' OR b.name LIKE '%Amazon%' THEN 'TAM'
            WHEN b.name LIKE '%AdX%' THEN 'AdX'
            WHEN len(body[0]:lineItemId::varchar) > 10 THEN 'AdX'
            WHEN c.name LIKE '%TAM%' OR c.name LIKE '%Amazon%' THEN 'TAM'
            WHEN c.name LIKE '%AdX%' THEN 'AdX'
            WHEN c.name LIKE '%CS%' OR c.name LIKE '%S2S%' THEN 'Prebid'
            WHEN b.name LIKE '39_%_%' THEN 'Direct'
            WHEN b.name LIKE '38_%_%' THEN 'Direct'
            WHEN b.name LIKE '8_%_%_%' AND b.name LIKE '%IX%' THEN 'Prebid'
            WHEN b.name LIKE '8_%_%_%' THEN 'Ignore - House, Test, Pub Deal'
            WHEN b.name LIKE '7_%_%_%' THEN 'PG'
            WHEN b.name LIKE '5_%_%_%' THEN 'PG'
            WHEN LEFT(b.name,1) = '4'
              AND RIGHT(LEFT(b.name,2),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,3),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,4),1) BETWEEN '0' AND '9'
              AND RIGHT(LEFT(b.name,5),1) = '_' THEN 'Affiliate'
            WHEN b.name LIKE '0_%_%_%' THEN 'Ignore'
            WHEN (body[0]:campaignId IS NULL
                  AND body[0]:slotTargeting:hb_bidder[0]::varchar IS NOT NULL)
              THEN 'Prebid'
            WHEN body[0]:companyIds IS NOT NULL THEN 'OB'
            WHEN c.id IS NOT NULL THEN 'Prebid'
            ELSE 'OB'
        END AS Integration,
        CASE
            WHEN split(body[0]['adUnitPath'],'/')[2]::varchar LIKE '%Outstream%' THEN 'Display'
            WHEN split(body[0]['adUnitPath'],'/')[2]::varchar LIKE '%Video%' THEN 'Video'
            ELSE 'Display'
        END AS Ad_Format,
        COUNT(*) AS CNT,
        'Today' AS timeframe
      FROM {table} a
      LEFT JOIN ANALYTICS.GAM360.ORDERS b
        ON a.body[0]:campaignId::VARCHAR = b.ID::VARCHAR
      LEFT JOIN ANALYTICS.GAM360.LINEITEM c
        ON c.id::VARCHAR = a.body[0]:lineItemId::VARCHAR
      WHERE convert_timezone('UTC','America/New_York',datetime)
        BETWEEN '{start_datetime}' AND '{end_datetime}'
        AND message = 'SlotRenderEnded::adImpression'
      GROUP BY ALL
    )
    SELECT * FROM today
    WHERE 1=1 {post_union_filter}
    """