Hive电商用户画像

1、用户画像

1.1 用户消费订单表

字段	类型	描述
user_id	bigint	用户ID
first_order_time	timestamp	第一次消费时间
last_order_time	timestamp	最近一次消费时间
first_order_ago	bigint	首单距今时间
last_order_ago	bigint	尾单距今时间
month1_hg_order_cnt	bigint	近30天购买次数（不含退拒）
month1_hg_order_amt	double	近30天购买金额（不含退拒）
month1_order_cnt	bigint	近30天购买次数（含退拒）
month1_order_amt	double	近30天购买金额（含退拒）
max_order_amt	double	最大消费金额
min_order_amt	double	最小消费金额
total_order_cnt	bigint	累计消费次数（不含退拒）
total_order_amt	double	累计消费金额（不含退拒）
total_coupon_amt	double	累计使用代金券金额
user_avg_amt	double	客单价（含退拒）
month1_user_avg_amt	double	近30天客单价（含退拒）
common_address	string	常用收货地址
common_paytype	string	常用支付方式
month1_cart_cnt	bigint	最近30天购物车次数
month1_cart_goods_cnt	bigint	最近30天购物车商品件数
month1_cart_submit_cnt	bigint	最近30天购物车提交商品件数
month1_cart_rate	double	最近30天购物车成功率
month1_cart_cancel_cnt	bigint	最近30天购物车取消商品件数
return_cnt	bigint	退货商品数量
return_amt	double	退货商品金额
reject_cnt	bigint	拒收商品数量
reject_amt	double	拒收商品金额
last_return_time	timestap	最后一次退货时间
school_order_cnt	bigint	学校下单次数
company_order_cnt	bigint	公司下单次数
home_order_cnt	bigint	在家下单次数
forenoon_order_cnt	bigint	上午下单次数
afternoon_order_cnt	bigint	下午下单次数
night_order_cnt	bigint	晚上下单次数
morning_order_cnt	bigint	凌晨下单次数

1.1.1 近30天订单

(case
 when order_date >= data_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'), 29) and 
 order_date<=from_unixtime(unix_timestamp(),'yyyy-MM-dd') then 
 1 
end) dat_30

1.1.2 退货和拒收

(case
 when order_status in (3,4) then
  1 
  else 
  0 
end) jt_flag

1.1.3 常用收货地址、常用支付方式

select
user_id,addr
from(
select
user_id,addr,row_number() over(partition by user_id order by addr_cnt desc) as rn
from(
select 
user_id, 
concat(nvl(area_name,''),nvl(area_name,'')) as addr,
count(1) as addr_cnt
from dm_ord_order
group by user_id, concat(nvl(area_name,''),nvl(area_name,''))
) o1
) o2 
where rn = 1

1.1.4 剩余指标计算

select t.user_id, -- 客户ID
min(create_time) first_order_time, -- 第一次消费时间
max(create_time) last_order_time, -- 最近一次消费时间
datediff(min(create_time),from_unixtime(unix_timestamp(),'yyyy-MM-dd')) first_order_ago, -- 首单距今时间
datediff(max(create_time),from_unixtime(unix_timestamp(),'yyyy-MM-dd')) last_order_ago, -- 尾单距今时间
sum(case when t.dat_30 = 1 and t.jt_flag=0 then 1 end) month1_hg_order_cnt, -- 近30天购物件数（不含退拒）
sum(case when t.dat_30 = 1 and t.jt_flag=0 then t.order_money end) month1_hg_order_amt, -- 近30天购物金额（不含退拒）
sum(dat_30) month1_order_cnt, -- 近30天购物件数（含退拒）
sum(case when t.dat_30 = 1 then t.order_money end) month1_hg_order_amt, -- 近30天购物金额（含退拒）
max(t.order_money) max_order_amt, -- 最大消费金额
min(t.order_money) min_order_amt, -- 最小消费金额
sum(case when t.jt_flag = 0 then 1 end) total_order_cnt, -- 累计消费次数（不含退拒）
sum(case when t.jt_flag = 0 then t.order_money end) total_order_amt, -- 累计消费金额（不含退拒）
sum(coupon_money) total_coupon_amt, -- 累计使用代金券金额
sum(t.order_money)/count(1) user_avg_amt, -- 客单价（含退拒）
sum(case when t.order_status = 3 then t1.goods_amount end) return_cnt, -- 退货商品数量
sum(case when t.order_status = 3 then t.order_money end) return_amt, -- 退货商品金额
sum(case when t.order_status = 4 then t1.goods_amount end) reject_cnt, -- 拒收商品数量
sum(case when t.order_status = 4 then t.order_money end) reject_amt, -- 拒收商品金额
max(case when t.order_status = 3 then t.create_time end) last_teturn_time, -- 最近一次退货时间
sum(case when t.order_addr = 1 then 1 end) school_order_cnt, -- 学校下单总数
sum(case when t.order_addr = 2 then 1 end) company_order_cnt, -- 公司下单总数
sum(case when t.order_addr = 3 then 1 end) home_order_cnt, -- 在家下单总数
sum(case when t.order_hour >= 8 and t.order_hour <= 11 then 1 end) forenoon_order_cnt, -- 上午下单总数
sum(case when t.order_hour >= 12 and t.order_hour <= 18 then 1 end) afternoon_order_cnt, -- 下午下单总数
sum(case when t.order_hour >= 19 and t.order_hour <= 22 then 1 end) night_order_cnt, -- 晚上下单总数
sum(case when t.order_hour >= 23 and t.order_hour <= 7 then 1 end) morning_order_cnt, -- 凌晨下单总数
hour(create_time) order_hour
from dm_ord_order t 
left join (select order_id, sum(goods_amount) goods_amount from dm_ord_order_goods) 
on (t.order_id = t1.order_id)
group by t.user_id

