WITH channel_data as (

SELECT fulldate,
EXTRACT(YEAR from fulldate) as year
,EXTRACT(QUARTER from fulldate) as quarter
,EXTRACT(MONTH from fulldate) as month
,EXTRACT(WEEK from fulldate) as week
,EXTRACT(DAY from fulldate) as day
,channel
,lower_source as source
,lower_campaign as campaign
,case when lower_campaign in ('nl test 4.2',
                        'nl test 5.2',
                        'nl test 3.2',
                        'nl test 1.2',
                        'nl test 2.2') then 'Test_NL'
      when lower_campaign in ('search:google:nl:nl:designername:bmm::women',
                        'search:google:nl:nl:designername:exact::women',
                        'search:google:nl:nl:designername:bmm::unisex',
                        'dsa:google:nl:nl:designername::unisex',
                        'search:google:nl:nl:designername:exact::unisex') then 'Control_NL'
      when lower_campaign in ('search:google:ap:en:designername:bmm::women',
                         'search:google:ap:en:designername:exact::women',
                         'search:google:ap:en:designername:bmm::unisex',
                         'search:google:ap:en:generics:bmm:clothing') then 'Control_APAC'
      when lower_campaign in ('sg test 4.2',
                        'sg test 2.2',
                        'sg test 3.2',
                        'sg text 1.2') then 'Test_APAC'     end as Type
,lower_subchannel as subchannel
,CASE
    WHEN channel IN ('ppc', 'affiliates', 'display')
    THEN 'Paid'
    ELSE 'Non_Paid'
END AS channel_type
--,region
,cast(sum(orders) as numeric) as orders
,cast(sum(app_orders) as numeric)  as app_orders
,cast(sum(web_orders) as numeric) as web_orders
,cast(sum(new_orders) as numeric) as new_orders
,cast(sum(existing_orders) as numeric) as exist_orders
,cast(sum(impressions) as numeric) as impressions
,cast(sum(clicks) as numeric) as clicks
,cast(sum(visits) as numeric) as visits
,cast(sum(app_visits) as numeric) as app_visits
,cast(sum(web_visits) as numeric) as web_visits
,cast(sum(GTV_USD) as numeric) as GTV_USD
,cast(sum(App_GTV_USD) as numeric) as App_GTV_USD
,cast(sum(Web_GTV_USD) as numeric) as Web_GTV_USD
,cast(sum(New_GTV_USD) as numeric) as New_GTV_USD
,cast(sum(Existing_GTV_USD) as numeric) as Existing_GTV_USD
,cast(sum(GMV_USD) as numeric) as GMV_USD
,cast(sum(App_GMV_USD) as numeric) as App_GMV_USD
,cast(sum(Web_GMV_USD) as numeric) as Web_GMV_USD
,cast(sum(New_GMV_USD) as numeric) as New_GMV_USD
,cast(sum(Existing_GMV_USD) as numeric) as Existing_GMV_USD
,cast(sum(New_CostWithFees_USD) as numeric) as New_CostWithFees_USD
,cast(sum(Existing_CostWithFees_USD) as numeric) as Existing_CostWithFees_USD
,cast(sum(CostWithFees_USD) as numeric) as CostWithFees_USD
,cast(sum(PromoCost_USD) as numeric) as PromoCost_USD
,cast(sum(FreeShippingCost_USD) as numeric) as FreeShippingCost_USD
FROM `ff-marketing-analytics.core.trafficdashboard_app_lt_summary`
WHERE fulldate >= '2020-02-05'
GROUP BY fulldate, year, quarter, month, week, channel, channel_type, source ,subchannel, campaign--region

)

select * --fulldate, sum(gtv_usd) as gtv, sum(CostWithFees_USD) as cost
from channel_data
where channel = 'ppc'
and campaign in ('nl test 4.2',
'nl test 5.2',
'nl test 3.2',
'nl test 1.2',
'nl test 2.2',
'search:google:nl:nl:designername:bmm::women',
'search:google:nl:nl:designername:exact::women',
'search:google:nl:nl:designername:bmm::unisex',
'dsa:google:nl:nl:designername::unisex',
'search:google:nl:nl:designername:exact::unisex',
'sg test 4.2',
'sg test 2.2',
'sg test 3.2',
'sg text 1.2',
'search:google:ap:en:designername:bmm::women',
'search:google:ap:en:designername:exact::women',
'search:google:ap:en:designername:bmm::unisex',
'search:google:ap:en:generics:bmm:clothing')
-- and subchannel in ('shopping','ppc nonbrand')
-- group by fulldate
order by fulldate