raena_analytics_scripts/redshift/new_user_funnel_report.sh

304 lines
11 KiB
Bash
Raw Permalink Normal View History

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 IF EXISTS raena_analytics.om_clevertap_install_jan_may;
CREATE TABLE raena_analytics.om_clevertap_install_jan_may AS
SELECT App_install_date,
email,
phone,
user_id
FROM
(SELECT *,
row_number() over (partition BY user_id
ORDER BY app_install_date) AS R
FROM
(SELECT cast('2022-04-01' as date) AS App_install_date,
email,
phone,
email AS user_id
FROM raena_analytics.clevertap_april_csv
UNION SELECT '2022-03-30' AS App_install_date,
email,
phone,
email AS user_id
FROM raena_analytics.clevertap_march_csv
WHERE phone NOT IN
(SELECT DISTINCT profile_phone
FROM raena_analytics.app_installed)
UNION
SELECT cast(left(ts,4) || '-' || right(left(ts,6),2) || '-' || right(left(ts,8),2) AS date) AS App_install_date,
profile_email,
profile_phone,
CASE WHEN profile_email=' ' THEN profile_objectid ELSE profile_email END AS user_id
FROM clevertap.app_installed)A)B
WHERE R=1 ;
DROP TABLE IF EXISTS raena_analytics.om_clevertap_install_jan_may_2;
CREATE TABLE raena_analytics.om_clevertap_install_jan_may_2 AS
SELECT user_id,
email,
phone,
to_char(app_install_date,'month') AS MONTH,
date_part('DAY',app_install_date) AS DAY,
address_line1,
address_line2,
city,
province,
app_install_date
FROM
(SELECT a.user_id,
a.email,
phone,
CASE
WHEN app_install_date='2022-03-30'
OR app_install_date='2022-04-01' THEN cast(b.created_at AS date)
ELSE app_install_date
END AS app_install_date,
coalesce(address_line1,customer_address_line1) AS address_line1,
coalesce(address_line2,customer_address_line2) AS address_line2,
coalesce(city,customer_city) AS city,
coalesce(province,customer_province) AS province
FROM raena_analytics.om_clevertap_install_jan_may a
LEFT JOIN
(SELECT mobile,
email,
created_at,
address_line1,
address_line2,
city,
province
FROM
(SELECT email,
replace(mobile,'+','') AS mobile,
cast(created_at AS date) AS created_at,
address_line1,
address_line2,
city,
province,
row_number() over (partition BY email
ORDER BY created_at) AS R
FROM raena_user_management.user)A
WHERE R=1
AND email is NOT NULL) b ON a.email=b.email
AND a.app_install_date<=b.created_at
LEFT JOIN
(SELECT DISTINCT user_id,
email,
address_line1 AS customer_address_line1,
address_line2 AS customer_address_line2,
city AS customer_city,
province AS customer_province,
row_number() over (partition BY email
ORDER BY created_at) AS R,
cast(created_at AS date) AS created_at
FROM raena_user_management.customer) c ON a.email=c.email
AND c.R=1
AND a.app_install_date<=b.created_at)A;
DROP TABLE IF EXISTS raena_analytics.om_clevertap_install_jan_may_3;
CREATE TABLE raena_analytics.om_clevertap_install_jan_may_3 AS
SELECT a.*,
cast(b.created_at AS date) AS transaction_date,
transacted_email
FROM raena_analytics.om_clevertap_install_jan_may_2 a
LEFT JOIN
(SELECT reseller_email AS transacted_email ,
min(transaction_date) AS created_at
FROM raena_analytics.gm_dashboard
GROUP BY 1) b ON a.email=transacted_email;
DROP TABLE IF EXISTS raena_analytics.om_clevertap_install_jan_may_final;
CREATE TABLE raena_analytics.om_clevertap_install_jan_may_final AS
SELECT *,
CASE
WHEN email NOTNULL
AND rtrim(ltrim(email)) != '' THEN 'Yes'
ELSE 'No'
END AS email_flag,
CASE
WHEN phone NOTNULL
AND rtrim(ltrim(phone)) != '' THEN 'Yes'
ELSE 'No'
END AS phone_flag,
CASE
WHEN address_line1 NOTNULL
AND rtrim(ltrim(address_line1)) != '' THEN 'Yes'
ELSE 'No'
END AS address_flag,
CASE
WHEN transacted_email NOTNULL
AND rtrim(ltrim(transacted_email)) != ''
AND transaction_date>=app_install_date
AND transaction_date<=app_install_date+interval'30 day' THEN 'Yes'
ELSE 'No'
END AS transacted_flag
FROM raena_analytics.om_clevertap_install_jan_may_3 ;
DROP TABLE IF EXISTS raena_analytics.om_clevertap_install_jan_may_order;
CREATE TABLE raena_analytics.om_clevertap_install_jan_may_order AS
SELECT *
FROM raena_analytics.om_clevertap_install_jan_may_final
WHERE transacted_email NOTNULL
AND rtrim(ltrim(transacted_email)) != ''; ;
INSERT INTO raena_analytics.Test_1_week_om_clevertap_install_jan_may_final
SELECT *,
CURRENT_DATE-1 AS Report_date
FROM raena_analytics.om_clevertap_install_jan_may_final;
DROP TABLE IF EXISTS raena_analytics.om_reseller_info;
CREATE TABLE raena_analytics.om_reseller_info AS
SELECT DISTINCT json_extract_path_text(reseller_info,'mobile',true) AS reseller_mobile ,
json_extract_path_text(reseller_info,'email',true) AS reseller_email,
reseller_id
FROM raena_order_management.order
WHERE id LIKE '%DSF';
DROP TABLE IF EXISTS raena_analytics.OM_Order_id_payment_id;
CREATE TABLE raena_analytics.OM_Order_id_payment_id AS WITH NS AS
(SELECT 1 AS n
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12
UNION ALL SELECT 13
UNION ALL SELECT 14
UNION ALL SELECT 15
UNION ALL SELECT 16
UNION ALL SELECT 17
UNION ALL SELECT 18
UNION ALL SELECT 19
UNION ALL SELECT 20
UNION ALL SELECT 21
UNION ALL SELECT 22
UNION ALL SELECT 23
UNION ALL SELECT 24
UNION ALL SELECT 25)
SELECT id,
max(created_at) created_at,
replace(cast(substring(cast(TRIM(SPLIT_PART(B.order_ids , ',', NS.n))AS varchar),2,length(cast(TRIM(SPLIT_PART(B.order_ids , ',', NS.n))AS varchar))-2) AS varchar),'\"','') AS order_id
FROM NS
INNER JOIN raena_order_management.payment B ON NS.n <= REGEXP_COUNT(B.order_ids , ',') + 1
GROUP BY 1,
3;
delete from raena_analytics.OM_Events_user_type
where cast(ts as date)>='$reportDate';
insert into raena_analytics.OM_Events_user_type
SELECT A.*,
CASE
WHEN date_trunc('Month',A.ts)::date = date_trunc('Month',first_install_date)::date
AND date_trunc('Month',A.ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
WHEN date_trunc('Month',A.ts)::date-date_trunc('Month',first_install_date)::date>= 30
AND date_trunc('Month',A.ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
WHEN date_trunc('Month',A.ts)::date-date_trunc('Month',first_install_date)::date>=30
AND date_trunc('Month',A.ts)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
ELSE 'Na'
END AS New_existing_flag
FROM (select * from clevertap.clevertap_master_data where events in ('add_to_cart','view_item','dsfpage_pdp_view' ,'dsfpage_submit','pdp_setmargin','dsfpage_chatwithseller','dsfpage_buynow',
'share_whatsapp','share_generic','share_facebook','share_tiktok','share_instagram','video_page_play','reviews_show_all','video_page_productclick',
'video_page_instagram','video_page_whatsapp','video_page_generic','video_page_tiktok','video_page_facebook','video_page_play') and ts::date >= '$reportDate') A
LEFT JOIN raena_analytics.user_type_table flag ON replace(A.phone,'+','')=flag.reseller_mobile;
DROP TABLE IF EXISTS raena_analytics.om_order_brand_type;
DROP TABLE IF EXISTS raena_analytics.om_order_brand_type;
CREATE TABLE raena_analytics.om_order_brand_type AS
SELECT DISTINCT sso.order_id,
coalesce(ssopc.sku,parent_sku) AS sku,
CASE
WHEN b.name IN ('HISTOIRE NATURELLE',
'INGRID',
'W DRESSROOM',
'FORENCOS',
'DEWYCEL',
'GLUTANEX',
'BELLFLOWER',
'ONE THING',
'BEAUSTA') THEN 'EL/PL'
WHEN b.name IN ('LUXCRIME',
'SKINTIFIC',
'TRUEVE',
'SANIYE',
'BEAUDELAB',
'BRASOV',
'FACE REPUBLIC',
'SKIN1004',
'PREMIERE BEAUTE',
'ALLURA',
'LIPLAPIN',
'ROUNDLAB',
'FACE FLUX',
'DOLLGORAE',
'SKINUA',
'PUREFORET',
'SKINTIFIC',
'OHMYSKIN',
'FEAT FOR SKIN',
'SECONDATE',
'KYND',
'PURNAMA',
'BASE',
'LAVIE LASH',
'REI SKIN',
'USTRAA',
'BRUNBRUN PARISGLOWINC',
'SOONHAN',
'THE YEON',
'MIXSOON',
'KOSE COSMEPORT') THEN 'High GM'
END Brand_type,
b.name AS brand_name
FROM (select distinct id,order_id,parent_sku from raena_order_management.sales_sub_order) sso
LEFT JOIN (select distinct sales_sub_order_id,sku from raena_order_management.sales_sub_order_parent_child) ssopc ON sso.id=ssopc.sales_sub_order_id
INNER JOIN raena_catalog_management.product p ON coalesce(ssopc.sku,parent_sku)=p.sku
INNER JOIN raena_catalog_management.brand b ON p.brand_id=b.id;
" > /home/ec2-user/cronjob/redshift/sql_code/new_user_funnel_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/new_user_funnel_etl.sql