TL;DR

Podcast（音声配信サービス）のログを用いて、曜日・時間帯ごとに再生回数を集計し、ダッシュボードに可視化します。 BigQueryとGoogle Data Studioを利用しています。

事業概要

今回はポッドキャスト制作会社PitPa（ピトパ）のデータを扱います。人気声優による豪華コンテンツなどを手掛けています。

シマラヤジャパン株式会社（以下シマラヤジャパン／ CEO 安陽（あんよう）／所在地：東京都渋谷区）と、株式会社PitPa（以下PitPa／代表取締役石部達也（いしべたつや）／東京都渋谷区）は、共同制作ボイスドラマ『THE LEAKS（ザ・リークス）』の配信を、2020年9月29日（火）より開始いたしました。

音声プラットフォーム「himalaya（ヒマラヤ）」で、人気声優・内田彩＆浅沼晋太郎を起用したオリジナルボイスドラマ『THE LEAKS』を配信開始

PitPaの創業エピソードは拙著『個人開発をはじめよう！』でも取り上げています。当初はCtoCの音声投稿アプリでしたが、現在はピボットしてコンテンツ制作を主軸にしています。私は業務委託としてデータ分析基盤・KPIダッシュボード構築を担当しています。

個人開発をはじめよう！クリエイター25人の実践エピソード (技術の泉シリーズ（NextPublishing）)

発売日: 2020/04/03
メディア: Kindle版

ゴール設定

どの曜日・時間帯に再生回数が多いか（少ないか）を可視化します。

f:id:yuzutas0:20210128130937p:plain

上記が実際のアウトプットです。

画面上部に「日曜日、月曜日、火曜日...」と曜日が記載されています。
画面左部に「12 AM, 1 AM, 2 AM...」と時間が記載されています。
他に比べると「火曜日」の「午後3時台」の数値が大きくなっているのが分かります。
このスクリーンショットはテストアカウントの数値です。火曜日の午後にテスト投稿して、関係者が再生したのだろうな〜、と推察できます。

※本番データについては過去にインフォグラフィックを公開しています。ページは閉鎖済みですが、参考までに当時のプレスリリースを張っておきます。

prtimes.jp

このように「どの曜日・時間帯に再生回数が多いか」を表示し、KPIダッシュボードに埋め込んでいます。 KPIダッシュボードは、CEOである石部氏の意思決定や、取引先への状況説明、投資家・VCへのレポーティングに使われています。

Podcastリスナーに刺さる曜日・時間帯を可視化することで、解像度の高い現状分析・施策検討・効果測定が可能になります。

アクセスログの収集

f:id:yuzutas0:20210128210841p:plain

このシステム構成で解説を進めます。

コンテンツ（音声ファイル）をストレージ・CDNに置く。
ポッドキャストを再生するアプリケーション（Spotifyなど）からストレージ・CDNにHTTPリクエストでアクセスする。

※実際のポッドキャストサービスの通信では、ProxyやWebAPIを中継する場合もありますし、HTTP以外のプロトコルを使うこともあります。

いずれにせよ「音声再生データ」に該当するのは、以下のどちらかです。

該当エンドポイントのアクセスログ。
音声再生アプリケーションの操作ログ。

今回は制作会社（コンテンツプロバイダー）なので1に該当します。

このアクセスログをDWH（BigQuery）に連携します。技術的な詳細は割愛します。

元データの仕様

f:id:yuzutas0:20210128203912p:plain

このER構成を想定します。

accessLog: アクセスログ
- listenerId: リスナー識別子（文字列）
- contentId: コンテンツ識別子（文字列）
- timeMicros: アクセス日時（UNIXタイムスタンプ）
contentMaster: コンテンツマスタ
- contentId: コンテンツ識別子（文字列）
- contentName: コンテンツ名（文字列）
- speakerId: スピーカー識別子（文字列）
speakerMaster: スピーカーマスタ
- speakerId: スピーカー識別子（文字列）
- speakerName: スピーカー名（文字列）

実際の仕様とは若干異なりますが、趣旨は伝わると思います。

スタースキーマの作成

f:id:yuzutas0:20210128203929p:plain

「コンテンツマスタ」と「スピーカーマスタ」を軸にして「アクセスログ」を集計できるようにスタースキーマ（っぽいもの）を作ります。 WEBサイトにおける「PageView」（ページ閲覧数）に該当する概念なので「ContentPlay」（コンテンツ再生数）と命名しましょう。

SELECT
  a.timeMicros,
  a.listenerId,
  c.contentName,
  s.speakerName,
FROM
  accessLog AS a
INNER JOIN
  contentMaster AS c
ON
  a.contentId = c.contentId
INNER JOIN
  speakerMaster AS s
ON
  c.speakerId = s.speakerId

補足1: ユースケースとしては「特定のスピーカー」「特定のコンテンツ」に絞って表示することが可能になります。例えば、ビジネス系コンテンツと、エンタメ系コンテンツでは、聴かれる曜日や時間が違うのではないか、といった仮説を検証できます。

