データマネジメントチームの冨田 恭平です。
今回はデータを集計する際の考え方についてお話します。Treasure Data CDPではデータベースに格納されたテーブルに対し、SQLを用いることでデータを集計・処理することができます。
データの確認であれば簡単なSQLを書くことで行うことができますが、BIツールで可視化するためのデータ作成やCRMなどに使うためのマスターデータを作成するためには、複雑なSQLが必要になってくることも多いかと思います。そういった場合でも、簡単なSQLの繰り返しでデータを作るれるようになるための考え方をご紹介します。
データの把握
例えば、「このCSブログのアクセス数をBIツールで可視化したい（=目的）」とするとします。よくあるCDPの活用事例だと「売上の分析をしたい」、「ロイヤリティ指標を作りたい」、「セグメント配信をしたい」などがありますが、こうしたきっかけに対して最初に行うべき事はなんでしょうか。要件のヒアリングをする、アウトプットイメージを固める、など「目的」を整理・細分化することから始めることが多いかと思いますが、個人的には「今持っているデータを把握」することが最初に行うべきことだと考えています。
今回の目的に応じたデータの把握であれば、
- WebサイトのアクセスログがJavascriptタグで取得できている（=PV単位のログがある）
- PVログの中でも以下の情報が使えそう
– ログインID（ログインしないと閲覧できないため、ユーザーIDとして利用）
– タイムスタンプ（閲覧した日時）
– ページURL
– ページタイトル
– デバイス情報（ユーザーエージェントを利用、PCかスマホか、ブラウザなど）
あたりが最初に目をつける部分でしょうか。
インプットとアウトプットの棚卸し
ここからは、「データの把握（インプット）」と「目的の整理（アウトプット）」を行き来しながら考えていくのですが、例としてはこのような形です。
- アウトプット視点だと
- 記事のカテゴリ別のPV状況を把握したい
→ページのカテゴリマスタがあればできる - ユーザー単位だけでなく企業単位でのPV状況を把握したい
→ユーザーIDに紐づく企業マスタがあればできる
- インプット視点だと
- ページの滞在時間を取得する（JSタグとGTMの組み合わせ）
→記事の読み込み度合いを可視化できそう
こうした思考を繰り返し、インプットとアウトプットを細かく棚卸ししていくことで、「データ集計」は非常に簡単になると考えていますし、このプロセスが「データ集計構造を考える」ことだと思っています。
1次処理データの用意
最終的には以下の3テーブルを一次処理データとして用意することとします。
- PVのログ抽出データ
- ユーザーマスタ
- 記事マスタ
– ログインID
– タイムスタンプ
– ページURL
– デバイス情報（PCまたはスマホ）
– ログインID
– 企業名
– ページURL
– 記事カテゴリ
– 記事タイトル（ページURLと1対1となるようマスタ化）
ここまで用意できれば、1のPVログへユーザーマスタをログインIDでJOIN、2の記事マスタをページURLでJOINすることで、CSブログのアクセス数を可視化するための元データが完成です。
シンプルな題材としてアクセスログを使いましたが、「インプットとアウトプットを細かく棚卸する」ことを意識するとデータ集計が簡単になってくると思いますので、是非試してみてください。
今回は以上です。
