Shariar
/
raena_analytics_scripts
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
raena_analytics_scripts/redshift/gamification_etl.sh

908 lines
34 KiB
Bash
Raw Permalink Normal View History

first commit
2024-06-24 12:26:08 +00:00
#!/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")
date
echo 'reportDate'=$reportDate
echo "
DROP TABLE raena_analytics.challenge_user_wise_rewards;
CREATE TABLE raena_analytics.challenge_user_wise_rewards AS
SELECT challenge_id,
challenge_name,
challenge_type,
challenge_status,
CC.name ,
CC.email,
CC.mobile,
rnk
FROM
(SELECT C.challenge_id,
challenge_name,
challenge_type,
challenge_status,
reseller_id ,
current_progress,
rank()over(partition BY C.challenge_id
ORDER BY current_progress DESC ,gross_profit DESC,total_revenue DESC ,randomness DESC) rnk
FROM
(SELECT DISTINCT challengeid challenge_id ,
name challenge_name,
TYPE challenge_type,
status challenge_status
FROM raena_analytics_challenge_management.challenge_management_main cm ) D
LEFT JOIN
(SELECT *
FROM
(SELECT reseller_id,
challenge_id ,
current_progress,
gross_profit,
total_revenue,
randomness,
row_number() over (partition BY reseller_id,challenge_id
ORDER BY updated_at DESC) rn
FROM raena_challenge_management.rank_progress ) A
WHERE rn= 1) C ON C.challenge_id = D.challenge_id
ORDER BY C.challenge_id ,
rnk,
reseller_id) AA
LEFT JOIN raena_user_management.user CC ON AA.reseller_id = cast(CC.id AS varchar)
ORDER BY challenge_id,
rnk;
---------
DROP TABLE raena_analytics.cohort_level_reseller;
CREATE TABLE raena_analytics.cohort_level_reseller AS
SELECT reseller_id ,
CASE
WHEN max_payment_amount BETWEEN 0 AND 2000000 THEN '0to2M'
WHEN max_payment_amount BETWEEN 2000000 AND 10000000 THEN '2Mto10M'
WHEN max_payment_amount > 10000000 THEN '>10M'
END Bucket
FROM
(SELECT reseller_id ,
max(payment_amount) max_payment_amount
FROM
(SELECT reseller_id ,
date_trunc('Month',created_at)::date trnsaction_mount,
sum(payment_amount)payment_amount
FROM raena_order_management.order
GROUP BY 1,
2) A
GROUP BY 1) B ;
drop table raena_analytics.challenge_brand_sku_mapping;
create table raena_analytics.challenge_brand_sku_mapping
as
select distinct
A.challengeid challenge_id ,
A.name challenge_name ,
A.type challenge_type,
A.status challenge_status,
cast(A.startdate::TIMESTAMP +interval'7 hours' AS date) challenge_start_date,
cast(A.enddate::TIMESTAMP+interval'7 hours' AS date) challenge_end_date ,
cast(A.createdat::TIMESTAMP+interval'7 hours'AS date) challenge_created_date,
brandid ,productsku,
reseller_id
from raena_analytics_challenge_management.challenge_management_main A
left join raena_analytics_challenge_management.participatingbrands AA on A.challengeid = AA.challengeid
LEFT JOIN
(SELECT DISTINCT challenge_id ,
reseller_id
FROM raena_challenge_management.challenge_participant) b ON A.challengeid = B.challenge_id;
drop table raena_analytics.reseller_revenue_gamification;
create table raena_analytics.reseller_revenue_gamification
as
SELECT order_date,
order_id ,
reseller_id ,
order_placed_by,
sku,
brand_id,
sum(amount) amount ,
sum(cogs_amount) cogs_amount,
sum(quantity) quantity,
sum(shipping_amount) shipping_amount
FROM
(SELECT order_date,
A.order_id ,
reseller_id ,
shipment_id,
case when A.order_id = C.order_id then C.new_shipment_amount else D.new_shipment_amount end shipping_amount,
A.sku,
A.brand_id,
CASE WHEN order_placed_by='merchant' THEN 'Merchant'
WHEN order_placed_by='admin' THEN 'Admin Panel'
WHEN order_placed_by='reseller' THEN 'App'
END order_placed_by,
sum(amount) amount ,
sum(cogs_amount) cogs_amount,
sum(quantity) quantity
FROM raena_challenge_management.order_ledger A
LEFT JOIN
(SELECT DISTINCT id ,
created_at,
order_placed_by
FROM raena_order_management.order
WHERE created_at::date >='2023-11-01') B ON A.order_id = B.id
LEFT JOIN raena_analytics.sku_level_shipping_fee_final C
on A.order_id = C.order_id
and A.sku = C.sku
left join raena_analytics.sku_level_shipping_fee_old_final D
on A.order_id = D.external_id
and A.sku = D.sku
WHERE status NOT IN ('CANCELLED',
'RETURNED') and order_date >='2023-11-03'
GROUP BY 1,
2,
3,
4,
5,
6,
7,
8)c
GROUP BY 1,
2,
3,
4,5,6;
DROP TABLE raena_analytics.challenge_rewards_data;
CREATE TABLE raena_analytics.challenge_rewards_data AS
SELECT distinct A.challenge_id,
B.name AS challenge_name ,
B.type AS challenge_type,
A.type AS reward_type ,
A.name AS reward_name,
A.reseller_id,
A.status rewards_status,
C.name reseller_name ,
C.mobile ,
C.email ,
challenge_target,
value AS total_rewards,
json_extract_path_text(json_extract_path_text(claim_meta,'addressDetails',TRUE),'name',TRUE) address_reseller_name ,
json_extract_path_text(json_extract_path_text(claim_meta,'addressDetails',TRUE),'address',TRUE) reseller_address ,
json_extract_path_text(json_extract_path_text(claim_meta,'addressDetails',TRUE),'phoneNumber',TRUE) reseller_phonenumber ,
json_extract_path_text(json_extract_path_text(claim_meta,'bankDetails',TRUE),'bankId',TRUE) reseller_bankid ,
json_extract_path_text(json_extract_path_text(claim_meta,'bankDetails',TRUE),'bankCode',TRUE) reseller_code ,
json_extract_path_text(json_extract_path_text(claim_meta,'bankDetails',TRUE),'bankAccount',TRUE) reseller_account
FROM raena_challenge_management.challenge_reward_status A
LEFT JOIN raena_analytics_challenge_management.challenge_management_main B ON A.challenge_id = B.challengeid
LEFT JOIN raena_user_management.user C ON A.reseller_id = C.id ;
drop table raena_analytics.challenge_rewards_base_table;
create table raena_analytics.challenge_rewards_base_table
as
SELECT challenge_id ,
reseller_id ,
sum(total_rewards) rewards
FROM
(select *
from raena_analytics.challenge_rewards_data
where reward_type in('CASHBACK' ,'EXTERNAL_PRODUCTS','SPARK_POINTS')) D
GROUP BY 1,
2;
drop table raena_analytics.gamification_metrics_base_table_statge1_new;
CREATE TABLE raena_analytics.gamification_metrics_base_table_statge1_new AS
SELECT A.challenge_id ,
A.challenge_name ,
A.challenge_type,
A.challenge_status,
A.challenge_start_date,
A.challenge_end_date ,
A.challenge_created_date,
A.reseller_id,
coalesce(cast(C.order_date::TIMESTAMP+interval'7 hours'AS date),cast(CC.order_date::TIMESTAMP+interval'7 hours'AS date)) transaction_date,
coalesce(C.order_id,CC.order_id) order_id ,
A.brandid ,
A.productsku,
coalesce(C.amount,CC.amount) amount,
coalesce(C.cogs_amount,CC.cogs_amount) cogs_amount,
coalesce(C.quantity,CC.quantity) quantity,
D.rewards,
coalesce(C.shipping_amount,CC.shipping_amount) new_shipment_amount,
--case when C.order_id = E.order_id then E.new_shipment_amount else EE.new_shipment_amount end new_shipment_amount,
F.name AS reseller_name ,
F.email AS reseller_email,
F.mobile AS reseller_mobile,
G.bucket,
coalesce(C.order_placed_by,CC.order_placed_by) order_placed_by
FROM raena_analytics.challenge_brand_sku_mapping A
LEFT JOIN raena_analytics.reseller_revenue_gamification C
ON C.order_date::TIMESTAMP+interval'7 hours' BETWEEN A.challenge_start_date AND A.challenge_end_date
AND A.reseller_id = C.reseller_id
and A.brandid =C.brand_id and (A.productsku is not null or A.productsku ='')
LEFT JOIN raena_analytics.reseller_revenue_gamification CC
ON CC.order_date::TIMESTAMP+interval'7 hours' BETWEEN A.challenge_start_date AND A.challenge_end_date
AND A.reseller_id = CC.reseller_id
and A.productsku = CC.sku and (A.productsku is not null or A.productsku ='')
LEFT JOIN raena_analytics.challenge_rewards_base_table D ON A.challenge_id = D.challenge_id
AND A.reseller_id = D.reseller_id
AND C.reseller_id = D.reseller_id
LEFT JOIN raena_user_management.user F ON A.reseller_id = cast(F.id AS varchar)
LEFT JOIN raena_analytics.cohort_level_reseller G ON A.reseller_id = G.reseller_id
WHERE challenge_type <> 'MILESTONE'
UNION
SELECT A.challengeid challenge_id ,
A.name challenge_name ,
A.type challenge_type,
A.status challenge_status,
cast(A.startdate::TIMESTAMP +interval'7 hours' AS date) challenge_start_date,
cast(A.enddate::TIMESTAMP+interval'7 hours' AS date) challenge_end_date ,
cast(A.createdat::TIMESTAMP+interval'7 hours'AS date) challenge_created_date,
B.reseller_id,
cast(C.order_date::TIMESTAMP+interval'7 hours'AS date) transaction_date,
'',
'',
'',
0 amount,
0 Cogs_amount,
0 quantity,
D.rewards,
0 ,
'' ,
'',
'',
G.bucket,
''
FROM raena_analytics_challenge_management.challenge_management_main A
LEFT JOIN
(SELECT DISTINCT reseller_id
FROM raena_challenge_management.challenge_participant
UNION SELECT DISTINCT reseller_id
FROM raena_challenge_management.order_ledger) b ON 1=1
LEFT JOIN
(SELECT order_date,
order_id ,
reseller_id ,
sum(amount) amount ,
sum(cogs_amount) cogs_amount,
sum(quantity) quantity
FROM raena_challenge_management.order_ledger
GROUP BY 1,
2,
3)c ON b.reseller_id = c.reseller_id
INNER JOIN
(SELECT challenge_id ,
reseller_id ,
sum(total_rewards) rewards
FROM
(select *
from raena_analytics.challenge_rewards_data
where reward_type in('CASHBACK' ,'EXTERNAL_PRODUCTS','SPARK_POINTS')) D
GROUP BY 1,
2) D ON A.challengeid = D.challenge_id
AND B.reseller_id = D.reseller_id
LEFT JOIN raena_user_management.user F ON B.reseller_id = cast(F.id AS varchar)
LEFT JOIN raena_analytics.cohort_level_reseller G ON B.reseller_id = G.reseller_id
WHERE A.type = 'MILESTONE'
ORDER BY 1,
challenge_start_date;
DROP TABLE raena_analytics.gamification_metrics_base_table_statge1;
CREATE TABLE raena_analytics.gamification_metrics_base_table_statge1 AS
SELECT A.challengeid challenge_id ,
A.name challenge_name ,
A.type challenge_type,
A.status challenge_status,
cast(A.startdate::TIMESTAMP +interval'7 hours' AS date) challenge_start_date,
cast(A.enddate::TIMESTAMP+interval'7 hours' AS date) challenge_end_date ,
cast(A.createdat::TIMESTAMP+interval'7 hours'AS date) challenge_created_date,
B.reseller_id,
cast(C.order_date::TIMESTAMP+interval'7 hours'AS date) transaction_date,
C.order_id ,
C.amount,
C.cogs_amount,
C.quantity,
D.rewards,
shipping_amount new_shipment_amount,
--case when C.order_id = E.order_id then E.new_shipment_amount else EE.new_shipment_amount end new_shipment_amount,
F.name AS reseller_name ,
F.email AS reseller_email,
F.mobile AS reseller_mobile,
G.bucket,
order_placed_by
FROM raena_analytics_challenge_management.challenge_management_main A
LEFT JOIN
(SELECT DISTINCT challenge_id ,
reseller_id
FROM raena_challenge_management.challenge_participant) b ON A.challengeid = B.challenge_id
LEFT JOIN
(SELECT order_date,
order_id ,
reseller_id ,
order_placed_by,
sum(amount) amount ,
sum(cogs_amount) cogs_amount,
sum(quantity) quantity,
sum(shipping_amount) shipping_amount
FROM
(SELECT order_date,
order_id ,
reseller_id ,
shipment_id,
shipping_amount,
CASE WHEN order_placed_by='merchant' THEN 'Merchant' WHEN order_placed_by='admin' THEN 'Admin Panel' WHEN order_placed_by='reseller' THEN 'App' END order_placed_by,
sum(amount) amount ,
sum(cogs_amount) cogs_amount,
sum(quantity) quantity
FROM raena_challenge_management.order_ledger A
LEFT JOIN
(SELECT DISTINCT id ,
created_at,
order_placed_by
FROM raena_order_management.order
WHERE created_at::date >='2023-10-01') B ON A.order_id = B.id
LEFT JOIN
(SELECT DISTINCT id ,
shipping_amount
FROM raena_order_management.sales_sub_order_shipment
WHERE status NOT IN ('CANCELLED',
'RETURNED')) C ON A.shipment_id=C.id
WHERE status NOT IN ('CANCELLED',
'RETURNED')
GROUP BY 1,
2,
3,
4,
5,
6)c
GROUP BY 1,
2,
3,
4) C ON C.order_date::TIMESTAMP+interval'7 hours' BETWEEN A.startdate::TIMESTAMP+interval'7 hours'
AND (A.enddate::TIMESTAMP+interval'7 hours')
AND B.reseller_id = C.reseller_id
LEFT JOIN
(SELECT challenge_id ,
reseller_id ,
sum(total_rewards) rewards
FROM
(select *
from raena_analytics.challenge_rewards_data
where reward_type in('CASHBACK' ,'EXTERNAL_PRODUCTS','SPARK_POINTS')) D
GROUP BY 1,
2) D ON A.challengeid = D.challenge_id
AND B.reseller_id = D.reseller_id
AND C.reseller_id = D.reseller_id
LEFT JOIN raena_user_management.user F ON B.reseller_id = cast(F.id AS varchar)
LEFT JOIN raena_analytics.cohort_level_reseller G ON B.reseller_id = G.reseller_id
WHERE A.type <> 'MILESTONE'
UNION
SELECT A.challengeid challenge_id ,
A.name challenge_name ,
A.type challenge_type,
A.status challenge_status,
cast(A.startdate::TIMESTAMP +interval'7 hours' AS date) challenge_start_date,
cast(A.enddate::TIMESTAMP+interval'7 hours' AS date) challenge_end_date ,
cast(A.createdat::TIMESTAMP+interval'7 hours'AS date) challenge_created_date,
B.reseller_id,
cast(C.order_date::TIMESTAMP+interval'7 hours'AS date) transaction_date,
'' ,
0 amount,
0 Cogs_amount,
0 quantity,
D.rewards,
0 ,
'' ,
'',
'',
G.bucket,
''
FROM raena_analytics_challenge_management.challenge_management_main A
LEFT JOIN
(SELECT DISTINCT reseller_id
FROM raena_challenge_management.challenge_participant
UNION SELECT DISTINCT reseller_id
FROM raena_challenge_management.order_ledger) b ON 1=1
LEFT JOIN
(SELECT order_date,
order_id ,
reseller_id ,
sum(amount) amount ,
sum(cogs_amount) cogs_amount,
sum(quantity) quantity
FROM raena_challenge_management.order_ledger
GROUP BY 1,
2,
3)c ON b.reseller_id = c.reseller_id
INNER JOIN
(SELECT challenge_id ,
reseller_id ,
sum(total_rewards) rewards
FROM
(select *
from raena_analytics.challenge_rewards_data
where reward_type in('CASHBACK' ,'EXTERNAL_PRODUCTS','SPARK_POINTS')) D
GROUP BY 1,
2) D ON A.challengeid = D.challenge_id
AND B.reseller_id = D.reseller_id
LEFT JOIN raena_user_management.user F ON B.reseller_id = cast(F.id AS varchar)
LEFT JOIN raena_analytics.cohort_level_reseller G ON B.reseller_id = G.reseller_id
WHERE A.type = 'MILESTONE'
ORDER BY 1,
challenge_start_date;
DROP TABLE raena_analytics.challenge_type_retention;
CREATE TABLE raena_analytics.challenge_type_retention AS
SELECT A.challenge_type,
min_order_date AS first_transaction_date,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=0 THEN A.reseller_id END) AS M0,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=1 THEN A.reseller_id END) AS M1,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=2 THEN A.reseller_id END) AS M2,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=3 THEN A.reseller_id END) AS M3,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=4 THEN A.reseller_id END) AS M4,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=5 THEN A.reseller_id END) AS M5,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=6 THEN A.reseller_id END) AS M6,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)>6 THEN A.reseller_id END) AS GT_M6
FROM
(SELECT challenge_type,
reseller_id ,
min(date_trunc('Month',transaction_date)::date) min_order_date
FROM raena_analytics.gamification_metrics_base_table_statge1_new
WHERE order_id IS NOT NULL
AND reseller_id IN
(SELECT DISTINCT C.id
FROM raena_analytics.charged_events_gamification_direct_revenue A
LEFT JOIN raena_user_management.user C ON A.phone = replace(C.mobile ,'+','')
WHERE challenge_detail_page>0
AND view_item_session>0
AND begin_checkout_session>0
AND finish_checkout_session>0
AND confirm_payment_session>0
AND charged>0)
GROUP BY 1,
2) A
LEFT JOIN
(SELECT DISTINCT challenge_type,
reseller_id ,
date_trunc('Month',transaction_date)::date order_date
FROM raena_analytics.gamification_metrics_base_table_statge1_new
WHERE order_id IS NOT NULL
AND reseller_id IN
(SELECT DISTINCT C.id
FROM raena_analytics.charged_events_gamification_direct_revenue A
LEFT JOIN raena_user_management.user C ON A.phone = replace(C.mobile ,'+','')
WHERE challenge_detail_page>0
AND view_item_session>0
AND begin_checkout_session>0
AND finish_checkout_session>0
AND confirm_payment_session>0
AND charged>0)) B ON A.reseller_id = B.reseller_id
AND A.challenge_type = B.challenge_type
GROUP BY 1 ,
2;
drop table raena_analytics.challenge_cohort_type_retention;
CREATE TABLE raena_analytics.challenge_cohort_type_retention AS
SELECT A.challenge_type,
bucket,
min_order_date AS first_transaction_date,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=0 THEN A.reseller_id END) AS M0,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=1 THEN A.reseller_id END) AS M1,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=2 THEN A.reseller_id END) AS M2,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=3 THEN A.reseller_id END) AS M3,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=4 THEN A.reseller_id END) AS M4,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=5 THEN A.reseller_id END) AS M5,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=6 THEN A.reseller_id END) AS M6,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)>6 THEN A.reseller_id END) AS GT_M6
FROM
(SELECT challenge_type,
reseller_id ,
min(date_trunc('Month',transaction_date)::date) min_order_date
FROM raena_analytics.gamification_metrics_base_table_statge1_new
WHERE order_id IS NOT NULL
AND reseller_id IN
(SELECT DISTINCT C.id
FROM raena_analytics.charged_events_gamification_direct_revenue A
LEFT JOIN raena_user_management.user C ON A.phone = replace(C.mobile ,'+','')
WHERE challenge_detail_page>0
AND view_item_session>0
AND begin_checkout_session>0
AND finish_checkout_session>0
AND confirm_payment_session>0
AND charged>0)
GROUP BY 1,
2) A
LEFT JOIN
(SELECT DISTINCT challenge_type,
reseller_id ,
date_trunc('Month',transaction_date)::date order_date
FROM raena_analytics.gamification_metrics_base_table_statge1_new
WHERE order_id IS NOT NULL
AND reseller_id IN
(SELECT DISTINCT C.id
FROM raena_analytics.charged_events_gamification_direct_revenue A
LEFT JOIN raena_user_management.user C ON A.phone = replace(C.mobile ,'+','')
WHERE challenge_detail_page>0
AND view_item_session>0
AND begin_checkout_session>0
AND finish_checkout_session>0
AND confirm_payment_session>0
AND charged>0)) B ON A.reseller_id = B.reseller_id
AND A.challenge_type = B.challenge_type
LEFT JOIN
(SELECT *
FROM raena_analytics.cohort_level_reseller) C ON A.reseller_id= C.reseller_id
GROUP BY 1,
2,
3;
/*
DROP TABLE raena_analytics.challenge_type_retention;
CREATE TABLE raena_analytics.challenge_type_retention AS
SELECT challenge_type,
min_order_date AS first_transaction_date,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=0 THEN A.reseller_id END) AS M0,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=1 THEN A.reseller_id END) AS M1,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=2 THEN A.reseller_id END) AS M2,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=3 THEN A.reseller_id END) AS M3,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=4 THEN A.reseller_id END) AS M4,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=5 THEN A.reseller_id END) AS M5,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=6 THEN A.reseller_id END) AS M6,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)>6 THEN A.reseller_id END) AS GT_M6
FROM
(SELECT TYPE challenge_type,
B.reseller_id ,
min(date_trunc('Month',order_date)::date) min_order_date
FROM raena_analytics_challenge_management.challenge_management_main A
LEFT JOIN
(SELECT DISTINCT challenge_id ,
reseller_id
FROM raena_challenge_management.challenge_participant) b ON A.challengeid = B.challenge_id
inner JOIN
(SELECT order_date,
order_id ,
reseller_id ,
status order_status,
sum(amount) amount ,
sum(cogs_amount) cogs_amount,
sum(quantity) quantity
FROM raena_challenge_management.order_ledger
GROUP BY 1,
2,
3,
4)c ON C.order_date::TIMESTAMP+interval'7 hours' BETWEEN A.startdate::TIMESTAMP+interval'7 hours' AND (A.enddate::TIMESTAMP+interval'7 hours')
AND B.reseller_id = C.reseller_id
WHERE TYPE <> 'MILESTONE'
GROUP BY 1,
2
UNION SELECT TYPE challenge_type,
B.reseller_id ,
min(date_trunc('Month',order_date)::date) min_order_date
FROM raena_analytics_challenge_management.challenge_management_main A
LEFT JOIN
(SELECT DISTINCT reseller_id
FROM raena_challenge_management.challenge_participant) b ON 1=1
inner JOIN
(SELECT order_date,
order_id ,
reseller_id ,
status order_status,
sum(amount) amount ,
sum(cogs_amount) cogs_amount,
sum(quantity) quantity
FROM raena_challenge_management.order_ledger
GROUP BY 1,
2,
3,
4)c ON b.reseller_id = c.reseller_id
WHERE TYPE = 'MILESTONE'
GROUP BY 1,
2) A
LEFT JOIN
(SELECT DISTINCT date_trunc('Month',order_date)::date order_date,
reseller_id
FROM raena_challenge_management.order_ledger A ) B ON A.reseller_id = B.reseller_id
GROUP BY 1 ,
2;
drop table raena_analytics.challenge_cohort_type_retention;
CREATE TABLE raena_analytics.challenge_cohort_type_retention AS
SELECT challenge_type,
bucket,
min_order_date AS first_transaction_date,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=0 THEN A.reseller_id END) AS M0,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=1 THEN A.reseller_id END) AS M1,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=2 THEN A.reseller_id END) AS M2,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=3 THEN A.reseller_id END) AS M3,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=4 THEN A.reseller_id END) AS M4,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=5 THEN A.reseller_id END) AS M5,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)=6 THEN A.reseller_id END) AS M6,
count(DISTINCT CASE WHEN datediff(MONTH,A.min_order_date,B.order_date)>6 THEN A.reseller_id END) AS GT_M6
FROM
(SELECT TYPE challenge_type,
B.reseller_id ,
min(date_trunc('Month',order_date)::date) min_order_date
FROM raena_analytics_challenge_management.challenge_management_main A
LEFT JOIN
(SELECT DISTINCT challenge_id ,
reseller_id
FROM raena_challenge_management.challenge_participant) b ON A.challengeid = B.challenge_id
inner JOIN
(SELECT order_date,
order_id ,
reseller_id ,
status order_status,
sum(amount) amount ,
sum(cogs_amount) cogs_amount,
sum(quantity) quantity
FROM raena_challenge_management.order_ledger
GROUP BY 1,
2,
3,
4)c ON C.order_date::TIMESTAMP+interval'7 hours' BETWEEN A.startdate::TIMESTAMP+interval'7 hours' AND (A.enddate::TIMESTAMP+interval'7 hours')
AND B.reseller_id = C.reseller_id
WHERE TYPE <> 'MILESTONE'
GROUP BY 1,
2
UNION SELECT TYPE challenge_type,
B.reseller_id ,
min(date_trunc('Month',order_date)::date) min_order_date
FROM raena_analytics_challenge_management.challenge_management_main A
LEFT JOIN
(SELECT DISTINCT reseller_id
FROM raena_challenge_management.challenge_participant) b ON 1=1
inner JOIN
(SELECT order_date,
order_id ,
reseller_id ,
status order_status,
sum(amount) amount ,
sum(cogs_amount) cogs_amount,
sum(quantity) quantity
FROM raena_challenge_management.order_ledger
GROUP BY 1,
2,
3,
4)c ON b.reseller_id = c.reseller_id
WHERE TYPE = 'MILESTONE'
GROUP BY 1,
2) A
LEFT JOIN
(SELECT DISTINCT date_trunc('Month',order_date)::date order_date,
reseller_id
FROM raena_challenge_management.order_ledger A ) B ON A.reseller_id = B.reseller_id
LEFT JOIN
(SELECT *
FROM raena_analytics.cohort_level_reseller) C ON A.reseller_id= C.reseller_id
GROUP BY 1,
2,
3; */
DROP TABLE IF EXISTS raena_analytics.gamification_funnel_base_stage1;
CREATE TABLE raena_analytics.gamification_funnel_base_stage1 AS
SELECT ts::date ,
events,
name ,
email ,
phone,
json_extract_path_text(event_props,'CT Session Id',TRUE) sessionid ,
json_extract_path_text(event_props,'page_name',TRUE) page_name ,
json_extract_path_text(event_props,'ProductList',TRUE) brand_list
FROM clevertap.clevertap_master_data
WHERE events IN ('load_challenge_home_banner' ,
'click_on_challenge_home_banner',
'bottom_bar_challenge',
'click_on_challenge_home_banner',
'page_load',
'view_item',
'begin_checkout',
'Charged',
'view_cart',
'add_to_cart',
'confirm_payment',
'finish_checkout',
'click_on_milestone')
AND ts::date >='2023-11-07';
DROP TABLE raena_analytics.gamification_funnel_base_stage2;
CREATE TABLE raena_analytics.gamification_funnel_base_stage2 AS
SELECT ts::date ,
sessionid,
name ,
email ,
phone,
count(DISTINCT CASE WHEN events ='page_load'
AND page_name ='Home' THEN sessionid END) home_banner_session,
count(DISTINCT CASE WHEN events ='click_on_challenge_home_banner' THEN sessionid END) click_challenge_home_banner_session,
count(DISTINCT CASE WHEN events ='bottom_bar_challenge' THEN sessionid END) bottom_bar_challenge,
count(distinct case when events ='click_on_milestone' then sessionid end) load_challenge_milestone_page,
count(DISTINCT CASE WHEN events ='page_load'
AND page_name ='Challenges' THEN sessionid END) challenge_home_page,
count(DISTINCT CASE WHEN events ='page_load'
AND page_name ='challenge-details' THEN sessionid END) challenge_detail_page,
count(DISTINCT CASE WHEN events ='page_load'
AND page_name ='ProductList' THEN sessionid END) brand_page,
count(DISTINCT CASE WHEN events ='view_item' THEN sessionid END) view_item_session,
count(DISTINCT CASE WHEN events ='view_cart' THEN sessionid END)view_cart_session,
count(DISTINCT CASE WHEN events ='begin_checkout' THEN sessionid END) begin_checkout_session,
count(DISTINCT CASE WHEN events ='finish_checkout' THEN sessionid END) finish_checkout_session,
count(DISTINCT CASE WHEN events ='confirm_payment' THEN sessionid END) confirm_payment_session
FROM raena_analytics.gamification_funnel_base_stage1
GROUP BY 1,
2,
3,
4,
5;
DROP TABLE raena_analytics.gamification_funnel_base_stage3;
CREATE TABLE raena_analytics.gamification_funnel_base_stage3 AS
SELECT ts::date ,
sessionid,
name ,
email ,
phone,
count(DISTINCT CASE WHEN events ='Charged' THEN sessionid END) Charged
FROM raena_analytics.gamification_funnel_base_stage1
WHERE ts::date >='2023-11-07'
GROUP BY 1,
2,
3,
4,
5;
DROP TABLE IF EXISTS raena_analytics.gamification_funnel_base_stage1_direct_revenue;
CREATE TABLE raena_analytics.gamification_funnel_base_stage1_direct_revenue AS
SELECT ts::date ,
events,
name ,
email ,
phone,
json_extract_path_text(event_props,'CT Session Id',TRUE) sessionid ,
json_extract_path_text(event_props,'challengeId',TRUE) challengeid
FROM clevertap.clevertap_master_data
WHERE events ='page_load' and json_extract_path_text(event_props,'page_name',TRUE) ='challenge-details'
AND ts::date >='2023-11-07';
DROP TABLE IF EXISTS raena_analytics.gamification_funnel_base_stage2_direct_revenue;
CREATE TABLE raena_analytics.gamification_funnel_base_stage2_direct_revenue AS
SELECT ts::date ,
events,
name ,
email ,
phone,
json_extract_path_text(event_props,'CT Session Id',TRUE) sessionid ,
json_extract_path_text(event_props,'Payment ID',TRUE) payment_id
FROM clevertap.clevertap_master_data
WHERE events IN ('view_item',
'begin_checkout',
'Charged',
'confirm_payment',
'finish_checkout')
AND ts::date >='2023-11-07';
drop table raena_analytics.gamification_funnel_base_stage3_direct_revenue;
CREATE TABLE raena_analytics.gamification_funnel_base_stage3_direct_revenue AS
SELECT ts::date ,
sessionid,
name ,
email ,
phone,
count(DISTINCT CASE WHEN events ='view_item' THEN sessionid END) view_item_session,
count(DISTINCT CASE WHEN events ='begin_checkout' THEN sessionid END) begin_checkout_session,
count(DISTINCT CASE WHEN events ='finish_checkout' THEN sessionid END) finish_checkout_session,
count(DISTINCT CASE WHEN events ='confirm_payment' THEN sessionid END) confirm_payment_session
FROM raena_analytics.gamification_funnel_base_stage2_direct_revenue
GROUP BY 1,
2,
3,
4,
5;
DROP TABLE raena_analytics.gamification_funnel_base_stage4_direct_revenue;
CREATE TABLE raena_analytics.gamification_funnel_base_stage4_direct_revenue AS
SELECT ts::date ,
sessionid,
name ,
email ,
phone,
payment_id,
count(DISTINCT CASE WHEN events ='Charged' THEN sessionid END) Charged,
order_id
FROM raena_analytics.gamification_funnel_base_stage2_direct_revenue A
left join raena_analytics.payment_order B on A.payment_id = B.id
WHERE events = 'Charged'
GROUP BY 1,
2,
3,
4,
5,6,8;
DROP TABLE IF EXISTS raena_analytics.charged_events_gamification_direct_revenue;
CREATE TABLE raena_analytics.charged_events_gamification_direct_revenue AS
SELECT A.ts,
A.phone,
A.challengeid,
count(DISTINCT A.sessionid) challenge_detail_page,
count(DISTINCT CASE WHEN view_item_session>0 THEN A.sessionid END) view_item_session,
count(DISTINCT CASE WHEN begin_checkout_session>0 THEN A.sessionid END) begin_checkout_session,
count(DISTINCT CASE WHEN finish_checkout_session>0 THEN A.sessionid END) finish_checkout_session ,
count(DISTINCT CASE WHEN confirm_payment_session>0 THEN A.sessionid END) confirm_payment_session,
count(DISTINCT CASE WHEN Charged>0 THEN A.sessionid END) charged
FROM raena_analytics.gamification_funnel_base_stage1_direct_revenue A
LEFT JOIN raena_analytics.gamification_funnel_base_stage3_direct_revenue B ON A.sessionid = B.sessionid
LEFT JOIN
(SELECT DISTINCT A.order_id ,
phone ,
charged ,
challenge_id
FROM raena_analytics.gamification_funnel_base_stage4_direct_revenue A
INNER JOIN raena_analytics.gamification_metrics_base_table_statge1_new B ON A.order_id = B.order_id) c ON B.phone = C.phone
AND A.challengeid = C.challenge_id
GROUP BY 1,
2,
3;
" > /home/ec2-user/cronjob/redshift/sql_code/gamification_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/gamification_etl.sql