どうもどうも。
安立です。
久しぶりの投稿です。
今回は、組織内共有が目的で、ZennのPublicationの統計データをLooker Studioで可視化した話です。
なぜやろうと思ったか
ソニックムーブでは複数人がZennのPublicationで記事を書いています。
ZennのPublicationには統計データを確認できる機能がありますが、無料プランでは直近2週間分のデータしか閲覧できません。
それ以上の期間を見るには「Publication Pro」（有料プラン）への加入が必要になります。
組織としてZennでのアウトプットをお試しで始めたということもあり、課金するという選択が取りにくい状況でした。
その結果、統計データがほとんど活用できない状態が続いていました。
組織として取り組んでいるのに、誰がどれくらい読まれているか、月にどれくらい記事が公開されているか、全体像が誰にも分かりません。
振り返りにも使えないし、「よく読まれた記事」の共有も属人的になっていました。
そこで、エクスポートできるCSVデータを活用して、組織全員がブラウザから見られるダッシュボードを自前で作るという方針にしました。
作ったもの
完成したダッシュボードには以下のグラフを用意しました。
- 月次PV数（折れ線グラフ・表）
- メンバー別PV（表・退職者表示あり）
- ユニット別PV（表） ※ユニットというのはエンジニア組織内のチームのことです。
- 人気記事ランキング（表）
- 累計記事数（スコアカード）
- 月次公開記事数（棒グラフ）
期間フィルタを右上に置いていて、全グラフが連動します。
システム構成
※ユーザーマスタ（氏名・ユニット・在籍状況）はGoogleスプレッドシートで管理し、BigQueryの外部テーブルとして参照しています。
CSVのフォーマット
Zennからエクスポートできるデータは以下のカラムで構成されています。
| カラム | BigQueryの列名 | 型 |
|---|---|---|
| ユーザー名 | username |
STRING |
| slug | slug |
STRING |
| タイトル | title |
STRING |
| PV発生日 | pv_date |
DATE |
| PV数 | pv_count |
INTEGER |
| 記事公開日 | published_at |
DATE（NULLABLE） |
published_at がNULLABLEになっている理由は後述します。
ハマったポイントと工夫
1. 旧CSVに記事公開日（published_at）がない
Zennのエクスポート仕様が途中で変わり、古いデータには published_at が入っていません。
月ごとの公開記事数を出すにはこの情報が必要なのに、旧データでは取れません。
対処：BigQuery VIEWで同じslugの別レコードから補完する
同じslugの記事でも、日付の違うCSVには published_at が入っているケースがあります。そこで、slug単位で published_at が存在するレコードをVIEWで参照して、NULLのレコードに補完する方針を取りました。
※snapshot_dateはGASでBigQueryにロードする際に追加しているものです。この問題の対処のために必要となるものです。
WITH slug_published AS (
SELECT DISTINCT
slug,
FIRST_VALUE(published_at IGNORE NULLS) OVER (
PARTITION BY slug
ORDER BY snapshot_date DESC
) AS resolved_published_at
FROM `your-project.zenn_analytics.pv_daily`
)
このViewをベースにすることで、Looker Studio側には常にクリーンなデータが渡るようにしました。
※この方法だと、仕様変更後にPVが発生しない記事がある場合には公開日データが存在しない状態になってしまうという落とし穴はありつつも、Zennの記事は結構昔のものでも月に1〜5回くらいは誰かしらが見るので、問題ありませんでした。
2. usernameの大文字小文字不一致
CSVの username とユーザーマスタの id を結合したかったのですが、大文字小文字が混在しているケースがありました。
対処：JOINでLOWER()を使って正規化
ON LOWER(p.username) = LOWER(u.id)
両辺をLOWER()で統一することで解決しました。
3. ZennのIDが数値扱いされてスプレッドシートで先頭の0が消える
ユーザーマスタをGoogleスプレッドシートで管理していたのですが、012345 のようなIDを入力するとスプレッドシートが数値として解釈し、12345 に変換してしまっていました。
CSVのusernameは 012345 のままなので、JOINが機能しない状態になっていました。
対処：セルの書式をテキスト（文字列）に変更してから入力
スプレッドシートでIDの列を選択 → セルの書式を「テキスト」に変更 → IDを再入力することで解決しました。先頭の0が保持されるようになります。
4. Looker Studioのキャッシュが頑固に残る
BigQuery側のデータを更新しても、Looker Studioに反映されないケースが何度かありました。最初は「フィールドを更新」ボタンを押していたのですが、これは効きませんでした。
原因を調べたところ、「フィールドを更新」と「データを更新」は用途が全く異なることが分かりました。
| 操作 | 目的 |
|---|---|
| フィールドを更新 | BigQuery側でカラムを追加・削除したときにスキーマ構造をLooker Studioへ同期する |
| データを更新 | キャッシュを破棄してBigQueryに再クエリし、データの中身を最新化する |
対処：「データを更新」ボタンを使う
画面右上のメニューから「データを更新」を実行するとキャッシュが破棄され、BigQueryから最新データが再取得されます。ユーザーマスタの値を変更した場合もこちらが正しい操作です。
なお、BigQuery側でカラムを追加・変更した場合は「フィールドを更新」が別途必要になります。両者を使い分けると良いです。
今後の課題
Apps Scriptのトリガー管理
Apps Scriptは共有ドライブに設置していますが、トリガー（定期実行の設定）はスクリプトを作成したユーザー（自分）に紐付いています。
自分が退職・異動した際にトリガーが止まるリスクがあるため、引き継ぎ手順を整備する必要があります。
その他、自身の管理から外れた時向けの引き継ぎ資料は作成しないとなぁと思っています。
おわりに
ZennのPV可視化自体はそこまで難しくありませんが、実運用に耐えるデータパイプラインを作るとなると細かいところでの工夫が多くなる印象でした。
特にCSVフォーマットの変化への対応や、ユーザーマスタとのJOINあたりは「やってみて初めて気づく」系の問題が多かったです。
同じようにZennチームでの発信状況を可視化したいと考えている方の参考になれば幸いです。
構築手順の詳細（BigQueryのVIEW定義、Apps Scriptのコード全文など）は別記事でまとめる予定です。
今回作るにあたって、クエリ部分はかなりAIに助けられました。
良い時代になりましたね。
それではまた次回、「ZennのPVデータをBigQuery+Looker Studioで可視化する構築手順」（予定）でお会いしましょう。
Discussion