🐙

BigQueryでのデータ分析にARRAYを活用する

2022/12/05に公開

機械学習

こんにちは。
株式会社ココナラのR&Dグループ/検索・推薦グロースチーム所属のエンジニア、Unseoと申します。
本記事では、BigQuery上でのデータ分析にARRAYを活用した分析手法をご紹介いたします。
検索システムのログ分析をテーマに進めていきますが、広告やレコメンドエンジンなど、その他ログの分析にも応用できるテクニックかと思いますので、

普段の分析をもう少し精緻にしたい
BigQueryでできることを増やしたい
ユーザの行動ログ分析に挑戦したい

といった方々の参考になれば幸いです。

はじめに

ココナラは、［一人ひとりが「自分のストーリー」を生きていく世の中をつくる］をビジョンに掲げ、スキル・知識・経験を売り買いできるサービス（スキル）のマッチングプラットフォームです。現在、ユーザーは300万人を超え、サービスの種類は450種類以上と日本最大級のスキルマーケットに拡大しております。我々R&Dグループは、アルゴリズムとデータを活用し、お客様の良好なマッチングを創出すること・最適化することを使命としています。検索システムはマッチングを実現する方法であるため、我々R&Dグループの注力領域のひとつです。本ブログでは、検索システムの現状分析をテーマとして、BigQueryのARRAYを活用した分析についてご紹介いたします。

要件

ココナラやその他ECサービスにおいて、検索システムを介したユーザ行動の流れを単純化すると上図のようになります。
検索システムの性能を測るために、下記情報を持つテーブルを実装していくことを目指します。

どのアイテムを閲覧したか
どのアイテムをクリックしたか
クリックしたアイテムが何番目に表示されていたか

といった情報が必要です。これら3つの要件を満たすテーブルを、簡単な例から徐々に複雑にしながら実装していきます。

実装

未加工のログからユーザ行動テーブルを作成

下記のような形式で、ログが集められているところから分析を開始します。
user1がitem1とitem2を閲覧（view）しています。

[未加工のログテーブル]

user	action	item
user1	view	item1
user1	view	item2
user2	view	item1
user2	view	item2
user2	click	item1
user2	click	item2

ユーザ単位での集計を素直に実装すると以下のようなクエリになると思います。

-- このクエリは動きません
SELECT
  user,
  IF(action = 'view', item, NULL) AS viewed_items,
  IF(action = 'click', item, NULL) AS clicked_items,
FROM
  raw_log
GROUP BY
  user

しかし、viewed_itemsとclicked_itemsのカラムは、集約関数を適用する必要があるため、このままでは上記クエリは動作しません。
そこで、複数要素をまとめてARRAYにするのが、ARRAY_AGG関数です。(参考: Work with arrays)
ARRAY_AGG関数を使って次のようにitemを行動別に集約します。

SELECT
  user,
  ARRAY_AGG(IF(action = 'view', item, NULL) IGNORE NULLS) AS viewed_items, 
  ARRAY_AGG(IF(action = 'click', item, NULL) IGNORE NULLS) AS clicked_items
FROM
  raw_log
GROUP BY
  user

このクエリを実行した結果は次のとおりです。

[結果]

user	keyword	viewed_items	clicked_items
user1	'イラスト'	[item1, item2]	[]
user2	'イラスト'	[item1, item2]	[item1, item2]

これで各itemを、viewed_itemsとclicked_itemsにそれぞれ格納できました。このテーブルをユーザ行動（user_action）テーブルと呼ぶことにします。
この結果から、次のような定性的な分析が容易になりました。

user1はitem1とitem2を閲覧している。クリックはしていない。
user2はitem1とitem2を閲覧している。そしてitem1とitem2をクリックした。

検索キーワード別CTRの集計

次に、キーワード毎のインプレッション数と、クリック数、クリック率（CTR）の集計をします。CTRは様々な単位での集計が考えられますが、キーワードとユーザの組み合わせを1単位として集計してみます。この単位は、比較的シンプルなクエリで、大雑把な検索傾向を掴むのに便利です。

さきほどのテーブルに検索キーワードの列を追加した、次のようなテーブルを想定します。
実際には複数のキーワードが存在しますが、1つのキーワードに絞って考えます。

[未加工のログテーブルver.2]

user	keyword	action	item
user1	'イラスト'	view	item1
user1	'イラスト'	view	item2
user2	'イラスト'	view	item1
user2	'イラスト'	view	item2
user2	'イラスト'	click	item1
user2	'イラスト'	click	item2

キーワードでGROUP BYしてCOUNTIFで適当な条件のものを集計します。

user_action AS ( ~~ユーザ行動のクエリ~~ ),

SELECT
  COUNTIF(ARRAY_LENGTH(viewed_items) > 0) AS impressions,
  COUNTIF(ARRAY_LENGTH(clicked_items) > 0) AS clicks,
  COUNTIF(ARRAY_LENGTH(clicked_items) > 0) / COUNTIF(ARRAY_LENGTH(viewed_items) > 0) AS CTR
FROM
  user_action
GROUP BY
  keyword

