296 lines
9.3 KiB
Bash
296 lines
9.3 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")
|
|
echo 'reportDate'=$reportDate
|
|
|
|
echo "
|
|
DROP TABLE if exists public.campaign_base_data_V3;
|
|
|
|
|
|
CREATE TABLE public.campaign_base_data_V3 AS
|
|
SELECT DISTINCT channel,
|
|
media_source ,
|
|
campaign_type,
|
|
campaign campaign_name,
|
|
fb_adset_name,
|
|
af_siteid,
|
|
ad_id,
|
|
A.user_id customer_user_id ,
|
|
device_brand,
|
|
device_model,
|
|
platform,
|
|
cast(install_time AS date) install_date,
|
|
sum(impressions) impressions,
|
|
sum(clicks) total_clicks,
|
|
sum(install) total_install,
|
|
os_version ,
|
|
app_version,
|
|
A.City_id,
|
|
B.profile_phone phone,
|
|
install_time
|
|
FROM (select distinct * from raena_appsflyer.dw_marketing_install_stats) A
|
|
LEFT JOIN
|
|
(SELECT DISTINCT profile_objectid,
|
|
profile_phone
|
|
FROM clevertap.app_installed) B ON A.user_id = B.profile_objectid
|
|
GROUP BY 1,
|
|
2,
|
|
3,
|
|
4,
|
|
5,
|
|
6,
|
|
7,
|
|
8,
|
|
9,
|
|
10,
|
|
11,
|
|
12,
|
|
16,
|
|
17,
|
|
18,
|
|
19,
|
|
20;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.campaign_base_data_V4;
|
|
|
|
|
|
CREATE TABLE public.campaign_base_data_V4 AS
|
|
SELECT DISTINCT A.*,
|
|
B.province,
|
|
ttl_order,
|
|
ttl_amount ,
|
|
CASE
|
|
WHEN reseller_tier_name_gold>0 THEN 'GOLD'
|
|
WHEN reseller_tier_name_silver>0 THEN 'SILVER'
|
|
WHEN reseller_tier_name_bronze>0 THEN 'BRONZE'
|
|
END highest_Tier ,
|
|
reg_date,
|
|
reseller_email,
|
|
reseller_mobile,
|
|
conversion_date
|
|
FROM public.campaign_base_data_V3 A
|
|
LEFT JOIN
|
|
(SELECT A.id reseller_id ,
|
|
count(DISTINCT B.id) ttl_order,
|
|
sum(B.total_amount) AS ttl_amount ,
|
|
A.city,
|
|
A.province,
|
|
A.created_at AS reg_date,
|
|
sum(CASE WHEN json_extract_path_text(B.reseller_info,'tierName',TRUE) ='GOLD' THEN 1 END) reseller_tier_name_gold,
|
|
sum(CASE WHEN json_extract_path_text(B.reseller_info,'tierName',TRUE) ='SILVER' THEN 1 END) reseller_tier_name_silver,
|
|
sum(CASE WHEN json_extract_path_text(B.reseller_info,'tierName',TRUE) ='BRONZE' THEN 1 END) reseller_tier_name_bronze,
|
|
min(B.created_at) conversion_date,
|
|
A.email reseller_email,
|
|
A.mobile AS reseller_mobile
|
|
FROM raena_user_management.user A
|
|
LEFT JOIN raena_order_management.order B ON A.id = B.reseller_id and B.payment_status = 'Paid' and is_campaign= 'false'
|
|
GROUP BY 1,
|
|
4,
|
|
5,
|
|
6,
|
|
11,
|
|
12) B ON phone = replace(B.reseller_mobile,'+','');
|
|
|
|
|
|
DROP TABLE IF EXISTS public.reseller_post_gm;
|
|
|
|
|
|
CREATE TABLE public.reseller_post_gm AS
|
|
SELECT reseller_name ,
|
|
reseller_email ,
|
|
reseller_mobile,
|
|
sum(Before_Discount_GM) AS Pre_Disc_GM,
|
|
sum(After_discount_GM) AS Post_Disc_GM,
|
|
sum(GM_GAP) AS GM_GAP,
|
|
sum(Total_Wholesale_price) AS Pre_Disc_Revenue,
|
|
sum(a.Total_Payment_Price) AS Post_Disc_Revenue,
|
|
sum(Blended_gm_target)/count(1) AS Blended_gm_target,
|
|
cast(sum(cast((a.Total_Payment_Price) AS float))/sum(cast((b.Total_Payment_Price)AS float)) AS decimal(10,4)) AS Revenue,
|
|
sum(Number_of_resellers) AS No_of_resellers,
|
|
sum(Number_of_orders) AS No_of_orders
|
|
FROM
|
|
(SELECT reseller_name ,
|
|
reseller_email ,
|
|
reseller_mobile,
|
|
1 AS id,
|
|
cast((sum(((retail_price*quantity)-(seller_margin*quantity))-(cogs*quantity))
|
|
/sum(case when retail_price<>0 then (retail_price*quantity)-(seller_margin*quantity) end)) as decimal(10,4)) as Before_Discount_GM,
|
|
cast((sum((quantity*discounted_price)-(cogs*quantity))
|
|
/sum(case when discounted_price<>0 then quantity*discounted_price end)) as decimal(10,4)) as After_discount_GM,
|
|
cast((sum(((retail_price*quantity)-(seller_margin*quantity))-(cogs*quantity))
|
|
/sum(case when retail_price<>0 then (retail_price*quantity)-(seller_margin*quantity) end)) as decimal(10,4))-
|
|
cast((sum((quantity*discounted_price)-(cogs*quantity))
|
|
/sum(case when discounted_price<>0 then quantity*discounted_price end)) as decimal(10,4))
|
|
as GM_GAP, Sum(gm_target)/count(OM_GM_DB_Product_category.sku) Blended_gm_target,
|
|
sum((cast(retail_price AS int)*quantity)-(cast(seller_margin AS int)*quantity)) AS Total_Wholesale_price,
|
|
sum(quantity*cast(discounted_price AS int)) AS Total_Payment_Price,
|
|
count(DISTINCT reseller_id) AS Number_of_resellers,
|
|
count(DISTINCT external_id) AS Number_of_orders
|
|
FROM OM_GM_DB_Product_category
|
|
GROUP BY reseller_name ,
|
|
reseller_email ,
|
|
reseller_mobile) a
|
|
LEFT JOIN
|
|
(SELECT 1 AS id,
|
|
sum(quantity*discounted_price) AS Total_Payment_Price
|
|
FROM OM_GM_DB_Product_category) b ON a.id=b.id
|
|
WHERE replace(reseller_mobile,'+','') IN
|
|
(SELECT DISTINCT phone
|
|
FROM public.campaign_base_data_V4
|
|
WHERE phone <> '')
|
|
GROUP BY 1,
|
|
2,
|
|
3
|
|
ORDER BY 10 DESC, 2 ASC;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.top_brand_reseller;
|
|
|
|
|
|
CREATE TABLE public.top_brand_reseller AS
|
|
SELECT DISTINCT reseller_mobile ,
|
|
brand_name ,
|
|
sku_name ,
|
|
sku
|
|
FROM
|
|
(SELECT reseller_mobile ,
|
|
sku_name,
|
|
brand_name,
|
|
OM_GM_DB_Product_category.sku,
|
|
sum(quantity*cast(discounted_price AS int)) AS Total_Payment_Price ,
|
|
row_number() over(partition BY reseller_mobile
|
|
ORDER BY sum(quantity*cast(discounted_price AS int)) DESC) rnk
|
|
FROM OM_GM_DB_Product_category
|
|
GROUP BY sku_name,
|
|
OM_GM_DB_Product_category.sku,
|
|
brand_name,
|
|
reseller_mobile) AA
|
|
WHERE rnk = 1
|
|
AND replace(reseller_mobile,'+','') IN
|
|
(SELECT DISTINCT phone
|
|
FROM public.campaign_base_data_V4
|
|
WHERE phone <> '');
|
|
|
|
|
|
DROP TABLE IF EXISTS public.launched_total;
|
|
|
|
|
|
CREATE TABLE public.launched_total AS
|
|
SELECT profile_phone ,
|
|
count(1) app_launched
|
|
FROM clevertap.app_launched al
|
|
WHERE profile_phone IN
|
|
(SELECT DISTINCT phone
|
|
FROM public.campaign_base_data_V4
|
|
WHERE phone <> '')
|
|
GROUP BY profile_phone;
|
|
|
|
|
|
DROP TABLE IF EXISTS public.campaign_base_data_v5;
|
|
|
|
|
|
CREATE TABLE public.campaign_base_data_v5 AS
|
|
SELECT A.*,
|
|
B.Post_Disc_GM*100 Post_discount_gm,
|
|
B.Post_Disc_Revenue,
|
|
C.brand_name,
|
|
C.sku_name,
|
|
C.sku,
|
|
D.app_launched
|
|
FROM public.campaign_base_data_V4 A
|
|
LEFT JOIN public.reseller_post_gm B ON A.reseller_mobile = B.reseller_mobile
|
|
LEFT JOIN public.top_brand_reseller C ON A.reseller_mobile= C.reseller_mobile
|
|
LEFT JOIN public.launched_total D ON A.phone= D.profile_phone;
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS public.campaign_base_data_final_v2;
|
|
|
|
|
|
CREATE TABLE public.campaign_base_data_final_v2 AS
|
|
SELECT channel,
|
|
media_source,
|
|
campaign_type,
|
|
campaign_name,
|
|
fb_adset_name,
|
|
af_siteid,
|
|
ad_id ,
|
|
customer_user_id ,
|
|
device_brand,
|
|
device_model,
|
|
platform ,
|
|
install_date,
|
|
install_time ,
|
|
impressions,
|
|
total_clicks,
|
|
total_install,
|
|
os_version ,
|
|
app_version ,
|
|
city_id ,
|
|
phone ,
|
|
province ,
|
|
ttl_order ,
|
|
ttl_amount total_amount,
|
|
highest_tier,
|
|
reg_date,
|
|
reseller_email,
|
|
conversion_date,
|
|
Post_discount_gm,
|
|
BRAND_NAME ,
|
|
SKU_NAME ,
|
|
SKU ,
|
|
app_launched TOTAL_app_launched, B.Current_tier
|
|
FROM public.campaign_base_data_v5 A
|
|
LEFT JOIN
|
|
(SELECT mobile ,
|
|
reseller_tier_name Current_tier ,
|
|
created_at
|
|
FROM
|
|
(SELECT created_at,
|
|
json_extract_path_text(A.reseller_info,'mobile',TRUE) mobile,
|
|
json_extract_path_text(A.reseller_info,'tierName',TRUE) reseller_tier_name,
|
|
rank() over(partition BY json_extract_path_text(A.reseller_info,'mobile',TRUE)
|
|
ORDER BY created_at DESC) rnk
|
|
FROM raena_order_management.
|
|
ORDER A
|
|
ORDER BY created_at DESC) D
|
|
WHERE rnk = 1) B ON A.reseller_mobile= B.mobile;
|
|
|
|
|
|
|
|
DROP TABLE IF EXISTS public.campaign_base_data_final_unistall;
|
|
|
|
|
|
CREATE TABLE public.campaign_base_data_final_unistall AS
|
|
SELECT distinct city_id ,
|
|
cast(install_time AS date) install_date ,
|
|
install_time ,
|
|
cast(uninstall_time AS date) uninstall_date ,
|
|
uninstall_time ,
|
|
media_source,
|
|
channel,
|
|
campaign,
|
|
fb_adset_name,
|
|
af_siteid,
|
|
device_brand,
|
|
device_model,
|
|
platform,
|
|
ad_id,
|
|
install,
|
|
uninstall
|
|
FROM raena_appsflyer.dw_marketing_uninstall_stats;
|
|
|
|
" > /home/ec2-user/cronjob/appsflyer/etl_appsflyer.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/appsflyer/etl_appsflyer.sql > etl_appsflyer.log
|
|
|