test

mail@pastecode.io avatar
unknown
sql
2 months ago
11 kB
6
Indexable
Never

-----------AGC----------------
-----------create sales metrics from gc datatsets----------

create temp table agc_order_base_{TEMPORARY_TABLE_SEQUENCE} as

(
    select  dcgoi.marketplace_id,
            DATE(dcgoi.order_day) as calendar_day,
    		nvl(dcgoi.asin, 'Unknown') as asin,
            CASE
                    WHEN LOWER(dcgoi.device_sub_type) LIKE '%tablet%' THEN 'Tablet'
                    WHEN LOWER(dcgoi.device_type) = 'pc' THEN 'Desktop'
                    WHEN dcgoi.device_type is NULL THEN 'Unknown'
                	ELSE dcgoi.device_type
            END as device_type_std,
    		nvl(tcru.traffic_channel_id,0) as traffic_channel_id,
    
(COUNT(DISTINCT CASE WHEN (figt.gc_transaction_type_id  = 1) THEN figt.gc_id  ELSE NULL END)) -
                   (COUNT(DISTINCT CASE WHEN (figt.gc_transaction_type_id  IN (12,14)) THEN figt.gc_id  ELSE NULL END))  AS "gcs_units_net"

            , (COUNT(DISTINCT CASE WHEN (figt.gc_transaction_type_id  = 1) THEN dcgoi.order_id  ELSE NULL END)) -
                   (COUNT(DISTINCT CASE WHEN (figt.gc_transaction_type_id  IN (12,14)) THEN dcgoi.order_id  ELSE NULL END))  AS "gcs_orders_net"

             ,(COALESCE(SUM(CASE WHEN (figt.gc_transaction_type_id  = 1) THEN figt.amount  ELSE NULL END), 0)) + (COALESCE(SUM(CASE WHEN (figt.gc_transaction_type_id  = 23) THEN figt.amount  ELSE NULL END), 0)) -
              (COALESCE(SUM(CASE WHEN (figt.gc_transaction_type_id  = 12) THEN figt.amount  ELSE NULL END), 0)) - (COALESCE(SUM(CASE WHEN (figt.gc_transaction_type_id  = 14) THEN figt.amount  ELSE NULL END), 0))                 AS "gcs_issuance_amount_net"
        from gcbi_ddl.dim_csi_gc_order_items as dcgoi
                 inner join gcbi_ddl.fct_int_gc_transactions as figt
                            on  dcgoi.gc_id = figt.gc_id
                            and dcgoi.marketplace_id = figt.marketplace_id
                            and figt.gc_transaction_type_id in (1,12,14)
                 left join charybdis_ddl.gc_order_traffic_details trfc
                            ON  dcgoi.order_id       = cast(trfc.order_id as varchar(40))
                            AND dcgoi.marketplace_id = trfc.marketplace_id
                            AND dcgoi.order_day = trfc.order_day
                 LEFT JOIN gcbi_ddl.o_gc_traffic_channel_rollups tcru
                            ON trfc.traffic_channel_id = tcru.traffic_channel_id
          WHERE 1=1
        AND dcgoi.order_day BETWEEN TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-{FREE_FORM} AND TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')
    	AND dcgoi.marketplace_id IN ({MARKETPLACE_ID})
        group by 1, 2, 3, 4, 5
    );   
    
    


-------------------------Traffic base data creation---------------
------------------------------------------------------------------

-------------------------detect human sessions for AGC--------------------

CREATE temp table hits_{TEMPORARY_TABLE_SEQUENCE} as
select octh.* from

(select hit_hour_utc, 
cast(hit_day as date) as hit_day,
asin,
site_variant,
gl_product_group,ref_marker, 
is_glance_view,
hit_type,
session_id,
marketplace_id,
page_type,
page_type_id_source,
channel_id,
session_traffic_channel
 
    from andes.GCBI_DDL.STG_O_CUSTOMER_TRAFFIC_HITS 
    	where hit_day BETWEEN TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-{FREE_FORM} AND TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-1
    	and ( page_type_id_source IN ('ASIN', 'asin', 'titleId', 'GTI') OR UPPER (page_type) IN ('DETAIL', 'DETAILAW') )
    	and marketplace_id in (1,7)
   	 and gl_product_group=228) octh
     
			INNER JOIN  
            
            (select distinct session_id 
            		from andes.RobotDetection.O_HUMAN_SESSIONS  
                	where session_day BETWEEN TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-{FREE_FORM} AND TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-1
                    and legal_entity_id in (101,115)
                    ) oh
                    
	on octh.session_id=oh.session_id;

