repurchase

复购用户标签住房标签

​ 本程序用户给用户打标签，包括复购用户标签、住房标签、RFM标签，其中复购用户标签和RFM标签是通过用户订单计算而来（Orders表）。住房标签通过用户收货地址，调用百度地图API计算经纬度，参考安居客网站房价数据，计算而来。百度地图API每天只有3万额度，因此每天只能计算3万用户的经纬度，本程序采用定时执行的办法，每天爬取经纬度。

一、数据表结构

elasticsearch

• anjuke_data(安居客房价数据表)

改表储存了安居客全网的数据 | 字段 | 属性 | 描述 | | ---------- | ----- | -------- | | City | text | 城市 | | Community | text | 小区名称 | | Address | text | 地址 | | HousePrice | float | 价格 | | Unit | text | 单位 | | MainId | text | 地区ID | | Location | text | 经纬度 |

二、数据抽取

​ 复购标签、RFM标签均通过订单计算而来，具体通过clickhouse的sql语句来实现，因此需要把Orders等表格抽取到clickhouse数据仓库。考虑到Orders表数据产生后状态可变，同时clickhouse的数据不可以更新，删除，只能按分区删除，因此Orders表数据在clickhouse中以月为单位分区存储，定时抽取任务以月份为单位抽取数据。执行流程时，先将clickhouse中本月分区的数据删除，再从sqlserver中拉取本月数据，从而达到增量抽取。此外，由于订单的状态可变，因此，本程序每天都将本月和上月的数据进行刷新一遍，以保证数据一致。

datax任务地址

服务	路径
datax JOB文件	139.159.192.185/data/software/datax/job
定时任务	139.159.192.185/data/software/datax_task
crontab定时任务	0 1 * * * . /etc/profile; sh /data/software/datax_task/datax_task.sh

三、复购用户计算

​ 复购标签通过订单数据按月份计算而来，例如某个用户上个月有购买行为，这个月也有购买行为，则这个用户本月为复购用户，相应地会获取本月复购相对应的标签。但因为订单状态时可变的，所以可能会出现某个用户今天获得了一个复购的标签，明天该用户退单了就不是复购用户了，因此只要用户在本月有订单则会创建相应的复购标签，复购标签中有isLastMonthCustomer、isBeforeCustomer两个字段记录用户在上月个是否有购买行为和本月之前用户是否有购买行为。

1、复购用户筛选

• 读取Clickhouse Orders表

  SELECT COUNT(DISTINCT AConsigneePhone2) as counts from Orders WHERE AConsigneePhone2 != '' and OrderStatus in(50,70,150) and IsReturn=0 AND OrdersCode not like '%BF' and toStartOfMonth(OrderTime) = '" + thisYearAndMonth + "-01';
  

  2、复购用户金额次数计算

  SELECT AConsigneePhone2, groupArray(OriginType) as OriginType, groupArray(RM) as RM, groupArray(RN) as RN from (select AConsigneePhone2, OriginType, sum(Sum) as RM, count(AConsigneePhone2) as RN from Orders where OrderStatus  in(50,70,150) and IsReturn=0 AND OrdersCode not like '%BF' and toStartOfMonth(OrderTime) = '" + thisYearAndMonth + "-01' group by AConsigneePhone2, OriginType) GROUP by AConsigneePhone2 ORDER BY AConsigneePhone2
  

  3、复购用户标签存ES

• 复购标签库 | 标签ID | 标签名称 | 标签类型 | |---|---|---| |10000002| 201901| 复购用户| |10000003| 201902| 复购用户| |10000004| 201903| 复购用户| |10000005| 201904| 复购用户| |...| | |

4、复购标签格式

