【Looker】アソシエーション分析的なものを実現してみる

Lookerでアソシエーション分析的なものを実現してみました。

具体的には、特定の商品を購入しているユーザーが他にどんな商品を購入してるか、あるいは、特定の商品を購入したユーザー以外のユーザーがどんな商品を購入しているかを分析するためのLookMLを実装しました。

Lookerのサポートに参考になりそうな実装方法を教えていただきましたが、利用する際にわかりづらいのでこれを元に別の実装方法を考えてみました。

利用するテーブル

sales_orderをいう受注テーブルを元に解説していきます。

ベースとなるView

view: sales_order {
  sql_table_name: `sales_order` ;;

  # 注文ID
  dimension: order_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.order_id ;;
  }

  # ユーザID
  dimension: user_id {
    type: number
    sql: ${TABLE}.user_id ;;
  }

  # 注文日時
  dimension_group: order_at {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      year
    ]
    sql: ${TABLE}.order_at ;;
  }

  # 商品ID
  dimension: product_id {
    type: number
    sql: ${TABLE}.product_id ;;
  }

  # 商品カテゴリ
  dimension: product_category {
    type: string
    sql: ${TABLE}.product_category ;;
  }

  # 売上
  measure: sales {
    type: sum
    sql: ${TABLE}.sales ;;
  }
  
  # ユーザー数
  measure: count_users {
    type: count_distinct
    sql: ${user_id} ;;
  }
}

実現方法について

今回ユーザーIDベースでフィルタするためのviewを作成し、exploreでINNER JOINすることでユーザーIDベースのフィルタを実現していきたいと思います。
実際にLookMLとともに実装方法について解説していきます。

フィルタ用のviewを作成する

以下のようなviewを作成します。

view: sales_order_user_filter {
  derived_table: {
    sql:
      SELECT
        user_id
      FROM `sales_order` as t0
      
      {% if parameter_filter_user_condition._parameter_value == 'intersect'
        and (filter_product_id._is_filtered or filter_product_category._is_filtered) %}
      WHERE EXISTS (
        SELECT 1 FROM `sales_order` as t1
        WHERE t0.user_id = t1.user_id
        AND {% condition filter_product_id %} product_id {% endcondition %}
        AND {% condition filter_product_category %} product_category {% endcondition %}
      )
        
      {% elsif parameter_filter_user_condition._parameter_value == 'except'
        and (filter_product_id._is_filtered or filter_product_category._is_filtered) %}
      WHERE NOT EXISTS (
        SELECT 1 FROM `sales_order` as t1
        WHERE t0.user_id = t1.user_id
        AND {% condition filter_product_id %} product_id {% endcondition %}
        AND {% condition filter_product_category %} product_category {% endcondition %}
      )
      
      {% endif %}
      GROUP BY user_id
    ;;
  }

  parameter: parameter_filter_user_condition {
    type: unquoted
    label: "condition"
    group_label: "User Level Filter"
    group_item_label: "condition"
    allowed_value: {
      label: "指定した条件のユーザーを除く"
      value: "except"
    }
    allowed_value: {
      label: "指定した条件のユーザーを含む"
      value: "intersect"
    }
    default_value: "except"
  }

  # 商品IDフィルタ
  filter: filter_product_id {
    type: number
    group_label: "User Level Filter"
    suggest_dimension: sales_order.product_id
  }

  # 商品カテゴリ
  filter: filter_product_category {
    type: number
    group_label: "User Level Filter"
    suggest_dimension: sales_order.product_category
  }

  # ユーザID
  dimension: user_id {
    type: number
    sql: ${TABLE}.user_id ;;
    primary_key: yes
    hidden: yes
  }
}

parameter_filter_user_condition

ユーザーのフィルタ条件を設定するためのパラメータです。
SQLでいうとINとNOT IN、あるいはEXISTとNOT EXISTかを決めるためのパラメータです。

filter_product_id, filter_product_category

実際にユーザーをフィルタするための条件です。
suggest_dimensionとして、元のview(sales_order)のdimensionを参照しています。

user_id

JOINするために利用します。JOINでしか利用しないのでhiddenとしています。

derived_tableのSQL

SQLでは、定義したパラメータとフィルタを利用して、SQLを動的に生成しています。
parameter_filter_user_conditionで、EXISTSにするか、NOT EXISTSにするかをLiquidを使って切り替えています。
fillter(filter_product_id, filter_product_category)を利用し、サブクエリの条件として設定し対象のuser_idのみを出力するようにしています。

元のviewにINNER JOINする

exploreで元のviewに以下のようにINNER JOINします。

explore: sales_order {
  view_label: "sales order"
  join: sales_order_user_filter {
    view_label: "sales order"
    type: inner
    sql_on: ${sales_order.user_id} = ${sales_order_user_filter.user_id} ;;
  }
}

Exploreでの確認

実装したLookMLはExploreでは以下のようになります。

実行されるSQL

追加したフィルタで、

• 条件: 指定した条件のユーザーを除く
• product_category: 1

とすると以下のようなSQLが生成されます。

WITH句内でproduct_category=1を購入したユーザーを除外したユーザーIDを抽出し、sales_orderテーブルに対してINNER JOINしているので、意図した通りのデータが取得できています。

WITH sales_order_user_filter AS (SELECT
        user_id
      FROM `sales_order` as t0
      WHERE NOT EXISTS (
        SELECT 1 FROM `sales_order` as t1
        WHERE t0.user_id = t1.user_id
        AND 1=1 -- no filter on 'sales_order_user_filter.filter_product_id'
        AND ( product_category  = 1)
      )
    )
    
SELECT
    sales_order.product_category  AS sales_order_product_category,
    COUNT(DISTINCT sales_order.user_id ) AS sales_order_count_users
FROM `sales_order`  AS sales_order
INNER JOIN sales_order_user_filter ON sales_order.user_id = sales_order_user_filter.user_id
GROUP BY
    1
ORDER BY
    2 DESC
LIMIT 500

まとめ

• 課題
  • 特定の商品を買った人が他にどんな商品を買っているのか分析したい
  • 特定の商品を買った人を除外して分析したい
• 解決方法
  • parameter, filterを利用してexists, not existsを利用したSQLを動的に抽出、INNER JOINすることでユーザーIDベースのフィルタを実現
• その他のユースケース
  • ユーザー x セグメント（1:n）のデータで、特定のセグメントに属するユーザーを除外して分析する、などでも利用することができます

---

と、ここまで書きましたが、他にいい実現方法があれば教えて下さい。