漏鬥分析 - Data Lake Analytics - Deprecated

DLA漏鬥分析旨在協助營運人員分析一個多步驟操作過程中每一步的轉化與流失情況。假設購買商品過程中，需要觸發一系列事件：啟動 > 登入 > 搜尋商品 > 查看商品 > 產生訂單等。營運人員分析某段時間內（例如，2017年01月05號~2017年02月05號）所有依次觸發啟動 > 登入 > 搜尋商品 > 查看商品 > 產生訂單事件的人群的轉化流失情況，即計算所有使用者中觸發登入事件的總人數A、A中觸發搜尋商品事件的總人數B、B中觸發查看商品事件的總人數C以及C中觸發產生訂單事件的總人數D。

漏鬥分析中包含時間視窗的概念，即需要保證所有事件在同一個視窗期內觸發。例如時間視窗為1天，使用者001觸發搜尋商品事件的時間和觸發登入事件的時間間隔在一天內，搜尋商品事件才有效，否則視為無效。同理，使用者001觸發查看商品事件的時間和觸發登入事件的時間間隔也必須在一天內。時間視窗可以為1天、3天、7天或者1小時、6小時等任意長時間段。

前提條件

本文樣本中的測試資料存放區在OSS中，您可以按照以下步驟將您的測試資料存放區在OSS中。

開通OSS服務。
控制台建立儲存空間。

控制台上傳檔案。

本文樣本中將以下測試資料上傳到OSS的funnel_data目錄。

 1000002  1483258815538  收藏商品  {}  20170101
 1000002  1483274981790  啟動  {}  20170101
 1000002  1483223087508  搜尋商品  {"content": "computer", "page_num": 1}  20170101
 1000002  1483232016805  搜尋商品  {"content": "Apple", "page_num": 2}  20170101
 1000002  1483200895552  訂單付款  {}  20170101
 1000003  1483218002826  搜尋商品  {"content": "computer", "page_num": 2}  20170101
 1000003  1483206471681  加入購物車  {}  20170101
 1000003  1483284553531  加入購物車  {}  20170101
 1000003  1483279891663  瀏覽商品  {"brand": "Apple", "price": 9500}  20170101
 1000003  1483259182702  加入購物車  {}  20170101
 1000004  1483260505099  登入  {}  20170101
 1000004  1483231995064  收藏商品  {}  20170101
 1000004  1483206318588  加入購物車  {}  20170101
 1000004  1483241973408  登入  {}  20170101
 1000004  1483202785557  收藏商品  {}  20170101
 1000005  1483214265416  收藏商品  {}  20170101
 1000005  1483206343383  收藏商品  {}  20170101
 1000005  1483229265488  瀏覽商品  {"brand": "HuaW", "price": 9500}  20170101
 1000005  1483244294552  產生訂單  {"price_all": 5000}  20170101
 1000005  1483246988534  登入  {}  20170101
 1000006  1483207838307  收藏商品  {}  20170101

開始使用漏鬥分析之前，您需要在DLA中完成以下準備工作。

步驟一：建立OSS Schema

CREATE Schema funnel_test_schema
WITH DBPROPERTIES (
  catalog = 'oss',
  location = 'oss://bucket-name/'
)

步驟二：建立表

CREATE EXTERNAL TABLE IF NOT EXISTS funnel_test ( 
     user_id bigint NOT NULL COMMENT '',
     event_time bigint NOT NULL COMMENT '',
     event_id int NOT NULL COMMENT '',
     event_name varchar NOT NULL COMMENT '',
     event_attr varchar NOT NULL COMMENT '',
     event_date date NOT NULL COMMENT ''
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
STORED AS TEXTFILE 
LOCATION 'oss://bucket-name/funnel_data/';

步驟三：使用漏鬥分析函數進行資料分析

按照啟動 > 登入 > 搜尋商品 > 查看商品 > 產生訂單的事件觸發順序，對應的事件ID為10001 > 10002 > 10003 > 10004 > 10007。

以下樣本通過funnel_count函數，查詢1月1號到20號一共20天、時間視窗為7天（單位毫秒）、事件個數為5個的漏鬥，同時能夠儲存路徑各節點的人群。

  SELECT user_id,
  funnel_count(event_time,7 * 86400000,event_id,
  '10001,10002,10003,10004,10007') AS xwho_state
  FROM funnel_test
  WHERE event_id IN (10001, 10002, 10003, 10004, 10007)
  AND event_date BETWEEN '2017-01-01' AND '2017-01-20'
  GROUP BY user_id;

以下樣本在funnel_count函數的基礎上使用funnel_sum函數，得出定義轉化路徑的總體轉化率。

  SELECT funnel_sum(xwho_state, 5)
  FROM (
    SELECT user_id,funnel_count(event_time,7 * 86400000, event_id,
   '10001,10002,10003,10004,10007') AS xwho_state
    FROM funnel_test
    WHERE event_id IN (10001, 10002, 10003, 10004, 10007)
      AND event_date BETWEEN '2017-01-01' AND '2017-01-20'
    GROUP BY user_id 
  );

如果需要對事件對應的JSON屬性列event_attr的某個屬性進行過濾，例如對10004類事件中price在3500到5000之間的資料進行過濾。

  SELECT funnel_sum(xwho_state, 5)
  FROM (
    SELECT user_id,funnel_count(event_time, 7 * 86400000,event_id,
    '10001,10002,10003,10004,10007') AS xwho_state
    FROM funnel_test
    WHERE event_id IN (10001, 10002, 10003, 10004, 10007)
      AND event_date BETWEEN '2017-01-01' AND '2017-01-20'
       OR (event_id = 10004 AND
           json_extract_scalar(event_attr, '$.price') BETWEEN 3500 AND 5000)
    GROUP BY user_id 
  );

上述路徑和事件通過ID進行表徵，實際情境中可能只有事件的名稱，而沒有事件對應的ID，目前DLA也支援直接使用事件名稱（本例中的event_name列）進行路徑事件的表徵。

SELECT funnel_sum(xwho_state, 7) AS funnel
FROM (SELECT user_id,funnel_count(event_time, 7 * 86400000,event_name,'啟動,登入,搜尋商品,瀏覽商品,產生訂單,訂單付款,評價商品') AS xwho_state FROM funnel_test
      WHERE event_name IN 
      ('啟動', '登入', '搜尋商品', '瀏覽商品', '產生訂單', '訂單付款', '評價商品')
      GROUP BY user_id);