MaxCompute:MaxComputeを使用したeコマースシナリオでのファンネル分析の実行

前提条件

• Simple Log Serviceが有効化されています。 詳細については、「入門」をご参照ください。

• MaxComputeが有効化され、MaxComputeプロジェクトが作成されます。 詳細については、「MaxComputeとDataWorksの有効化」および「MaxComputeプロジェクトの作成」をご参照ください。

• DataWorksが有効化されています。 詳しくは、「購入ガイド」をご参照ください。

• Quick BIが有効になっています。 詳細については、「購入、アップグレード、ダウングレード、更新、料金滞納」をご参照ください。

手順

1. Simple Log Serviceを使用してログデータを収集します。

   Simple log Serviceを使用してログデータを収集する方法の詳細については、「データ収集の概要」をご参照ください。 この例では、テストデータが使用されます。 テストデータをダウンロードするには、[TestData] をクリックします。

2. MaxComputeを使用して、オフラインコンピューティングデータモデルを構築します。

   1. DataWorksコンソールの [DataStudio] ページで、次のステートメントを実行して、ods_user_trace_dataという名前の運用データストア (ODS) テーブルを作成します。

      -- The dt column is used to partition the table by time in the unit of days. 
      CREATE TABLE IF NOT EXISTS ods_user_trace_data
      (
          md5                     STRING COMMENT 'First eight characters of the MD5 value of the user ID',
          uid                     STRING COMMENT 'User ID',
          ts                      BIGINT COMMENT 'Timestamp of a user operation',
          ip                      STRING COMMENT 'IP address',
          status                  BIGINT COMMENT 'Status code returned by the server',
          bytes                   BIGINT COMMENT 'Number of bytes sent to the client',
          device_brand            STRING COMMENT 'Device brand',
          system_type             STRING COMMENT 'OS type: Android, IOS, ipad, or Windows_phone',
          customize_event         STRING COMMENT 'Custom event: logon, logoff, purchase, registration, click, background running, user switching, browsing, or comment',
          use_time                BIGINT COMMENT 'The duration for which the app is used at a time. This field is required when the custom event is logoff, background running, or user switching.',
          customize_event_content STRING COMMENT 'The content of the custom event. This field is required when the custom event is browsing or comment.'
      ) 
      PARTITIONED BY
      (
          dt STRING  
      );

      説明

      • 上記のテーブル作成ステートメントのフィールドは、テストデータに基づいて作成されます。 DataWorksコンソールでテーブルを作成する方法の詳細については、「MaxCompute SQLタスクの開発」をご参照ください。

   2. 次のステートメントを実行して、ods_user_trace_dataテーブルにパーティションを追加します。

      ALTER TABLE ods_user_trace_data ADD PARTITION (dt=${bdp.system.bizdate});

   3. 収集したログデータをMaxComputeに移行します。

      収集したログデータをMaxComputeに移行する方法の詳細については、「ログデータのMaxComputeへの移行」をご参照ください。

   4. DataWorksコンソールの [DataStudio] ページで、次のステートメントを実行して、dw_user_trace_dataという名前のデータウェアハウス詳細 (DWD) テーブルを作成します。

      -- The dt column is used to partition the table by time in the unit of days. 
      CREATE TABLE IF NOT EXISTS dw_user_trace_data
      (
          uid                     STRING COMMENT 'User ID',
          device_brand            STRING COMMENT 'Device brand',
          system_type             STRING COMMENT 'OS type: Android, IOS, ipad, or Windows_phone',
          customize_event         STRING COMMENT 'Custom event: logon, logoff, purchase, registration, click, background running, user switching, browsing, or comment',
          use_time                BIGINT COMMENT 'The duration for which the app is used at a time. This field is required when the custom event is logoff, background running, or user switching.',
          customize_event_content STRING COMMENT 'The content of the custom event. This field is required when the custom event is browsing or comment.'
      ) 
      PARTITIONED BY
      (
          dt STRING 
      );

      説明

      DWDの詳細については、「データウェアハウス詳細レイヤー」をご参照ください。

   5. 次のステートメントを実行して、dw_user_trace_dataテーブルにデータを挿入します。

      INSERT INTO dw_user_trace_data PARTITION (dt = '${bdp.system.bizdate}')
      SELECT  uid
              ,device_brand
              ,system_type
              ,customize_event
              ,use_time
              ,customize_event_content
      FROM    ods_user_trace_data
      WHERE   dt = '${bdp.system.bizdate}'
      ;

   6. DataWorksコンソールのDataStudioページで、次のステートメントを実行して、rpt_user_trace_dataという名前のアプリケーションデータサービス (ADS) テーブルを作成します。

      -- The dt column is used to partition the table by time in the unit of days. 
      CREATE TABLE IF NOT EXISTS rpt_user_trace_data
      (
          browse      STRING COMMENT 'Page views',
          click       STRING COMMENT 'Clicks',
          purchase    STRING COMMENT 'Purchase quantity',
          browse_rate STRING COMMENT 'Browse conversion rate',
          click_rate  STRING COMMENT 'Click conversion rate'
      ) 
      PARTITIONED BY
      (
          dt STRING 
      );

      説明

      ADSの詳細については、「データウェアハウスをレイヤーに分割する」をご参照ください。

   7. DataStudioページで次のSQL文を実行して、ビジネスコードロジックを記述します。

      INSERT OVERWRITE TABLE rpt_user_trace_data PARTITION (dt=${bdp.system.bizdate})
      SELECT browse AS Page views
            ,click AS Clicks
            ,purchase AS Purchase quantity
            ,concat(round((click/browse)*100,2),'%') AS Click conversion rate
            ,concat(round((purchase/click)*100,2),'%') AS Purchase conversion rate 
      FROM
      (SELECT dt,count(1) browse FROM dw_user_trace_data WHERE customize_event='browse' 
       AND dt = ${bdp.system.bizdate} GROUP BY dt) a
      LEFT JOIN
      (SELECT dt,count(1) click FROM dw_user_trace_data WHERE customize_event='click' 
       AND dt = ${bdp.system.bizdate} GROUP BY dt) b
      ON a.dt=b.dt
      LEFT JOIN
      (SELECT dt,count(1) purchase FROM dw_user_trace_data WHERE customize_event='purchase' 
      AND dt = ${bdp.system.bizdate} GROUP BY dt)c 
      ON  a.dt=c.dt 
      ;

      説明

      ユーザの行動は、商品ページを閲覧し、商品をクリックし、次いで商品を購入することから始まる。 各段階でのコンバージョン率は、あるページから次のページに移動するユーザーの割合です。 たとえば、クリックのコンバージョン率は、次の式に基づいて計算されます。商品をクリックしたユーザー数 /ページを閲覧したユーザー数。

   8. rpt_user_trace_dataテーブルのオフラインデータ計算結果を照会します。

      • 例：

        SELECT * FROM rpt_user_trace_data WHERE dt='20231126';

      • 返された結果:

        +------------+------------+------------+-------------+------------+------------+
        | browse     | click      | purchase   | browse_rate | click_rate | dt         |
        +------------+------------+------------+-------------+------------+------------+
        | 35         | 16         | 2          | 45.71%      | 12.5%      | 20231126   |
        +------------+------------+------------+-------------+------------+------------+

