46 lines
2.4 KiB
Bash
46 lines
2.4 KiB
Bash
#!/bin/bash
|
|
|
|
|
|
echo -e " \n----------- Conversion Report Metabase Dashboard Code --------------\n"
|
|
|
|
echo "
|
|
--------------------------Conversion Report Business metrics--------------------------
|
|
|
|
drop table if exists public.om_conversion_order_data_1
|
|
create table public.om_conversion_order_data_1
|
|
as
|
|
select distinct cast(created_at as date) as created_date,date_part('year',created_at) as order_year,date_part('month',created_at) as order_month,
|
|
a.reseller_id,id as order_id,json_extract_path_text(reseller_info,'email',TRUE) as transacted_email,replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') as transacted_mobile,
|
|
payment_amount,shipping_to,order_placed_by,b.Monthly_Tier,c.Max_Tier_of_any_month,
|
|
rank() over(partition by a.reseller_id order by date_part('year',created_at),date_part('month',created_at)) as R
|
|
from raena_order_management.order a
|
|
inner join (
|
|
select distinct
|
|
reseller_id,order_year,order_month,
|
|
case when revenue<2000000 then '<2 mn' when revenue>=2000000 and revenue<=10000000 then '2-10 mn' when revenue>10000000 then '10+ mn' end as Monthly_Tier
|
|
from (
|
|
select date_part('year',created_at) as order_year,date_part('month',created_at) as order_month,reseller_id
|
|
,sum(payment_amount) as revenue
|
|
from raena_order_management.order
|
|
where payment_status='Paid' and cast(is_archived as varchar)='false'
|
|
group by date_part('year',created_at),date_part('month',created_at),reseller_id
|
|
) where reseller_id notnull
|
|
) b on a.reseller_id=b.reseller_id and date_part('year',a.created_at) =b.order_year and date_part('month',a.created_at)=b.order_month
|
|
inner join (
|
|
select distinct
|
|
reseller_id,case when revenue<2000000 then '<2 mn' when revenue>=2000000 and revenue<=10000000 then '2-10 mn' when revenue>10000000 then '10+ mn' end as Max_Tier_of_any_month
|
|
from (
|
|
select reseller_id,max(payment_amount) as revenue
|
|
from raena_order_management.order
|
|
where payment_status='Paid' and cast(is_archived as varchar)='false'
|
|
group by reseller_id
|
|
) where reseller_id notnull
|
|
) c on a.reseller_id=c.reseller_id
|
|
where payment_status='Paid' and cast(is_archived as varchar)='false'
|
|
order by 1,3
|
|
|
|
" > /home/ec2-user/cronjob/new_users/conversion_report.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/new_users/conversion_report.sql > /conversion_report.log
|
|
|