The Challenge of Data Anonymization in Amazon Marketing Cloud (AMC)

WITH IMPRESSIONS_CTE AS (
      SELECT
        USER_ID,
        ARRAY_TO_STRING(ARRAY_SORT(COLLECT(DISTINCT AD_PRODUCT_TYPE)), ',') AS EXPOSURE_GROUP,
        -- MIN(IMPRESSION_DT) AS MIN_IMPRESSION_DT,
        SUM(COST_MICROCENTS) AS COST_MICROCENTS
      FROM
        (
          SELECT
            I.USER_ID,
            'DSP' AS AD_PRODUCT_TYPE,
            -- I.IMPRESSION_DT,
            I.total_cost * 1000 as COST_MICROCENTS
          FROM
            DSP_IMPRESSIONS I
          WHERE
            I.total_cost > 0
            AND I.impression_date BETWEEN '%1$s' and '%2$s'
          UNION ALL
          SELECT
            I.USER_ID,
            I.AD_PRODUCT_TYPE,
            -- I.EVENT_DT AS IMPRESSION_DT,
            I.spend as COST_MICROCENTS
          FROM
            SPONSORED_ADS_TRAFFIC I
          WHERE
            I.spend > 0
            AND I.event_date BETWEEN '%1$s' and '%2$s'
        )
      GROUP BY
        1
    )
    SELECT
      EXPOSURE_GROUP,
      COST,
      USERS_PURCHASED AS USERS_THAT_PURCHASED,
      AD_EXPOSED_USERS AS UNIQUE_REACH,
      TOTAL_PURCHASES AS TOTAL_PURCHASES,
      TOTAL_PRODUCT_SALES AS TOTAL_PRODUCT_SALES
    FROM (
        SELECT
          I.EXPOSURE_GROUP as EXPOSURE_GROUP,
          0 AS COST,
          COUNT(DISTINCT P.USER_ID) AS USERS_PURCHASED,
          0 AS AD_EXPOSED_USERS,
          SUM(P.total_purchases_clicks) AS TOTAL_PURCHASES,
          SUM(P.total_product_sales_clicks) AS TOTAL_PRODUCT_SALES
        FROM
          AMAZON_ATTRIBUTED_EVENTS_BY_TRAFFIC_TIME P
          FULL OUTER JOIN IMPRESSIONS_CTE I ON I.USER_ID = P.USER_ID
        WHERE
          P.TOTAL_PURCHASES > 0
          AND P.click_date BETWEEN '%1$s' and '%2$s'
          -- for AMAZON_ATTRIBUTED_EVENTS_BY_TRAFFIC_TIME conversion events may have occurred up to 30 days.
          -- 7 days for SP
          AND ((ad_product_type = 'sponsored_products' AND SECONDS_BETWEEN (click_dt, conversion_event_dt) <= 604800)
          -- 14 days otherwise
          OR (ad_product_type <> 'sponsored_products' AND SECONDS_BETWEEN (click_dt, conversion_event_dt) <= 1209600))
          -- MIN_IMPRESSION_DT can't be used due to FULL OUTER JOIN
          --  AND CONVERSION_EVENT_DT > MIN_IMPRESSION_DT
        GROUP BY
          1
       UNION ALL
        SELECT
          EXPOSURE_GROUP,
          SUM(COST_MICROCENTS) / (1000*1000*100) as COST,
          0 AS USERS_PURCHASED,
          COUNT(DISTINCT USER_ID) AS AD_EXPOSED_USERS,
          0 as TOTAL_PURCHASES,
          0 as TOTAL_PRODUCT_SALES
        FROM
          IMPRESSIONS_CTE
        GROUP BY
          1
        )