85 lines
4.2 KiB
MySQL
85 lines
4.2 KiB
MySQL
|
|
||
|
|
--------------------------DB Funnel New Users--------------------------
|
||
|
|
|
||
|
|
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
|
||
|
|
|
||
|
|
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(created_at as date) else app_install_date end as app_install_date,
|
||
|
|
b.address_line1,address_line2,city,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,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
|
||
|
|
)
|
||
|
|
|
||
|
|
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
|
||
|
|
|
||
|
|
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)) != ''
|
||
|
|
|
||
|
|
--launch
|
||
|
|
Drop table if exists public.om_app_launched_db_funnel
|
||
|
|
create table public.om_app_launched_db_funnel
|
||
|
|
as
|
||
|
|
select * from (
|
||
|
|
select distinct b.app_install_date,a.App_launch_date,a.user_id,
|
||
|
|
case when b.phone isnull then 'New'
|
||
|
|
when App_launch_date >= app_install_date and App_launch_date < cast(dateadd(day,30,app_install_date) as date) then 'Yes' else 'No' end as flag,
|
||
|
|
row_number() over (partition by a.User_id order by App_launch_date) as R
|
||
|
|
from (
|
||
|
|
select distinct cast(left(ts,4) || '-' || right(left(ts,6),2) || '-' || right(left(ts,8),2) as date) as App_launch_date,
|
||
|
|
profile_phone,profile_objectid as launch_profile_objectid ,case when profile_email=' ' then profile_objectid else profile_email end as user_id
|
||
|
|
from clevertap.app_launched
|
||
|
|
) a
|
||
|
|
inner join om_clevertap_install_jan_may_final b on a.user_id=b.user_id
|
||
|
|
) where flag='Yes' order by user_id
|
||
|
|
|
||
|
|
|