AM Analytics
Power BI Report

SELECT DISTINCT
    CAST(sent_date_pt AS DATE)               AS sent_date
    , odd.brand
    , odd.email_subject                          AS title
    , odd.deployment_name
    , oot.type_name                              AS deployment_type
    , oot.DESIGNATION_NAME                       AS designation
    , odd.delivered_total
    , odd.opens_total
    , odd.opens_unique
    , odd.opens_rate
    , odd.clicks_total
    , odd.clicks_unique
    , odd.clicks_rate
    , odd.optouts_total
    , odd.optouts_rate
FROM
    schema.bi_analytics.v_deployment_details    AS odd
    INNER JOIN schema.virtual_db.omail_deployment  AS ood
        ON odd.deployment_name = ood.NAME
    INNER JOIN schema.virtual_db.omail_type  AS oot
        ON ood.DEPLOYMENT_TYPE_ID = oot.DEPLOYMENT_TYPE_ID
ORDER BY
    sent_date DESC

WITH base AS (
    SELECT
        ocp.PRODUCT_SUBSCRIPTION_ID
        , ocp.CUSTOMER_ID
        , ocp.PRODUCT_ID
        , ocp.PRODUCT_ORIGINAL_ORDER
        , ocp.VERIFICATION_DATE
        , ocp.PRODUCT_VERSION_TYPE
        , ocp.PRODUCT_PROMO_CODE
        , ocp.AMOUNT
        , DATEADD(month, 1, ocp.product_original_order)  AS month_after_original_order
    FROM
        schema.virtual_db.customer_product ocp
    WHERE
        NULLIF(ocp.PRODUCT_PROMO_CODE, '') IS NOT NULL
        AND ocp.PRODUCT_PROMO_CODE != 'Conv'
)

, brand_map AS (
    SELECT DISTINCT
        Brand_Finance
        , Brand_Abbr
    FROM
        schema.silver.brand_mapping
)

, demo AS (
    SELECT
        oc.CUSTOMER_ID
        , oc.FIRST_NAME
        , oc.LAST_NAME
    FROM
        schema.virtual_db.customer oc
)

SELECT
    b.product_subscription_id
    , b.customer_id
    , d.first_name
    , d.last_name
    , bm.Brand_Finance                           AS brand_map
    , op.Name                                    AS product
    , op.product_type_name                       AS product_type
    , CASE
        WHEN b.product_version_type = 'P' THEN 'Print'
        WHEN b.product_version_type = 'D' THEN 'Digital'
        WHEN b.product_version_type = 'B' THEN 'Both'
        ELSE b.product_version_type
      END                                          AS version_type
    , b.product_original_order                   AS original_order_date
    , b.verification_date
    , CASE
        WHEN DATEADD(year, 1, b.verification_date) <= CURRENT_DATE THEN 'Already 1YR'
        WHEN b.verification_date > b.month_after_original_order  THEN 'Renewed'
        WHEN b.verification_date <= b.month_after_original_order THEN 'New'
        ELSE 'Unknown'
      END                                          AS status
    , b.product_promo_code                       AS promo_code
    , b.amount
FROM
    base b
    INNER JOIN schema.virtual_db.product op
        ON b.PRODUCT_ID = op.PRODUCT_ID
    INNER JOIN demo d
        ON b.customer_id = d.customer_id
    LEFT JOIN brand_map bm
        ON UPPER(op.NAME) LIKE CONCAT('NL-', UPPER(bm.Brand_Abbr), '%')