1.2 用户营销信息表

字段	类型	描述
user_id	bigint	用户ID
tag_phone	string	营销手机号
monthly_activity	string	活跃状态：注册未购买、活跃、沉睡、流失
user_value	string	用户价值模型：利用RFM模型结合K-Means算法将用户分为：高价值、中价值、一般价值

1.2.1 Spark SQL 计算30天内用户的活跃状态

public class SparkUtils {
    // 定义会话池
    private static ThreadLocal<SparkSession> sessionPool = new ThreadLocal<>();

    public static SparkSession initSession() {
        if (sessionPool.get() != null) {
            return sessionPool.get();
        }
        SparkSession session = SparkSession.builder().appName("etl")
                .master("local[*]")
                .enableHiveSupport()
                .getOrCreate();
        sessionPool.set(session);
        return session;
    }
}

public class ConversionEtl {
    public static void main(String[] args) {
        SparkSession session = SparkUtils.initSession();

        ConversionVo conversionVo = conversionBehaviorCount(session);
        System.out.println(conversionVo);
    }

    public static ConversionVo conversionBehaviorCount(SparkSession session){
        // 查询下过订单的用户
        Dataset<Row> orderMember = session.sql("select distinct(member_id) from ecommerce.t_order " +
                "where order_status=2");

        // 将购买次数超过 1 次的用户查出来
        Dataset<Row> orderAgainMember = session.sql("select t.member_id as member_id " +
                " from (select count(order_id) as orderCount," +
                " member_id from ecommerce.t_order " +
                " where order_status=2 group by member_id) as t " +
                " where t.orderCount>1");

        // 查询充值过的用户
        Dataset<Row> charge = session.sql("select distinct(member_id) as member_id " +
                "from ecommerce.t_coupon_member where coupon_channel = 1");

        Dataset<Row> join = charge.join(
                orderAgainMember,
                orderAgainMember.col("member_id")
                        .equalTo(charge.col("member_id")),
                "inner");

        // 统计各层级的数量
        long order = orderMember.count();
        long orderAgain = orderAgainMember.count();
        long chargeCoupon = join.count();

        // 包装成VO
        ConversionVo vo = new ConversionVo();
        vo.setPresent(1000L); // 目前数据中没有，直接给定值
        vo.setClick(800L);
        vo.setAddCart(600L);
        vo.setOrder(order);
        vo.setOrderAgain(orderAgain);
        vo.setChargeCoupon(chargeCoupon);

        return vo;
    }

    @Data
    static class ConversionVo{
        private Long present; 
        private Long click;
        private Long addCart;
        private Long order;
        private Long orderAgain;
        private Long chargeCoupon;
    }
}

1.2.2 Spark ML 构建用户价值模型

1.2.2.1 RFM模型

• 统计180天内订单总金额、购买次数以及最近一次购买日期

