test
unknown
sql
a year ago
11 kB
8
Indexable
-----------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;Editor is loading...
Leave a Comment