補足2: 実際にはデータクレンジングっぽいこともこのフェーズで実施します。不正アクセスを省いたり、バージョン1のアクセスログとバージョン2のアクセスログをマージしたり、近い時間に同じリスナーから同じコンテンツにアクセスしていたら重複と見なしたり、etc...。

補足3: 上記サンプルでは、簡単化のために SELECT xxxId を可能な範囲で削っています。 LookerなどのBIツールを使う場合は、ドリルダウンや他テーブル結合に備えて、 SELECT xxxId を明示的に残したほうが良いかなと思います。

曜日マスタの作成

以下のSQLで DayOfWeek というVIEWテーブルを用意します。表示内容と結合用コードを持っています。

SELECT
  SUBSTR(master, 0, 6) AS display, -- 'A. 日曜日'
  SUBSTR(master, 8, 3) AS code, -- 'Sun'
FROM 
  UNNEST([
    'A. 日曜日 Sun',
    'B. 月曜日 Mon',
    'C. 火曜日 Tue',
    'D. 水曜日 Wed',
    'E. 木曜日 Thu ',
    'F. 金曜日 Fri',
    'G. 土曜日 Sat'
  ]) AS master

曜日・時間の変換

ダッシュボード表示用に曜日・時間を集計します。

SELECT
  CP.listenerId,
  CP.contentName,
  CP.speakerName,
  DW.display AS dayOfWeek,
  FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_MICROS(CP.timeMicros), 'Asia/Tokyo') AS dateString,
  FORMAT_TIMESTAMP('%H', TIMESTAMP_MICROS(CP.timeMicros), 'Asia/Tokyo') AS hourString,
FROM
  ContentPlay AS CP
INNER JOIN
  DayOfWeek AS DW
ON
  FORMAT_DATE("%a", DATE(TIMESTAMP_MICROS(CP.timeMicros), 'Asia/Tokyo')) = DW.code

Unixタイムスタンプとは別に、日付（YYYY-mm-dd）を持たせています。 Google Data Studio を使うときに、カレンダーUIで対象期間を絞り込めるようになります。

f:id:yuzutas0:20210128131526p:plain

曜日のソート

「A. 日曜日」「B. 月曜日」……と、レコードの文頭にアルファベットを添えたのは、「Ascending」（昇順）設定によって「日、月、火、水、木、金、土」の順番で表示するためです。

BIツールによっては、カラムA（曜日ナンバー：数値型）でソートして、カラムB（曜日名：文字列型）を表示することも可能です。ダッシュボードの構築当時は、まだGoogle DataStudioの機能が弱かったのと、将来的に他ツール移植することを想定して、なるべくSQLで工夫しました。

prefixは数字？アルファベット？

最初は「0. 日曜日」「1. 月曜日」「2. 火曜日」……と「0〜6の採番」で表示しました。すると、ステークホルダーから「間違っていますよ」「2日は火曜日ではなくて水曜日ですよ」と指摘を受けました。「2. 火曜日」の「2.」を「◯年 ◯月 2日」の「2日」だと誤解させてしまいました。

私は「日曜日が0なのだから、日付ではないと気付くはずだ」と甘く考えていました。失敗でした。ダッシュボードの閲覧者は、わざわざ「0.日曜日」を確認して、数字の意味を読み解く必要があります。ユーザーフレンドリーなUIデザインとは言えません。

ひと目で意味を理解できるダッシュボードでなければ、多くのステークホルダーに見せられないでしょう。数字では誤解を招くことが分かったので「A. 日曜日」「B. 月曜日」……という表記に変えました。

BIツールでの可視化

Google Data Studio にて、ビジュアライズを作成します。英語版の設定で説明します。

Chart

Pivot table with heatmap を選択します。

DATA

Data source ：上記で作成したBigQueryのテーブルを参照します。
Date Range Dimension ： dateString カラムを指定します。
- SQLのSELECT結果は 文字列型 ですが、Google Data Studio で Type：Date（YYYYMMDD） を設定できます。
- Date型 にしておくと前述のカレンダーUIで対象期間を絞り込めます。
Row dimension ： hourString カラムを指定します。
- SQLのSELECT結果は 文字列型 ですが、Google Data Studioで Type：Hour（hh） を設定できます。
- Hour型 にしておくと 9 AM といった表示が可能です。
- 上を 00 時〜下を 23 時で表示したいので Row（行） に設定します。
Column dimension ： dayOfWeek カラムを指定します。
- 左を A. 日曜日 〜右を G. 土曜日 で表示したいので Column（列） に設定します。
Metric ：リスナーUU count(distinct listenerId) または、再生回数 count(*) を設定します。
Sorting Row #1 ： hourString カラムで Ascending（昇順） 設定にします。
- 上を 00 時〜下を 23 時で表示します。
Sorting Column #1 ： dayOfWeek カラムで Ascending（昇順） 設定にします。
- 左を A. 日曜日 〜右を G. 土曜日 で表示します。