BigQueryで特集ページのコンバージョン貢献を集計する

特集ページのコンバージョン貢献

特集ページが見られたセッションで予約に至ったかどうかを把握する方法を紹介します。以前のエントリーでも書きましたが、GoogleアナリティクスのUI上でも特集ページごとにセグメントを設定すれば可能ですが、数が多いと面倒ですのでBigQueryで集計してしまいます。さらにデータポータルからそのテーブルを参照してモニタリングすることもできそうです。

特集ページによっては、セッション単位ではなくユーザー単位の行動を追う方がいいケースもあるかもしれませんが、ひとまずセッション単位で出してみたいと思います。

特集ページのセッション数集計

 こちらは紹介済のクエリですが、/product/とindex.htmlの間にある特集ページを識別するディレクトリだけをtokusyuというカラムに入れてPV数とセッション数を集計しています。

　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
　;

 eコマースサイトでトランザクションの計測をしている場合は、ここにtotals.transactionsをsumすればいいように思えるのですが、それでは正確な数はかえってきません。それを確認するために、以下のようなクエリを実行してみてください。

　select
　visitId,
　hits.page.pagePath,
　hits.page,
　totals.transactions
　from `{bigqueryプロジェクトID}.{データセット}.ga_sessions_日付`
　,unnest(hits) as hit
　order by 4
　;

 order by でトランザクションがあった順番にならべていますが、トランザクションの数字は該当するvisitIdのすべてのpagepathに入っているはずです。

下のような結果になっていると思いますが、この状態でtransactionsを合計すると実際よりも大きな数字になってしまいます。

 セッション単位でトランザクション情報を格納したテーブルとジョイン

hits.transactions.transactionIdに予約番号のような値を送っている場合、重複を除外してカウントすればいいように思いますが、hits.transactions.transactionIdは予約完了ページのpagepathにしか紐づいていません。特集ページの行にはtransactionIdは存在しませんのでそれを使って集計する方法も適いません。

そこで、セッションとtransactionIdを紐づけたテーブルを用意し、最初に紹介したテーブルにjoinする方法をとります。

　with pv as(
　select
　concat(fullvisitorid,'_',visitid) as id,
　regexp_extract(hits.page.pagePath,r'www.example.com/product/(.*)/index.html') as 　      tokusyu,
　concat(clientId,visitId,date,(visitstarttime+hits.time))as pageview,　
　visitid,　
　from `{bigqueryプロジェクトID}.{データセット}.ga_sessions_日付`
　,unnest(hits) as hits),
　tx as(
　select
　concat(fullvisitorid,'_',visitid) as id,
　hits.transaction.transactionId,
　from `{bigqueryプロジェクトID}.{データセット}.ga_sessions_日付`
　,unnest(hits) as hits)
　select
　tokusyu
　count(distinct pageview) as pv,
　count(distinct visitid),
　count(distinct transactionid)
　(select
　tokusyu,
　pageview,
　visitId,
　transactionId,
　from pv
　left join tx
　using(id)
　group by 1,2,3,4)
　group by 1
　;

まず、pvという名前でトランザクション以外のカラムをまとめます。ページビュー数は、fullvisitorid・visitid・date・(visitstarttime+hits.time)を結合することでhitが発生したユニークな値として格納しておきます。

txテーブルには、transactionIdとpvテーブル同様にfullvisitoridとvisitidを結合したユニークなセッションの情報をidと言う名前で格納します。

このidというカラムをキーにしてpvとtxをjoinすると、tokusyu、pageview、visitIdにtransactionIdを加えたテーブルができます。これをサブクエリとして集計すれば、特集単位のトランザクション貢献を把握することができます。

deviceCategoryやtrafficSource.mediumのカラムを追加すればより深堀できそうです。お試しください。