1062 lines
48 KiB
Bash
1062 lines
48 KiB
Bash
|
#!/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.daily_recommendation_ingredients_split;
|
||
|
|
|
||
|
|
create table raena_analytics.daily_recommendation_ingredients_split
|
||
|
|
as
|
||
|
|
select reseller_email ,sku,replace(replace(replace(\" Top Marketing Ingredients \",'{',''),'}',''),'\"','') Ingredients,score,after_discount
|
||
|
|
from raena_recommendation_engine.daily_recommendation_ingredients;
|
||
|
|
|
||
|
|
drop table raena_analytics.daily_recommendation_concerns_split;
|
||
|
|
|
||
|
|
create table raena_analytics.daily_recommendation_concerns_split
|
||
|
|
as
|
||
|
|
select reseller_email ,sku,replace(replace(replace(\" Concern(s) \" ,'{',''),'}',''),'\"','') Ingredients,score,after_discount
|
||
|
|
from raena_recommendation_engine.daily_recommendation_concerns drc ;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE public.product_metrics_calendar;
|
||
|
|
|
||
|
|
CREATE TABLE public.product_metrics_calendar AS
|
||
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
||
|
|
'New' user_type
|
||
|
|
FROM raena_order_management.order
|
||
|
|
UNION
|
||
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
||
|
|
'Activation' user_type
|
||
|
|
FROM raena_order_management.order
|
||
|
|
UNION
|
||
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
||
|
|
'Existing' user_type
|
||
|
|
FROM raena_order_management.order
|
||
|
|
UNION
|
||
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
||
|
|
'Na' user_type
|
||
|
|
FROM raena_order_management.order;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.user_type_table ;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.user_type_table AS
|
||
|
|
SELECT DISTINCT profile_phone reseller_mobile,
|
||
|
|
First_transaction_date,
|
||
|
|
min_created_date first_install_date
|
||
|
|
FROM
|
||
|
|
(SELECT replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') reseller_mobile,
|
||
|
|
min(created_at) First_transaction_date
|
||
|
|
FROM raena_order_management.order
|
||
|
|
WHERE payment_status = 'Paid'
|
||
|
|
GROUP BY 1) A
|
||
|
|
INNER JOIN
|
||
|
|
(SELECT profile_phone,
|
||
|
|
min(created_at) min_created_date
|
||
|
|
FROM
|
||
|
|
(SELECT profile_phone,
|
||
|
|
MIN(cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date)) created_at
|
||
|
|
FROM clevertap.app_installed
|
||
|
|
GROUP BY profile_phone
|
||
|
|
UNION SELECT replace(mobile,'+','') profile_phone,
|
||
|
|
MIN(cast(created_at AS date)) AS created_at
|
||
|
|
FROM raena_user_management.user u
|
||
|
|
GROUP BY 1) A
|
||
|
|
GROUP BY 1) B ON A.reseller_mobile=B.profile_phone;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.total_uninstalled;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.total_uninstalled AS
|
||
|
|
SELECT case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
|
||
|
|
else 'Na'
|
||
|
|
END user_type,
|
||
|
|
transaction_date,
|
||
|
|
count(1) total_event ,
|
||
|
|
Count(DISTINCT A.profile_phone) Total_users
|
||
|
|
FROM
|
||
|
|
(SELECT profile_objectid,
|
||
|
|
CASE
|
||
|
|
WHEN profile_phone<>'' THEN profile_phone
|
||
|
|
END profile_phone,
|
||
|
|
profile_email,
|
||
|
|
profile_platform,
|
||
|
|
eventname,
|
||
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
|
||
|
|
FROM clevertap.app_uninstalled) A
|
||
|
|
LEFT JOIN public.user_type_table B ON A.profile_phone = B.reseller_mobile
|
||
|
|
GROUP BY 1,
|
||
|
|
2;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.total_installed;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.total_installed AS
|
||
|
|
SELECT case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
|
||
|
|
else 'Na'
|
||
|
|
END user_type,
|
||
|
|
transaction_date,
|
||
|
|
count(1) total_event ,
|
||
|
|
Count(DISTINCT A.profile_phone) Total_users
|
||
|
|
FROM
|
||
|
|
(SELECT profile_objectid,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
profile_platform,
|
||
|
|
eventname,
|
||
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
|
||
|
|
FROM clevertap.app_installed) A
|
||
|
|
LEFT JOIN public.user_type_table B ON A.profile_phone = B.reseller_mobile
|
||
|
|
GROUP BY 1,
|
||
|
|
2;
|
||
|
|
|
||
|
|
drop table public.Active_user;
|
||
|
|
|
||
|
|
CREATE TABLE public.Active_user AS
|
||
|
|
SELECT date_trunc('Month',A.transaction_date)::date event_created_date,
|
||
|
|
date_trunc('Month',first_install_date)::date install_date,
|
||
|
|
date_trunc('Month',First_transaction_date)::date first_transaction_date,
|
||
|
|
CASE
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',coalesce(first_install_date,D.created_at::date))::date THEN 'New'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
|
||
|
|
AND ( date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date
|
||
|
|
OR First_transaction_date IS NULL) THEN 'Activation'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
|
||
|
|
WHEN date_trunc('Month',first_install_date)::date IS NULL
|
||
|
|
AND first_transaction_date IS NULL THEN 'New'
|
||
|
|
WHEN date_trunc('Month',first_install_date)::date IS NULL
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
|
||
|
|
WHEN date_trunc('Month',first_install_date)::date IS NULL
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
|
||
|
|
ELSE 'Na'
|
||
|
|
END user_type,
|
||
|
|
transaction_date,
|
||
|
|
count(1) total_event ,
|
||
|
|
A.profile_phone,
|
||
|
|
A.profile_objectid
|
||
|
|
FROM
|
||
|
|
(SELECT profile_objectid,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
profile_platform,
|
||
|
|
eventname,
|
||
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
|
||
|
|
FROM clevertap.app_launched) A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') reseller_mobile,
|
||
|
|
cast(min(created_at) AS DATE) First_transaction_date
|
||
|
|
FROM raena_order_management.order
|
||
|
|
WHERE payment_status = 'Paid'
|
||
|
|
GROUP BY 1) B ON A.profile_phone = B.reseller_mobile
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT profile_objectid,
|
||
|
|
MIN(cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date)) first_install_date
|
||
|
|
FROM clevertap.app_installed
|
||
|
|
GROUP BY profile_objectid) C ON A.profile_objectid= C.profile_objectid
|
||
|
|
LEFT JOIN raena_user_management.user D ON A.profile_phone = replace(D.mobile,'+','')
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4,
|
||
|
|
5,
|
||
|
|
7,
|
||
|
|
8;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.total_view_item;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.total_view_item AS
|
||
|
|
SELECT case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
|
||
|
|
else 'Na'
|
||
|
|
END user_type,
|
||
|
|
transaction_date,
|
||
|
|
count(1) total_event ,
|
||
|
|
A.profile_objectid
|
||
|
|
FROM
|
||
|
|
(SELECT profile_objectid,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
profile_platform,
|
||
|
|
eventname,
|
||
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
|
||
|
|
FROM clevertap.view_item) A
|
||
|
|
LEFT JOIN public.user_type_table B ON A.profile_phone = B.reseller_mobile
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
4;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.total_first_transacting_user;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.total_first_transacting_user AS
|
||
|
|
SELECT case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
|
||
|
|
else 'Na'
|
||
|
|
END user_type,
|
||
|
|
transaction_date,
|
||
|
|
profile_phone,
|
||
|
|
count(1) total_event ,
|
||
|
|
Count(DISTINCT A.reseller_id) Total_users,
|
||
|
|
sum(total_orders) total_orders,
|
||
|
|
sum(revenue) revenue
|
||
|
|
FROM
|
||
|
|
(SELECT reseller_id ,
|
||
|
|
replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') profile_phone,
|
||
|
|
created_at::date transaction_date,
|
||
|
|
count(DISTINCT id) total_orders,
|
||
|
|
sum(payment_amount) AS revenue
|
||
|
|
FROM raena_order_management.order
|
||
|
|
WHERE payment_status='Paid'
|
||
|
|
AND is_campaign = 'false'
|
||
|
|
AND cast(is_archived AS varchar)='false'
|
||
|
|
AND order_placed_by ='reseller'
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3) A
|
||
|
|
LEFT JOIN public.user_type_table B ON A.profile_phone = B.reseller_mobile
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3;
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.post_discount_gm;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.post_discount_gm AS
|
||
|
|
SELECT transaction_date,
|
||
|
|
case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
|
||
|
|
else 'Na'
|
||
|
|
END user_type,
|
||
|
|
sum(discounted_price*quantity) discounted_price,
|
||
|
|
sum(cogs*quantity) cogs,
|
||
|
|
count(DISTINCT external_id) AS Number_of_orders
|
||
|
|
FROM OM_GM_DB_Product_category A
|
||
|
|
LEFT JOIN public.user_type_table B ON A.reseller_mobile =B.reseller_mobile
|
||
|
|
WHERE is_campaign='false'
|
||
|
|
AND order_placed_by='reseller'
|
||
|
|
GROUP BY 1,
|
||
|
|
2;
|
||
|
|
|
||
|
|
drop table if exists public.Active_user_for_mau_stage;
|
||
|
|
|
||
|
|
CREATE TABLE public.Active_user_for_mau_stage AS
|
||
|
|
SELECT profile_objectid,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
profile_platform,
|
||
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
|
||
|
|
FROM clevertap.app_installed
|
||
|
|
union
|
||
|
|
SELECT profile_objectid,
|
||
|
|
profile_phone,
|
||
|
|
profile_email,
|
||
|
|
profile_platform,
|
||
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) transaction_date
|
||
|
|
FROM clevertap.app_launched;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
drop table if exists public.Active_user_for_mau;
|
||
|
|
|
||
|
|
CREATE TABLE public.Active_user_for_mau AS
|
||
|
|
SELECT date_trunc('Month',A.transaction_date)::date event_created_date,
|
||
|
|
date_trunc('Month',first_install_date)::date install_date,
|
||
|
|
date_trunc('Month',First_transaction_date)::date first_transaction_date,
|
||
|
|
CASE
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',(first_install_date))::date THEN 'New'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date>=date_trunc('Month',first_install_date)::date
|
||
|
|
AND ( date_trunc('Month',A.transaction_date)::date <= date_trunc('Month',First_transaction_date)::date
|
||
|
|
or date_trunc('Month',First_transaction_date)::date is null ) THEN 'Activation'
|
||
|
|
else 'Existing'
|
||
|
|
END user_type,
|
||
|
|
transaction_date,
|
||
|
|
count(1) total_event ,
|
||
|
|
A.profile_phone,
|
||
|
|
A.profile_objectid
|
||
|
|
FROM public.Active_user_for_mau_stage A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT profile_objectid profile_objectid,
|
||
|
|
cast(min(transaction_date) AS DATE) First_transaction_date
|
||
|
|
FROM (SELECT profile_objectid,
|
||
|
|
(cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date)) transaction_date
|
||
|
|
FROM clevertap.charged )
|
||
|
|
GROUP BY 1) B ON A.profile_objectid = B.profile_objectid
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT profile_objectid,
|
||
|
|
MIN(cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date)) first_install_date
|
||
|
|
FROM clevertap.app_installed
|
||
|
|
GROUP BY profile_objectid) C ON A.profile_objectid= C.profile_objectid
|
||
|
|
--LEFT JOIN raena_user_management.user D ON A.profile_phone = replace(D.mobile,'+','')
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4,
|
||
|
|
5,
|
||
|
|
7,
|
||
|
|
8;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.lm_revenue_gm;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.lm_revenue_gm AS
|
||
|
|
SELECT transaction_date,
|
||
|
|
case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
|
||
|
|
else 'Na'
|
||
|
|
END user_type,
|
||
|
|
sum(CASE WHEN a.external_id = BB.order_id
|
||
|
|
AND a.sku = BB.sku
|
||
|
|
AND (CASE WHEN item_type LIKE '%Bundle%' THEN 'Bundle' ELSE 'Product' END)=BB.product_class THEN BB.new_shipment_amount ELSE CC.new_shipment_amount END) shipping_amount,
|
||
|
|
sum(a.discounted_price*a.quantity) discounted_price
|
||
|
|
FROM
|
||
|
|
(SELECT *
|
||
|
|
FROM OM_GM_DB_Product_category
|
||
|
|
WHERE order_placed_by ='reseller'
|
||
|
|
AND is_campaign='false') A
|
||
|
|
LEFT JOIN public.sku_level_shipping_fee_final BB ON a.external_id = BB.order_id
|
||
|
|
AND a.sku = BB.sku
|
||
|
|
AND (CASE
|
||
|
|
WHEN item_type LIKE '%Bundle%' THEN 'Bundle'
|
||
|
|
ELSE 'Product'
|
||
|
|
END)=BB.product_class
|
||
|
|
LEFT JOIN public.sku_level_shipping_fee_old_final CC ON a.external_id = CC.external_id
|
||
|
|
AND a.sku = CC.sku
|
||
|
|
AND (CASE
|
||
|
|
WHEN item_type LIKE '%Bundle%' THEN 'Bundle'
|
||
|
|
ELSE 'Product'
|
||
|
|
END)=CC.product_class
|
||
|
|
LEFT JOIN public.user_type_table B ON A.reseller_mobile=B.reseller_mobile
|
||
|
|
GROUP BY 1,
|
||
|
|
2;
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.gm_order_count;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.gm_order_count AS
|
||
|
|
SELECT transaction_date,
|
||
|
|
case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
|
||
|
|
else 'Na'
|
||
|
|
END user_type,
|
||
|
|
sum(Customer_order) Customer_order,
|
||
|
|
sum(Marketplace_order)Marketplace_order,
|
||
|
|
sum(total_dropship_order) total_dropship_order,
|
||
|
|
sum(total_order) total_order
|
||
|
|
FROM
|
||
|
|
(SELECT transaction_date,
|
||
|
|
reseller_mobile,
|
||
|
|
count(DISTINCT CASE WHEN order_recipient= 'Customer' THEN external_id END) AS Customer_order,
|
||
|
|
count(DISTINCT CASE WHEN order_recipient= 'Marketplace' THEN external_id END) AS Marketplace_order,
|
||
|
|
count(DISTINCT CASE WHEN order_recipient IN ('Customer', 'Marketplace') THEN external_id END) total_dropship_order,
|
||
|
|
count(DISTINCT external_id) total_order
|
||
|
|
FROM OM_GM_DB_Product_category
|
||
|
|
WHERE is_campaign='false'
|
||
|
|
AND order_placed_by ='reseller'
|
||
|
|
GROUP BY 1,
|
||
|
|
2) A
|
||
|
|
LEFT JOIN public.user_type_table B ON A.reseller_mobile=B.reseller_mobile
|
||
|
|
GROUP BY 1,
|
||
|
|
2;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.post_discount_dsf_revenue;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.post_discount_dsf_revenue AS
|
||
|
|
SELECT transaction_date,
|
||
|
|
case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',first_install_date)::date
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'New'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>= 30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',First_transaction_date)::date THEN 'Activation'
|
||
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',first_install_date)::date>=30
|
||
|
|
AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',First_transaction_date)::date>=30 THEN 'Existing'
|
||
|
|
else 'Na'
|
||
|
|
END user_type,
|
||
|
|
sum(payment_amount) discounted_price,
|
||
|
|
count(DISTINCT id) AS Number_of_orders,
|
||
|
|
count(DISTINCT reseller_id) AS total_users
|
||
|
|
FROM
|
||
|
|
(SELECT reseller_id ,
|
||
|
|
id ,
|
||
|
|
payment_amount ,
|
||
|
|
replace(json_extract_path_text(reseller_info,'mobile',TRUE),'+','') reseller_mobile,
|
||
|
|
created_at::date transaction_date
|
||
|
|
FROM raena_order_management.order
|
||
|
|
WHERE is_campaign='false'
|
||
|
|
AND lower(id) LIKE '%dsf'
|
||
|
|
AND payment_status='Paid') A
|
||
|
|
LEFT JOIN public.user_type_table B ON A.reseller_mobile =B.reseller_mobile
|
||
|
|
GROUP BY 1,
|
||
|
|
2;
|
||
|
|
|
||
|
|
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;
|
||
|
|
|
||
|
|
|
||
|
|
select max(transaction_date) uninstalled_td from public.total_uninstalled;
|
||
|
|
select max(transaction_date) installed_td from public.total_installed;
|
||
|
|
select max(transaction_date) launched_td from public.Active_user;
|
||
|
|
select max(transaction_date) view_td from public.total_view_item;
|
||
|
|
select max(transaction_date) gm_td from public.lm_revenue_gm;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.overall_conversion_trend;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.overall_conversion_trend AS
|
||
|
|
SELECT date_trunc('day',A.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
||
|
|
'Day'frequency,
|
||
|
|
A.user_type ,
|
||
|
|
cast(sum(total_orders) AS decimal(22,4)) total_orders,
|
||
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
||
|
|
FROM
|
||
|
|
(SELECT date_trunc('day',public.product_metrics_calendar.transaction_date)::date transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type,
|
||
|
|
count(DISTINCT profile_objectid) MAU
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.active_user ON public.product_metrics_calendar.transaction_date =public.active_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.active_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT date_trunc('day',public.product_metrics_calendar.transaction_date)::date transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
sum(total_orders) total_orders
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_first_transacting_user ON public.product_metrics_calendar.transaction_date =public.total_first_transacting_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.total_first_transacting_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2 ) B ON A.transaction_date = B.transaction_date
|
||
|
|
AND A.user_type = B.user_type
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Month',A.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',A.transaction_date) AS transaction_time,
|
||
|
|
'Month'frequency,
|
||
|
|
A.user_type ,
|
||
|
|
cast(sum(total_orders) AS decimal(22,4)) total_orders,
|
||
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
||
|
|
FROM
|
||
|
|
(SELECT date_trunc('Month',public.product_metrics_calendar.transaction_date)::date transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type,
|
||
|
|
count(DISTINCT profile_objectid) MAU
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.active_user ON public.product_metrics_calendar.transaction_date =public.active_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.active_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT date_trunc('Month',public.product_metrics_calendar.transaction_date)::date transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
sum(total_orders) total_orders
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_first_transacting_user ON public.product_metrics_calendar.transaction_date =public.total_first_transacting_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.total_first_transacting_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2 ) B ON A.transaction_date = B.transaction_date
|
||
|
|
AND A.user_type = B.user_type
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Week',A.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',A.transaction_date) AS transaction_time,
|
||
|
|
'Week'frequency,
|
||
|
|
A.user_type ,
|
||
|
|
cast(sum(total_orders) AS decimal(22,4)) total_orders,
|
||
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
||
|
|
FROM
|
||
|
|
(SELECT date_trunc('Week',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type,
|
||
|
|
count(DISTINCT profile_objectid) MAU
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.active_user ON public.product_metrics_calendar.transaction_date =public.active_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.active_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT date_trunc('Week',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
sum(total_orders) total_orders
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_first_transacting_user ON public.product_metrics_calendar.transaction_date =public.total_first_transacting_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.total_first_transacting_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2 ) B ON A.transaction_date = B.transaction_date
|
||
|
|
AND A.user_type = B.user_type
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Quarter',A.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',A.transaction_date) AS transaction_time,
|
||
|
|
'Quarter'frequency,
|
||
|
|
A.user_type ,
|
||
|
|
cast(sum(total_orders) AS decimal(22,4)) total_orders,
|
||
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
||
|
|
FROM
|
||
|
|
(SELECT date_trunc('Quarter',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type,
|
||
|
|
count(DISTINCT profile_objectid) MAU
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.active_user ON public.product_metrics_calendar.transaction_date =public.active_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.active_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT date_trunc('Quarter',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
sum(total_orders) total_orders
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_first_transacting_user ON public.product_metrics_calendar.transaction_date =public.total_first_transacting_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.total_first_transacting_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2 ) B ON A.transaction_date = B.transaction_date
|
||
|
|
AND A.user_type = B.user_type
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Year',A.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',A.transaction_date) AS transaction_time,
|
||
|
|
'Year'frequency,
|
||
|
|
A.user_type ,
|
||
|
|
cast(sum(total_orders) AS decimal(22,4)) total_orders,
|
||
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
||
|
|
FROM
|
||
|
|
(SELECT date_trunc('Year',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type,
|
||
|
|
count(DISTINCT profile_objectid) MAU
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.active_user ON public.product_metrics_calendar.transaction_date =public.active_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.active_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT date_trunc('year',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
sum(total_orders) total_orders
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_first_transacting_user ON public.product_metrics_calendar.transaction_date =public.total_first_transacting_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.total_first_transacting_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2 ) B ON A.transaction_date = B.transaction_date
|
||
|
|
AND A.user_type = B.user_type
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.overall_converting_user_trend;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.overall_converting_user_trend AS
|
||
|
|
SELECT date_trunc('Year',A.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
||
|
|
'Year'frequency,
|
||
|
|
A.user_type ,
|
||
|
|
cast(sum(total_users) AS decimal(22,4)) total_user,
|
||
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) converting_user
|
||
|
|
FROM
|
||
|
|
(SELECT date_trunc('year',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type,
|
||
|
|
count(DISTINCT profile_objectid) MAU
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.active_user ON public.product_metrics_calendar.transaction_date =public.active_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.active_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT date_trunc('year',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
count(DISTINCT profile_phone) total_users
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_first_transacting_user ON public.product_metrics_calendar.transaction_date =public.total_first_transacting_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.total_first_transacting_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) B ON A.transaction_date = B.transaction_date
|
||
|
|
AND A.user_type = B.user_type
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Quarter',A.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',A.transaction_date) AS transaction_time,
|
||
|
|
'Quarter'frequency,
|
||
|
|
A.user_type ,
|
||
|
|
cast(sum(total_users) AS decimal(22,4)) total_user,
|
||
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) converting_user
|
||
|
|
FROM
|
||
|
|
(SELECT date_trunc('Quarter',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type,
|
||
|
|
count(DISTINCT profile_objectid) MAU
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.active_user ON public.product_metrics_calendar.transaction_date =public.active_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.active_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT date_trunc('Quarter',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
count(DISTINCT profile_phone) total_users
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_first_transacting_user ON public.product_metrics_calendar.transaction_date =public.total_first_transacting_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.total_first_transacting_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) B ON A.transaction_date = B.transaction_date
|
||
|
|
AND A.user_type = B.user_type
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('month',A.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',A.transaction_date) AS transaction_time,
|
||
|
|
'Month'frequency,
|
||
|
|
A.user_type ,
|
||
|
|
cast(sum(total_users) AS decimal(22,4)) total_user,
|
||
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) converting_user
|
||
|
|
FROM
|
||
|
|
(SELECT date_trunc('month',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type,
|
||
|
|
count(DISTINCT profile_objectid) MAU
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.active_user ON public.product_metrics_calendar.transaction_date =public.active_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.active_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT date_trunc('month',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
count(DISTINCT profile_phone) total_users
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_first_transacting_user ON public.product_metrics_calendar.transaction_date =public.total_first_transacting_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.total_first_transacting_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) B ON A.transaction_date = B.transaction_date
|
||
|
|
AND A.user_type = B.user_type
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Week',A.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',A.transaction_date) AS transaction_time,
|
||
|
|
'Week'frequency,
|
||
|
|
A.user_type ,
|
||
|
|
cast(sum(total_users) AS decimal(22,4)) total_user,
|
||
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) converting_user
|
||
|
|
FROM
|
||
|
|
(SELECT date_trunc('Week',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type,
|
||
|
|
count(DISTINCT profile_objectid) MAU
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.active_user ON public.product_metrics_calendar.transaction_date =public.active_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.active_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT date_trunc('Week',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
count(DISTINCT profile_phone) total_users
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_first_transacting_user ON public.product_metrics_calendar.transaction_date =public.total_first_transacting_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.total_first_transacting_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) B ON A.transaction_date = B.transaction_date
|
||
|
|
AND A.user_type = B.user_type
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Day',A.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',A.transaction_date) AS transaction_time,
|
||
|
|
'Day'frequency,
|
||
|
|
A.user_type ,
|
||
|
|
cast(sum(total_users) AS decimal(22,4)) total_user,
|
||
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) converting_user
|
||
|
|
FROM
|
||
|
|
(SELECT date_trunc('Day',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type,
|
||
|
|
count(DISTINCT profile_objectid) MAU
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.active_user ON public.product_metrics_calendar.transaction_date =public.active_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.active_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT date_trunc('Day',public.product_metrics_calendar.transaction_date) transaction_date,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
count(DISTINCT profile_phone) total_users
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_first_transacting_user ON public.product_metrics_calendar.transaction_date =public.total_first_transacting_user.transaction_date
|
||
|
|
AND public.product_metrics_calendar.user_type =coalesce(public.total_first_transacting_user.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2) B ON A.transaction_date = B.transaction_date
|
||
|
|
AND A.user_type = B.user_type
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4 ;
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.revenue_trend;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.revenue_trend AS
|
||
|
|
SELECT date_trunc('Day',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_year,
|
||
|
|
'Day'frequency,
|
||
|
|
user_type ,
|
||
|
|
sum(revenue) Total_revenue
|
||
|
|
FROM public.total_first_transacting_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Week',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_time,
|
||
|
|
'Week'frequency,
|
||
|
|
user_type ,
|
||
|
|
sum(revenue)
|
||
|
|
FROM public.total_first_transacting_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Month',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_time,
|
||
|
|
'Month'frequency,
|
||
|
|
user_type ,
|
||
|
|
sum(revenue)
|
||
|
|
FROM public.total_first_transacting_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Quarter',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_time,
|
||
|
|
'Quarter'frequency,
|
||
|
|
user_type ,
|
||
|
|
sum(revenue)
|
||
|
|
FROM public.total_first_transacting_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Year',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_time,
|
||
|
|
'Year'frequency,
|
||
|
|
user_type ,
|
||
|
|
sum(revenue)
|
||
|
|
FROM public.total_first_transacting_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4;
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.arpu_trend;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.arpu_trend AS
|
||
|
|
SELECT date_trunc('Day',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_year,
|
||
|
|
'Day'frequency,
|
||
|
|
user_type ,
|
||
|
|
cast(sum(revenue) AS decimal(22,4)) total_revenue,
|
||
|
|
CASE
|
||
|
|
WHEN cast(count(DISTINCT profile_phone) AS decimal(22,4)) <>0 THEN cast(count(DISTINCT profile_phone) AS decimal(22,4))
|
||
|
|
END arpu
|
||
|
|
FROM public.total_first_transacting_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Week',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_time,
|
||
|
|
'Week'frequency,
|
||
|
|
user_type ,
|
||
|
|
cast(sum(revenue) AS decimal(22,4)) total_revenue,
|
||
|
|
CASE
|
||
|
|
WHEN cast(count(DISTINCT profile_phone) AS decimal(22,4)) <>0 THEN cast(count(DISTINCT profile_phone) AS decimal(22,4))
|
||
|
|
END arpu
|
||
|
|
FROM public.total_first_transacting_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Month',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_time,
|
||
|
|
'Month'frequency,
|
||
|
|
user_type ,
|
||
|
|
cast(sum(revenue) AS decimal(22,4)) total_revenue,
|
||
|
|
CASE
|
||
|
|
WHEN cast(count(DISTINCT profile_phone) AS decimal(22,4)) <>0 THEN cast(count(DISTINCT profile_phone) AS decimal(22,4))
|
||
|
|
END arpu
|
||
|
|
FROM public.total_first_transacting_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Quarter',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_time,
|
||
|
|
'Quarter'frequency,
|
||
|
|
user_type ,
|
||
|
|
cast(sum(revenue) AS decimal(22,4)) total_revenue,
|
||
|
|
CASE
|
||
|
|
WHEN cast(count(DISTINCT profile_phone) AS decimal(22,4)) <>0 THEN cast(count(DISTINCT profile_phone) AS decimal(22,4))
|
||
|
|
END arpu
|
||
|
|
FROM public.total_first_transacting_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Year',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_time,
|
||
|
|
'Year'frequency,
|
||
|
|
user_type ,
|
||
|
|
cast(sum(revenue) AS decimal(22,4)) total_revenue,
|
||
|
|
CASE
|
||
|
|
WHEN cast(count(DISTINCT profile_phone) AS decimal(22,4)) <>0 THEN cast(count(DISTINCT profile_phone) AS decimal(22,4))
|
||
|
|
END arpu
|
||
|
|
FROM public.total_first_transacting_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4 ;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.install_uninstall_ratio_trend;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.install_uninstall_ratio_trend AS
|
||
|
|
SELECT date_trunc('Year',public.product_metrics_calendar.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',public.product_metrics_calendar.transaction_date) AS transaction_year,
|
||
|
|
'Year'frequency,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
cast(Sum(coalesce(public.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
|
||
|
|
cast(sum(CASE WHEN public.total_installed.total_event>0 THEN coalesce(public.total_installed.total_event,0) END) AS decimal(22,4)) total_install
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_uninstalled ON public.product_metrics_calendar.Transaction_date = public.total_uninstalled.Transaction_date
|
||
|
|
AND public.product_metrics_calendar.User_type = coalesce(public.total_uninstalled.user_type,'Na')
|
||
|
|
LEFT JOIN public.total_installed ON public.product_metrics_calendar.Transaction_date = public.total_installed.Transaction_date
|
||
|
|
AND public.product_metrics_calendar.User_type =coalesce(public.total_installed.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Quarter',public.product_metrics_calendar.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',public.product_metrics_calendar.transaction_date) AS transaction_time,
|
||
|
|
'Quarter'frequency,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
cast(Sum(coalesce(public.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
|
||
|
|
cast(sum(CASE WHEN public.total_installed.total_event>0 THEN coalesce(public.total_installed.total_event,0) END) AS decimal(22,4)) total_install
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_uninstalled ON public.product_metrics_calendar.Transaction_date = public.total_uninstalled.Transaction_date
|
||
|
|
AND public.product_metrics_calendar.User_type = coalesce(public.total_uninstalled.user_type,'Na')
|
||
|
|
LEFT JOIN public.total_installed ON public.product_metrics_calendar.Transaction_date = public.total_installed.Transaction_date
|
||
|
|
AND public.product_metrics_calendar.User_type =coalesce(public.total_installed.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Month',public.product_metrics_calendar.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',public.product_metrics_calendar.transaction_date) AS transaction_time,
|
||
|
|
'Month'frequency,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
cast(Sum(coalesce(public.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
|
||
|
|
cast(sum(CASE WHEN public.total_installed.total_event>0 THEN coalesce(public.total_installed.total_event,0) END) AS decimal(22,4)) total_install
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_uninstalled ON public.product_metrics_calendar.Transaction_date = public.total_uninstalled.Transaction_date
|
||
|
|
AND public.product_metrics_calendar.User_type = coalesce(public.total_uninstalled.user_type,'Na')
|
||
|
|
LEFT JOIN public.total_installed ON public.product_metrics_calendar.Transaction_date = public.total_installed.Transaction_date
|
||
|
|
AND public.product_metrics_calendar.User_type =coalesce(public.total_installed.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Week',public.product_metrics_calendar.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',public.product_metrics_calendar.transaction_date) AS transaction_time,
|
||
|
|
'Week'frequency,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
cast(Sum(coalesce(public.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
|
||
|
|
cast(sum(CASE WHEN public.total_installed.total_event>0 THEN coalesce(public.total_installed.total_event,0) END) AS decimal(22,4)) total_install
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_uninstalled ON public.product_metrics_calendar.Transaction_date = public.total_uninstalled.Transaction_date
|
||
|
|
AND public.product_metrics_calendar.User_type = coalesce(public.total_uninstalled.user_type,'Na')
|
||
|
|
LEFT JOIN public.total_installed ON public.product_metrics_calendar.Transaction_date = public.total_installed.Transaction_date
|
||
|
|
AND public.product_metrics_calendar.User_type =coalesce(public.total_installed.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Day',public.product_metrics_calendar.transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',public.product_metrics_calendar.transaction_date) AS transaction_time,
|
||
|
|
'Day'frequency,
|
||
|
|
public.product_metrics_calendar.user_type ,
|
||
|
|
cast(Sum(coalesce(public.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
|
||
|
|
cast(sum(CASE WHEN public.total_installed.total_event>0 THEN coalesce(public.total_installed.total_event,0) END) AS decimal(22,4)) total_install
|
||
|
|
FROM public.product_metrics_calendar
|
||
|
|
LEFT JOIN public.total_uninstalled ON public.product_metrics_calendar.Transaction_date = public.total_uninstalled.Transaction_date
|
||
|
|
AND public.product_metrics_calendar.User_type = coalesce(public.total_uninstalled.user_type,'Na')
|
||
|
|
LEFT JOIN public.total_installed ON public.product_metrics_calendar.Transaction_date = public.total_installed.Transaction_date
|
||
|
|
AND public.product_metrics_calendar.User_type =coalesce(public.total_installed.user_type,'Na')
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS public.mau_trend;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE public.mau_trend AS
|
||
|
|
SELECT date_trunc('Year',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_time,
|
||
|
|
'Year'frequency,
|
||
|
|
user_type ,
|
||
|
|
cast(count(DISTINCT profile_objectid) AS decimal(22,4)) mau
|
||
|
|
FROM public.active_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Quarter',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_time,
|
||
|
|
'Quarter'frequency,
|
||
|
|
user_type ,
|
||
|
|
cast(count(DISTINCT profile_objectid) AS decimal(22,4)) total_revenue
|
||
|
|
FROM public.active_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Month',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_time,
|
||
|
|
'Month'frequency,
|
||
|
|
user_type ,
|
||
|
|
cast(count(DISTINCT profile_objectid) AS decimal(22,4)) total_revenue
|
||
|
|
FROM public.active_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Week',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_time,
|
||
|
|
'Week'frequency,
|
||
|
|
user_type ,
|
||
|
|
cast(count(DISTINCT profile_objectid) AS decimal(22,4)) total_revenue
|
||
|
|
FROM public.active_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4
|
||
|
|
UNION ALL
|
||
|
|
SELECT date_trunc('Day',transaction_date)::date AS transaction_time,
|
||
|
|
date_part('Year',transaction_date) AS transaction_time,
|
||
|
|
'Day'frequency,
|
||
|
|
user_type ,
|
||
|
|
cast(count(DISTINCT profile_objectid) AS decimal(22,4)) total_revenue
|
||
|
|
FROM public.active_user
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4;
|
||
|
|
|
||
|
|
|
||
|
|
" > /home/ec2-user/cronjob/product_metrics/product_metrics.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/product_metrics/product_metrics.sql
|
||
|
|
|
||
|
|
|