AnalyticDB:漏斗と保持関数

テストデータセット

このトピックでは、Tianchi LabのTaobao動作データを使用して、漏斗関数と保持関数をテストします。 詳細については、「Taobao For Recommendationのユーザー行動データ」をご参照ください。

次の表に、テストデータセットで提供される4種類のユーザー動作を示します。

テストを実行する前に、テストデータセットをOSS (Object Storage Service) にアップロードし、OSSからAnalyticDB for MySQLにデータをインポートする必要があります。

1. テストデータセットをOSSにアップロードします。 詳細については、「オブジェクトのアップロード」をご参照ください。

2. OSS外部テーブルを使用して、AnalyticDB for MySQLにデータをインポートします。

   1. OSS外部テーブルを作成します。

      CREATE TABLE 'user_behavior_oss '(
        'user_id' 文字列、
        'item_id' 文字列、
        'cate_id' 文字列、
        'event' 文字列、
         's'bigint) エンジン='oss'
      TABLE_PROPERTIES = '{
      "endpoint":"oss-cn-zhangjiakou.aliyuncs.com" 、"accessid":"******" 、"accesskey":"*******" 、"url":"oss://<bucket-name>/user_behavior/" 、"delimiter":","
      } ' 

      OSS外部テーブルの構文の詳細については、「外部テーブルを使用してdata Warehouse Editionにデータをインポートする」トピックの「パーティション分割されていないオブジェクトのOSS外部テーブルの作成」をご参照ください。

   2. AnalyticDB for MySQLテストテーブルを作成します。

      CREATE TABLE user_behavior (
        uid文字列、
        イベント文字列,
        ts文字列
      ) 

   3. OSS外部テーブルからテストテーブルにデータをインポートします。

      SUBMIT JOB INSERT OVERWRITE user_behavior SELECT user_id , event, ts FROM user_behavior_oss;

window_funnel

window_funnel関数は、スライディングタイムウィンドウ内のイベントリストを検索し、連続イベントの最大数をカウントするために使用されます。 window_funnel関数は、指定したイベントリストの最初のイベントからカウントを開始し、イベントを順番にチェックしてから、連続イベントの最大数を返します。

• c1、c2、およびc3を含むイベントリストを指定し、ユーザーデータにc1、c2、c3、およびc4を含む場合、関数は3を返します。

• c1、c2、c3を含むイベントリストを指定し、ユーザーデータにc4、c3、c2、c1を含む場合、関数は1を返します。

• c1、c2、c3を含むイベントリストを指定し、ユーザーデータにc4とc3を含む場合、関数は0を返します。

構文

window_funnel (ウィンドウ、モード、タイムスタンプ、cond1、cond2、... 、condN)

パラメーター

uid、window_funnel(cast (整数として1800) 、"default" 、TIMESTAMPDIFF('second' 、'2017-11-25 00:00:00:00. 000 '、ts) 、event = 'pv' 、event = 'fav' 、event = 'cart' 、event = 'buy') をuser_behaviorから漏斗として選択します。ts > '2017-11-25 00:25 00:00:00' および '000' <000.000'

例

たとえば、次の変換パスに基づいて、2017-11-25 00:00:00から2017-11-26 00:00:00までの変換ステータスを分析します。商品の参照> 商品をお気に入りに追加する> 商品をショッピングカートに追加する> 商品を購入します。 関連するSQL文には、次の条件が含まれます。

• スライド時間ウィンドウのサイズは30分で、1,800秒に相当します。

• クエリ時間は、2017-11-25 00:00:00から2017-11-26 00:00:00で、タイムスタンプ形式の1511539200から1511625600に相当します。

• イベントステップは、以下の順序で実行される: 商品をブラウズする> 商品をお気に入りに追加する> 商品をショッピングカートに追加する> 商品を購入する。

次のSQL文を実行します。

SELECT funnel、count(1) FROM (SELECT uid、window_funnel (1800は整数) 、"default" 、ts、event='pv' 、event='fav' 、event='cart' 、event='buy') as funnel FROM user_behavior WHERE ts > 1511539200 AND ts < 1511625600 group by uid) GROUP funnel;

サンプル結果:

+ ------- -----------
| funnel | count(1) |
+ ------- -----------
| 0 | 19687 |
| 2 | 78458 |
| 3 | 11640 |
| 1 | 596104 |
| 4 | 746 |
+ ------- -----------
セットの5行 (0.64秒) 

retention

保持機能は、イベントが条件を満たすかどうかを分析するためのパラメータとして条件のセットを使用できます。

構文

保持 (cond1, cond2, ..., cond32)

パラメーター

例

たとえば、2017年11月25日現在のユーザー保持状況を分析したいとします。 関連するSQL文には、次の条件が含まれます。

• 分析の開始日は2017-11-25です。