select 
month6_hg_order_amt,--180天内订单总金额（不含退拒）
month6_hg_order_cnt,--180天内购买次数（不含退拒）
last_order_time --最近一次购买日期
from 
dm_user_protrait_order_sale

1.2.2.2 K-Means算法

• 高价值用户（上一次购买时间集中在最近一个月，180天内消费次数大约为10-25次，180天内消费金额集中在1000-2000元）
  
• 中价值用户（上一次购买时间较久，平均分布在1个月到3个月不等，180天内消费次数大约为10次以内，180天内消费金额集中在500-1500元）
  
• 一般价值用户（上一次购买时间集中在最近一个月，180天内消费次数在5次以内，180天内消费金额集中在500元以内）
  

2、Spark SQL 平台指标统计

2.1 统计用户信息（如 性别、地域、年龄段等）的分布情况

public static List<MemberSex> memberSexEtl(SparkSession session) {
	// 先用sql得到每个性别的count统计数据
	Dataset<Row> dataset = session.sql("select sex as memberSex, count(id) as sexCount " +
			" from ecommerce.t_member group by sex");

	List<String> list = dataset.toJSON().collectAsList();

	// 对每一个元素依次map成MemberSex，收集起来
	List<MemberSex> result = list.stream()
			.map( str -> JSON.parseObject(str, MemberSex.class))
			.collect(Collectors.toList());

	return result;
}

2.2 统计平台近期新增注册人数、每日的总订单量以及订单流水金额等

// 近七天注册人数统计
String memberSql = "select date_format(create_time,'yyyy-MM-dd') as day," +
		" count(id) as regCount, max(id) as memberCount " +
		" from ecommerce.t_member where create_time >='%s' " +
		" group by date_format(create_time,'yyyy-MM-dd') order by day";

memberSql = String.format(memberSql, DateUtil.DateToString(sevenDayBefore, DateStyle.YYYY_MM_DD_HH_MM_SS));
Dataset<Row> memberDs = session.sql(memberSql);

2.3 统计活动前后平台注册量、订单量、订单总金额的周环比

public static List<RegVo> regWeekCount(SparkSession session) {

	LocalDate now = LocalDate.of(2019, Month.NOVEMBER, 30);
	Date nowDay = Date.from(now.atStartOfDay(ZoneId.systemDefault()).toInstant());

	Date lastTwoWeekFirstDay = DateUtil.addDay(nowDay, -14);

	String sql = "select date_format(create_time,'yyyy-MM-dd') as day," +
			" count(id) as regCount from ecommerce.t_member " +
			" where create_time >='%s' and create_time < '%s' " +
			" group by date_format(create_time,'yyyy-MM-dd')";
	sql = String.format(sql,
			DateUtil.DateToString(lastTwoWeekFirstDay, DateStyle.YYYY_MM_DD_HH_MM_SS),
			DateUtil.DateToString(nowDay, DateStyle.YYYY_MM_DD_HH_MM_SS));
	Dataset<Row> dataset = session.sql(sql);
	List<String> list = dataset.toJSON().collectAsList();
	List<RegVo> result = list.stream()
			.map(str -> JSON.parseObject(str, RegVo.class))
			.collect(Collectors.toList());
	return result;
}

2.4 统计优惠券即将过期时间，供短信营销服务进行对持券用户的消费唤醒

public static List<CouponRemindVo> couponRemindCount(SparkSession session){

	LocalDate now = LocalDate.of(2019, Month.NOVEMBER, 30);
	Date nowDay = Date.from(now.atStartOfDay(ZoneId.systemDefault()).toInstant());
	Date sevenDayBefore = DateUtil.addDay(nowDay, -7);

	String sql ="select date_format(create_time,'yyyy-MM-dd') as day, " +
			" count(member_id) as couponCount " +
			" from ecommerce.t_coupon_member where coupon_id != 1 " +
			" and create_time >= '%s' " +
			" group by date_format(create_time,'yyyy-MM-dd')";

	sql = String.format(sql,
			DateUtil.DateToString(sevenDayBefore, DateStyle.YYYY_MM_DD_HH_MM_SS));
	Dataset<Row> dataset = session.sql(sql);

	List<String> list = dataset.toJSON().collectAsList();
	List<CouponRemindVo> result = list.stream()
			.map(str -> JSON.parseObject(str, CouponRemindVo.class))
			.collect(Collectors.toList());
	return result;

}