需求三：七日流失用户相关指标

实现思路

所谓流失用户，就是加入某个用户在2024-01-01注册或使用了APP后，直到2024-01-08这7天内都未再次打开APP，那么就认为该用户已流失。

可以这样来实现。

• 首先，基于DWS层的dws_app_open_history表，获取最近8天的新增或者活跃用户数据，根据did分组，获得它对应的多个日期。

• 然后，对did相应的日期进行过滤，获取其中最大的日期，并判断该日期是否等于当前日期 - 7，如果满足则说明该用户已流失。

• 接着，将满足条件的用户数据保存到DWS层的dws_user_lost表中。

• 最后，对dws_user_lost表中的数据进行聚合统计，获得用户7日流失数量，保存到APP层的app_user_lost表中。

DWS层开发

根据实现思路，需要在DWS层初始化dws_user_lost表。

> cd /home/work/warehouse_user_action
> vi dws_shopmall_requirement03_init.sh

#!/bin/bash
# 需求三：七日流失用户相关指标
# dws层数据库和表初始化，只需要执行一次

hive -e "
create database if not exists dws_shopmall;

create external table if not exists dws_shopmall.dws_user_lost (
    did    string
) partitioned by(dt string)
  row format delimited  
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/dws/user_lost';
"

然后，需要从dws_app_open_history表中获取最近8天的新增或者活跃用户数据。

> cd /home/work/warehouse_user_action
> vi dws_shopmall_requirement03_add_partition.sh

#!/bin/bash
# 需求三：七日流失用户相关指标
# 每天凌晨执行一次
if [ "d$1" = "d" ]
then
dt=`date +%Y%m%d --date="1 days ago"`
else
dt=$1
fi

# 日期格式转换，将 20240101 转换为 2024-01-01
dt_new=`date +%Y-%m-%d --date="${dt}"`

hive -e "
insert overwrite table dws_shopmall.dws_user_lost partition(dt='${dt}') select
did
from dws_shopmall.dws_app_open_history
where dt >= regexp_replace(date_add('${dt_new}', -7), '-', '')
group by did
having max(dt) = regexp_replace(date_add('${dt_new}', -7), '-', '');
"

可以通过定时任务来执行它。

> crontab -e

# 每天凌晨0点1分执行
1 0 * * * /home/work/warehouse_user_action/dws_shopmall_requirement03_add_partition.sh

APP层开发

然后再初始化APP层的app_user_lost表。

> cd /home/work/warehouse_user_action
> vi app_shopmall_requirement03_init.sh

#!/bin/bash
# 需求三：七日流失用户相关指标
# app层数据库和表初始化，只需要执行一次

hive -e "
create database if not exists app_shopmall;

create external table if not exists app_shopmall.app_user_lost (
    num    int
) partitioned by(dt string)
  row format delimited  
  fields terminated by '\t'
  location 'hdfs://server01:9000/data/app/user_lost';
"

再来给它添加分区。

> cd /home/work/warehouse_user_action
> vi app_shopmall_requirement03_add_partition.sh

#!/bin/bash
# 需求三：七日流失用户相关指标
# 每天凌晨执行一次
if [ "d$1" = "d" ]
then
dt=`date +%Y%m%d --date="1 days ago"`
else
dt=$1
fi

hive -e "
insert overwrite table app_shopmall.app_user_lost partition(dt='${dt}') select
count(*) as num
from dws_shopmall.dws_user_lost
where dt = '${dt}';
"

可以通过定时任务来执行它。

> crontab -e

# 每天凌晨0点1分执行
1 0 * * * /home/work/warehouse_user_action/app_shopmall_requirement03_add_partition.sh

---

感谢支持

更多内容，请移步《超级个体》。