BigQueryでページカテゴリ単位のPV数、セッション数を集計する

BigQueryでGoogleアナリティクスデータのページカテゴリ単位のPV数、セッション数を集計する

 GoogleアナリティクスのUI上なら、ディレクトリ単位のPV数の把握は容易にできますが、ディレクトリ単位のセッション数となるとセグメントをかける必要があります。

特集ページ等の種類が多くなるといちいちセグメントをかけて集計するのは面倒ですし、増えるとまたセグメントを設定しなければいけません。

BigQueryで集計すれば、GA上にセグメントを設定する必要もなく、増えたとしても勝手に集計してくれるので楽ちんです。

URL単位のPV数はヒット単位で集計する

BigQuery Export のスキーマを見ると「totals.pageviews」がありますので、これをsumすればいいのかなと思ってしまうそうです。が、「totals.pageviews」は「セッション中の合計ページビュー数」と説明されており、これはページ単位の集計には使えません。

「totals」とつくフィールドは集計された値ですので、ページ単位の合算は「hits.page」をカウントします。

　select
　hits.page.pagePath,
　count(hits.page) as pageview,
　count(distinct visitid) as session　
　from `{bigqueryプロジェクトID}.{データセット}.ga_sessions_日付`
　,unnest(hits) as hits
　group by 1
　;

 これで、ページ単位の集計はできましたが、もう少しおおきなくくり（特集ページ単位やディレクトリ単位）で把握するにはもうひと手間必要です。

ページをカテゴライズする

1URL毎ではなくて、ディレクトリ単位や特集ページ単位で集計したいことってありますよね。いくつか方法はあると思いますが、ここでは正規表現を使って特定のディレクトリだけ抜き出す方法を紹介します。

サイトが、「www.example.com/product/特集/index.html」のような構造だった場合、この特集のディレクトリだけ取り出して集計する方法がこちらです。

　select
　regexp_extract(hits.page.pagePath,r'www.example.com/product/(.*)/index.html') as 　      tokusyu,
　count(hits.page) as pageview,
　count(distinct visitid) as session　
　from `{bigqueryプロジェクトID}.{データセット}.ga_sessions_日付`
　,unnest(hits) as hits
　group by 1
　;

 regexp_extract以下で特集ディレクトリだけ抜き出して、tokusyuというカラムに格納せよという処理をしています。

selectにhits.page.pagePathを追加して実行すると、特集ディレクトリだけがtokusyuのカラムに入っていると思います。

応用効くと思いますので、是非お試しください。