Lookerで購買データから購買回数を算出して分析してみた

大阪オフィスの玉井です。

実店舗やECサイトの購買履歴データを分析に使っている企業はとても多いと思います。購買データを分析する中でよくあるのが、購買回数に基づく分析です。「初回に買われている商品、2回目に買われている商品は何が人気なのか？」「この顧客は何回購買しているのか？」といったことを調べたります。

今回は、Lookerで上記のような分析をする時の一例を紹介したいと思います。

前準備

環境

• Looker 6.24.30
• Amazon RDS
  • PostgreSQL
• Windows 10 pro
  • Google Chrome 79.0.3945.130

使用するデータ

• Superstoreサンプルデータ（不具合修正版） |Tableau Community Forums
  • 購買データのサンプル
  • これの「Order」をPostgreSQLにロードして使用

やってみた

オーダーID別に番号を付与するテーブルを作る

Superstoreデータを入れておいたPostgreSQLをLookerのconnectionに登録し、そのままProjectまで作ってしまいます。modelファイルと下記のようなviewファイルが生成されます。

で、まず最初にやることは、「購買一つ一つに番号を付与」です。もうちょっと言い方を変えると、「オーダーID毎に、購買日時の古い順から、番号を付けていく」という感じです。さらに言い方を変えると、「オーダーIDに『このオーダーIDは○○さんの何回目の購買です』ということがわかる番号を付与する」という感じでしょうか。くどいですか？

どうやって付与するか…って話ですが、「オーダーID」と「購買番号」の2つのカラムのテーブルを別途作成します。テーブルの作成といっても、DB側で作業するのはではなく、Looker側で作成します（派生テーブルを使用します）。

まず、新たにviewファイル（order_sequence）を作成します。

次に、LookMLで派生テーブルを定義します。テーブルの内容は先述した「『オーダーID』と『購買番号』の2つのカラムのテーブル」です。

derived_table: {
　sql:
    SELECT
    "Order ID" as order_id
    , ROW_NUMBER() OVER (PARTITION BY "Customer ID" ORDER BY "Order Date") as　user_order_sequence_number
  　FROM "order";;
  }

sqlの部分で記述したSELECT文で取得した結果を、そのまま派生テーブルとします。

注意点としてはROW_NUMBER()でしょうか。これはWINDOW関数と呼ばれるもので、LookerではなくDB側（SQL側）の機能です。これはDBによっては使えない場合もあるので、注意しましょう（古いMySQLなど）。

派生テーブルを定義したら、そのカラムを使ってdimensionも定義しましょう。

  dimension: order_id {
    primary_key: yes
    sql: ${TABLE}.order_id ;;
  }

  dimension: user_order_sequence_number {
    type: number
    sql: ${TABLE}.user_order_sequence_number ;;
  }

order_idと、それが何回目の購買なのかを示すuser_order_sequence_numberを定義しました。

既存の購買データに購買番号テーブルを結合する

派生テーブルを定義したviewファイル「order_sequence」を定義したら、そこで定義したテーブルを、既存購買テーブル（order）に結合します。

今回のProjectで自動で生成されたmodelファイルに、下記を追記します。

explore: order {
  join: order_sequence {
    foreign_key: order.order_id
  }
}

既存購買テーブルであるorderに、先程定義した派生テーブルorder_sequenceを結合します。Looker上で結合するには、色々な書き方がありますが、上記ではorderのorder_idとorder_sequenceのorder_idを結びつけています（order_sequenceのviewファイル上で、order_idをプライマリーキーに指定しているため、この書き方ができる）。

確認してみる

Exploreで、顧客名をフィルタリングして、ある顧客の購買履歴を見てみました。

オーダーID毎に番号が付いていますね。分析の仕方次第では「初回で一番買われている商品のジャンル」等を出すことができます。

ちょっと応用

派生テーブルに、下記のdimensionを定義します。

measure: user_order_max_purchase {
  type: max
  sql: ${TABLE}.user_order_sequence_number ;;
}

購買番号の最大値を取得するdimensionです。これを利用すると、どの顧客が何回購買してくれたかわかるようになります。

購買回数ベスト20みたいなグラフも作れますね。

おわりに

Lookerは「可視化したいグラフに必要なデータはどのようなものか」っていうのを考えて、それをLookMLで起こしていく…っていう流れがベターなのかなと感じました。

参考資料