CRM_Full_Feature_Workflow
Demographic Features
city_tier & address_city
Categories
Tables
Query
gender
Categories
Tables
Query
income level
Categories
(0) 0-150
(1) 150-280
(2) 280-500
(3) 500-1000
(4) 1000+ (million idr per month)
unknown
Tables
Query
age_group
Tables
Categories
Query
language
Categories
Tables
Query
has_kids
Categories
Table
Query
os_platform
Categories
Tables
Query
device_brand
Categories
Tables
Query
mobile_device_price_range
Categories
Tables
Query
HDFS
/projects/clv_calculation_daily/hdfs/dev/lijing_test/clv_feature_breakdown_analysis/user_feature_snapshot_from_diverse_sources
Table
dev_clv_calculation_daily.crm_user_feature_daily_snapshot
partition
grass_date
grass_region
Consumption
shopee_rewards_tier (Halted)
Categories
Tables
Query
Mart_Buyer
Categories
Yes
No
(newly added) Count Order_id
Tables
Query
DP_Buyer
Categories
Yes
No
(newly added) Count Order_id
Tables
Query
Mum_Membership
Categories
Yes
No
Tables
Query
sensitivity_cod_level
Categroies
Yes (1)
No (0)
unknown
Table
Query
MP_Purchase_Level (Halted)
mp_category
Categories
Tables
Query
Mall_Buyer
Categories
Yes
No
(newly added) Count Order_id
Tables
mp_order.dwd_order_item_all_ent_df__reg_s0_live
Query
Shopee_food_buyer
Categories
Yes
No
(newly added) Count Order_id
Tables
Query
HDFS
'/projects/clv_calculation_daily/hdfs/dev/lijing_test/clv_feature_breakdown_analysis/user_feature_snapshot_from_in_app_purchase_behaviour'
Table
dev_clv_calculation_daily.crm_in_app_purchase_feature_daily_snapshot
partition
grass_date
Payment
Main Query
COD
Categories
Query
shopee_pay_later
Categories
Query
shopee_wallet
Categories
Query
bank_transfer
Categories
Query
credit_card
Categories
Query
shopee_credit
Query
Categories
other_payment_methods
Categories
Query
HDFS
'/projects/clv_calculation_daily/hdfs/dev/lijing_test/clv_feature_breakdown_analysis/user_feature_snapshot_from_in_app_purchase_behaviour'
Table
dev_clv_calculation_daily.crm_in_app_purchase_feature_daily_snapshot
partition
grass_date
In-app feature
Main Query to get home_page clicks
pop_up
Categories
Frequency from 0 - ++
Frequency Segments
Query
home_banner
Categories
Frequency from 0 - ++
Frequency Segments
Query
flash_sale
Categories
Frequency from 0 - ++
Frequency Segments
Query
shopee_mall
Categories
Frequency from 0 - ++
Frequency Segments
Query
categories
Categories
Frequency from 0 - ++
Frequency Segments
Query
top_product
Categories
Frequency from 0 - ++
Frequency Segments
Query
daily_discover
Categories
Frequency from 0 - ++
Frequency Segments
Query
digital_products
Categories
Frequency from 0 - ++
Frequency Segments
Query
shopee_live
Categories
Frequency from 0 - ++
Frequency Segments
Query
search_bar
Categories
Frequency from 0 - ++
Frequency Segments
Query
all_game_play
Categories
Frequency from 0 - ++
Frequency Segments
Query
Table
dev_clv_crm.crm_in_app_traffic_feature_daily_snapshot
Partition
feature
grass_region
grass_date
Acquisition
last_install_source
Categories
Tables
Query
last_install_channel
Query
Categories
Tables
register_channel
Categories
Tables
Query
HDFS
/projects/clv_calculation_daily/hdfs/dev/lijing_test/clv_feature_breakdown_analysis/user_feature_snapshot_from_diverse_sources
Table
dev_clv_calculation_daily.crm_user_feature_daily_snapshot
partition
grass_date
Engagement
A1 / A7 / A14 / A30 / A60
Categories
Table
Query
Active Frequency
Categories
Table
Query
HDFS
/projects/clv_calculation_daily/hdfs/dev/lijing_test/clv_feature_breakdown_analysis/user_feature_snapshot_from_diverse_sources
Table
dev_clv_calculation_daily.crm_user_feature_daily_snapshot
partition
grass_date
Incentive
nuz_vouche_pv
Categories
Yes
No
Tables
Query
nuz_voucher_fsv
Categories
Yes
No
Tables
Query
nuz_free_gift
Categories
Yes
No
Tables
Query
nuz_exclusive_deals (only ID)
Categories
Yes
No
Tables
Query
Net_checkout_with_voucher_l90
Categories
Tables
Query
low_price
Categories
Tables
Query
referral
Categories
Tables
Query
sarp
Categories
Yes
No
Tables
Query
murah_lebay (only ID)
Categories
Yes
No
Tables
Query
HDFS
'/projects/clv_calculation_daily/hdfs/dev/lijing_test/clv_feature_breakdown_analysis/user_feature_snapshot_from_in_app_voucher_usage'
Table
dev_clv_calculation_daily.crm_in_app_voucher_usage_feature_daily_snapshot
partition
grass_date