Untitled

WITH 
--Get the required dimensions
raw_adjust as(
    SELECT T1.* , IFNULL(REGEXP_EXTRACT(T1.campaign, T2.test_round),'unknown') round 
  , IFNULL(REGEXP_EXTRACT(T1.campaign, T2.country), 'unknown') country 
  , IFNULL(REGEXP_EXTRACT(T1.campaign, T2.test_stage), 'unknown') test_stage 
    FROM
  (
    SELECT IFNULL(campaign, 'unknown') campaign_name
  , IFNULL(campaign_id, 'unknown') campaign_id 
  , IFNULL(`adp-prod-274402.test`.rename_media_source(media_source), 'unknown') media_source
  , UPPER(IFNULL(platform, 'unknown')) platform
  , IFNULL(product_code, 'unknown') product_code 
  , LOWER("{{" || campaign || "}}{{" ||adgroup || "}}{{" || creative || "}}")  campaign 
  , creative AS ad_name 
 -- , creative_id AS ad_id 
  , install_date
  , day_since_install 
  , cost
  , retention
  , ad_revenue 
  , total_revenue
  , engagement_seconds
  FROM adp-prod-274402.data_warehouse.adjust_cohort_reporting 
  WHERE date(_partitiontime) >='2024-09-01' 
  AND  install_date between PARSE_DATE('%Y%m%d' ,@DS_START_DATE) 
    AND  PARSE_DATE('%Y%m%d',@DS_END_DATE )
  ) T1 
  LEFT JOIN 
  `adp-prod-274402.product_metadata.ua_mapping_patterns` T2
  ON T1.media_source = T2.media_source  
),
--create date_array with day_since_install 1->7 for each round test
date_array AS(
  SELECT * FROM  
  (
    SELECT DISTINCT product_code
    , media_source 
    , platform
    , test_stage 
    , round 
    , country
    , ad_name 
--    , ad_id 
    FROM raw_adjust
  ) 
  CROSS JOIN UNNEST(GENERATE_ARRAY(1,30)) day_since_install
),
--SUMMARIZE all metrics by round and day_since_install 
adjust_summary AS(
  SELECT media_source 
  , product_code 
  , platform
  , round 
  , country 
  , test_stage
  , ad_name 
--  , ad_id 
  , day_since_install 
  , SUM(cost) cost 
  , MIN(install_date) start_date 
  , SUM(retention) active_installs 
  , SUM(ad_revenue) ad_revenue 
  , SUM(total_revenue) revenue
  , SUM(engagement_seconds/60) engage_mins
  FROM raw_adjust 
  FULL JOIN date_array 
  USING(media_source, product_code, platform, round, country, test_stage, ad_name,  day_since_install)
  GROUP BY ALL
),