BigQueryの配列関数のひとつ、ARRAY_LENGTHによって、各ARRAY内のアイテムの有無を判定して計上しています。このように基本的なARRAYにまつわる操作は標準でサポートされているので、簡単に実装できますね。

[結果]

keyword	impression	clicks	CTR
'イラスト'	2	1	0.5

イラストのキーワードが2回検索されていて、1人のユーザによるクリックがあるので、CTRは50％。うまく計算できました。

クリックしたアイテムの順位の取得

ユーザとしては、欲しいアイテムが検索結果の上位に表示されていると便利です。
ユーザに、より便利な検索結果を提供するために、現状を把握する必要があります。
現状把握のために、この記事ではアイテムの表示位置や、クリックされたアイテムの位置を調べます。

次のテーブルを考えてみます。

[ユーザ行動テーブルver.2]

user	keyword	viewed_items	clicked_items
user1	イラスト	[風景,人物,アニメ]	[風景,アニメ]

viewed_itemsは検索結果として表示された順番でARRAYに格納されているとします。

このテーブルに、クリックされたアイテムが何番目か、という列を追加したいです。
これには一工夫必要で、以下のようなやり方が考えられます（他にもたくさん方法はあるかもしれません）。

事前に検索結果のアイテムと順位を持つテーブルを別で作っておいて結合する。
clicked_itemsの要素を1つずつviewed_itemsから探索して順位を求める。

本記事では、ARRAYを活用した手軽な分析として2.の手法をご紹介します。

まずは、2.の手法を前半と後半の2つの手順に分解します。

clicked_itemsの要素を1つずつ取り出す。
取り出した要素をviewed_itemsから探して順位を求める。

手順1.について、はUNNEST関数によって実現します。

SELECT
  item
FROM
  UNNEST(clicked_items) AS item

UNNEST演算子はARRAYをテーブルに変換する関数です。そのため、上のコードにあるよう、SELECT文で要素を1つずつ取り出すことができます。

次に、手順2.です。
今はviewed_itemsのARRAYが表示順に並んでいることを仮定しているため、求めるアイテムがARRAYの何番目に格納されているかわかれば順位がわかります。
例えばPython言語だと、listに対してindexメソッドで実現される機能ですね。
BigQueryのARRAYにはこのような関数は実装されていないので自前で書いていきます。

SELECT
  offset + 1
FROM
  UNNEST(viewed_items) AS item
  WITH OFFSET AS offset
WHERE
  item = clicked_item

UNNEST演算子はWITH OFFSET句をともに用いることで、ARRAYの要素とインデックスの列を作ります。UNNEST と WITH OFFSET
要素とインデックスの列を、WHERE句でフィルタリングして、求める要素のインデックスを得ます。
WITH OFFSET句で得られるインデックスは0から始まるため、1を加算して、クリックされたアイテムの表示順位が得られました。

この処理を一般化してユーザ定義関数（UDF）にしておきましょう。
後に書くクエリがぐっと読みやすくなります。

CREATE TEMP FUNCTION
  GET_INDEX(arr ARRAY<STRING>, target_element STRING)
  RETURNS INT64
  AS (
    (SELECT
      offset + 1
    FROM
      UNNEST(arr) AS element
      WITH OFFSET AS offset
    WHERE
      element = target_element
    )
  );

仕上げに、手順1.と2.を組み合わせてクエリを完成させます。

SELECT
  user,
  keyword,
  viewed_items,
  clicked_items,
  ARRAY(
    (SELECT GET_INDEX(viewed_items, item) FROM UNNEST(clicked_items) AS item)
  ) AS clicked_item_ranks
FROM
  user_action_v2

さきほど定義したGET_INDEXを使って、clicked_items内のアイテムがviewed_itemsの何番目にあるか探索し、そのインデックスをARRAYに集約しています。
このクエリを実行した結果が以下です。

[結果]

user	keyword	viewed_items	clicked_items	clicked_item_ranks
user1	イラスト	[風景,人物,アニメキャラ]	[風景,アニメキャラ]	[1,3]

このテーブルから、

user1が”イラスト”というキーワードを検索
[風景,人物,アニメキャラ]というアイテムを閲覧
[風景,アニメキャラ]をクリック
- そして、"風景"は1番目、"アニメキャラ"は3番目に表示されていた。

という行動がわかります。これで当初予定していた要件はすべて満たされました。

おわりに

最後にできあがったテーブルを利用すると、本記事内で集計したCTRよりもさらに詳細な、ユーザ・アイテム単位でのCTRも計算できます。BigQuery SQL力をもう一段階ステップアップする課題としてぜひ挑戦してみてください。

ココナラでは、一緒に事業のグロースを推進していただける様々な領域のエンジニアを募集しています。
ココナラの開発や裏側を知ってみたい！　など少しでも興味が湧いたという方も大歓迎です。オンラインでぜひお話ししましょう！

エンジニア｜カジュアル面談

ココナラのエンジニアについてもっと知りたい！という方はこちらもよければご確認ください。

ユーザのストーリーをテクノロジーでサポートする

はじめに

要件

実装

未加工のログからユーザ行動テーブルを作成

検索キーワード別CTRの集計

クリックしたアイテムの順位の取得

おわりに

Discussion