296 lines
11 KiB
MySQL
296 lines
11 KiB
MySQL
|
|
||
|
|
DROP TABLE raena_analytics.campaign_inappEvent_base_data_base;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.campaign_inappEvent_base_data_base AS
|
||
|
|
SELECT channel,
|
||
|
|
media_source ,
|
||
|
|
campaign campaign_name,
|
||
|
|
adset fb_adset_name,
|
||
|
|
site_id af_siteid,
|
||
|
|
ad,
|
||
|
|
customer_user_id ,
|
||
|
|
split_part(device_model,'::',1) device_brand,
|
||
|
|
split_part(device_model,'::',2) device_model,
|
||
|
|
platform,
|
||
|
|
--install_time::date install_date,
|
||
|
|
count(CASE WHEN lower(attributed_touch_type)='click' THEN 1 END) total_clicks,
|
||
|
|
os_version ,
|
||
|
|
app_version,
|
||
|
|
A.City,
|
||
|
|
original_url,
|
||
|
|
--coalesce(reseller_mobile,profile_phone) AS reseller_mobile,
|
||
|
|
advertising_id,
|
||
|
|
event_time::date event_time,
|
||
|
|
attributed_touch_time::date attributed_touch_time,
|
||
|
|
count( case when lower(attributed_touch_type)='impression' then 1 end )total_impression
|
||
|
|
FROM raena_appsflyer.dw_marketing_inappevent_stats A
|
||
|
|
GROUP BY 1,
|
||
|
|
2,
|
||
|
|
3,
|
||
|
|
4,
|
||
|
|
5,
|
||
|
|
6,
|
||
|
|
7,
|
||
|
|
8,
|
||
|
|
9,
|
||
|
|
10,
|
||
|
|
12,
|
||
|
|
13,14,
|
||
|
|
15,
|
||
|
|
16,17,18;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE raena_analytics.campaign_inappEvent_base_data;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.campaign_inappEvent_base_data AS
|
||
|
|
SELECT A.* ,
|
||
|
|
D.min_created_date install_date,
|
||
|
|
coalesce(reseller_mobile,C.profile_phone) AS reseller_mobile
|
||
|
|
FROM raena_analytics.campaign_inappEvent_base_data_base A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT B.id ,
|
||
|
|
replace(B.mobile,'+','') AS reseller_mobile
|
||
|
|
FROM raena_user_management.user B) B ON customer_user_id = cast(B.id AS varchar)
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT DISTINCT profile_objectid,
|
||
|
|
profile_phone
|
||
|
|
FROM clevertap.app_installed) C ON A.customer_user_id = C.profile_objectid
|
||
|
|
left 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) D ON B.reseller_mobile=D.profile_phone;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.gm_dashboard_appflyer_base_v1;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.gm_dashboard_appflyer_base_v1 AS
|
||
|
|
SELECT count(DISTINCT external_id) ttl_orders,
|
||
|
|
sum(discounted_price*quantity) ttl_amount,
|
||
|
|
sum(retail_price*quantity)total_retail_price,
|
||
|
|
tier_name,
|
||
|
|
cast(transaction_date AS date)transaction_date,
|
||
|
|
reseller_id
|
||
|
|
FROM raena_analytics.gm_dashboard
|
||
|
|
WHERE is_campaign= 'false'
|
||
|
|
GROUP BY
|
||
|
|
4,
|
||
|
|
5,6;
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.user_base_appflyer_v1;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.user_base_appflyer_v1 AS
|
||
|
|
SELECT id,
|
||
|
|
city,
|
||
|
|
province,
|
||
|
|
created_at,
|
||
|
|
email,
|
||
|
|
mobile
|
||
|
|
FROM raena_user_management.user;
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.campaign_inappEvent_base_data_v1;
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.campaign_inappEvent_base_data_v1 AS
|
||
|
|
SELECT DISTINCT A.*,
|
||
|
|
B.province,
|
||
|
|
ttl_order,
|
||
|
|
ttl_amount ,
|
||
|
|
total_retail_price,
|
||
|
|
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,
|
||
|
|
conversion_date,
|
||
|
|
last_transaction
|
||
|
|
FROM raena_analytics.campaign_inappEvent_base_data A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT A.id reseller_id ,
|
||
|
|
sum(ttl_orders) ttl_order,
|
||
|
|
sum(ttl_amount) ttl_amount,
|
||
|
|
sum(total_retail_price) total_retail_price,
|
||
|
|
A.city,
|
||
|
|
A.province,
|
||
|
|
A.created_at AS reg_date,
|
||
|
|
sum(CASE WHEN B.tier_name='GOLD' THEN 1 END) reseller_tier_name_gold,
|
||
|
|
sum(CASE WHEN B.tier_name='SILVER' THEN 1 END) reseller_tier_name_silver,
|
||
|
|
sum(CASE WHEN B.tier_name='BRONZE' THEN 1 END) reseller_tier_name_bronze,
|
||
|
|
min(B.transaction_date) conversion_date,
|
||
|
|
max(B.transaction_date) last_transaction,
|
||
|
|
A.email reseller_email,
|
||
|
|
replace(mobile,'+','') AS reseller_mobile
|
||
|
|
FROM raena_analytics.user_base_appflyer_v1 A
|
||
|
|
LEFT JOIN raena_analytics.gm_dashboard_appflyer_base_v1 B ON cast(A.id AS varchar) = B.reseller_id
|
||
|
|
GROUP BY 1, 5,
|
||
|
|
6,
|
||
|
|
7,
|
||
|
|
13,14) B ON A.reseller_mobile = B.reseller_mobile;
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.campaign_inappEvent_base_data_v2;
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.campaign_inappEvent_base_data_v2 AS
|
||
|
|
SELECT reseller_mobile,
|
||
|
|
sum(After_discount_GM) AS Post_Disc_GM,
|
||
|
|
sum(a.Total_Payment_Price) AS Post_Disc_Revenue,
|
||
|
|
sum(total_retail_amount)total_retail_amount
|
||
|
|
FROM
|
||
|
|
(SELECT reseller_mobile,
|
||
|
|
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,
|
||
|
|
sum(quantity*cast(discounted_price AS int)) AS Total_Payment_Price,
|
||
|
|
sum(quantity*retail_price) total_retail_amount
|
||
|
|
FROM raena_analytics.OM_GM_DB_Product_category
|
||
|
|
GROUP BY reseller_mobile) a
|
||
|
|
INNER JOIN
|
||
|
|
(SELECT DISTINCT reseller_mobile phone
|
||
|
|
FROM raena_analytics.campaign_inappEvent_base_data
|
||
|
|
WHERE reseller_mobile <> '') B ON replace(reseller_mobile,'+','')=phone
|
||
|
|
GROUP BY 1;
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.campaign_inappEvent_base_data_v3;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.campaign_inappEvent_base_data_v3 AS
|
||
|
|
SELECT DISTINCT replace(reseller_mobile,'+','') reseller_mobile ,
|
||
|
|
brand_name ,
|
||
|
|
sku_name ,
|
||
|
|
sku
|
||
|
|
FROM
|
||
|
|
(SELECT *
|
||
|
|
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 raena_analytics.OM_GM_DB_Product_category
|
||
|
|
GROUP BY sku_name,
|
||
|
|
OM_GM_DB_Product_category.sku,
|
||
|
|
brand_name,
|
||
|
|
reseller_mobile) AA
|
||
|
|
WHERE rnk = 1) B
|
||
|
|
INNER JOIN
|
||
|
|
(SELECT DISTINCT reseller_mobile phone
|
||
|
|
FROM raena_analytics.campaign_inappEvent_base_data
|
||
|
|
WHERE reseller_mobile <> '') C ON replace(reseller_mobile,'+','') = phone;
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.launched_total_in_app_campaign;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.launched_total_in_app_campaign AS
|
||
|
|
SELECT replace(profile_phone,'+','') profile_phone ,
|
||
|
|
count(1) app_launched
|
||
|
|
FROM clevertap.app_launched al
|
||
|
|
INNER JOIN
|
||
|
|
(SELECT DISTINCT reseller_mobile phone
|
||
|
|
FROM raena_analytics.campaign_inappEvent_base_data
|
||
|
|
WHERE reseller_mobile <> '') C ON profile_phone = phone
|
||
|
|
GROUP BY profile_phone;
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.campaign_inappEvent_base_data_v4 ;
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.campaign_inappEvent_base_data_v4 AS
|
||
|
|
SELECT A.*,
|
||
|
|
B.Post_Disc_GM*100 Post_discount_gm,
|
||
|
|
B.Post_Disc_Revenue,
|
||
|
|
B.total_retail_amount,
|
||
|
|
C.brand_name,
|
||
|
|
C.sku_name,
|
||
|
|
C.sku,
|
||
|
|
D.app_launched
|
||
|
|
FROM raena_analytics.campaign_inappEvent_base_data_v1 A
|
||
|
|
LEFT JOIN raena_analytics.campaign_inappEvent_base_data_v2 B ON replace(A.reseller_mobile,'+','') = replace(B.reseller_mobile,'+','')
|
||
|
|
LEFT JOIN raena_analytics.campaign_inappEvent_base_data_v3 C ON replace(A.reseller_mobile,'+','')= replace(C.reseller_mobile,'+','')
|
||
|
|
LEFT JOIN raena_analytics.launched_total_in_app_campaign D ON replace(A.reseller_mobile,'+','')= replace(D.profile_phone,'+','');
|
||
|
|
|
||
|
|
DROP TABLE IF EXISTS raena_analytics.campaign_inappEvent_final;
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.campaign_inappEvent_final AS
|
||
|
|
SELECT channel,
|
||
|
|
media_source,
|
||
|
|
campaign_name,
|
||
|
|
fb_adset_name,
|
||
|
|
af_siteid,
|
||
|
|
ad,
|
||
|
|
advertising_id,
|
||
|
|
customer_user_id ,
|
||
|
|
device_brand,
|
||
|
|
device_model,
|
||
|
|
platform ,
|
||
|
|
install_date,
|
||
|
|
total_clicks,
|
||
|
|
total_impression,
|
||
|
|
os_version ,
|
||
|
|
app_version ,
|
||
|
|
city,
|
||
|
|
A.reseller_mobile ,
|
||
|
|
province ,
|
||
|
|
ttl_order ,
|
||
|
|
ttl_amount total_amount,
|
||
|
|
total_retail_amount,
|
||
|
|
highest_tier,
|
||
|
|
reg_date,
|
||
|
|
reseller_email,
|
||
|
|
conversion_date,
|
||
|
|
Post_discount_gm,
|
||
|
|
post_disc_revenue,
|
||
|
|
BRAND_NAME ,
|
||
|
|
SKU_NAME ,
|
||
|
|
SKU ,
|
||
|
|
app_launched TOTAL_app_launched,
|
||
|
|
B.Current_tier,
|
||
|
|
original_url,
|
||
|
|
attributed_touch_time,
|
||
|
|
event_time ,
|
||
|
|
bucket,
|
||
|
|
CASE WHEN date_trunc('Month',A.event_time)::date = date_trunc('Month',install_date)::date
|
||
|
|
AND date_trunc('Month',A.event_time)::date = date_trunc('Month',conversion_date)::date THEN 'New'
|
||
|
|
WHEN date_trunc('Month',A.event_time)::date-date_trunc('Month',install_date)::date>= 30
|
||
|
|
AND date_trunc('Month',A.event_time)::date = date_trunc('Month',conversion_date)::date THEN 'Activation'
|
||
|
|
WHEN date_trunc('Month',A.event_time)::date-date_trunc('Month',install_date)::date>=30
|
||
|
|
AND date_trunc('Month',A.event_time)::date - date_trunc('Month',conversion_date)::date>=30 THEN 'Existing'
|
||
|
|
WHEN (date_part('Month',install_date) =2 or date_part('Month',conversion_date)=2 ) and
|
||
|
|
date_trunc('Month',A.event_time)::date-date_trunc('Month',install_date)::date>= 28
|
||
|
|
AND date_trunc('Month',A.event_time)::date = date_trunc('Month',conversion_date)::date THEN 'Activation'
|
||
|
|
WHEN (date_part('Month',install_date) =2 or date_part('Month',conversion_date)=2 ) and
|
||
|
|
date_trunc('Month',A.event_time)::date-date_trunc('Month',install_date)::date>=28
|
||
|
|
AND date_trunc('Month',A.event_time)::date - date_trunc('Month',conversion_date)::date>=28 THEN 'Existing'
|
||
|
|
ELSE 'Na'
|
||
|
|
END AS New_existing_flag
|
||
|
|
FROM raena_analytics.campaign_inappEvent_base_data_v4 A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT reseller_mobile mobile ,
|
||
|
|
tier_name Current_tier ,
|
||
|
|
transaction_date created_at
|
||
|
|
FROM
|
||
|
|
(SELECT transaction_date,
|
||
|
|
A.reseller_mobile,
|
||
|
|
tier_name,
|
||
|
|
rank() over(partition BY reseller_mobile
|
||
|
|
ORDER BY transaction_date DESC) rnk
|
||
|
|
FROM raena_analytics.gm_dashboard A
|
||
|
|
) D
|
||
|
|
WHERE rnk = 1 and mobile <> '') B ON A.reseller_mobile= B.mobile
|
||
|
|
left join (select A.*, replace(B.mobile,'+','') mobile from raena_analytics.cohort_level_reseller A
|
||
|
|
left join raena_user_management.user B on A.reseller_id = B.id) C on A.reseller_mobile = C.mobile
|
||
|
|
--LEFT JOIN raena_analytics.user_type_table flag ON replace(A.reseller_mobile,'+','')=flag.reseller_mobile
|
||
|
|
;
|
||
|
|
|
||
|
|
|