3. ダッシュボードにデータを表示します。

   Quick BIを使用して、Webサイトのユーザープロファイル分析用のテーブルデータを表示するダッシュボードを作成できます。 詳細については、「クラウドデータソースの追加」および「ファンネルチャート」をご参照ください。

   重要

   • Quick BIコンソールでデータセットを作成する前に、MaxComputeプロジェクトで3層モデルが有効になっているかどうかを確認する必要があります。

     MaxComputeでsetproject; コマンドを実行して、プロジェクトプロパティのodps.name space.schemaパラメーターの値を照会できます。

     • このパラメーターの値がtrueの場合、プロジェクトの3層モデルが有効になります。 この場合、カスタムSQL文を使用してデータセットを作成する必要があります。 Quick BIは、MaxComputeの3層モデルをサポートしていません。 フィールドをドラッグしてデータセットを作成すると、Quick BIで自動的に生成されたSQL文の実行に失敗し、テーブルが見つからないことを示すエラーメッセージが表示されます。

     • このパラメーターの値がfalseの場合、プロジェクトの3層モデルは有効になりません。 カスタムSQL文を使用するか、フィールドをドラッグしてデータセットを作成できます。

   • 次の漏斗グラフを生成するには、テーブルオブジェクトがods_user_trace_dataであるデータセットを選択し、customize_eventフィールドをfunnel Layering/Dimensionフィールドにドラッグする必要があります。