-----------
 
 create temp table gc_funnel_traffic_base_{TEMPORARY_TABLE_SEQUENCE} as
 Select cast(hit_day as date) AS calendar_day,
       h.marketplace_id,
       h.asin,
       CASE
                WHEN LOWER(TRIM(h.site_variant)) LIKE '%mobile%' OR
                     LOWER(TRIM(h.site_variant)) LIKE '%phone%' OR
                     LOWER(TRIM(h.site_variant)) LIKE '%phon%' OR
                     LOWER(TRIM(h.site_variant)) LIKE '%mshop%' OR
                     LOWER(TRIM(h.site_variant)) LIKE '%mob%'
                THEN 'Mobile'
                WHEN LOWER(TRIM(h.site_variant)) LIKE '%pc%' OR
                     LOWER(TRIM(h.site_variant)) LIKE '%desktop%' OR
                     LOWER(TRIM(h.site_variant)) LIKE '%mac%'
                THEN 'Desktop'
                WHEN LOWER(TRIM(h.site_variant)) LIKE '%tablet%' OR
                     LOWER(TRIM(h.site_variant)) LIKE '%ipad%' OR
                     LOWER(TRIM(h.site_variant)) LIKE '%tab%' OR
                     LOWER(TRIM(h.site_variant)) LIKE '%kindle%'
                THEN 'Tablet'
                ELSE 'Unknown'
             END as device,
    case when os.traffic_channel_id is not null then os.traffic_channel_id 
         when os.traffic_channel_id is null then os.traffic_channel_id 
         end as session_traffic_channel_id,
    --case when h.gl_product_group=228 then 'AGC' when h.gl_product_group in (396,397) then 'BGC' end as brand,
    ref_marker as node_1,
	sum(case when (is_glance_view=1 and  lower(h.hit_type) in ('page-hit', 'back-forward-button')
 ) then 1 else 0 END) as total_gv_with_case

    from 
    hits h
    left join (select session_id, cast(first_viewed_page_day as date) as first_viewed_page_day, traffic_channel_id ,marketplace_id
                                from andes.Clickstream.O_SESSIONS where marketplace_id in (1,7) 
                                and region_id =1
                                and first_viewed_page_day between TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-{FREE_FORM} AND TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD')-1) os
    ON h.session_id = os.session_id 
    and h.marketplace_id=os.marketplace_id
  and hit_day = first_viewed_page_day
  where  1=1
group by 1,2,3,4,5,6,7;



------ derive ingress point 1 from ref tags dataset and eliminate duplicates-------

create temp table reftag_dups_check_{TEMPORARY_TABLE_SEQUENCE} as
select
    node_1, prefix, description from
    (select distinct node_1 from gc_funnel_traffic_base_{TEMPORARY_TABLE_SEQUENCE}) a
    left join gcbi_ddl.ref_tags b
   on lower( regexp_replace(a.node_1,'_','^') ) like lower( regexp_replace(prefix,'_','^') );


create temp table reftag_dups_check_{TEMPORARY_TABLE_SEQUENCE}as
select * from (
select
    *,
    row_number() over(partition by node_1  order by length(prefix) desc) as sort_order
from reftag_dups_check_{TEMPORARY_TABLE_SEQUENCE})
where sort_order=1;

-------- dedupliacted ref tags addition to traffic base table and normalise sales and traffic data--------

create temp table agc_combine_order_funnel_{TEMPORARY_TABLE_SEQUENCE} AS
select
coalesce(a.calendar_day, b.calendar_day) as calendar_day,
coalesce(a.marketplace_id, b.marketplace_id) as marketplace_id,
nvl(coalesce(a.asin, b.asin),'Unknown')  as asin,
nvl(coalesce(a.device, b.device_type_std), 'Unknown') as device_type,
nvl(coalesce(a.session_traffic_channel, b.traffic_channel_id),0) as traffic_channel_id,
nvl(fet.description, 'Unknown') as node_1,
total_gv as gcs_total_gv,
nvl(gcs_units_net*ratio_to_report(gcs_units_net) OVER (partition by coalesce(a.calendar_day, b.calendar_day),coalesce(a.marketplace_id, b.marketplace_id),coalesce(a.asin, b.asin),nvl(coalesce(a.device, b.device_type_std),'Unknown') ,nvl(coalesce(a.session_traffic_channel, b.traffic_channel_id),0),nvl(coalesce(a.form_factor, b.form_factor),'unknown'),0)) as ratio_gcs_units_net,
nvl(gcs_orders_net*ratio_to_report(gcs_orders_net) OVER (partition by coalesce(a.calendar_day, b.calendar_day),coalesce(a.marketplace_id, b.marketplace_id),coalesce(a.asin, b.asin),nvl(coalesce(a.device, b.device_type_std),'Unknown') ,nvl(coalesce(a.session_traffic_channel, b.traffic_channel_id),0),nvl(coalesce(a.form_factor, b.form_factor),'unknown'),0)) as ratio_gcs_orders_net,
nvl(gcs_issuance_amount_net*ratio_to_report(gcs_issuance_amount_net) OVER (partition by coalesce(a.calendar_day, b.calendar_day),coalesce(a.marketplace_id, b.marketplace_id),coalesce(a.asin, b.asin),nvl(coalesce(a.device, b.device_type_std),'Unknown') ,nvl(coalesce(a.session_traffic_channel, b.traffic_channel_id),0),nvl(coalesce(a.form_factor, b.form_factor),'unknown'),0)) as ratio_gcs_issuance_amount_net,
nvl(gcs_issuance_amount_net*ratio_to_report(gcs_issuance_amount_net_usd) OVER (partition by coalesce(a.calendar_day, b.calendar_day),coalesce(a.marketplace_id, b.marketplace_id),coalesce(a.asin, b.asin),nvl(coalesce(a.device, b.device_type_std),'Unknown') ,nvl(coalesce(a.session_traffic_channel, b.traffic_channel_id),0),nvl(coalesce(a.form_factor, b.form_factor),'unknown'),0)) as ratio_gcs_issuance_amount_net_usd
from gc_funnel_traffic_base_{TEMPORARY_TABLE_SEQUENCE} a
full outer join agc_order_base_{TEMPORARY_TABLE_SEQUENCE} b
on a.calendar_day = b.calendar_day
and a.marketplace_id = b.marketplace_id
and a.asin = b.asin
and a.session_traffic_channel = b.traffic_channel_id
and a.device = b.device_type_std
left join reftag_dups_check_{TEMPORARY_TABLE_SEQUENCE}fet
on a.node_1=fet.node_1;

---------add dimensions to sales and traffic metrics-------


create temp table agc_funnel_sales_agg_temp_{TEMPORARY_TABLE_SEQUENCE} as
select
a.calendar_day,
a.marketplace_id,
1 as Region_id,
'agc' as gc_business_stream,
nvl(a.device_type,'Unknown') as device_type,
a.asin,
a.Traffic_Channel_Id,
CASE
    WHEN LOWER(TRIM(nvl(tcru.traffic_channel_rollup,'Unknown'))) IN ('google shopping','aap','aap_channel','robots','subsidiaries','deep linking','other','mobile devices','push','dra','sms') THEN 'Other'
    WHEN LOWER(TRIM(nvl(tcru.traffic_channel_rollup,'Unknown'))) IN ('',' ','null','untagged') OR tcru.traffic_channel_rollup IS NULL THEN 'unknown'
    ELSE LOWER(TRIM(nvl(tcru.traffic_channel_rollup,'Unknown')))
    END as Traffic_Channel,
    tcru.traffic_channel_rollup as Traffic_Channel_Group1,
    tcru.gc_channel_class as Traffic_Channel_Group2,
   node_1 as Ingress_Poin1,
nvl(sum(a.gcs_total_gv),0) as Glance_Views,
nvl(sum(a.ratio_gcs_units_net),0) as Normalize_Gc_Netunit,
nvl(sum(a.ratio_gcs_orders_net),0) as Normalize_Gc_Netorder,
nvl(sum(a.ratio_gcs_issuance_amount_net),0) as Normalize_Gc_Netissuance_Usd,
nvl(sum(a.ratio_gcs_issuance_amount_net_usd),0) as Normalize_Gc_Netissuance_Locale,
'USD' as Base_Currency_Code
from agc_combine_order_funnel_{TEMPORARY_TABLE_SEQUENCE} a
left outer join gcbi_ddl.dim_csm_asin_attributes d
on  a.asin= d.asin
and a.marketplace_id = d.marketplace_id
LEFT JOIN gcbi_ddl.o_gc_traffic_channel_rollups tcru
ON a.traffic_channel_id = tcru.traffic_channel_id
group by 1,2,3,4,5,6,7,8,9,10,11;
Leave a Comment