• 初日のアクティブユーザー数はsum(r[1]) です。 2日目から7日目までの保持ユーザ数は、sum(r[2]), sum(r[3])… sum(r[7]) である。

次のSQL文を実行します。

SELECT sum(r[1]) 、sum(r[2]) 、sum(r[3]) 、sum(r[4]) 、sum(r[5]) 、sum(r[6]) 、sum(r[7]) から (SELECT retention(ds=''2017-11-25 'AND event='pv') 、ds='2017-11-25' 、ds='2017-11-26 '、ds='2017-11-27' 、ds='2017-11-28 '、ds='2017-11-29' 、ds='2017-11-29 '、ds='2017-11-30') AS r FROM user_behavior_date GROUP BY uid);

サンプル結果:

+ ------------------------------------------------------------------------------------------
| sum(r[1]) | sum(r[2]) | sum(r[3]) | sum(r[4]) | sum(r[5]) | sum(r[6]) | sum(r[7]) |
----------- ----------------------------------------------------------------------------------
| 686953 | 686953 | 544367 | 529979 | 523516 | 524530 | 528105 |
----------- ----------------------------------------------------------------------------------
1行セット (2.96秒) 

retention_range_countおよびretention_range_sum

保持分析関数 (retention_range_countおよびretention_range_sum) を使用して、ユーザー成長分析シナリオで視覚化グラフを生成し、ユーザー保持を分析できます。 retention_range_count関数を使用して、各ユーザーの保持ステータスを記録できます。 この関数の戻り値は2次元配列で、retention_range_sum関数の入力パラメーターとして使用できます。 retention_range_sum関数を使用して、すべてのユーザーの1日の保持率を要約できます。

構文

• retention_range_count

  retention_range_count(is_first, is_active, dt, intervals, outputFormat)

• retention_range_sum

  retention_range_sum(retention_range_count_result)

パラメーター

例

たとえば、2022年5月1日から2022年5月4日までのユーザーデータに基づいて、2022年5月1日および2022年5月2日の保持ステータスを照会するとします。 アクティベーションイベントはログインで、保持イベントは有料です。

1. テストテーブルを作成し、テーブルにデータを挿入します。

   1. テーブルを作成します。

      CREATE TABLEイベント (uid文字列、イベント文字列、ds日付);

   2. テーブルにデータを挿入します。

      INSERT INTOイベント値 ("user1", "pay", "2022-05-01"),("user1", "login", "2022-05-01"),("user1", "pay", "2022-05-02"),("user1", "login", "2022-05-02") ("user2" 、"login" 、"2022-05-01") 、("user3" 、"login" 、"2022-05-02") 、("user3" 、"pay" 、"2022-05-03") 、("user3" 、"pay" 、"2022-05-04");

      サンプルテストデータ:

      + ------- -------- -------------
      | uid | イベント | ds |
      + ------ ------- --------------
      | user1 | ログイン | 2022-05-01 |
      | user1 | 支払う | 2022-05-01 |
      | user1 | ログイン | 2022-05-02 |
      | user1 | 支払う | 2022-05-02 |
      | user2 | ログイン | 2022-05-01 |
      | user3 | ログイン | 2022-05-02 |
      | user3 | 支払う | 2022-05-03 |
      | user3 | 支払う | 2022-05-04 |
      + ------ ------- -------------- 

2. 各ユーザーの保持ステータスを照会します。

   SELECT
     uid,
     r
   から
     (
       SELECT
         uid,
         retention_range_count(event = 'login', event = 'pay', ds, array(1, 2))
       FROM
         event
       GROUP BY
         uid
     ) AS t
   注文によって
     uid; 

   サンプル結果:

   + ------- + ----------------------------- +
   | uid | r |
   + ------- + ----------------------------- +
   | user1 | [[738642,0,0],[738641,1,0]] |
   | user2 | [[738641,0,0]] |
   | user3 | [[738642,1,1]] |
   + ------- + ----------------------------- + 

3. すべてのユーザーの1日の保持率を照会します。

   SELECT
     from_days(u [1]) AS ds,
     u [3] / u [2] AS retention_d1、
     u [4] / u [2] AS retention_d2
   から
     (
       SELECT
         retention_range_sum(r) AS r
       FROM
         (
           SELECT
             uid,
             retention_range_count(event = 'login', event = 'pay', ds, array(1, 2))
           FROM
             event
           GROUP BY
             uid
         ) AS t
       ORDER BY
         uid
     ) rとして、
     unnest(r.r) AS t(u); 

   サンプル結果:

   + ---------- -----------------------------------------
   | ds | retention_d1 | retention_d2 |
   + ---------- ---------------- --------------------------
   | 2022-05-02 | 0.5 | 0.5 |
   | 2022-05-01 | 0.5 | 0.0 |
   + ---------- ------------------------------------------