1883 lines
78 KiB
Bash
1883 lines
78 KiB
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("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("Concerns" ,'{',''),'}',''),'\"','') Ingredients,score,after_discount
|
|
from raena_recommendation_engine.daily_recommendation_concerns drc ;
|
|
|
|
|
|
DROP TABLE raena_analytics.product_metrics_calendar;
|
|
|
|
CREATE TABLE raena_analytics.product_metrics_calendar AS
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'New' user_type,
|
|
'<=2M'bucket
|
|
FROM raena_order_management.order
|
|
union
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'New' user_type,
|
|
'>10M'bucket
|
|
FROM raena_order_management.order
|
|
union
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'New' user_type,
|
|
'2M-10M'bucket
|
|
FROM raena_order_management.order
|
|
union
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'New' user_type,
|
|
'Na'bucket
|
|
FROM raena_order_management.order
|
|
UNION
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'Activation' user_type,
|
|
'<=2M'bucket
|
|
FROM raena_order_management.order
|
|
UNION
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'Activation' user_type,
|
|
'>10M'bucket
|
|
FROM raena_order_management.order
|
|
UNION
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'Activation' user_type,
|
|
'2M-10M'bucket
|
|
FROM raena_order_management.order
|
|
UNION
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'Activation' user_type,
|
|
'Na'bucket
|
|
FROM raena_order_management.order
|
|
UNION
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'Existing' user_type,
|
|
'<=2M'bucket
|
|
FROM raena_order_management.order
|
|
UNION
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'Existing' user_type,
|
|
'>10M'bucket
|
|
FROM raena_order_management.order
|
|
UNION
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'Existing' user_type,
|
|
'2M-10M'bucket
|
|
FROM raena_order_management.order
|
|
UNION
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'Existing' user_type,
|
|
'Na'bucket
|
|
FROM raena_order_management.order
|
|
UNION
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'Na' user_type,
|
|
'<=2M'bucket
|
|
FROM raena_order_management.order
|
|
UNION
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'Na' user_type,
|
|
'>10M'bucket
|
|
FROM raena_order_management.order
|
|
UNION
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'Na' user_type,
|
|
'2M-10M'bucket
|
|
FROM raena_order_management.order
|
|
UNION
|
|
SELECT DISTINCT CREATED_AT::date transaction_date ,
|
|
'Na' user_type,
|
|
'Na'bucket
|
|
FROM raena_order_management.order;
|
|
|
|
DROP TABLE raena_analytics.cohort_level_reseller;
|
|
|
|
|
|
CREATE TABLE raena_analytics.cohort_level_reseller AS
|
|
SELECT reseller_id ,
|
|
CASE
|
|
WHEN max_payment_amount BETWEEN 0 AND 2000000 THEN '<=2M'
|
|
WHEN max_payment_amount BETWEEN 2000000 AND 10000000 THEN '2M-10M'
|
|
WHEN max_payment_amount > 10000000 THEN '>10M'
|
|
END Bucket
|
|
FROM
|
|
(SELECT reseller_id ,
|
|
max(payment_amount) max_payment_amount
|
|
FROM
|
|
(SELECT reseller_id ,
|
|
date_trunc('Month',created_at)::date trnsaction_mount,
|
|
sum(payment_amount)payment_amount
|
|
FROM raena_order_management.order
|
|
GROUP BY 1,
|
|
2) A
|
|
GROUP BY 1) B ;
|
|
|
|
|
|
drop table if exists raena_analytics.order_base_for_user_type;
|
|
|
|
create table raena_analytics.order_base_for_user_type
|
|
as
|
|
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' or id like 'PL%'
|
|
GROUP BY 1;
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.user_type_table ;
|
|
|
|
|
|
CREATE TABLE raena_analytics.user_type_table AS
|
|
SELECT DISTINCT profile_phone reseller_mobile,
|
|
First_transaction_date,
|
|
min_created_date first_install_date
|
|
FROM raena_analytics.order_base_for_user_type 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 raena_analytics.total_registered_user;
|
|
|
|
|
|
CREATE TABLE raena_analytics.total_registered_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,
|
|
bucket,
|
|
transaction_date,
|
|
Count(DISTINCT id) Total_users
|
|
FROM (SELECT distinct id ,replace(mobile,'+','') mobile,
|
|
created_at::date transaction_date
|
|
FROM raena_user_management.user) A
|
|
LEFT JOIN raena_analytics.user_type_table B ON A.mobile = B.reseller_mobile
|
|
left join raena_analytics.cohort_level_reseller on A.id = cohort_level_reseller.reseller_id
|
|
GROUP BY 1,
|
|
2,3;
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.total_uninstalled;
|
|
|
|
|
|
CREATE TABLE raena_analytics.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,
|
|
bucket,
|
|
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 raena_analytics.user_type_table B ON A.profile_phone = B.reseller_mobile
|
|
left join raena_user_management.user C on A.profile_phone = replace(C.mobile,'+','')
|
|
left join raena_analytics.cohort_level_reseller on C.id = cohort_level_reseller.reseller_id
|
|
GROUP BY 1,
|
|
2,3;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.total_installed;
|
|
|
|
|
|
CREATE TABLE raena_analytics.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,
|
|
bucket,
|
|
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 raena_analytics.user_type_table B ON A.profile_phone = B.reseller_mobile
|
|
left join raena_user_management.user C on A.profile_phone = replace(C.mobile,'+','')
|
|
left join raena_analytics.cohort_level_reseller on C.id = cohort_level_reseller.reseller_id
|
|
GROUP BY 1,
|
|
2,3;
|
|
|
|
DROP TABLE raena_analytics.Active_user;
|
|
|
|
|
|
CREATE TABLE raena_analytics.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(distinct profile_phone) total_event ,
|
|
A.profile_phone,
|
|
A.profile_objectid,
|
|
bucket
|
|
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 raena_analytics.order_base_for_user_type 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,'+','')
|
|
left join raena_analytics.cohort_level_reseller on D.id = cohort_level_reseller.reseller_id
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5,
|
|
7,
|
|
8,9;
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.total_view_item;
|
|
|
|
|
|
CREATE TABLE raena_analytics.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,
|
|
bucket,
|
|
count(distinct profile_objectid) total_event,
|
|
profile_phone
|
|
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 raena_analytics.user_type_table B ON A.profile_phone = B.reseller_mobile
|
|
left join raena_user_management.user C on A.profile_phone = replace(C.mobile,'+','')
|
|
left join raena_analytics.cohort_level_reseller on C.id = cohort_level_reseller.reseller_id
|
|
GROUP BY 1,
|
|
2,
|
|
3,5;
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.total_checkout;
|
|
|
|
|
|
CREATE TABLE raena_analytics.total_checkout 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,
|
|
bucket,
|
|
count(distinct profile_objectid) total_event,
|
|
profile_phone
|
|
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.begin_checkout) A
|
|
LEFT JOIN raena_analytics.user_type_table B ON A.profile_phone = B.reseller_mobile
|
|
left join raena_user_management.user C on A.profile_phone = replace(C.mobile,'+','')
|
|
left join raena_analytics.cohort_level_reseller on C.id = cohort_level_reseller.reseller_id
|
|
GROUP BY 1,
|
|
2,
|
|
3,5;
|
|
|
|
|
|
drop table if exists raena_analytics.order_base_user_transaction;
|
|
|
|
create table raena_analytics.order_base_user_transaction
|
|
as
|
|
SELECT reseller_id ,
|
|
replace(reseller_mobile,'+','') profile_phone,
|
|
transaction_date::date transaction_date,
|
|
count(DISTINCT external_id ) total_orders,
|
|
sum(quantity*discounted_price) AS revenue,
|
|
sum((retail_price*quantity)-(seller_margin*quantity)) as Pre_discount_revenue,
|
|
sum(quantity) total_quantity
|
|
FROM raena_analytics.gm_dashboard
|
|
WHERE is_campaign = 'false'
|
|
AND order_placed_by ='App'
|
|
GROUP BY 1,
|
|
2,
|
|
3;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.total_first_transacting_user;
|
|
|
|
|
|
CREATE TABLE raena_analytics.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,
|
|
bucket,
|
|
count(1) total_event ,
|
|
Count(DISTINCT A.reseller_id) Total_users,
|
|
sum(total_orders) total_orders,
|
|
sum(revenue) revenue,
|
|
sum(Pre_discount_revenue)Pre_discount_revenue,
|
|
sum(total_quantity) total_quantity
|
|
FROM raena_analytics.order_base_user_transaction A
|
|
LEFT JOIN raena_analytics.user_type_table B ON A.profile_phone = B.reseller_mobile
|
|
left join raena_analytics.cohort_level_reseller on A.reseller_id = cohort_level_reseller.reseller_id
|
|
GROUP BY 1,
|
|
2,
|
|
3,4;
|
|
|
|
|
|
drop table if exists raena_analytics.base_matrics_table;
|
|
|
|
CREATE TABLE raena_analytics.base_matrics_table AS
|
|
SELECT A.transaction_date,
|
|
A.user_type,
|
|
A.bucket,
|
|
A.install ,
|
|
B.register,
|
|
avg_dau,
|
|
avg_wau,
|
|
avg_mau,
|
|
pdp_unique,
|
|
add_to_cart,
|
|
checkout,
|
|
transacting_user,
|
|
gmv,
|
|
uninstall
|
|
FROM
|
|
(SELECT date_trunc('Month',transaction_date)::date transaction_date ,
|
|
user_type,
|
|
bucket,
|
|
Sum(Total_users)install
|
|
FROM raena_analytics.total_installed
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Month',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
Sum(total_users) register
|
|
FROM raena_analytics.total_registered_user
|
|
GROUP BY 1,
|
|
2,
|
|
3)B ON A.transaction_date = B.transaction_date
|
|
AND A.user_type = B.user_type
|
|
AND A.bucket = B.bucket
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Month',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
sum(total_user) /count(DISTINCT transaction_date) avg_dau
|
|
FROM
|
|
(SELECT transaction_date ,
|
|
user_type,
|
|
bucket ,
|
|
count(DISTINCT profile_phone) total_user
|
|
FROM raena_analytics.active_user
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3) C ON A.transaction_date = C.transaction_date
|
|
AND A.user_type = C.user_type
|
|
AND A.bucket = C.bucket
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Month',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
sum(total_user) /count(DISTINCT transaction_date) avg_wau
|
|
FROM
|
|
(SELECT date_trunc('Week',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket ,
|
|
count(DISTINCT profile_phone) total_user
|
|
FROM raena_analytics.active_user
|
|
GROUP BY 1,
|
|
2,
|
|
3)
|
|
GROUP BY 1,
|
|
2,
|
|
3) D ON A.transaction_date = D.transaction_date
|
|
AND A.user_type = D.user_type
|
|
AND A.bucket = D.bucket
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Month',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket ,
|
|
count(DISTINCT profile_phone) avg_mau
|
|
FROM raena_analytics.active_user
|
|
GROUP BY 1,
|
|
2,
|
|
3) E ON A.transaction_date = E.transaction_date
|
|
AND A.user_type = E.user_type
|
|
AND A.bucket = E.bucket
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Month',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(distinct profile_phone) pdp_unique
|
|
FROM raena_analytics.total_view_item
|
|
GROUP BY 1,
|
|
2,
|
|
3) F ON A.transaction_date = F.transaction_date
|
|
AND A.user_type = F.user_type
|
|
AND A.bucket = F.bucket
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Month',ts)::date transaction_date,
|
|
new_existing_flag user_type,
|
|
bucket,
|
|
count(DISTINCT phone) add_to_cart
|
|
FROM raena_analytics.OM_Events_user_type
|
|
LEFT JOIN raena_user_management.user C ON OM_Events_user_type.phone = replace(C.mobile,'+','')
|
|
LEFT JOIN raena_analytics.cohort_level_reseller ON C.id = cohort_level_reseller.reseller_id
|
|
WHERE events = 'add_to_cart'
|
|
GROUP BY 1,
|
|
2,
|
|
3) G ON A.transaction_date = G.transaction_date
|
|
AND A.user_type = G.user_type
|
|
AND A.bucket = G.bucket
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Month',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(distinct profile_phone) checkout
|
|
FROM raena_analytics.total_checkout
|
|
GROUP BY 1,
|
|
2,
|
|
3) H ON A.transaction_date = H.transaction_date
|
|
AND A.user_type = H.user_type
|
|
AND A.bucket = H.bucket
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Month',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) transacting_user,
|
|
sum(Pre_discount_revenue) gmv
|
|
FROM raena_analytics.total_first_transacting_user
|
|
GROUP BY 1,
|
|
2,
|
|
3) I ON A.transaction_date = I.transaction_date
|
|
AND A.user_type = I.user_type
|
|
AND A.bucket = I.bucket
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Month',transaction_date)::date transaction_date ,
|
|
user_type,
|
|
bucket,
|
|
Sum(Total_users)uninstall
|
|
FROM raena_analytics.total_uninstalled
|
|
GROUP BY 1,
|
|
2,
|
|
3) J ON A.transaction_date = J.transaction_date
|
|
AND A.user_type = j.user_type
|
|
AND A.bucket = j.bucket;
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.post_discount_gm;
|
|
|
|
|
|
CREATE TABLE raena_analytics.post_discount_gm AS
|
|
SELECT transaction_date,
|
|
case WHEN date_trunc('Month',A.transaction_date)::date = date_trunc('Month',B.first_install_date)::date
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',B.First_transaction_date)::date THEN 'New'
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',B.first_install_date)::date>= 30
|
|
AND date_trunc('Month',A.transaction_date)::date = date_trunc('Month',B.First_transaction_date)::date THEN 'Activation'
|
|
WHEN date_trunc('Month',A.transaction_date)::date-date_trunc('Month',B.first_install_date)::date>=30
|
|
AND date_trunc('Month',A.transaction_date)::date - date_trunc('Month',B.First_transaction_date)::date>=30 THEN 'Existing'
|
|
else 'Na'
|
|
END user_type,
|
|
cohort bucket,
|
|
sum(discounted_price*quantity) discounted_price,
|
|
sum(cogs*quantity) cogs,
|
|
count(DISTINCT external_id) AS Number_of_orders
|
|
FROM raena_analytics.OM_GM_DB_Product_category A
|
|
LEFT JOIN raena_analytics.user_type_table B ON replace(A.reseller_mobile,'+','') =B.reseller_mobile
|
|
WHERE is_campaign='false'
|
|
AND order_placed_by='App'
|
|
GROUP BY 1,
|
|
2,3;
|
|
|
|
drop table if exists raena_analytics.Active_user_for_mau_stage;
|
|
|
|
CREATE TABLE raena_analytics.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 raena_analytics.Active_user_for_mau;
|
|
|
|
|
|
CREATE TABLE raena_analytics.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 raena_analytics.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) A
|
|
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 raena_analytics.lm_revenue_gm;
|
|
|
|
|
|
CREATE TABLE raena_analytics.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 raena_analytics.OM_GM_DB_Product_category
|
|
WHERE order_placed_by ='reseller'
|
|
AND is_campaign='false') A
|
|
LEFT JOIN raena_analytics.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 raena_analytics.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 raena_analytics.user_type_table B ON A.reseller_mobile=B.reseller_mobile
|
|
GROUP BY 1,
|
|
2;
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.gm_order_count;
|
|
|
|
|
|
CREATE TABLE raena_analytics.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 raena_analytics.user_type_table B ON A.reseller_mobile=B.reseller_mobile
|
|
GROUP BY 1,
|
|
2;
|
|
*/
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.post_discount_dsf_revenue;
|
|
|
|
|
|
CREATE TABLE raena_analytics.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 ,
|
|
external_id id ,
|
|
discounted_price*quantity payment_amount ,
|
|
reseller_mobile,
|
|
transaction_date
|
|
FROM raena_analytics.gm_dashboard
|
|
WHERE is_campaign='false'
|
|
AND lower(external_id) LIKE '%dsf') A
|
|
LEFT JOIN raena_analytics.user_type_table B ON A.reseller_mobile =B.reseller_mobile
|
|
GROUP BY 1,
|
|
2;
|
|
|
|
----OM_Events_user_type;created in db_funnel report
|
|
|
|
|
|
|
|
select max(transaction_date) uninstalled_td from raena_analytics.total_uninstalled;
|
|
select max(transaction_date) installed_td from raena_analytics.total_installed;
|
|
select max(transaction_date) launched_td from raena_analytics.Active_user;
|
|
select max(transaction_date) view_td from raena_analytics.total_view_item;
|
|
select max(transaction_date) gm_td from raena_analytics.lm_revenue_gm;
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.overall_conversion_trend;
|
|
|
|
|
|
CREATE TABLE raena_analytics.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 ,
|
|
A.bucket,
|
|
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',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type,
|
|
raena_analytics.product_metrics_calendar.bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
LEFT JOIN
|
|
(SELECT date_trunc('day',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
sum(total_orders) total_orders
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) B ON A.transaction_date = B.transaction_date
|
|
AND A.user_type = B.user_type
|
|
AND A.bucket = B.bucket
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Month',A.transaction_date)::date AS transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Month'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
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',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type,
|
|
raena_analytics.product_metrics_calendar.bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Month',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
sum(total_orders) total_orders
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) B ON A.transaction_date = B.transaction_date
|
|
AND A.user_type = B.user_type
|
|
AND A.bucket = B.bucket
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Week',A.transaction_date)::date AS transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Week'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
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',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type,
|
|
raena_analytics.product_metrics_calendar.bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Week',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
sum(total_orders) total_orders
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) B ON A.transaction_date = B.transaction_date
|
|
AND A.user_type = B.user_type
|
|
AND A.bucket = B.bucket
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Quarter',A.transaction_date)::date AS transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Quarter'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
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',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type,
|
|
raena_analytics.product_metrics_calendar.bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Quarter',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
sum(total_orders) total_orders
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) B ON A.transaction_date = B.transaction_date
|
|
AND A.user_type = B.user_type
|
|
AND A.bucket = B.bucket
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
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 ,
|
|
A.bucket,
|
|
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',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type,
|
|
raena_analytics.product_metrics_calendar.bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Year',raena_analytics.product_metrics_calendar.transaction_date)::date transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
sum(total_orders) total_orders
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) B ON A.transaction_date = B.transaction_date
|
|
AND A.user_type = B.user_type
|
|
AND A.bucket = B.bucket
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5;
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.average_dau_trend;
|
|
|
|
|
|
CREATE TABLE raena_analytics.average_dau_trend AS
|
|
SELECT A.transaction_date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Day'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4))/Count(distinct transaction_date) mau
|
|
FROM
|
|
(SELECT date_trunc('day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.active_user
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Week',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Week'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4))/Count(distinct transaction_date) mau
|
|
FROM
|
|
(SELECT date_trunc('Day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.active_user
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Month',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Month'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4))/Count(distinct transaction_date) mau
|
|
FROM
|
|
(SELECT date_trunc('Day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.active_user
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Quarter',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Quarter'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4))/Count(distinct transaction_date) mau
|
|
FROM
|
|
(SELECT date_trunc('Day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.active_user
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Year',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Year'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4))/Count(distinct transaction_date) mau
|
|
FROM
|
|
(SELECT date_trunc('Day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.active_user
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.overall_converting_user_trend;
|
|
|
|
|
|
CREATE TABLE raena_analytics.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 ,
|
|
A.bucket,
|
|
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',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type,
|
|
raena_analytics.product_metrics_calendar.bucket,
|
|
count(DISTINCT profile_objectid) MAU
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
LEFT JOIN
|
|
(SELECT date_trunc('year',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
count(DISTINCT profile_phone) total_users
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) B ON A.transaction_date = B.transaction_date
|
|
AND A.user_type = B.user_type
|
|
and A.bucket = B.bucket
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Quarter',A.transaction_date)::date AS transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Quarter'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
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',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type,
|
|
raena_analytics.product_metrics_calendar.bucket,
|
|
count(DISTINCT profile_objectid) MAU
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Quarter',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
count(DISTINCT profile_phone) total_users
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) B ON A.transaction_date = B.transaction_date
|
|
AND A.user_type = B.user_type
|
|
and A.bucket = B.bucket
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Month',A.transaction_date)::date AS transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Month'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
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',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type,
|
|
raena_analytics.product_metrics_calendar.bucket,
|
|
count(DISTINCT profile_objectid) MAU
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Month',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
count(DISTINCT profile_phone) total_users
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) B ON A.transaction_date = B.transaction_date
|
|
AND A.user_type = B.user_type
|
|
and A.bucket = B.bucket
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Week',A.transaction_date)::date AS transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Week'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
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',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type,
|
|
raena_analytics.product_metrics_calendar.bucket,
|
|
count(DISTINCT profile_objectid) MAU
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Week',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
count(DISTINCT profile_phone) total_users
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) B ON A.transaction_date = B.transaction_date
|
|
AND A.user_type = B.user_type
|
|
and A.bucket = B.bucket
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
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 ,
|
|
A.bucket,
|
|
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',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type,
|
|
raena_analytics.product_metrics_calendar.bucket,
|
|
count(DISTINCT profile_objectid) MAU
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.active_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.active_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.active_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.active_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
LEFT JOIN
|
|
(SELECT date_trunc('Day',raena_analytics.product_metrics_calendar.transaction_date) transaction_date,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
count(DISTINCT profile_phone) total_users
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_first_transacting_user ON raena_analytics.product_metrics_calendar.transaction_date =raena_analytics.total_first_transacting_user.transaction_date
|
|
AND raena_analytics.product_metrics_calendar.user_type =coalesce(raena_analytics.total_first_transacting_user.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_first_transacting_user.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3) B ON A.transaction_date = B.transaction_date
|
|
AND A.user_type = B.user_type
|
|
and A.bucket = B.bucket
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.arpu_trend;
|
|
|
|
|
|
CREATE TABLE raena_analytics.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 ,
|
|
bucket,
|
|
sum(total_orders) total_orders,
|
|
count(distinct profile_phone) transacting_user,
|
|
cast(sum(revenue) AS decimal(22,4)) total_revenue,
|
|
cast(sum(total_quantity) as decimal(22,4)) total_quantity,
|
|
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 raena_analytics.total_first_transacting_user
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Week',transaction_date)::date AS transaction_time,
|
|
date_part('Year',transaction_date) AS transaction_time,
|
|
'Week'frequency,
|
|
user_type ,
|
|
bucket,
|
|
sum(total_orders) total_orders,
|
|
count(distinct profile_phone) transacting_user,
|
|
cast(sum(revenue) AS decimal(22,4)) total_revenue,
|
|
cast(sum(total_quantity) as decimal(22,4)) total_quantity,
|
|
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 raena_analytics.total_first_transacting_user
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Month',transaction_date)::date AS transaction_time,
|
|
date_part('Year',transaction_date) AS transaction_time,
|
|
'Month'frequency,
|
|
user_type ,
|
|
bucket,
|
|
sum(total_orders) total_orders,
|
|
count(distinct profile_phone) transacting_user,
|
|
cast(sum(revenue) AS decimal(22,4)) total_revenue,
|
|
cast(sum(total_quantity) as decimal(22,4)) total_quantity,
|
|
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 raena_analytics.total_first_transacting_user
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Quarter',transaction_date)::date AS transaction_time,
|
|
date_part('Year',transaction_date) AS transaction_time,
|
|
'Quarter'frequency,
|
|
user_type ,
|
|
bucket,
|
|
sum(total_orders) total_orders,
|
|
count(distinct profile_phone) transacting_user,
|
|
cast(sum(revenue) AS decimal(22,4)) total_revenue,
|
|
cast(sum(total_quantity) as decimal(22,4)) total_quantity,
|
|
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 raena_analytics.total_first_transacting_user
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Year',transaction_date)::date AS transaction_time,
|
|
date_part('Year',transaction_date) AS transaction_time,
|
|
'Year'frequency,
|
|
user_type ,
|
|
bucket,
|
|
sum(total_orders) total_orders,
|
|
count(distinct profile_phone) transacting_user,
|
|
cast(sum(revenue) AS decimal(22,4)) total_revenue,
|
|
cast(sum(total_quantity) as decimal(22,4)) total_quantity,
|
|
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 raena_analytics.total_first_transacting_user
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5 ;
|
|
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.pdp_trend;
|
|
|
|
|
|
CREATE TABLE raena_analytics.pdp_trend AS
|
|
SELECT A.transaction_date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Day'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.total_view_item
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Week',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Week'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.total_view_item
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Month',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Month'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.total_view_item
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Quarter',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Quarter'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.total_view_item
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Year',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Year'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.total_view_item
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5;
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.add_to_cart_trend;
|
|
|
|
|
|
CREATE TABLE raena_analytics.add_to_cart_trend AS
|
|
SELECT A.transaction_date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Day'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',ts)::date transaction_date,
|
|
new_existing_flag user_type,
|
|
bucket,
|
|
count(DISTINCT phone) MAU
|
|
from raena_analytics.OM_Events_user_type
|
|
left join raena_user_management.user C on OM_Events_user_type.phone = replace(C.mobile,'+','')
|
|
left join raena_analytics.cohort_level_reseller on C.id = cohort_level_reseller.reseller_id
|
|
where events = 'add_to_cart'
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Week',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Week'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',ts)::date transaction_date,
|
|
new_existing_flag user_type,
|
|
bucket,
|
|
count(DISTINCT phone) MAU
|
|
from raena_analytics.OM_Events_user_type
|
|
left join raena_user_management.user C on OM_Events_user_type.phone = replace(C.mobile,'+','')
|
|
left join raena_analytics.cohort_level_reseller on C.id = cohort_level_reseller.reseller_id
|
|
where events = 'add_to_cart'
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Month',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Month'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',ts)::date transaction_date,
|
|
new_existing_flag user_type,
|
|
bucket,
|
|
count(DISTINCT phone) MAU
|
|
from raena_analytics.OM_Events_user_type
|
|
left join raena_user_management.user C on OM_Events_user_type.phone = replace(C.mobile,'+','')
|
|
left join raena_analytics.cohort_level_reseller on C.id = cohort_level_reseller.reseller_id
|
|
where events = 'add_to_cart'
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Quarter',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Quarter'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',ts)::date transaction_date,
|
|
new_existing_flag user_type,
|
|
bucket,
|
|
count(DISTINCT phone) MAU
|
|
from raena_analytics.OM_Events_user_type
|
|
left join raena_user_management.user C on OM_Events_user_type.phone = replace(C.mobile,'+','')
|
|
left join raena_analytics.cohort_level_reseller on C.id = cohort_level_reseller.reseller_id
|
|
where events = 'add_to_cart'
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Year',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Year'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',ts)::date transaction_date,
|
|
new_existing_flag user_type,
|
|
bucket,
|
|
count(DISTINCT phone) MAU
|
|
from raena_analytics.OM_Events_user_type
|
|
left join raena_user_management.user C on OM_Events_user_type.phone = replace(C.mobile,'+','')
|
|
left join raena_analytics.cohort_level_reseller on C.id = cohort_level_reseller.reseller_id
|
|
where events = 'add_to_cart'
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5;
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.checkout_trend;
|
|
|
|
|
|
CREATE TABLE raena_analytics.checkout_trend AS
|
|
SELECT A.transaction_date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Day'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.total_checkout
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Week',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Week'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.total_checkout
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Month',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Month'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.total_checkout
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Quarter',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Quarter'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.total_checkout
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5
|
|
UNION ALL
|
|
SELECT date_trunc('Year',transaction_date)::date transaction_time,
|
|
date_part('Year',A.transaction_date) AS transaction_year,
|
|
'Year'frequency,
|
|
A.user_type ,
|
|
A.bucket,
|
|
cast(sum(CASE WHEN MAU<>0 THEN MAU END) AS decimal(22,4)) mau
|
|
FROM
|
|
(SELECT date_trunc('day',transaction_date)::date transaction_date,
|
|
user_type,
|
|
bucket,
|
|
count(DISTINCT profile_phone) MAU
|
|
FROM raena_analytics.total_checkout
|
|
GROUP BY 1,
|
|
2,
|
|
3) A
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5;
|
|
DROP TABLE IF EXISTS raena_analytics.install_uninstall_ratio_trend;
|
|
|
|
|
|
CREATE TABLE raena_analytics.install_uninstall_ratio_trend AS
|
|
SELECT date_trunc('Year',raena_analytics.product_metrics_calendar.transaction_date)::date AS transaction_time,
|
|
date_part('Year',raena_analytics.product_metrics_calendar.transaction_date) AS transaction_year,
|
|
'Year'frequency,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
cast(Sum(coalesce(raena_analytics.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
|
|
cast(sum(CASE WHEN raena_analytics.total_installed.total_event>0 THEN coalesce(raena_analytics.total_installed.total_event,0) END) AS decimal(22,4)) total_install
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_uninstalled ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_uninstalled.Transaction_date
|
|
AND raena_analytics.product_metrics_calendar.User_type = coalesce(raena_analytics.total_uninstalled.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket = coalesce(raena_analytics.total_uninstalled.bucket,'Na')
|
|
LEFT JOIN raena_analytics.total_installed ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_installed.Transaction_date
|
|
AND raena_analytics.product_metrics_calendar.User_type =coalesce(raena_analytics.total_installed.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_installed.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Quarter',raena_analytics.product_metrics_calendar.transaction_date)::date AS transaction_time,
|
|
date_part('Year',raena_analytics.product_metrics_calendar.transaction_date) AS transaction_time,
|
|
'Quarter'frequency,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
cast(Sum(coalesce(raena_analytics.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
|
|
cast(sum(CASE WHEN raena_analytics.total_installed.total_event>0 THEN coalesce(raena_analytics.total_installed.total_event,0) END) AS decimal(22,4)) total_install
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_uninstalled ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_uninstalled.Transaction_date
|
|
AND raena_analytics.product_metrics_calendar.User_type = coalesce(raena_analytics.total_uninstalled.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket = coalesce(raena_analytics.total_uninstalled.bucket,'Na')
|
|
LEFT JOIN raena_analytics.total_installed ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_installed.Transaction_date
|
|
AND raena_analytics.product_metrics_calendar.User_type =coalesce(raena_analytics.total_installed.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_installed.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Month',raena_analytics.product_metrics_calendar.transaction_date)::date AS transaction_time,
|
|
date_part('Year',raena_analytics.product_metrics_calendar.transaction_date) AS transaction_time,
|
|
'Month'frequency,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
cast(Sum(coalesce(raena_analytics.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
|
|
cast(sum(CASE WHEN raena_analytics.total_installed.total_event>0 THEN coalesce(raena_analytics.total_installed.total_event,0) END) AS decimal(22,4)) total_install
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_uninstalled ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_uninstalled.Transaction_date
|
|
AND raena_analytics.product_metrics_calendar.User_type = coalesce(raena_analytics.total_uninstalled.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket = coalesce(raena_analytics.total_uninstalled.bucket,'Na')
|
|
LEFT JOIN raena_analytics.total_installed ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_installed.Transaction_date
|
|
AND raena_analytics.product_metrics_calendar.User_type =coalesce(raena_analytics.total_installed.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_installed.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Week',raena_analytics.product_metrics_calendar.transaction_date)::date AS transaction_time,
|
|
date_part('Year',raena_analytics.product_metrics_calendar.transaction_date) AS transaction_time,
|
|
'Week'frequency,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
cast(Sum(coalesce(raena_analytics.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
|
|
cast(sum(CASE WHEN raena_analytics.total_installed.total_event>0 THEN coalesce(raena_analytics.total_installed.total_event,0) END) AS decimal(22,4)) total_install
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_uninstalled ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_uninstalled.Transaction_date
|
|
AND raena_analytics.product_metrics_calendar.User_type = coalesce(raena_analytics.total_uninstalled.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket = coalesce(raena_analytics.total_uninstalled.bucket,'Na')
|
|
LEFT JOIN raena_analytics.total_installed ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_installed.Transaction_date
|
|
AND raena_analytics.product_metrics_calendar.User_type =coalesce(raena_analytics.total_installed.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_installed.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Day',raena_analytics.product_metrics_calendar.transaction_date)::date AS transaction_time,
|
|
date_part('Year',raena_analytics.product_metrics_calendar.transaction_date) AS transaction_time,
|
|
'Day'frequency,
|
|
raena_analytics.product_metrics_calendar.user_type ,
|
|
raena_analytics.product_metrics_calendar.bucket ,
|
|
cast(Sum(coalesce(raena_analytics.total_uninstalled.total_event,0)) AS decimal(22,4)) total_uninstall,
|
|
cast(sum(CASE WHEN raena_analytics.total_installed.total_event>0 THEN coalesce(raena_analytics.total_installed.total_event,0) END) AS decimal(22,4)) total_install
|
|
FROM raena_analytics.product_metrics_calendar
|
|
LEFT JOIN raena_analytics.total_uninstalled ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_uninstalled.Transaction_date
|
|
AND raena_analytics.product_metrics_calendar.User_type = coalesce(raena_analytics.total_uninstalled.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket = coalesce(raena_analytics.total_uninstalled.bucket,'Na')
|
|
LEFT JOIN raena_analytics.total_installed ON raena_analytics.product_metrics_calendar.Transaction_date = raena_analytics.total_installed.Transaction_date
|
|
AND raena_analytics.product_metrics_calendar.User_type =coalesce(raena_analytics.total_installed.user_type,'Na')
|
|
AND raena_analytics.product_metrics_calendar.bucket =coalesce(raena_analytics.total_installed.bucket,'Na')
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5;
|
|
|
|
|
|
DROP TABLE IF EXISTS raena_analytics.mau_trend;
|
|
|
|
|
|
CREATE TABLE raena_analytics.mau_trend AS
|
|
SELECT date_trunc('Year',transaction_date)::date AS transaction_date,
|
|
date_part('Year',transaction_date) AS transaction_time,
|
|
'Year'frequency,
|
|
user_type ,
|
|
bucket,
|
|
cast(count(DISTINCT profile_objectid) AS decimal(22,4)) mau
|
|
FROM raena_analytics.active_user
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Quarter',transaction_date)::date AS transaction_time,
|
|
date_part('Year',transaction_date) AS transaction_time,
|
|
'Quarter'frequency,
|
|
user_type ,
|
|
bucket,
|
|
cast(count(DISTINCT profile_objectid) AS decimal(22,4)) mau
|
|
FROM raena_analytics.active_user
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Month',transaction_date)::date AS transaction_time,
|
|
date_part('Year',transaction_date) AS transaction_time,
|
|
'Month'frequency,
|
|
user_type ,
|
|
bucket,
|
|
cast(count(DISTINCT profile_objectid) AS decimal(22,4)) mau
|
|
FROM raena_analytics.active_user
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Week',transaction_date)::date AS transaction_time,
|
|
date_part('Year',transaction_date) AS transaction_time,
|
|
'Week'frequency,
|
|
user_type ,
|
|
bucket,
|
|
cast(count(DISTINCT profile_objectid) AS decimal(22,4)) mau
|
|
FROM raena_analytics.active_user
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5
|
|
UNION ALL
|
|
SELECT date_trunc('Day',transaction_date)::date AS transaction_time,
|
|
date_part('Year',transaction_date) AS transaction_time,
|
|
'Day'frequency,
|
|
user_type ,
|
|
bucket,
|
|
cast(count(DISTINCT profile_objectid) AS decimal(22,4)) mau
|
|
FROM raena_analytics.active_user
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,5;
|
|
|
|
|
|
" > /home/ec2-user/cronjob/redshift/sql_code/product_matrics_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/product_matrics_etl.sql
|
|
|
|
|