179 lines
8.5 KiB
Bash
179 lines
8.5 KiB
Bash
#!/bin/bash
|
|
|
|
echo "
|
|
|
|
--------------------------Revenue Leakage -------------------------
|
|
|
|
drop table if exists om_clevertap_install_jan_may;
|
|
|
|
|
|
create table 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 '2022-04-01' as App_install_date,email,phone,email as user_id FROM public.clevertap_april_csv
|
|
union
|
|
SELECT '2022-03-30' as App_install_date,email,phone,email as user_id FROM public.clevertap_march_csv where phone not in (select distinct profile_phone from clevertap.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
|
|
)
|
|
) where R=1 ;--and App_install_date>='2022-01-01'
|
|
|
|
drop table if exists om_clevertap_install_jan_may_2;
|
|
|
|
|
|
create table 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 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
|
|
)
|
|
where R=1 and email notnull) 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
|
|
);
|
|
|
|
--select count(*) from om_clevertap_install_jan_may_2 --494190
|
|
|
|
drop table if exists om_clevertap_install_jan_may_3;
|
|
|
|
|
|
|
|
create table om_clevertap_install_jan_may_3
|
|
as
|
|
select a.*,cast(b.created_at as date) as transaction_date,transacted_email
|
|
--case when cast(b.created_at as date) isnull then 'Never transacted'
|
|
--when cast(b.created_at as date)>=app_install_date and cast(b.created_at as date)<=cast(dateadd(day,30,app_install_date) as date) then 'Yes' else 'No' end as transacted_flag
|
|
from om_clevertap_install_jan_may_2 a
|
|
left join (select json_extract_path_text(reseller_info,'email',TRUE) as transacted_email ,min(created_at) as created_at
|
|
from raena_order_management.order where payment_status='Paid' and cast(is_archived as varchar)='false' group by 1 ) b
|
|
on a.email=transacted_email;
|
|
|
|
----select count(*) from om_clevertap_install_jan_may_3 --495191
|
|
|
|
drop table if exists om_clevertap_install_jan_may_final;
|
|
|
|
|
|
create table 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<=cast(dateadd(day,30,app_install_date) as date)
|
|
then 'Yes' else 'No' end as transacted_flag
|
|
from om_clevertap_install_jan_may_3 ;
|
|
|
|
--order data
|
|
drop table if exists om_clevertap_install_jan_may_order;
|
|
|
|
|
|
create table om_clevertap_install_jan_may_order
|
|
as
|
|
select * from om_clevertap_install_jan_may_final where transacted_email notnull and rtrim(ltrim(transacted_email)) != ''; ;
|
|
|
|
Insert into Test_1_week_om_clevertap_install_jan_may_final
|
|
select *,current_date-1 as Report_date
|
|
from om_clevertap_install_jan_may_final;
|
|
|
|
drop table if exists public.om_reseller_info;
|
|
|
|
create table public.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 public.OM_Order_id_payment_id;
|
|
|
|
CREATE TABLE public.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;
|
|
|
|
drop table if exists OM_Events_user_type;
|
|
|
|
create table OM_Events_user_type
|
|
as
|
|
select clevertap.clevertap_master_data.*,
|
|
CASE WHEN date_trunc('Month',clevertap.clevertap_master_data.ts)::date = date_trunc('Month',first_install_date)::date
|
|
AND date_trunc('Month',clevertap.clevertap_master_data.ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
|
|
WHEN date_trunc('Month',clevertap.clevertap_master_data.ts)::date-date_trunc('Month',first_install_date)::date>= 30
|
|
AND date_trunc('Month',clevertap.clevertap_master_data.ts)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
|
|
WHEN date_trunc('Month',clevertap.clevertap_master_data.ts)::date-date_trunc('Month',first_install_date)::date>=30
|
|
AND date_trunc('Month',clevertap.clevertap_master_data.ts)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
|
|
else 'Na' end AS New_existing_flag
|
|
from clevertap.clevertap_master_data
|
|
left join user_type_table flag ON replace(clevertap.clevertap_master_data.phone,'+','')=flag.reseller_mobile
|
|
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');
|
|
|
|
drop table if exists public.om_order_brand_type;
|
|
|
|
create table public.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 raena_order_management.sales_sub_order sso
|
|
left join 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/OOS/DB_funnel_new_users.sql
|
|
|
|
psql "host=raen-prd-sg-redshift-cluster.cdqj58hfx4p7.ap-southeast-1.redshift.amazonaws.com user=dbadmin dbname=analytics port=5439 password=5qCif6eyY3Kmg4z" -f/home/ec2-user/cronjob/OOS/DB_funnel_new_users.sql > DB_funnel_new_users.log
|