はじめまして、分析担当の高橋と申します。
2022年7月に入社し、現在はマーケティング施策の効果検証、KPI周りの集計や可視化などを行っています。

TVerではデータの集計、可視化にRedashというツールを使用していますが、その機能の一つであるQuery Parameterをフル活用し、継続利用かつ汎用性の高いクエリを書くための工夫を日々行っています。
本記事では、このQuery Parameterをどのように活用しているか、具体例も交えて紹介していきたいと思います。

こちらはTVer Advent Calendar 2022の6日目の記事です。

継続利用とか汎用性が高いと何が嬉しいの？

1つのクエリを使いまわせるので分析者の作業が減るというメリットはもちろんありますが、
真に嬉しいのは施策担当者自身がクエリを自由に使えるようにすることで仮説検証のサイクルを素早く回すことができる、という点にあると感じています。
そのため例えば「今週配信開始した〇〇という番組の視聴数を調べてほしい」というオーダーが来た際は、「期間Xに配信開始した番組Yが、期間Zにおいて視聴された（回数、ユニークブラウザ数）」など一般的な問題に置き換えてクエリを書くよう心掛けています。

ちなみに弊社の森藤が同様のテーマの記事をSoftware Design 12月号に寄稿しております。是非こちらもチェックしてみてください（本記事もこれを読みながら書いています）。

techblog.tver.co.jp

Query Parameter実例

RedashのQuery Parameterの実例に関する情報はあまり見かけない気がしたので、ここからはクエリに汎用性を持たせるための具体例をいくつか紹介していきます。

時刻指定

FROMとTOというパラメータを設定し、任意の期間で集計できるようにしています。
クエリの冒頭で入力日付をUTCに変換したものをDECLAREで定義し、以降の処理で呼び出して利用しています。

-- {{FROM}} {{TO}}
DECLARE __from TIMESTAMP DEFAULT TIMESTAMP("{{FROM}} 00:00:00", "Asia/Tokyo");
DECLARE __to TIMESTAMP DEFAULT TIMESTAMP("{{TO}} 23:59:59", "Asia/Tokyo");

SELECT
    TIMESTAMP_TRUNC(begin_at, WEEK(MONDAY), "Asia/Tokyo") AS w,
    user_id,
    COUNT(1) AS cnt
FROM
    `tver-prd.tver_logs.view_logs`
WHERE
    begin_at BETWEEN __from AND __to
GROUP BY
    w,
    user_id

集計の粒度

例えば再生数を集計する場合でも、

月次、週次、日次
使用デバイス（PC、スマホ、TV）
番組のジャンル毎

などなど、分析の切り口によって集計の粒度は逐一変わります。
これを毎回クエリを書いているのではしんどいので、以下のようなクエリを書くことで所望の粒度で集計が出来るようにしています。

-- {{FROM}} {{TO}} {{TRUNC_UNIT}} {{SEG_TIME}} {{SEG_DEVICE}} {{SEG_GENRE}}
DECLARE __from TIMESTAMP DEFAULT TIMESTAMP("{{FROM}} 00:00:00", "Asia/Tokyo");
DECLARE __to TIMESTAMP DEFAULT TIMESTAMP("{{TO}} 23:59:59", "Asia/Tokyo");

SELECT
    IF({{SEG_TIME}}, TIMESTAMP_TRUNC(begin_at, {{TRUNC_UNIT}}, "Asia/Tokyo", NULL)) AS t,
    IF({{SEG_DEVICE}}, device, NULL) AS device,
    IF({{SEG_GENRE}}, genre, NULL) AS genre,
    
    COUNT(1) AS cnt
FROM
    `tver-prd.tver_logs.play_logs`
WHERE
    begin_at BETWEEN __from AND __to
GROUP BY
    t,
    device,
    genre

SEGから始まるパラメータですが、(TRUE, FALSE)を選択できるようにDropdown Listで設定します。FALSEを選択すると該当の値がNULLで潰れるという仕組みです。

TRUNC_UNITには(MONTH, WEEK(MONDAY), DAY)など丸めたい単位を設定し、任意の時間軸での集計を可能にしています。
余談ですが放送業界では1週間の定義を、週末をまとめて扱うことができる月曜日~日曜日として扱うことが多いので、週次で集計したいときはWEEK(MONDAY)を使用しています。

一覧クエリ

Query Based Dropdown Listを使用すれば、既存のクエリの結果を指定することもでき、カーディナリティの高い列（番組名など）を指定して抽出する場合にとても重宝しています。
番組名、放送局など使用頻度の高い列はあらかじめ一覧:から始まるクエリで定義しておき、必要に応じて使用しています。
また、Query Based Dropdown Listを使用する場合、設定したクエリをタグに入れることで、どのクエリに依存しているか把握できるようにしています。

/*
[クエリ名]一覧: シリーズタイトル
*/

SELECT
    series_title
FROM
    series_master

/*
抽出用クエリ
*/

-- {{FROM}} {{TO}} {{SERIES_TITLE}}
DECLARE __from TIMESTAMP DEFAULT TIMESTAMP("{{FROM}} 00:00:00", "Asia/Tokyo");
DECLARE __to TIMESTAMP DEFAULT TIMESTAMP("{{TO}} 23:59:59", "Asia/Tokyo");

SELECT
    series_title,
    COUNT(1) AS cnt
FROM
    `tver-prd.tver_logs.play_logs`
WHERE
    series_title IN ({{SERIES_TITLE}})
GROUP BY
    series_title

その他

パラメータは二重中かっこ{{}}で定義できますが、クエリ上に一瞬でも存在しなくなるとパラメータのTypeがデフォルトのTextになってしまいます。
設定自体は大した手間ではないですが、何度も設定するのは面倒です（自分はクエリを切り貼りする際によくやられました）。
そこでクエリ先頭にコメントアウトでパラメータを全て定義し、これを回避できるようにしています。
記事中で紹介したクエリは全てそのように書いてあります。

終わりに

TVerでのクエリの汎用性を高める工夫について、RedashのQuery Parameterの活用例とともに紹介しました。
ただまあ当然ですが汎用性以外にも何を分析の切り口とするか、分析目的に対する回答として何を示せばよいかなどなど必要な要素はたくさんあり、「分析って奥が深いなぁ～」と感じる日々を送っています。
幸いTVerにはデータ分析に関して経験豊富な方が多数在籍しています。その方々を参考にしながら、引き続き楽しく精進していきたいと思っています！