427 lines
15 KiB
Bash
427 lines
15 KiB
Bash
|
#!/bin/bash
|
||
|
|
|
||
|
|
|
||
|
|
echo -e " \n----------- ACCEPTING NUMBER OF DAYS BEFORE THE RUN DATE FOR WHICH THE REPORT IS TO BE RUN --------------\n"
|
||
|
|
|
||
|
|
backDay=$1
|
||
|
|
|
||
|
|
echo $backDay
|
||
|
|
|
||
|
|
echo -e " \n------------- DATE IN THE REQUIRED FORMAT --------------\n"
|
||
|
|
reportDate=$(date -d"$backDay day ago" "+%Y-%m-%d")
|
||
|
|
echo 'reportDate'=$reportDate
|
||
|
|
|
||
|
|
echo "
|
||
|
|
|
||
|
|
drop table if exists raena_analytics.brand_promo_dashboard_table ;
|
||
|
|
|
||
|
|
create table raena_analytics.brand_promo_dashboard_table
|
||
|
|
as
|
||
|
|
select BB.name brand_name, type promo_type, sku ,'' free_sku, startdate , enddate, cast(coalesce(promo,'0') as float) promo ,cast(coalesce(Moq,'0') as float) moq,cast(coalesce(mov,'0') as float) mov
|
||
|
|
from raena_analytics.brand_promo_master AA
|
||
|
|
left join raena_catalog_management.brand BB on AA.brandid = cast(BB.id as varchar)
|
||
|
|
union
|
||
|
|
select BB.name brand_name,type ,
|
||
|
|
case when promoselection = 'ANY' then concat('ANY (', promoquantity_1)
|
||
|
|
when promosku_2 ='(' then concat(promosku_1,promoquantity_1)
|
||
|
|
else concat(concat(promosku_1,promoquantity_1),concat('+',concat(promosku_2,promoquantity_2))) end Promo_sku,
|
||
|
|
case when freeselection = 'ANY' then concat('ANY (', freequantity_1)
|
||
|
|
when freeselection = 'SAME' then (case when promosku_2 ='(' then concat(promosku_1,freequantity_1)
|
||
|
|
else concat(concat(promosku_1,freequantity_1),concat('+',concat(promosku_2,freequantity_2))) end)
|
||
|
|
when freesku_2 ='(' then concat(freesku_1,freequantity_1)
|
||
|
|
else concat(concat(freesku_1,freequantity_1),concat('+',concat(freesku_2,freequantity_2)))
|
||
|
|
end free_sku,
|
||
|
|
startdate,
|
||
|
|
enddate,
|
||
|
|
0 promo,
|
||
|
|
0 Moq,
|
||
|
|
0 mov
|
||
|
|
from
|
||
|
|
(
|
||
|
|
select id , name , brandid ,
|
||
|
|
type,
|
||
|
|
promobrand,
|
||
|
|
concat(split_part(promosku,';',1),'(') promosku_1,
|
||
|
|
concat(split_part(promosku,';',2),'(') promosku_2,
|
||
|
|
concat(split_part(promoquantity,';',1),')') promoquantity_1,
|
||
|
|
concat(split_part(promoquantity,';',2),')') promoquantity_2,
|
||
|
|
promoselection,
|
||
|
|
freebrand,
|
||
|
|
concat(split_part(freesku,';',1),'(') freesku_1,
|
||
|
|
concat(split_part(freesku,';',2),'(') freesku_2,
|
||
|
|
concat(split_part(freequantity,';',1),')') freequantity_1,
|
||
|
|
concat(split_part(freequantity,';',2),')') freequantity_2,
|
||
|
|
freeselection,
|
||
|
|
startdate,
|
||
|
|
enddate
|
||
|
|
from raena_analytics.brand_promo_master A
|
||
|
|
left join raena_analytics.buyngetx_promo B on A.id = B.promo_id
|
||
|
|
left join raena_analytics.buyngetx_free C on A.id = C.promo_id
|
||
|
|
where type = 'BUY_N_GET_X'
|
||
|
|
order by 1) AA
|
||
|
|
left join raena_catalog_management.brand BB on AA.brandid = cast(BB.id as varchar)
|
||
|
|
order by startdate ;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.clevertap_conversion_base;
|
||
|
|
|
||
|
|
--cast(left(ts,4) || '-' || right(left(ts,6),2) || '-' || right(left(ts,8),2) as date)
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.clevertap_conversion_base AS
|
||
|
|
SELECT profile_objectid,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
profile_platform,
|
||
|
|
eventname,
|
||
|
|
1 sort_id,
|
||
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
|
||
|
|
FROM clevertap.app_launched
|
||
|
|
UNION ALL
|
||
|
|
SELECT profile_objectid,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
profile_platform,
|
||
|
|
eventname,
|
||
|
|
2 sort_id,
|
||
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
|
||
|
|
FROM clevertap.view_item
|
||
|
|
UNION ALL
|
||
|
|
SELECT profile_objectid,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
profile_platform,
|
||
|
|
eventname,
|
||
|
|
3 sort_id,
|
||
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
|
||
|
|
FROM clevertap.view_cart
|
||
|
|
UNION ALL
|
||
|
|
SELECT profile_objectid,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
profile_platform,
|
||
|
|
eventname,
|
||
|
|
4 sort_id,
|
||
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
|
||
|
|
FROM clevertap.begin_checkout
|
||
|
|
UNION ALL
|
||
|
|
SELECT profile_objectid,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
profile_platform,
|
||
|
|
eventname,
|
||
|
|
5 sort_id,
|
||
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
|
||
|
|
FROM clevertap.finish_checkout
|
||
|
|
UNION ALL
|
||
|
|
SELECT profile_objectid,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
profile_platform,
|
||
|
|
eventname,
|
||
|
|
6 sort_id,
|
||
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
|
||
|
|
FROM clevertap.charged;
|
||
|
|
|
||
|
|
|
||
|
|
SELECT count(1),
|
||
|
|
max(transaction_date)
|
||
|
|
FROM raena_analytics.clevertap_conversion_base;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.clevertap_checkout_base;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.clevertap_checkout_base AS
|
||
|
|
SELECT DISTINCT profile_objectid,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
profile_platform,
|
||
|
|
eventname,
|
||
|
|
4 sort_id,
|
||
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
|
||
|
|
FROM clevertap.begin_checkout
|
||
|
|
UNION ALL
|
||
|
|
SELECT DISTINCT profile_objectid,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
profile_platform,
|
||
|
|
eventname,
|
||
|
|
5 sort_id,
|
||
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
|
||
|
|
FROM clevertap.finish_checkout;
|
||
|
|
|
||
|
|
|
||
|
|
SELECT count(1) ,
|
||
|
|
max(transaction_date)
|
||
|
|
FROM raena_analytics.clevertap_checkout_base;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.order_base;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.order_base AS
|
||
|
|
SELECT min((cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date))) AS transaction_date ,
|
||
|
|
profile_phone
|
||
|
|
FROM clevertap.app_installed
|
||
|
|
GROUP BY profile_phone;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.usder_base;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.usder_base AS
|
||
|
|
SELECT A.name ,
|
||
|
|
A.id ,
|
||
|
|
B.name tier_name,
|
||
|
|
A.mobile ,
|
||
|
|
A.email,
|
||
|
|
A.province,
|
||
|
|
A.area_id
|
||
|
|
FROM raena_user_management.user A
|
||
|
|
LEFT JOIN raena_user_management.tier B ON A.tier_id = B.id;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.order_clevertab_stage1;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.order_clevertab_stage1 AS
|
||
|
|
SELECT A.transaction_date ,
|
||
|
|
tier_name,
|
||
|
|
name Reseller_name,
|
||
|
|
replace(mobile,'+','') mobile,
|
||
|
|
email ,
|
||
|
|
province,
|
||
|
|
area_id,
|
||
|
|
id reseller_id
|
||
|
|
FROM raena_analytics.order_base A
|
||
|
|
LEFT JOIN raena_analytics.usder_base B ON A.profile_phone= replace(mobile,'+','')
|
||
|
|
ORDER BY id ;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.user_type_stage;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.user_type_stage AS
|
||
|
|
SELECT A.transaction_date,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
B.Reseller_name,
|
||
|
|
CASE
|
||
|
|
WHEN cast(A.transaction_date AS date) BETWEEN B.transaction_date AND B.transaction_date+interval '30 days' THEN 'New'
|
||
|
|
ELSE 'Existing'
|
||
|
|
END user_type,
|
||
|
|
B.tier_name,
|
||
|
|
province,
|
||
|
|
area_id,
|
||
|
|
count(CASE WHEN eventname = 'begin_checkout' THEN coalesce(profile_phone,profile_email) END) dst_begin_checkout,
|
||
|
|
count(CASE WHEN eventname = 'finish_checkout' THEN coalesce(profile_phone,profile_email) END) dst_finish_checkout
|
||
|
|
FROM raena_analytics.clevertap_checkout_base A
|
||
|
|
LEFT JOIN raena_analytics.order_clevertab_stage1 B ON profile_phone =B.mobile
|
||
|
|
WHERE eventname IN ('begin_checkout',
|
||
|
|
'finish_checkout')
|
||
|
|
GROUP BY A.transaction_date,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
B.Reseller_name,
|
||
|
|
CASE
|
||
|
|
WHEN cast(A.transaction_date AS date) BETWEEN B.transaction_date AND B.transaction_date+interval '30 days' THEN 'New'
|
||
|
|
ELSE 'Existing'
|
||
|
|
END,
|
||
|
|
B.tier_name,
|
||
|
|
province,
|
||
|
|
area_id
|
||
|
|
ORDER BY 1 ;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.final_clevertab_stage;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.final_clevertab_stage AS
|
||
|
|
SELECT transaction_date,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
Reseller_name,
|
||
|
|
User_type,
|
||
|
|
tier_name,
|
||
|
|
products,
|
||
|
|
province,
|
||
|
|
area_id,
|
||
|
|
cart_id,
|
||
|
|
CURRENT_DATE-transaction_date AS age,
|
||
|
|
sum(dst_begin_checkout) dst_begin_checkout,
|
||
|
|
sum(dst_finish_checkout) dst_finish_checkout
|
||
|
|
FROM raena_analytics.user_type_stage A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT user_id ,
|
||
|
|
products,
|
||
|
|
mobile,
|
||
|
|
email ,
|
||
|
|
A.id cart_id ,
|
||
|
|
rank() over(partition BY user_id
|
||
|
|
ORDER BY A.created_at DESC) rnk
|
||
|
|
FROM
|
||
|
|
(SELECT DISTINCT id ,
|
||
|
|
products,
|
||
|
|
created_at ,
|
||
|
|
user_id
|
||
|
|
FROM raena_cart_management.cart
|
||
|
|
WHERE cleared = 'false') A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT DISTINCT cast(id AS varchar),
|
||
|
|
replace(mobile,'+','') mobile,
|
||
|
|
email
|
||
|
|
FROM raena_user_management.user) B ON A.user_id = B.id)B ON A.profile_phone = B.mobile
|
||
|
|
AND B.rnk= 1
|
||
|
|
GROUP BY transaction_date,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
Reseller_name,
|
||
|
|
User_type,
|
||
|
|
tier_name,
|
||
|
|
cart_id,
|
||
|
|
products,
|
||
|
|
province,
|
||
|
|
area_id,
|
||
|
|
CURRENT_DATE-transaction_date ;
|
||
|
|
|
||
|
|
|
||
|
|
select count(1) , max(transaction_date) from raena_analytics.final_clevertab_stage;
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.sku_wholesale_price ;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.sku_wholesale_price AS
|
||
|
|
SELECT sku,
|
||
|
|
CASE
|
||
|
|
WHEN json_extract_path_text(tier_price,'07030fbe-5801-4318-9e97-fe33fa169894',true)='' THEN '0'
|
||
|
|
ELSE json_extract_path_text(tier_price,'07030fbe-5801-4318-9e97-fe33fa169894',true)
|
||
|
|
END bronze_price,
|
||
|
|
CASE
|
||
|
|
WHEN json_extract_path_text(tier_price,'8eb95d6e-915a-4a91-9c12-fa43db995e19',true)='' THEN '0'
|
||
|
|
ELSE json_extract_path_text(tier_price,'8eb95d6e-915a-4a91-9c12-fa43db995e19',true)
|
||
|
|
END silver_price,
|
||
|
|
CASE
|
||
|
|
WHEN json_extract_path_text(tier_price,'bf645e97-8a48-4977-8367-e987489760f9',true)='' THEN '0'
|
||
|
|
ELSE json_extract_path_text(tier_price,'bf645e97-8a48-4977-8367-e987489760f9',true)
|
||
|
|
END gold_price,
|
||
|
|
CASE
|
||
|
|
WHEN json_extract_path_text(slashed_tier_price,'07030fbe-5801-4318-9e97-fe33fa169894',true)='' THEN '0'
|
||
|
|
ELSE json_extract_path_text(slashed_tier_price,'07030fbe-5801-4318-9e97-fe33fa169894',true)
|
||
|
|
END slashed_bronze_price,
|
||
|
|
CASE
|
||
|
|
WHEN json_extract_path_text(slashed_tier_price,'8eb95d6e-915a-4a91-9c12-fa43db995e19',true)='' THEN '0'
|
||
|
|
ELSE json_extract_path_text(slashed_tier_price,'8eb95d6e-915a-4a91-9c12-fa43db995e19',true)
|
||
|
|
END slashed_silver_price,
|
||
|
|
CASE
|
||
|
|
WHEN json_extract_path_text(slashed_tier_price,'bf645e97-8a48-4977-8367-e987489760f9',true)='' THEN '0'
|
||
|
|
ELSE json_extract_path_text(slashed_tier_price,'bf645e97-8a48-4977-8367-e987489760f9',true)
|
||
|
|
END slashed_gold_price,
|
||
|
|
retail_price ,
|
||
|
|
cast((height*width*LENGTH)/6000 AS decimal(22,2)) volume_weight,
|
||
|
|
weight
|
||
|
|
FROM raena_catalog_management.product ;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE if exists raena_analyticsseq_0_to_1000;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analyticsseq_0_to_1000 AS
|
||
|
|
(SELECT row_number() over (
|
||
|
|
ORDER BY TRUE)::integer - 1 AS i
|
||
|
|
FROM raena_analytics.final_clevertab_stage LIMIT 2000);
|
||
|
|
|
||
|
|
drop table if exists raena_analytics.exploded_array;
|
||
|
|
|
||
|
|
create table raena_analytics.exploded_array
|
||
|
|
as
|
||
|
|
SELECT *,
|
||
|
|
JSON_EXTRACT_ARRAY_ELEMENT_TEXT(products, seq.i) AS json
|
||
|
|
FROM raena_analytics.final_clevertab_stage,
|
||
|
|
raena_analytics.seq_0_to_1000 AS seq
|
||
|
|
WHERE seq.i < JSON_ARRAY_LENGTH(products) and products like '[%]';
|
||
|
|
|
||
|
|
|
||
|
|
drop table raena_analytics.final_clevertab_sku_stage;
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.final_clevertab_sku_stage AS
|
||
|
|
SELECT transaction_date ,
|
||
|
|
profile_phone ,
|
||
|
|
profile_email ,
|
||
|
|
reseller_name ,
|
||
|
|
user_type ,
|
||
|
|
tier_name,
|
||
|
|
province,
|
||
|
|
area_id,
|
||
|
|
cart_id,
|
||
|
|
age,
|
||
|
|
dst_begin_checkout ,
|
||
|
|
dst_finish_checkout ,
|
||
|
|
json_extract_path_text(json,'sku',TRUE) sku_name,
|
||
|
|
json_extract_path_text(json,'quantity',TRUE) quantity
|
||
|
|
FROM raena_analytics.exploded_array;
|
||
|
|
|
||
|
|
|
||
|
|
select count(1) , max(transaction_date) from raena_analytics.final_clevertab_sku_stage;
|
||
|
|
|
||
|
|
drop table if exists raena_analytics.final_clevertab_sku_stage2;
|
||
|
|
|
||
|
|
create table raena_analytics.final_clevertab_sku_stage2
|
||
|
|
as
|
||
|
|
SELECT A.*,
|
||
|
|
CASE WHEN tier_name = 'GOLD' and gold_price<>'' THEN cast(gold_price as decimal(22,4))
|
||
|
|
WHEN tier_name = 'SILVER' and silver_price<>'' THEN cast(silver_price as decimal(22,4))
|
||
|
|
else cast(bronze_price as decimal(22,4))
|
||
|
|
END wholesale_price,
|
||
|
|
case when volume_weight>weight then volume_weight else weight end sku_weight,
|
||
|
|
CASE
|
||
|
|
WHEN ((case when volume_weight>weight then volume_weight else weight end)*cast(Quantity as int))<1.3 THEN 1
|
||
|
|
WHEN ((case when volume_weight>weight then volume_weight else weight end)*cast(Quantity as int))>=1.3
|
||
|
|
AND (ABS(((case when volume_weight>weight then volume_weight else weight end)*cast(Quantity as int))) - FLOOR(ABS(((case when volume_weight>weight then volume_weight else weight end)*cast(Quantity as int))))) BETWEEN 0.3 AND 0.999999
|
||
|
|
THEN FLOOR(((case when volume_weight>weight then volume_weight else weight end)*cast(Quantity as int)))+1
|
||
|
|
ELSE FLOOR(((case when volume_weight>weight then volume_weight else weight end)*cast(Quantity as int)))
|
||
|
|
END AS final_weight,
|
||
|
|
price
|
||
|
|
FROM raena_analytics.final_clevertab_sku_stage a
|
||
|
|
LEFT JOIN raena_analytics.sku_wholesale_price b ON a.sku_name = B.sku
|
||
|
|
left join (select destination_area_id , min(price) price from raena_transport_management.logistic_rate
|
||
|
|
group by 1) C on A.area_id = C.destination_area_id;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
select count(1) , max(transaction_date) from raena_analytics.final_clevertab_sku_stage2;
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.final_clevertab;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.final_clevertab AS
|
||
|
|
SELECT transaction_date ,
|
||
|
|
profile_phone ,
|
||
|
|
profile_email,
|
||
|
|
reseller_name,
|
||
|
|
user_type,
|
||
|
|
tier_name,
|
||
|
|
province,
|
||
|
|
cart_id,
|
||
|
|
age,
|
||
|
|
dst_begin_checkout,
|
||
|
|
dst_finish_checkout,
|
||
|
|
sku_name ,
|
||
|
|
cast(quantity as int)quantity,
|
||
|
|
cast(wholesale_price as int) wholesale_price,
|
||
|
|
final_weight,
|
||
|
|
price
|
||
|
|
FROM raena_analytics.final_clevertab_sku_stage2 ;
|
||
|
|
|
||
|
|
select count(1) , max(transaction_date) from raena_analytics.final_clevertab;
|
||
|
|
|
||
|
|
" > /home/ec2-user/cronjob/redshift/sql_code/cart_abandment_etl.sql
|
||
|
|
|
||
|
|
psql "host=redshift-cluster-1.cdqj58hfx4p7.ap-southeast-1.redshift.amazonaws.com user=dbadmin dbname=analytics port=5439 password=5qCif6eyY3Kmg4z" -f /home/ec2-user/cronjob/redshift/sql_code/cart_abandment_etl.sql
|
||
|
|
|
||
|
|
|