--Cumulative metrics by day_since_install, round 
adjust_cumulative as(
  SELECT * EXCEPT(start_date)
  , MIN(start_date) OVER(PARTITION BY product_code, platform, media_source, round, country ) start_date
  , SUM(engage_mins) OVER(PARTITION BY product_code, platform, media_source, round, country, ad_name ORDER BY day_since_install
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) acc_engage_mins 
  , SUM(ad_revenue) OVER(PARTITION BY product_code, platform, media_source, round, country, ad_name ORDER BY day_since_install
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) acc_ad_revenue
  , SUM(revenue) OVER(PARTITION BY product_code, platform, media_source, round, country, ad_name ORDER BY day_since_install
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) acc_revenue
  FROM adjust_summary
),
--summarize all metrics by campaign without day_since_install to display in looker
adjust_looker as(
  SELECT media_source
  , round 
  , country 
  , test_stage 
  , ad_name 
  , platform 
  , product_code
  , start_date 
  , SUM(CASE WHEN day_since_install =0 THEN engage_mins END) playtime_D0 
  , SUM(CASE WHEN day_since_install =1 THEN engage_mins END) playtime_D1
  , SUM(CASE WHEN day_since_install =2 THEN engage_mins END) playtime_D2
  , SUM(CASE WHEN day_since_install =3 THEN engage_mins END) playtime_D3
  , SUM(CASE WHEN day_since_install =7 THEN engage_mins END) playtime_D7
  , SUM(CASE WHEN day_since_install =1 THEN active_installs END) active_D1
  , SUM(CASE WHEN day_since_install =2 THEN active_installs END) active_D2 
  , SUM(CASE WHEN day_since_install =3 THEN active_installs END) active_D3
  , SUM(CASE WHEN day_since_install =7 THEN active_installs END) active_D7
  , SUM(CASE WHEN day_since_install =0 THEN acc_revenue END) acc_revenue_D0
  , SUM(CASE WHEN day_since_install =1 THEN acc_revenue END) acc_revenue_D1
  , SUM(CASE WHEN day_since_install =2 THEN acc_revenue END) acc_revenue_D2
  , SUM(CASE WHEN day_since_install =3 THEN acc_revenue END) acc_revenue_D3
  , SUM(CASE WHEN day_since_install =7 THEN acc_revenue END) acc_revenue_D7
  , SUM(CASE WHEN day_since_install =1 THEN acc_ad_revenue END) acc_ad_revenue_D1
  , SUM(CASE WHEN day_since_install =3 THEN acc_ad_revenue END) acc_ad_revenue_D3
  , SUM(CASE WHEN day_since_install =7 THEN acc_ad_revenue END) acc_ad_revenue_D7
  , SUM(CASE WHEN day_since_install =0 THEN active_installs END ) cohort_size
  , SUM(CASE WHEN day_since_install =0 THEN cost END) total_cost
  , SUM(revenue) total_revenue
  , SUM(ad_revenue) total_ad_revenue 
  --, 1 r -- using r to mapping to 
  FROM adjust_cumulative  
  GROUP BY ALL
),

-- campaign/creative performance relate to : impression, cost, install and click of adnetwork 
creative_performance as(
    SELECT T1.platform
  , T1.product_code
  , T1.media_source
  , T1.ad_name
  , IFNULL(REGEXP_EXTRACT(T1.campaign, T2.test_round),'unknown') round 
  , IFNULL(REGEXP_EXTRACT(T1.campaign, T2.country), 'unknown') country 
  , IFNULL(REGEXP_EXTRACT(T1.campaign, T2.test_stage), 'unknown') test_stage 
  , SUM(cost) cost
  , SUM(impressions) impressions
  , SUM(clicks) clicks
  , SUM(installs) installs
    FROM
  (
    SELECT 
   IFNULL(campaign_id, 'unknown') campaign_id 
  , IFNULL(`adp-prod-274402.test`.rename_media_source(media_source), 'unknown') media_source
  , UPPER(IFNULL(platform, 'unknown')) platform
  , IFNULL(product_code, 'unknown') product_code 
  , LOWER("{{" || campaign || "}}{{" ||adgroup || "}}{{" || creative || "}}")  campaign 
  , creative AS ad_name 
 -- , creative_id AS ad_id 
  , SUM(total_cost) cost
  , SUM(impressions) impressions
  , SUM(clicks) clicks 
  , SUM(installs) installs
  FROM adp-prod-274402.data_warehouse.adjust_user_activity_daily 
  WHERE date(_partitiontime) >='2024-09-01' 
    and event_date between PARSE_DATE('%Y%m%d' ,@DS_START_DATE) 
    AND  PARSE_DATE('%Y%m%d',@DS_END_DATE )
  GROUP BY ALL 
  HAVING impressions > 0 or cost>0
  ) T1 
  LEFT JOIN 
    `adp-prod-274402.product_metadata.ua_mapping_patterns` T2
  ON T1.media_source = T2.media_source
  GROUP BY ALL
)

SELECT a.*
, c.cost 
, c.impressions  
, c.clicks 
, c.installs
FROM adjust_looker a
FULL JOIN 
creative_performance c 
ON a.product_code = c.product_code 
AND a.platform = c.platform 
AND a.media_source = a.media_source 
AND a.round = c.round 
AND a.ad_name = c.ad_name 
--AND a.ad_id = c.ad_id 
where a.media_source is not null AND a.product_code not in ('pixelisle', 'blockc3')