こんにちは、TVerでデータ分析をしている高橋です。
弊社の分析業務の多くは BigQuery に蓄積されているログを使った分析で、大量のログを扱うため前処理から集計まで全てSQLで行っています。
本記事では、SQLを書く上で特に気を付けているテーブル結合時のケアについて紹介します。
分析業務の一例
「ホーム画面を開いてから10分以内にコンテンツを再生した割合を知りたい」という依頼が来ました1。
この集計は訪問ログと視聴ログを使い、ホーム画面に訪問したログを10分以内に再生した or 再生してないの2種類に分ければできそうです。
ここで、集計に用いるテーブルを簡単に紹介します。
訪問ログ (view_logs)
ホーム、マイページ、番組ページ、エピソードページなどに訪問したタイミングで発報されるログです。
ユーザー毎に時系列順に並べることで、サービス内でのページ遷移が分かります。
SELECT TIMESTAMP("2024-03-01 19:30:00") AS viewed_at, "hoge" AS user_id, "/home" AS url,
UNION ALL SELECT TIMESTAMP("2024-03-01 19:32:00"), "hoge", "/mypage/fav"
UNION ALL SELECT TIMESTAMP("2024-03-01 19:35:00"), "hoge", "/episodes"
UNION ALL SELECT TIMESTAMP("2024-03-01 21:45:00"), "fuga", "/home"
UNION ALL SELECT TIMESTAMP("2024-03-01 22:25:00"), "piyo", "/home"
UNION ALL SELECT TIMESTAMP("2024-03-01 22:30:00"), "hogera", "/home"
UNION ALL SELECT TIMESTAMP("2024-03-01 22:32:00"), "hogera", "/search"
UNION ALL SELECT TIMESTAMP("2024-03-01 22:34:00"), "hogera", "/home"
| viewed_at |
user_id |
url |
| 2024-03-01 19:30:00 |
hoge |
/home |
| 2024-03-01 19:32:00 |
hoge |
/mypage/fav |
| 2024-03-01 19:35:00 |
hoge |
/episodes |
| 2024-03-01 21:45:00 |
fuga |
/home |
| 2024-03-01 22:25:00 |
piyo |
/home |
| 2024-03-01 22:30:00 |
hogera |
/home |
| 2024-03-01 22:32:00 |
hogera |
/search |
| 2024-03-01 22:34:00 |
hogera |
/home |
視聴ログ (play_logs)
視聴中の行動が記録されているログです。これを見るとシークバー移動のタイミングなどが分かります。
今回は視聴開始した時刻の情報だけ使用します。
SELECT TIMESTAMP("2024-03-01 19:35:30") AS begin_at, "hoge" AS user_id, "begin" AS action,
UNION ALL SELECT TIMESTAMP("2024-03-01 22:26:00"), "piyo", "begin",
UNION ALL SELECT TIMESTAMP("2024-03-01 22:27:00"), "piyo", "begin",
UNION ALL SELECT TIMESTAMP("2024-03-01 22:35:00"), "hogera", "begin"
| begin_at |
user_id |
action |
| 2024-03-01 19:35:30 |
hoge |
begin |
| 2024-03-01 22:26:00 |
piyo |
begin |
| 2024-03-01 22:27:00 |
piyo |
begin |
| 2024-03-01 22:35:00 |
hogera |
begin |
集計用クエリを書いてみる
今回の集計を行うには、どのようなクエリを書けばよいでしょうか？
素朴にやるなら、
- 訪問ログからホーム画面（
/home）のログを抽出する
- 1に視聴ログを LEFT JOIN する
でしょうか。書いてみましょう。
SELECT
view_logs.*,
play_logs.begin_at,
play_logs.begin_at IS NOT NULL AS has_played,
FROM (
SELECT
*
FROM
view_logs
WHERE
url = "/home"
) AS view_logs
LEFT OUTER JOIN
play_logs
ON
view_logs.user_id = play_logs.user_id
AND play_logs.begin_at BETWEEN view_logs.viewed_at AND TIMESTAMP_ADD(view_logs.viewed_at, INTERVAL 10 MINUTE)
ORDER BY
viewed_at
| viewed_at |
user_id |
url |
begin_at |
has_played |
| 2024-03-01 19:30:00 |
hoge |
/home |
2024-03-01 19:35:30 |
true |
| 2024-03-01 21:45:00 |
fuga |
/home |
|
false |
| 2024-03-01 22:25:00 |
piyo |
/home |
2024-03-01 22:26:00 |
true |
| 2024-03-01 22:25:00 |
piyo |
/home |
2024-03-01 22:27:00 |
true |
| 2024-03-01 22:30:00 |
hogera |
/home |
2024-03-01 22:35:00 |
true |
| 2024-03-01 22:34:00 |
hogera |
/home |
2024-03-01 22:35:00 |
true |
一見良さそうに見えますが、以下の問題があります。
user_id=piyo の 1回の/home訪問に2回の再生が紐づいている（JOIN によってレコードが増えた、いわゆる重複）
user_id=hogera の2回の/home訪問に1回の再生が紐づいている
- 時系列で考えると再生に直接寄与したのは2回目の
/home訪問だと考えられるが、1回目の/home訪問も再生に寄与したと見なされてしまう
このまま集計すると間違った示唆を与えてしまうおそれがあります。
順番に解決していきましょう。
1回の訪問に2回の再生が紐づいているケース
このケースは短時間で複数回再生した場合に発生します（ザッピング的な再生など）。
今回の集計で興味があるのは10分以内の再生有無だけなので、/home訪問後最初の視聴が紐づくログだけ残すようにしましょう。
この処理は以下のようなQUALIFY句によって実現することができます。
QUALIFY
ROW_NUMBER() OVER(PARTITION BY user_id, viewed_at ORDER BY begin_at ASC) = 1
ちなみに viewed_at, user_id, url をキーとしてGROUP BYしても同様の処理が可能です。
個人的にはSELECT文を変更する必要がない QUALIFY句を使用することが多いです。
2回の訪問に1回の再生が紐づいているケース
短時間で回遊したのちに再生した場合などでしばしば発生します。
このケースは視聴ログの突合条件に次の/home訪問までに再生しているかという条件を追加することで排除できそうです。
はじめに、view_logsに次の/home訪問した時刻の列を追加します。
SELECT
*,
LEAD(viewed_at, 1) OVER(PARTITION BY user_id ORDER BY viewed_at) AS next_viewed_at,
FROM
view_logs
WHERE
url = "/home"
この列を使い、以下のような処理を突合部分に追加します。
AND IF(view_logs.next_viewed_at IS NOT NULL, play_logs.begin_at < view_logs.next_viewed_at, TRUE)
改良版クエリ
最終的にこのようになりました。
SELECT
view_logs.* EXCEPT(next_viewed_at),
play_logs.begin_at,
play_logs.begin_at IS NOT NULL AS has_played,
FROM (
SELECT
*,
LEAD(viewed_at, 1) OVER(PARTITION BY user_id ORDER BY viewed_at) AS next_viewed_at,
FROM
view_logs
WHERE
url = "/home"
) AS view_logs
LEFT OUTER JOIN
play_logs
ON
view_logs.user_id = play_logs.user_id
AND play_logs.begin_at BETWEEN view_logs.viewed_at AND TIMESTAMP_ADD(view_logs.viewed_at, INTERVAL 10 MINUTE)
AND IF(view_logs.next_viewed_at IS NOT NULL, play_logs.begin_at < view_logs.next_viewed_at, TRUE)
QUALIFY
ROW_NUMBER() OVER(PARTITION BY user_id, viewed_at ORDER BY begin_at ASC) = 1
ORDER BY
viewed_at
| viewed_at |
user_id |
url |
begin_at |
has_played |
| 2024-03-01 19:30:00 |
hoge |
/home |
2024-03-01 19:35:30 |
true |
| 2024-03-01 21:45:00 |
fuga |
/home |
|
false |
| 2024-03-01 22:25:00 |
piyo |
/home |
2024-03-01 22:26:00 |
true |
| 2024-03-01 22:30:00 |
hogera |
/home |
|
false |
| 2024-03-01 22:34:00 |
hogera |
/home |
2024-03-01 22:35:00 |
true |
viewed_at, begin_at どちらも一意化することができました。
このCTE を GROUP BYすることで目的の集計をすることができます。
最後に
テーブル結合時のケアについて2つの事例を紹介しましたが、これらの事象の発生を集計値だけ見て気付くことは非常に難しいです。
そのためレビューの際にはクエリのロジックを確認することは勿論のこと、中間テーブルの出力を確認したり個票チェックをしたりすることで集計ロジックの確からしさを検証しています。
同時に「レビューしやすいクエリ」を書くために、ロジックを考えたり社内ルール整備をしたりなどを日々行っています。この取り組みは分析チームが一丸となり、相当な力を入れて取り組んでいます2。
採用のこと
TVerでは一緒に分析業務をしてくれる方を募集しています。
カジュアル面談も受け付けていますので、「こういう取り組みのことをもっと知りたい」「普段どんな分析してるか知りたい」と思った方は以下よりお気軽にご連絡ください。お待ちしております！
herp.careers
1: 一例なのでかなり大味な依頼になっており、この集計結果を受けて具体的なアクションを行うことが難しいと予想されます。実際にこのような依頼が来た場合は、ホーム画面から再生までのユーザーの振る舞いや遷移についての仮説出しをしてからクエリを作成することが望ましいです。
2: テックブログにておいおい紹介する予定です。
