Editorial Insights
Power BI Report

SELECT DISTINCT
  CAST(ond.sent_date_pt AS Date)                                AS sent_date
  , ond.brand
  , ond.deployment_name
  , oot.type_name                                               AS deployment_type
  , ond.email_subject                                           AS title
  , ond.content_title
  , ond.link_url
  , ond.delivered_total                                         AS delivered_total
  , ond.opens_total                                             AS opens_total
  , ond.opens_unique                                            AS opens_unique
  , ond.clicks_total                                            AS clicks_total
  , ond.clicks_unique                                           AS clicks_unique
FROM
  schema.bi_analytics.v_newsletter_details          AS ond
JOIN schema.virtual_db.omail_deployment     AS ood
  ON ond.deployment_name = ood.NAME
JOIN schema.virtual_db.omail_type           AS oot
  ON ood.DEPLOYMENT_TYPE_ID = oot.DEPLOYMENT_TYPE_ID

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 brand_map AS
(
    SELECT DISTINCT
        Blank                                                                     AS property_id
        , Brand                                                                   AS brand
        , CASE
            WHEN NULLIF(NULLIF(Domain, '(not set)'), '') IS NOT NULL THEN
                CONCAT(
                    'https://www.'
                    , REGEXP_REPLACE(
                        REGEXP_REPLACE(LOWER(TRIM(Domain)), '^https?://', '')
                        , '^www\\.'
                        , ''
                    )
                )
            ELSE NULL
        END                                                                       AS host_name
    FROM
        schema.silver.brand_mapping
)

SELECT DISTINCT
    p.date
    , m.brand                                                                     AS brand
    , CASE
        WHEN NULLIF(NULLIF(p.page_path, '(not set)'), '') IS NULL
            AND NULLIF(NULLIF(m.host_name, '(not set)'), '') IS NULL THEN ''

        WHEN LOWER(TRIM(p.page_path)) LIKE 'http%' THEN
            REGEXP_REPLACE(
                LOWER(TRIM(p.page_path))
                , '^(https?://)?(www\\.)?'
                , 'https://www.'
            )

        WHEN NULLIF(NULLIF(m.host_name, '(not set)'), '') IS NULL THEN p.page_path

        WHEN LEFT(p.page_path, 1) = '/' THEN CONCAT(m.host_name, p.page_path)

        ELSE CONCAT(m.host_name, '/', p.page_path)
    END                                                                           AS link
    , SUM(p.total_users)                                                          AS total_users
    , SUM(p.new_users)                                                            AS new_users
    , SUM(p.event_count)                                                          AS event_count
    , SUM(p.screen_page_views)                                                    AS screen_page_views
    , SUM(p.user_engagement_duration)                                             AS user_engagement_duration
FROM
    schema.google_analytics.pages_path_report p
    LEFT JOIN brand_map m
        ON SPLIT_PART(p.property, '/', 2) = CAST(m.property_id AS STRING)
WHERE
    p.date >= '2025-01-01'
GROUP BY
    1, 2, 3

SELECT DISTINCT
    c.date
    , m.Brand                                                                           AS brand
    , CASE
        WHEN NULLIF(NULLIF(c.host_name, '(not set)'), '') IS NULL
            AND NULLIF(NULLIF(c.page_path, '(not set)'), '') IS NULL THEN NULL
        WHEN NULLIF(NULLIF(c.host_name, '(not set)'), '') IS NULL THEN c.page_path
        WHEN NULLIF(NULLIF(c.page_path, '(not set)'), '') IS NULL THEN c.host_name
        ELSE CONCAT('https://', c.host_name, c.page_path)
    END                                                                                 AS link
    , CASE  
        WHEN c.custom_event_content_type = '(not set)' THEN NULL
        ELSE c.custom_event_content_type
    END                                                                                 AS content_type
    , CASE
        WHEN c.custom_event_content_tag = '(not set)' THEN NULL
        ELSE c.custom_event_content_tag
    END                                                                                 AS tag
    , CASE
        WHEN c.custom_event_content_author = '(not set)' THEN NULL
        ELSE c.custom_event_content_author
    END                                                                                 AS author
    , CASE
        WHEN c.custom_event_content_company = '(not set)' THEN NULL
        ELSE c.custom_event_content_company
    END                                                                                 AS company
    , SUM(c.event_count)                                                                AS event_count
    , SUM(c.screen_page_views)                                                          AS page_views
FROM
    schema.ga4_custom_dimensions.custom_event_content c
    LEFT JOIN schema.silver.brand_mapping m
        ON SPLIT_PART(c.property, '/', 2) = CAST(m.Blank AS STRING)
WHERE
    date >= '2025-01-01'
GROUP BY
    1, 2, 3, 4, 5, 6, 7