Tags[
    {
        "TagTypeName" : "复购用户",
        "isBeforeCustomer" : "F",
        "TagTypeId" : "10",
        "TagName" : "201910",
        "TagId" : "10000011",
        "TagKey" : "10662",
        "RM" : 1960,
        "TagCrtTime" : "2019-12-15 21:57:09",
        "RN" : 1,
        "isLastMonthCustomer" : "F",
        "OriginTypeRepur" : [
            {
                "OriginType" : "54",
                "RM" : 1960,
                "RN" : 1
            }
        ]
    }
]

字段描述

字段	描述
TagTypeName	标签名字
isBeforeCustomer	本月以前是否有购买记录
TagTypeId	标签类型id
TagName	标签名称
TagId	标签Id
TagKey	标签key
RM	金额
TagCrtTime	标签创建时间
RN	次数
isLastMonthCustomer	上月是否有购买记录
OriginTypeRepur	分平台订单
OriginTypeRepur.OriginType	平台
OriginTypeRepur.RM	分平台金额
OriginTypeRepur.RN	分平台次数

四、RFM标签计算

​ RFM模型*是衡量客户价值和客户创利能力的重要工具和手段，本程序通过RFM值的高低将用户分为9类，具体划分如下

划分类型	R(最近一次消费时间)	F(消费频率)	M(消费金额)
重要价值	高	高	高
一般价值	高	高	低
重要发展	高	低	高
一般发展	高	低	低
重要保持	低	高	高
一般保持	低	高	低
重要挽留	低	低	高
一般挽留	低	低	低
其他	-	-	-

1、计算购买次数、购买金额、最近购买距今时间中位数

SELECT median(R) as medianR, median(F) as medianF, median(M) as medianM  from (SELECT AConsigneePhone2,  max(dateDiff('day', now(), OrderTime)) as R,sum(1.0) as F, sum(Sum) as M from Orders where OrderStatus in(50,70,150) and IsReturn=0 AND OrdersCode not like '%BF' group by AConsigneePhone2));

2、clickhouse中创建临时表存储用户RFM标签数据

CREATE TABLE IF NOT EXISTS  default.RFMTemp (`AConsigneePhone2` String, `R` Int16, `F` Int8, `M` Int64, `customerType` String) ENGINE = Memory;
TRUNCATE TABLE default.RFMTemp;

3、用户RFM标签数据存入临时表

​ medianF，medianM，medianR分别为购买次数、购买金额、最近购买距今时间中位数

INSERT INTO RFMTemp SELECT AConsigneePhone2, R, F, M, caseWithExpression(toString(((RO * 4) + (FO * 2)) + MO), '7', '重要价值',  '6', '一般价值', '5', '重要发展', '3', '重要保持', '4', '一般发展', '2', '一般保持', '1', '重要挽留', '0', '一般挽留', '其他') AS customerType FROM (SELECT AConsigneePhone2,  R,  F,  M,  multiIf(R > " + medianR + ", 1, 0) AS RO,  multiIf(F > " + medianF + ", 1, 0) AS FO,  multiIf(M > " + medianM + ", 1, 0) AS MO FROM  (SELECT AConsigneePhone2, max(dateDiff('day', now(), OrderTime)) AS R, sum(1) AS F,sum(Sum) AS M FROM Orders WHERE AConsigneePhone2 != '' and OrderStatus in(50,70,150) and IsReturn=0 AND OrdersCode not like '%BF' GROUP BY AConsigneePhone2));

​ RFM标签格式如下

Tags:[
    {
        "RM_isLastMonthCustomer" : 0,
        "TagName" : "一般发展",
        "F" : 1.0,
        "RM_isBeforeCustomer" : 0,
        "RN_isLastMonthCustomer" : 0,
        "TagCrtTime" : "2020-04-02 18:43:43",
        "M" : 6000.0,
        "TagTypeName" : "RFM标签",
        "R" : -151.0,
        "TagTypeId" : "9",
        "TagId" : "9000005",
        "TagKey" : "10646",
        "RN_isBeforeCustomer" : 0
    }
]

​ 字段描述

