基於MaxCompute構建企業使用者畫像

waj清風發表於2018-09-29

基於MaxCompute構建企業使用者畫像（使用者標籤的製作）
日誌

string

資料同步

Create

MaxCompute

使用者標籤
摘要： 在資料化營銷時代，資料的價值越發顯得更為珍貴。那如何讓自己的資料發揮價值，也就是說如何讓公司沉睡的資料能夠驅動業務發展給公司帶來商業價值？在營銷裡面我們都談精準營銷，談使用者畫像，那使用者畫像到底如何構建，使用者的標籤如何開發？本示例給與最簡單的demo，那個大家清楚認識基於MaxCompute如何構建企業使用者標籤。

背景：
在資料化營銷時代，資料的價值越發顯得更為珍貴。那如何讓自己的資料發揮價值，也就是說如何讓公司沉睡的資料能夠驅動業務發展給公司帶來商業價值？在營銷裡面我們都談精準營銷，談使用者畫像，那使用者畫像到底如何構建，使用者的標籤如何開發？本示例給與最簡單的demo，那個大家清楚認識基於MaxCompute如何構建企業使用者標籤。

資料說明：
8f94082e-45f3-4b9e-8144-7d3b5726e0e7.png

基站位置資訊：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中。支援毫秒級針對萬億級資料進行即時的多維分析透視和業務探索。
90ecf9a0-ce4a-4fd4-98cf-24bb70dfd764.png


相關文章