基于MaxCompute构建企业用户画像（用户标签的制作）

背景：

在数据化营销时代，数据的价值越发显得更为珍贵。那如何让自己的数据发挥价值，也就是说如何让公司沉睡的数据能够驱动业务发展给公司带来商业价值？在营销里面我们都谈精准营销，谈用户画像，那用户画像到底如何构建，用户的标签如何开发？本示例给与最简单的demo，那个大家清楚认识基于MaxCompute如何构建企业用户标签。

数据说明：

基站位置信息：s_user_cell_log

CREATE TABLE s_user_cell_log (
	user_id bigint COMMENT '用户id',
	cell string COMMENT '基站id',
	phone_type string COMMENT '接入设备类型',
	latitude double COMMENT '维度',
	longitude double COMMENT '经度',
	log_time datetime COMMENT '日志时间',
	app_type bigint COMMENT 'app类型'
)
COMMENT '基站位置信息'
PARTITIONED BY (
	ds string
)
LIFECYCLE 90;

设备位置信息：s_user_device_log

CREATE TABLE s_user_device_log (
	user_id bigint COMMENT '用户id',
	imei string COMMENT 'imei',
	latitude double COMMENT '维度',
	longitude double COMMENT '经度',
	log_time datetime COMMENT '日志时间',
	app_type bigint COMMENT 'app类型'
)
COMMENT '设备位置信息'
PARTITIONED BY (
	ds string
)
LIFECYCLE 90;

网页日志信息：s_user_web_log

CREATE TABLE s_user_web_log (
	user_id bigint COMMENT '用户id',
	cookie string COMMENT 'cookieid',
	ip string COMMENT 'ip',
	url string COMMENT 'url',
	log_time datetime COMMENT '日志时间',
	app_name string COMMENT 'app类型'
)
COMMENT '网页日志信息'
PARTITIONED BY (
	ds string
)
LIFECYCLE 90;

计算逻辑：

通过基站信息、手机设备位置信息以及网页日志信息，通过一定的计算逻辑来推演用户地址的基础信息：dwd_log_addr_d

CREATE TABLE dwd_log_addr_d (
	user_id bigint COMMENT '用户id',
	prov_id bigint COMMENT '省份id',
	city_id bigint COMMENT '城市id',
	dist_id bigint COMMENT '区域id',
	bizdate string COMMENT '日期'
)
COMMENT '用户地址基础表'
PARTITIONED BY (
	ds string
)
LIFECYCLE 90;

dwd_log_addr_d表的ODPS SQL处理逻辑如下：

 其中ip2region和lbs2region两个函数都需要用户自己来编写UDF来实现。  

insert overwrite table dwd_log_addr_d partition (ds='${bdp.system.bizdate}') 
select 
user_id  
,prov_id 
,city_id 
,dist_id 
from (select 
       user_id
       ,ip2region(ip,'province') as prov_id
       ,ip2region(ip,'city')     as city_id
       ,ip2region(ip,'district') as dist_id
      from s_user_web_log   --用户访问网页日志信息
      where ds='${bdp.system.bizdate}'
      union all 
      select 
       user_id
       ,lbs2region(latitude,longitude,'province') as prov_id
       ,lbs2region(latitude,longitude,'city')     as city_id
       ,lbs2region(latitude,longitude,'district') as dist_id
      from s_user_cell_log   --基站信息
      where ds='${bdp.system.bizdate}'
      union all 
      select 
       user_id
       ,lbs2region(latitude,longitude,'province') as prov_id
       ,lbs2region(latitude,longitude,,'city')     as city_id
       ,lbs2region(latitude,longitude,,'district') as dist_id
      from s_user_device_log    --用户手机位置信息
      where ds='${bdp.system.bizdate}'    
     ) a 
group by user_id  
,prov_id 
,city_id 
,dist_id ;

根据ODPS SQL（近90天内出现最多的位置来断定常驻地信息）来计算常驻地信息表dm_tag_addr_city

insert overwrite table dm_tag_addr_city partition (ds='${bdp.system.bizdate}') 
select 
user_id,
city_id as addr_city
from ( select 
       user_id,
       city_id,
       ROW_NUMBER(partition by user_id order by date_cnt desc ) as rn
       from ( select 
              user_id,
              city_id,
              count(distinct dt) as date_cnt
              from dwd_log_addr_d 
              where ds<='${bdp.system.bizdate}' 
              and ds>'${bizdate_90}'
            ) t0 
      ) t1     
where t1.rn=1;

--调度参数说明
bizdate_90=${yyyymmdd-90}

最终我们的开发好的标签要被业务系统所使用，通常情况下我们会同步至业务数据库如RDS，但在大数据时代，随着数据标签的海量增长，往往在标签的查询效率上要求更过，在本实例中，我们将用户标签数据同步至分析型数据库AnalyticDB中。支持毫秒级针对万亿级数据进行即时的多维分析透视和业务探索。