字段	描述
F	用户购买总次数
M	用户购买总金额
R	用户最近一次购买距今时间，为负数
RM_isBeforeCustomer	用户本月之前的购买总金额
RM_isLastMonthCustomer	用户在上月的购买金额
RN_isBeforeCustomer	用户本月之前的购买次数
RN_isLastMonthCustomer	用户上月购买次数
TagName	标签名称
TagTypeName	标签类型名称
TagId	标签ID
TagKey	标签key
TagCrtTime	标签创建时间

五、住房标签

1、用户住房标签库

标签ID	标签名称	标签类型
8000001	<=1000	用户住房房价
8000002	1001~2000	用户住房房价
8000003	2001~3000	用户住房房价
8000004	3001~4000	用户住房房价
8000005	4001~5000	用户住房房价
8000006	5001~6000	用户住房房价
...

2、用户经纬度获取

• 调用百度接口

  3、用户房价获取

• 爬取安居客数据

• ES获取用户离安居客数据库中最近的房子的价格，以此作为用户的房价

  4、住房标签计算

• 计算用户房价所在区间，打上相应标签

• 住房标签格式如下

  "Tags" : [
      {
          "TagTypeName" : "用户住房房价",
          "TagTypeId" : "8",
          "TagName" : "40001~50000",
          "lng" : 106.517725,
          "TagId" : "8000020",
          "TagKey" : "10633",
          "TagCrtTime" : "2020-02-25 17:23:33",
          "lat" : 29.597119
      }
  ]

六、定时执行

每天凌晨3点15开始执行，爬取经纬度打上房价标签（百度经纬度每天只有3额度根据地址获取经纬度） ----> 复购标签计算 ------> RFM标签计算

@Scheduled(cron = "0 15 3 * * ?")
    public void calculateRepurchaseOrRFMOrMemberbaseInfo() throws Exception {

        for (int i = 0; i < 20; i++) {
            calculateHousePriceTags.runGetHouseTagsMultiThread(15, 100);
        }


        String yearAndMonth = new SimpleDateFormat("yyyy-MM").format(new Date());

        int day = Integer.parseInt(new SimpleDateFormat("yyyy-MM-dd").format(new Date()).split("-")[2]);

        if (day < 15) {
            try {
                calculateRepurchaseUserTags.getRepurchaseUserByMonth(DateUtils.getOffsetMonth(yearAndMonth, -1));
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        try {
            calculateRepurchaseUserTags.getRepurchaseUserByMonth(yearAndMonth);
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            calculateRFMTags.calculateRFMTags();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

七、程序代码

类功能描述

位置	类名	描述
com.example.repurchase.calculation	CalculateHousePrice	用户房价计算类
	CalculateLatLng	用户经纬度计算类
com.example.repurchase.configuration	ClickHouseDruid	ClickHouse druid连接池
	EsConfiguration	Elasticsearch连接池
	SqlServerDruid	SqlServer Druid连接池
com.example.repurchase.getTags	CalculateHousePriceTags	房价标签计算类
	CalculateRepurchaseUserTags	用户复购标签计算类
	CalculateRFMTags	EFM标签计算类
com.example.repurchase.pojo	AddressRersult	百度经纬度地址实体类
	BaiduResult	百度经纬度结果实体类
	Location	封装经纬度
	Task01	定时任务类
	TransportAnjukeDataToEs	安居客数据抽取
com.example.repurchase.utils	BaiDuMapUnit	百度经纬度工具类
	DateUtils	日期工具类
	HouseTagsUtils	房价标签工具类
	SqlUtils	sql工具类
	TagsUtils	标签工具类

八、部署

maven打包

mvn clean package -Dmaven.test.skip=true

服务器端口：192.168.50.41:9099

目录 192.168.50.41/usr/local/src/pointgrowth

nohup java -jar repurchase-0.0.1-SNAPSHOT.jar  > log.file  2>&1 &