FirebaseとBigQueryでランキングを作る
Firebaseを活用して作られているサービスに、さらにBigQueryを活用してランキング機能を導入できるようにしていきます。今回の記事では、リアルタイムにランキングが更新されていくものではなく、1日ごとなど一定のタイミングで集計するようなランキングを想定しています。
全体でやることは大きく分けて以下の3つです。
- DBやGoogleAnalyticsのデータをBigQueryに取り込む
- BigQuery上でランキングの集計結果が得られるSQLを作る
- ランキングを更新したいタイミングでSQLを叩いてDBのランキング用テーブルを更新する
2でランキングロジックを組み立てるために必要なデータを1で集めてくる必要があります。あくまで想像ですが例えばZennの場合だと、記事のPV数やいいね数、投げ銭された金額やコメントの数、投稿日時、果ては投稿者の何本目の記事で今までどれくらいいいねがつけられてきているのかなどです。これらの数値をもとに、ある時点での記事のスコアを算出してスコアの高い順に並べるとランキングが完成します。スコアの計算部分はどのようなランキングにしたいのかによって大きく変わってくるので、今回は簡単なものを紹介します。
1. DBやGoogleAnalyticsのデータをBigQueryに取り込む
🚩ゴール：ランキングのスコアを算出するために必要になるデータはすべてBigQueryに集める
Google Analytics（GA）
PV数やイベント数を集計するにはGAが便利です。Firebaseの場合、プロジェクトの設定からBigQueryのリンクを押して、GAを有効にすれば自動的にGAのデータをBigQueryにエクスポートしてくれます👇
エクスポートし始めるまでに少し時間がかかることがあるので、本番リリース前の早めに有効にしておくのがオススメです。
Firestore
Firebase Extensionsの Export Collections to BigQuery を入れるのがオススメです。FirestoreのCollectionのパスやBigQueryのdataset, tableを指定すれば、Cloud FunctionsがBigQueryへFirestoreのデータをエクスポートしてくれます。例えばtableを novels と指定した場合、👇のようにnovels_raw_changelogとnovels_raw_latestが作成されます。
名前のとおりですが、raw_changelog はCREATE, UPDATE, DELETEのオペレーションが発生した記録がすべて残っています。raw_latestはBigQueryのViewという機能を使って作られたもので、現在のCollectionの状態をchangelogから得たものになります。基本的にはchangelogからデータを取得してくることになるでしょう。
余談ですが、このExport Collections to BigQueryの処理（というかExtensionsすべて）はオープンソースなので、気になるかたはやっていることを見てみるといいかもしれません（ボクは何回も読んでます）👇
このExport Collections to BigQueryは非常に簡単に導入できて便利ですが、BigQueryでSQLを書くときに少し面倒です。原因はテーブルのスキーマにあります👇
FirestoreのCollectionに保存しているデータはすべて data にSTRINGとして保存されています。中身はJSONをSTRINGとしてそのまま保存したものです（JavaScriptのJSON.stringifyとは少し違ってエスケープなどされていない）。例えば data が { "name": "moga" } だったとするとこれを取り出すSQLは👇のようになります。
SELECT TRIM(JSON_EXTRACT(data, '$.name'), '\"') FROM table_name;
JSON_EXTRACTはJSON文字列からvalueを取り出してくれる関数です。これで取り出した結果が残念なことに "moga" になってしまうのでTRIMで前後のダブルクオーテーションを取り除いています。
その他のデータ
もし任意のデータをBigQueryに取り込みたくなった場合は、Export Collections to BigQueryのコード を参考にしつつSDKを使って自分でinsertすればなんとかなります。ちなみにボクの場合、Firebase AuthenticationのデータもBigQueryに取り込んで分析用途で扱いたかったので、自前でエクスポートの処理を書いています（もちろん個人情報は除去した状態でエクスポートしています）。
2. BigQuery上でランキングの集計結果が得られるSQLを作る
🚩ゴール：前項で頑張って集めてきたデータを活用して、ランキングを作りたいコンテンツのIDとスコアを出力するSQLを作る
この部分はランキングの狙いによって大きく変わってくる部分になります。そのため今回は、ZennのTrendingを超絶簡単なスコア計算方法で出すイメージでSQLを作ります。以下のロジックにしてみましょう。
- 投稿から1週間以内の記事が対象
- スコア = いいね数 * 10 + 投げ銭された金額（円）
- これはわかりやすさのために超簡単にしているのであり、Zennは絶対にこんな単純なロジックではないと思います
BigQueryのデータセットはfirestore_export、テーブル名は、記事 = articles、いいね = likes、投げ銭 = nagesen とし、全てFirestoreからエクスポートしてきたものとします。SQLはあまり得意ではないので他に良い書き方があれば教えてください。また、雰囲気を掴んでもらうために書いているのでちょっと間違えていても許してください。
WITH articles AS (
SELECT
document_id as article_id,
FROM firestore_export.articles
WHERE
-- firestoreのtimestamp型はちょっとめんどい形で入ってる
TIMESTAMP_SECONDS(CAST(TRIM(JSON_EXTRACT(data, '$.createdAt._seconds'), '\"') as INT64)) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(). INTERVAL 7 DAY)
),
-- ここは普通はいいね数を持ったテーブルがあると思うし
-- WHERE INとか使ったほうがいいけどイメージね
like_summary AS (
SELECT
TRIM(JSON_EXTRACT(data, '$.article_id'), '\"') as article_id,
COUNT(*) as like_count,
FROM firestore_export.likes
GROUP BY
TRIM(JSON_EXTRACT(data, '$.article_id'), '\"')
),
-- ここも同様
nagesen_summary AS (
SELECT
TRIM(JSON_EXTRACT(data, '$.article_id'), '\"') as article_id,
SUM(CAST(TRIM(JSON_EXTRACT(data, '$.price'), '\"') as INT64) as total_sales,
FROM firestore_export.nagesen
GROUP BY
TRIM(JSON_EXTRACT(data, '$.article_id'), '\"')
),
scored_articles AS (
SELECT
articles.article_id,
ROUND(like_summary.like_count * 10 + nagesen_summary.total_sales, 4) as score
FROM articles
LEFT JOIN like_summary ON articles.article_id = like_summary.article_id
LEFT JOIN nagesen_summary ON articles.article_id = nagesen_summary.article_id
)
SELECT
article_id
score
FROM scored_articles
ORDER BY score DESC;
やっていることは単純で、いいね数や投げ銭金額を取ってきてスコアの計算方法通りに計算して、scoreの高い順に並び替えているだけです。
3. ランキングを更新したいタイミングでSQLを叩いてDBのランキング用テーブルを更新する
🚩ゴール：前項で作ったSQLを使ってサービス側のDBにランキングデータを保存する
ここまでくればあとはサービス側で扱えるようにするだけです。👆のSQLで言うscored_articlesをFirestoreに取り込みます。やり方は2通りあります。
- SDKを使って👆のSQLをそのまま投げる
- BigQueryのスケジュールされたクエリ機能を使って
scored_articlesを作っておいて、SDKからはSELECT文だけ投げる
1はランキングの集計ロジックが変わるたびにバックエンドのデプロイが必要です。対して2は、BigQuery上での変更のみで済みますが、BigQuery上でSQLが実行されるタイミングとサービス側で取得しに行くタイミングを合わせる必要があります。チームの特性に合わせて好きなほうを選ぶのが良いと思います。
TypeScriptのSDKを使ってクエリを投げるコードの例です👇
import { BigQuery } from '@google-cloud/bigquery'
type Row = {
article_id: string
score: number
}
const query = `pastes above SQL here`
const [job] = await new BigQuery().createQueryJob({ query })
const [rows, options] = await job.getQueryResults()
// rows: Row[]
job.getQueryResults で1度に取得できるのは20MBまでになっているのでご注意ください。ページングが必要な場合はoptionsに必要な情報が入っているのでそれをそのまま getQueryResults に渡してあげれば続きから取得してくれるようになっています。
取得してきたrowsをFirestoreに保存する際は以下のようなデータ構造がオススメです。
-
rankingscollection-
type: monthly, weeklyなどランキング種別を持つ createdAt-
rankedArticlescollectionarticleID-
rank: クライアントから取得することを考えて、scoreをそのまま持たずに順位へ変換しておく
-
ランキングは月間・週間・トレンド・総合のような様々な種類が出てきがちなので、rankingsにはtypeフィールドを持っておきましょう。また、クライアントから直接Firestoreにクエリを投げる場合は
- rankings.where(type = 'monthly').orderBy(createdAt, desc).limit(1) で指定した
typeの最新のデータを取得 - [1で取得したDocument].collection(rankedAtricles).orderBy(rank, asc) で実際のランキングデータを取得
のように2段階に分けて取得することになります。あとは2で取得してきたArticleをUI上で表示すればランキングの出来上がりです。
細かい話ですが rows を rankedArticles に保存するコードを書く際は、rankedArticlesを作成し終わってからrankingsを作成するようにしましょう。rankingsを先に作成してしまうとタイミングによっては空っぽのランキングが見えてしまいます。
以上でランキング機能の完成です！
おわりに
上述の実装方法はあくまでもランキング実装方法の1例です。もっといいやり方をご存じの方はぜひコメントいただけると幸いです。ちなみにこの記事での仕組みは、個人で開発している漫画管理サービスや弊社で開発を担当させてもらっているサービスで活用しています。
おまけ - ランキングとパーソナライズ
ランキングはWinner takes allになりがちです。ロジックに基づいてスコアを計算しているので、ハックもできてしまいます。Youtubeをはじめ、パーソナライズが強いサービスの人気は高いです。しかしボクが関わるような新規サービスの立ち上げ段階ではパーソナライズできるほどデータが貯まる前にサービスを終了してしまうこともあります。ニワトリたまごかもしれませんが、パーソナライズするためのデータを集めるためにランキングを作って比較的良質だと考えられるコンテンツをユーザーに届けるのはベターな選択だと思います。まだパーソナライズするところまでたどり着いたことがないので、そこまでサービスを延ばす手伝いをしていきたいです。そしてまだやったことのないパーソナライズの実装をヤッてみたいと思います。っていうポエムでした。おわり。
Discussion