Untitled

SELECT MAX(`Custom SQL Query`.`date`) AS `Calculation_302867123927130123`, 1.1000000000000001 AS `X__alias__0`
FROM (
    with applications as (
        SELECT appId, platform, name as appName, CONCAT(TRIM(REGEXP_EXTRACT(name, r"^([^-–—]*)")), ' ', LOWER(platform)) title, ltvShift
        FROM `playgendary-bi.meta_info.applications`
        WHERE appId in ('com.playgendary.homes', 'id1495346254') ) ,
    impressions_clicks AS (
        SELECT date, IFNULL(REGEXP_EXTRACT(app_id, r"^([^:]+)&hl"), app_id) as appId, media_source as mediaSource, campaign_id as campaignId, MAX(campaign_name) as campaignName, ad_id as adId , adset_id as adsetId, country_code as countryCode, SUM(impressions) AS impressions, SUM(IF(media_source='bytedanceglobal_int', af_clicks, clicks)) AS clicks
        FROM `playgendary-bi.dwh_ua_facts.aggregated_impressions_clicks` AS ds
        WHERE date BETWEEN '2021-08-01' AND CURRENT_DATE() AND
            IFNULL(REGEXP_EXTRACT(app_id, r"^([^:]+)&hl"), app_id) IN (SELECT distinct appId FROM applications) AND
            LOWER(media_source) NOT LIKE '%test%' --AND lower(media_source) NOT IN ('organic', 'restricted', 'null', 'googleadwords_int') AND media_source IS NOT NULL AND lower(media_source) NOT IN ('organic', 'restricted', 'null', 'googleadwords_int', 'af_cross_promotion', 'facebook_clicks') GROUP BY date, appId, mediaSource, campaignId, adId, adset_id, countryCode HAVING impressions > 0 OR clicks > 0 ) , installs_spend AS ( SELECT installDate as date, IFNULL(REGEXP_EXTRACT(appid, r"^([^:]+)&hl"), appid) as appId, mediaSource, campaignId, MAX(campaignName) as campaignName, adId as adId, adsetId as adsetId, countryCode as countryCode, SUM(installs) AS installs, SUM(spend) AS spend, FROM `playgendary-bi.aggregated_data.aggregated_cohort_revenue` WHERE installDate BETWEEN '2021-08-01' AND CURRENT_DATE() AND IFNULL(REGEXP_EXTRACT(appid, r"^([^:]+)&hl"), appid) IN (SELECT distinct appId FROM applications) AND lower(mediaSource) NOT IN ('organic', 'restricted', 'null', 'googleadwords_int', 'af_cross_promotion') AND LOWER(mediaSource) NOT LIKE '%test%' GROUP BY date, appId, mediaSource, campaignId, adId, adsetId, countryCode HAVING spend > 0 OR installs > 0 ), retention AS ( SELECT installDate AS date, appId, mediaSource, campaignId, MAX(campaignName) as campaignName, adId AS adId, IF(adsetId = 'null', REGEXP_EXTRACT(adId, r"^([^:]+)_"), adsetId) as adsetId, countryCode AS countryCode, SUM(IF(cohortDay = 1, eventsCount, 0)) players1, SUM(IF(cohortDay = 3, eventsCount, 0)) players3, SUM(IF(cohortDay = 7, eventsCount, 0)) players7 FROM `playgendary-bi.aggregated_data.aggregated_retention_events_daily` WHERE installDate BETWEEN '2021-08-01' AND CURRENT_DATE() AND appId IN (SELECT distinct appId FROM applications) AND lower(mediaSource) NOT IN ('organic', 'restricted', 'null', 'googleadwords_int', 'af_cross_promotion') AND LOWER(mediaSource) NOT LIKE '%test%' GROUP BY date, appId, mediaSource, campaignId, adId, adsetId, countryCode ), revenue as ( select installDate as date, appId, mediaSource, campaignId, countryCode, adId, adsetId as adsetId, sum(revenue*ifnull(v.vat, 0.7)) revenue , count(distinct if(date_diff(eventDate, installDate, day) <= 3, appsflyerId, null)) payers3, from `playgendary-bi.aggregated_data.daily_inapp_revenue` r left join `playgendary-bi.aggregated_data.vat` v on v.country_code = r.countryCode and v.platform = r.platform where appId in (SELECT distinct appId FROM applications) and installDate BETWEEN '2021-08-01' AND CURRENT_DATE() and date_diff(eventDate, installDate, day) <= 120 AND appId IN (SELECT distinct appId FROM applications) AND lower(mediaSource) NOT IN ('organic', 'restricted', 'null', 'googleadwords_int', 'af_cross_promotion') group by installDate, appId, mediaSource, campaignId, adId, adsetId, countryCode ), metrics as ( SELECT date, appId, mediaSource, campaignId, COALESCE(impressions_clicks.campaignName, installs_spend.campaignName) as campaignName, adId, adsetId, countryCode, impressions, clicks, installs, spend , revenue.revenue, payers3, players3 FROM impressions_clicks FULL JOIN installs_spend USING(date, appId, mediaSource, campaignId, adId, adsetId, countryCode) LEFT JOIN retention USING(date, appId, mediaSource, campaignId, adId, adsetId, countryCode) LEFT JOIN revenue USING(date, appId, mediaSource, campaignId, adId, adsetId, countryCode) ), creative_info AS ( SELECT appId, creativeName, ARRAY_AGG(googleDriveLink ORDER BY createdDate DESC LIMIT 1)[OFFSET (0)] googleDriveLink, CONCAT('https://drive.google.com/file/d/', ARRAY_AGG(fileId ORDER BY createdDate DESC LIMIT 1)[OFFSET (0)]) fileLink FROM `playgendary-bi.aggregated_data.creative_info` GROUP BY appId, creativeName ), metrics_and_creative_info as ( SELECT date, metrics.appId, adsetId, mediaSource, campaignId, campaignName, metrics.adId, countryCode, impressions, clicks, installs, spend , ap.creativeName, googleDriveLink, fileLink, revenue, players3, payers3 FROM metrics LEFT JOIN `playgendary-bi.aggregated_data.creative_ad_pair` AS ap ON metrics.adId = ap.adId AND STARTS_WITH(metrics.adId, ap.creativeName) LEFT JOIN creative_info AS info ON metrics.appId = info.appId AND ap.creativeName = info.creativeName ), casual_creative_core as ( SELECT date, m.appId, appName, platform, title, m.mediaSource, m.campaignId, m.campaignName, m.countryCode, m.adId, IFNULL( REGEXP_EXTRACT(REPLACE(m.adId, ' ', ''), r'_([a-zA-Z]{1}\d{1,3}_\w+?)_'), IF(ARRAY_LENGTH(SPLIT(REPLACE(adId, ' ', ''), '_')) > 2, SPLIT(REPLACE(adId, ' ', ''), '_')[OFFSET(2)], null)) AS creative, IFNULL( CONCAT( REGEXP_EXTRACT(REPLACE(adId, ' ', ''), r'_[a-zA-Z]{1}(\d{1,3})_'), IFNULL(REGEXP_EXTRACT(REPLACE(adId, ' ', ''), r'_[a-zA-Z]{1}\d{1,3}(_\d{2,3})'), ''), IFNULL(REGEXP_EXTRACT(REPLACE(adId, ' ', ''), r'_[a-zA-Z]{1}\d{1,3}_\d{2,3}.+(_<?v\d{1}>?)'), '') ), IF(ARRAY_LENGTH(SPLIT(REPLACE(adId, ' ', ''), '_')) > 1, SPLIT(REPLACE(adId, ' ', ''), '_')[OFFSET(1)], NULL)) AS adNum, CASE REGEXP_EXTRACT(REPLACE(adId, ' ', ''), r'_([a-zA-Z]{1})\d{1,3}_') WHEN 'V' THEN 'video' WHEN 'v' THEN 'video' WHEN 'p' THEN 'playable' WHEN 'P' THEN 'playable' WHEN 'e' THEN 'endcard' WHEN 'b' THEN 'banner' WHEN 'g' THEN 'gif' WHEN 'AAA' THEN 'AAA' ELSE IF( REGEXP_EXTRACT(REPLACE(adId, ' ', ''), r'Playgendary') = 'Playgendary', CASE WHEN adId LIKE '%vast%' THEN 'video' WHEN adId LIKE '%html%' THEN 'playable' WHEN adId LIKE '%native%' THEN 'banner' END, REGEXP_EXTRACT(REPLACE(campaignName, ' ', ''), r'AAA') ) END AS adType, --adsetId, --adset_name, IFNULL( REGEXP_EXTRACT(REPLACE(adId, ' ', ''), r'_[a-zA-Z]{1}\d{1,3}_\d{0,3}(\w+?)_'), IF(ARRAY_LENGTH(SPLIT(REPLACE(adId, ' ', ''), '_')) > 2, SPLIT(REPLACE(adId, ' ', ''), '_')[OFFSET(2)], null)) AS adName, IFNULL( REGEXP_EXTRACT(LOWER(REPLACE(adId, ' ', '')), r'(\d+x\d+)'), IF(LOWER(REPLACE(adId, ' ', '')) LIKE '%rotate%', 'Rotate', NULL)) AS adSize, spend, installs, impressions, clicks, revenue, creativeName, googleDriveLink, fileLink, IF(DATE_ADD(m.date, INTERVAL 3 DAY) >= CURRENT_DATE(), NULL, IF(IFNULL(installs, 0) = 0, NULL, IFNULL(players3, 0))) players3, payers3, ifnull(tiers.tier, 'WW ROW') geo_segment FROM metrics_and_creative_info m LEFT JOIN applications USING(appId) left join `playgendary-bi.coefs.ltv_dm_country_tiers` tiers on m.appId = tiers.appId and m.countryCode = tiers.countryCode left join `playgendary-bi.coefs.ltv_dm_coefficients` c on m.appId = c.appId and ifnull(tiers.tier, 'WW ROW') = c.tier and c.day = if(date_diff(current_date(), m.date, day)-1 > 120, 120, date_diff(current_date(), m.date, day)-1) left join `playgendary-bi.coefs.ltv_dm_extra_coefficients` e on m.appId = e.appId) , ga_inst_iap AS ( SELECT appId, campaignId, adGroupId, CASE adNetworkType WHEN 'YOUTUBE_WATCH' THEN 'video' WHEN 'SEARCH' THEN 'text' ELSE 'video' END AS network, date, countryCode, SUM(IF(conversionActionCategory = 'DOWNLOAD', conversions, 0)) installs_ga, SUM(IF(conversionActionCategory = 'PURCHASE', conversions, 0)) purchases_ga, SUM(IF(conversionActionCategory = 'PURCHASE', conversionsValue*ifnull(vat, 0.7), 0)) revenue_ga_vat, SUM(IF(conversionActionCategory = 'PURCHASE', conversionsValue, 0)) revenue_ga FROM `playgendary-bi.source_data.google_conversions` AS c LEFT JOIN `playgendary-bi.aggregated_data.vat` AS v ON c.countryCode = v.country_code AND IF(appId LIKE '%id%', 'ios', 'android') = v.platform WHERE date BETWEEN "2021-08-01" AND CURRENT_DATE() AND appId IN ('id1495346254', 'com.playgendary.homes') AND externalConversionSource = 'FIREBASE' GROUP BY appId, campaignId, adGroupId, network, date, countryCode ), ga_imp_cost AS ( SELECT s.appId, s.campaignId, s.adGroupId, MAX(campaignName) AS campaignName, MAX(adGroupName) AS adGroupName, CASE adNetworkType WHEN 'YOUTUBE_WATCH' THEN 'video' WHEN 'SEARCH' THEN 'text' ELSE 'video' END AS network, s.date, countryCode, IFNULL(h.targetCpa, s.targetCpa) AS targetCpa, IFNULL(h.targetRoas, s.targetRoas) AS targetRoas, IFNULL(h.campaignBudget, s.campaignBudget) AS campaignBudget, IFNULL(h.campaignBudget, s.campaignBudget) AS AdGroupCampaignBudget, SUM(impressions) AS impressions, SUM(clicks) AS clicks, SUM(cost) AS cost FROM `playgendary-bi.source_data.google_spend` AS s LEFT JOIN `playgendary-bi.source_data.google_target_roas_cpa_history` AS h ON s.appId = h.appId AND s.campaignId = h.campaignId AND s.date = h.date WHERE s.date BETWEEN "2021-08-01" AND CURRENT_DATE() AND s.appId IN ('id1495346254', 'com.playgendary.homes') GROUP BY appId, campaignId, adGroupId, network, date, countryCode, targetCpa, targetRoas, campaignBudget ), ga_creatives_spend AS ( SELECT storeId AS appId, campaignId, adGroupId, MAX(campaignName) AS campaignName, MAX(adGroupName) AS adGroupName, CASE network WHEN 'Youtube Videos' THEN 'video' WHEN 'Search Network' THEN 'text' ELSE 'video' END AS network, date, SUM(IF(network = 'Search Network', cost, 0)) search_spend, SUM(IF(network = 'Youtube Videos', cost, 0)) yt_spend, SUM(IF(network = 'Display Network' AND assetType = 'Video', cost, 0)) gdn_video_spend, SUM(IF(network = 'Display Network' AND assetType = 'HTML5', cost, 0)) gdn_html_spend FROM `playgendary-bi.source_data.google_ads_creative_stat` c WHERE storeId IN ('id1495346254', 'com.playgendary.homes') AND date BETWEEN "2021-08-01" AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) GROUP BY appId, campaignId, adGroupId, network, date ), af AS ( SELECT appId, campaignId, adsetId AS adGroupId, CASE siteId WHEN 'YouTubeVideos' THEN 'video' WHEN 'GoogleSearch' THEN 'text' ELSE 'video' END AS network, installDate AS date, countryCode, SUM(installs) AS installs FROM `playgendary-bi.aggregated_data.aggregated_cohort_revenue` WHERE installDate BETWEEN "2021-08-01" AND CURRENT_DATE() AND appId IN ('id1495346254', 'com.playgendary.homes') AND mediaSource = 'googleadwords_int' AND cohortDay <= 120 GROUP BY appId, campaignId, adGroupId, network, date, countryCode HAVING installs > 0 ), skad AS ( SELECT date, app_id AS appId, campaign_id AS campaignId, SUM(conversions) AS skad_installs, SUM(IF(IFNULL(conversions_value, 0) > 0, conversions, 0)) AS skad_payers1 FROM `playgendary-bi.source_data.google_skad` WHERE date BETWEEN "2021-08-01" AND CURRENT_DATE() AND app_id IN ('id1495346254') GROUP BY date, appId, campaignId ), ga_with_helper_rows AS( SELECT appId, campaignId, adGroupId, campaignName, adGroupName, network, date, countryCode, targetCpa, targetRoas, NULL AS campaignBudget, NULL AS AdGroupCampaignBudget, impressions, clicks, cost, 0 AS search_spend, 0 AS yt_spend, 0 AS gdn_video_spend, 0 AS gdn_html_spend, 0 AS gdn_other_spend, 0 AS helper_spend, NULL AS skad_installs, NULL AS skad_payers1 FROM ga_imp_cost UNION ALL SELECT appId, campaignId, adGroupId, campaignName, adGroupName, network, date, ' ' AS countryCode, NULL AS targetCpa, NULL AS targetRoas, NULL AS campaignBudget, NULL AS AdGroupCampaignBudget, 0 AS impressions, 0 AS clicks, 0 AS cost, search_spend, yt_spend, gdn_video_spend, gdn_html_spend, IFNULL(spend, 0) - search_spend - yt_spend - gdn_video_spend - gdn_html_spend AS gdn_other_spend, IFNULL(spend, 0) AS helper_spend, NULL AS skad_installs, NULL AS skad_payers1 FROM ga_creatives_spend LEFT JOIN ( SELECT appId, campaignId, adGroupId, network, date, SUM(cost) AS spend FROM ga_imp_cost GROUP BY appId, campaignId, adGroupId, network, date ) AS ga_imp_cost USING( appId, campaignId, adGroupId, network, date) UNION ALL SELECT DISTINCT appId, campaignId, ' ' AS adGroupId, campaignName, ' ' AS adGroupName, ' ' AS network, date, ' ' AS countryCode, NULL AS targetCpa, NULL AS targetRoas, campaignBudget, NULL AS AdGroupCampaignBudget, 0 AS impressions, 0 AS clicks, 0 AS spend, 0 AS search_spend, 0 AS yt_spend, 0 AS gdn_video_spend, 0 AS gdn_html_spend, 0 AS gdn_other_spend, 0 AS helper_spend, skad_installs, skad_payers1 FROM ga_imp_cost LEFT JOIN skad USING( date, appId, campaignId ) UNION ALL SELECT DISTINCT appId, campaignId, adGroupId, campaignName, adGroupName, ' ' AS network, date, ' ' AS countryCode, NULL AS targetCpa, NULL AS targetRoas, NULL AS campaignBudget, AdGroupCampaignBudget, 0 AS impressions, 0 AS clicks, 0 AS spend, 0 AS search_spend, 0 AS yt_spend, 0 AS gdn_video_spend, 0 AS gdn_html_spend, 0 AS gdn_other_spend, 0 AS helper_spend, 0 AS skad_installs, 0 AS skad_payers1 FROM ga_imp_cost ), p AS ( SELECT appId, campaignId, adsetId AS adGroupId, CASE siteId WHEN 'YouTubeVideos' THEN 'video' WHEN 'GoogleSearch' THEN 'text' ELSE 'video' END AS network, installDate AS date, countryCode, COUNT(DISTINCT(IF(DATE_DIFF(eventDate, installDate, DAY) <= 1, appsflyerId, NULL))) AS payers1, COUNT(DISTINCT(IF(DATE_DIFF(eventDate, installDate, DAY) <= 3, appsflyerId, NULL))) AS payers3, COUNT(DISTINCT(IF(DATE_DIFF(eventDate, installDate, DAY) <= 7, appsflyerId, NULL))) AS payers7, COUNT(DISTINCT(IF(DATE_DIFF(eventDate, installDate, DAY) <= 14, appsflyerId, NULL))) AS payers14, COUNT(DISTINCT(IF(DATE_DIFF(eventDate, installDate, DAY) <= 21, appsflyerId, NULL))) AS payers21, COUNT(DISTINCT(IF(DATE_DIFF(eventDate, installDate, DAY) <= 30, appsflyerId, NULL))) AS payers30, SUM(IF(DATE_DIFF(eventDate, installDate, DAY) <= 1, revenue*IFNULL(vat, 0.7), 0)) AS revenue1, SUM(IF(DATE_DIFF(eventDate, installDate, DAY) <= 3, revenue*IFNULL(vat, 0.7), 0)) AS revenue3, SUM(IF(DATE_DIFF(eventDate, installDate, DAY) <= 7, revenue*IFNULL(vat, 0.7), 0)) AS revenue7, SUM(IF(DATE_DIFF(eventDate, installDate, DAY) <= 14, revenue*IFNULL(vat, 0.7), 0)) AS revenue14, SUM(IF(DATE_DIFF(eventDate, installDate, DAY) <= 21, revenue*IFNULL(vat, 0.7), 0)) AS revenue21, SUM(IF(DATE_DIFF(eventDate, installDate, DAY) <= 30, revenue*IFNULL(vat, 0.7), 0)) AS revenue30, SUM(IF(DATE_DIFF(eventDate, installDate, DAY) <= 1, eventCount, 0)) AS purchases1, SUM(IF(DATE_DIFF(eventDate, installDate, DAY) <= 3, eventCount, 0)) AS purchases3, SUM(IF(DATE_DIFF(eventDate, installDate, DAY) <= 7, eventCount, 0)) AS purchases7, SUM(IF(DATE_DIFF(eventDate, installDate, DAY) <= 14, eventCount, 0)) AS purchases14, SUM(IF(DATE_DIFF(eventDate, installDate, DAY) <= 21, eventCount, 0)) AS purchases21, SUM(IF(DATE_DIFF(eventDate, installDate, DAY) <= 30, eventCount, 0)) AS purchases30, SUM(revenue*IFNULL(vat, 0.7)) AS revenue, SUM(eventCount) AS purchases, COUNT(DISTINCT appsflyerId) AS payers FROM `playgendary-bi.aggregated_data.daily_inapp_revenue` AS i LEFT JOIN `playgendary-bi.aggregated_data.vat` AS v ON i.countryCode = v.country_code AND i.platform = v.platform WHERE installDate BETWEEN '2021-08-01' AND CURRENT_DATE() AND appId IN ('id1495346254', 'com.playgendary.homes') AND mediaSource = 'googleadwords_int' AND DATE_DIFF(eventDate, installDate, day) <= 120 AND i.eventDate >= i.installDate GROUP BY appId, campaignId, adGroupId, network, date, countryCode ), rr AS ( SELECT appId, campaignId, adsetId AS adGroupId, CASE siteId WHEN 'YouTubeVideos' THEN 'video' WHEN 'GoogleSearch' THEN 'text' ELSE 'video' END AS network, installDate AS date, countryCode, SUM(IF(cohortDay = 3, eventsCount, 0)) AS players3, SUM(IF(cohortDay = 7, eventsCount, 0)) AS players7, SUM(IF(cohortDay = 30, eventsCount, 0)) AS players30 FROM `marketing-analytics-235713.aggregated_events_hd.retention_daily` WHERE installDate BETWEEN '2021-08-01' AND CURRENT_DATE() AND appId IN ('id1495346254', 'com.playgendary.homes') AND mediaSource = 'googleadwords_int' GROUP BY appId, campaignId, adGroupId, network, date, countryCode ), campaigns AS ( SELECT DISTINCT campaignId, LAST_VALUE(campaignName) OVER(PARTITION BY campaignId ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) campaignName FROM ga_imp_cost ), adgroups AS ( SELECT DISTINCT adGroupId, LAST_VALUE(adGroupName) OVER(PARTITION BY adGroupId ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) adGroupName FROM ga_imp_cost ), dm_gm_afgroups as ( SELECT *, SAFE_DIVIDE(SUM(payers1) OVER(camp), SUM(installs) OVER(camp)) C2B1_camp, SAFE_DIVIDE(SUM(payers3) OVER(camp), SUM(installs) OVER(camp)) C2B3_camp, SAFE_DIVIDE(SUM(payers7) OVER(camp), SUM(installs) OVER(camp)) C2B7_camp, SAFE_DIVIDE(SUM(payers14) OVER(camp), SUM(installs) OVER(camp)) C2B14_camp, SAFE_DIVIDE(SUM(payers21) OVER(camp), SUM(installs) OVER(camp)) C2B21_camp, SAFE_DIVIDE(SUM(payers30) OVER(camp), SUM(installs) OVER(camp)) C2B30_camp, SAFE_DIVIDE(SUM(purchases1) OVER(camp), SUM(installs) OVER(camp)) C2P1_camp, SAFE_DIVIDE(SUM(purchases3) OVER(camp), SUM(installs) OVER(camp)) C2P3_camp, SAFE_DIVIDE(SUM(purchases7) OVER(camp), SUM(installs) OVER(camp)) C2P7_camp, SAFE_DIVIDE(SUM(purchases14) OVER(camp), SUM(installs) OVER(camp)) C2P14_camp, SAFE_DIVIDE(SUM(purchases21) OVER(camp), SUM(installs) OVER(camp)) C2P21_camp, SAFE_DIVIDE(SUM(purchases30) OVER(camp), SUM(installs) OVER(camp)) C2P30_camp, SAFE_DIVIDE(SUM(revenue1) OVER(camp), SUM(installs) OVER(camp)) RPI1_camp, SAFE_DIVIDE(SUM(revenue3) OVER(camp), SUM(installs) OVER(camp)) RPI3_camp, SAFE_DIVIDE(SUM(revenue7) OVER(camp), SUM(installs) OVER(camp)) RPI7_camp, SAFE_DIVIDE(SUM(revenue14) OVER(camp), SUM(installs) OVER(camp)) RPI14_camp, SAFE_DIVIDE(SUM(revenue21) OVER(camp), SUM(installs) OVER(camp)) RPI21_camp, SAFE_DIVIDE(SUM(revenue30) OVER(camp), SUM(installs) OVER(camp)) RPI30_camp, SAFE_DIVIDE(SUM(revenue) OVER(camp), SUM(installs) OVER(camp)) RPI_camp FROM ( SELECT IFNULL(ga.appId, af.appId) AS appId, IFNULL(ga.campaignId, af.campaignId) AS campaignId, IFNULL(ga.adGroupId, af.adGroupId) AS adGroupId, IF(SUM(IF(DATE_DIFF(CURRENT_DATE(), IFNULL(ga.date, af.date), DAY) <= 3, IFNULL(cost, 0), 0)) OVER(PARTITION BY ga.campaignId) > 0, 'active', 'not_active') AS isActive, SUM(IF(DATE_DIFF(CURRENT_DATE(), ifnull(ga.date, af.date), DAY) <= 3, IFNULL(cost, 0), 0)) OVER(PARTITION BY ga.campaignId) AS cost_last_3_days, IFNULL(camp.campaignName, camp.campaignName) AS campaignName, IFNULL(adgr.adGroupName, adgr.adGroupName) AS adGroupName, IFNULL(ga.network, af.network) AS network, IFNULL(ga.date, af.date) AS date, IFNULL(ga.countryCode, af.countryCode) AS geo, (CASE WHEN t.tier = 'US' THEN '01_US' WHEN t.tier = 'JP' THEN '02_JP' WHEN t.tier = 'T1' THEN '03_T1' WHEN t.tier = 'Asia' THEN '04_Asia' WHEN t.tier = 'T3' THEN '05_T3' WHEN t.tier = 'WW Asia' THEN '06_WW Asia' ELSE 'Other' END) AS geo_segment, targetCpa, targetRoas, campaignBudget, AdGroupCampaignBudget, IFNULL(impressions, 0) AS impressions, IFNULL(clicks, 0) AS clicks, IFNULL(installs_ga, 0) AS installs_ga, IFNULL(purchases_ga, 0) AS purchases_ga, IFNULL(revenue_ga_vat, 0) AS revenue_ga_vat, IFNULL(revenue_ga, 0) AS revenue_ga, IFNULL(cost, 0) AS cost, IFNULL(installs, 0) AS installs, IF(DATE_ADD(date, INTERVAL 3 DAY) >= CURRENT_DATE(), NULL, IFNULL(players3, 0)) AS players3, IF(DATE_ADD(date, INTERVAL 7 DAY) >= CURRENT_DATE(), NULL, IFNULL(players7, 0)) AS players7, IF(DATE_ADD(date, INTERVAL 30 DAY) >= CURRENT_DATE(), NULL, IFNULL(players30, 0)) AS players30, IF(DATE_ADD(date, INTERVAL 1 DAY) >= CURRENT_DATE(), NULL, IFNULL(payers1, 0)) AS payers1, IF(DATE_ADD(date, INTERVAL 3 DAY) >= CURRENT_DATE(), NULL, IFNULL(payers3, 0)) AS payers3, IF(DATE_ADD(date, INTERVAL 7 DAY) >= CURRENT_DATE(), NULL, IFNULL(payers7, 0)) AS payers7, IF(DATE_ADD(date, INTERVAL 14 DAY) >= CURRENT_DATE(), NULL, IFNULL(payers14, 0)) AS payers14, IF(DATE_ADD(date, INTERVAL 21 DAY) >= CURRENT_DATE(), NULL, IFNULL(payers21, 0)) AS payers21, IF(DATE_ADD(date, INTERVAL 30 DAY) >= CURRENT_DATE(), NULL, IFNULL(payers30, 0)) AS payers30, IF(DATE_ADD(date, INTERVAL 1 DAY) >= CURRENT_DATE(), NULL, IFNULL(revenue1, 0)) AS revenue1, IF(DATE_ADD(date, INTERVAL 3 DAY) >= CURRENT_DATE(), NULL, IFNULL(revenue3, 0)) AS revenue3, IF(DATE_ADD(date, INTERVAL 7 DAY) >= CURRENT_DATE(), NULL, IFNULL(revenue7, 0)) AS revenue7, IF(DATE_ADD(date, INTERVAL 14 DAY) >= CURRENT_DATE(), NULL, IFNULL(revenue14, 0)) AS revenue14, IF(DATE_ADD(date, INTERVAL 21 DAY) >= CURRENT_DATE(), NULL, IFNULL(revenue21, 0)) AS revenue21, IF(DATE_ADD(date, INTERVAL 30 DAY) >= CURRENT_DATE(), NULL, IFNULL(revenue30, 0)) AS revenue30, IF(DATE_ADD(date, INTERVAL 1 DAY) >= CURRENT_DATE(), NULL, IFNULL(purchases1, 0)) AS purchases1, IF(DATE_ADD(date, INTERVAL 3 DAY) >= CURRENT_DATE(), NULL, IFNULL(purchases3, 0)) AS purchases3, IF(DATE_ADD(date, INTERVAL 7 DAY) >= CURRENT_DATE(), NULL, IFNULL(purchases7, 0)) AS purchases7, IF(DATE_ADD(date, INTERVAL 14 DAY) >= CURRENT_DATE(), NULL, IFNULL(purchases14, 0)) AS purchases14, IF(DATE_ADD(date, INTERVAL 21 DAY) >= CURRENT_DATE(), NULL, IFNULL(purchases21, 0)) AS purchases21, IF(DATE_ADD(date, INTERVAL 30 DAY) >= CURRENT_DATE(), NULL, IFNULL(purchases30, 0)) AS purchases30, IFNULL(revenue, 0) revenue, IFNULL(purchases, 0) purchases, IFNULL(payers, 0) payers, IFNULL(search_spend, 0) AS search_spend, IFNULL(yt_spend, 0) AS yt_spend, IFNULL(gdn_video_spend, 0) AS gdn_video_spend, IFNULL(gdn_html_spend, 0) AS gdn_html_spend, IFNULL(gdn_other_spend, 0) AS gdn_other_spend, IFNULL(helper_spend, 0) AS helper_s...(string is too long)