BigQueryで訪問回数ごとのユーザーCVRをだす

「訪問回数ごとのユーザー分布を集計する」でユーザーの訪問回数ごとの分布をだしました。このエントリーでは、その分布にコンバージョンしたユーザー数を追加して、訪問回数単位のユーザーコンバージョン率を出したいと思います。

手順としてはコンバージョンしたユーザーのテーブルと、訪問回数ごとのユーザー分布テーブルをJOINしてそれを集計することになります。

下は前述のエントリーのクエリですが、コンバージョンに関する内容はありません。

　select
　session_count,
　count(distinct fullvisitorid) as users
　from
　(select
　fullvisitorid,
　visitId,
　count(visitId)　
　over(
　partition by fullvisitorid
　order by visitId
　rows between unbounded preceding and unbounded following) as session_count
　from `bigqueryプロジェクトID.データセット.ga_sessions_日付`
　where totals.visits is not null
　order by 1,2)
　group by 1
　order by 1
　;

このクエリにコンバージョンしたユーザーテーブルをJOINして集計したのがこちらです。

　select
　session_count,
　count(distinct fullvisitorid) as users
　count(distinct cv_fullvisitorid) as cv_user,
　round(count(distinct cv_fullvisitorid) / count(distinct fullvisitorid),3)*100 as cvr
　from
　(select
　fullvisitorid,
　visitId,
　count(visitId)　
　over(
　partition by fullvisitorid
　order by visitId
　rows between unbounded preceding and unbounded following) as session_count
　from `{bigqueryプロジェクトID}.{データセット}.{ga_sessions_日付}`
　left join
　(select
　fullvisitorid as cv_fullvisitorid
　from `{bigqueryプロジェクトID}.{データセット}.{ga_sessions_日付}`
　where totals.transactions is not null)
　on fullvisitorid = cv_fullvisitorid)
　group by 1
　order by 1
　;

最下部のselect内でコンバージョンしたユーザーだけのテーブルを作成しています。それを訪問回数を集計するテーブルにJOINしています。ここまでのクエリを実行してスプレッドシートにエクスポートすると下のような表ができます。コンバージョンしたfullvisitoridがcv_fullvisitoridに格納されています。

この表を最上部のselect内で集計しているのですが、一般公開のGoogleアナリティクスデータの2017年8月1日のテーブルを実行した結果が下のようになります。

おそらく実際のデータでも同じように訪問回数が多くなるほど、CVRが高い結果になるのではないでしょうか。モチベーションの高いユーザーほどサイト訪問の頻度が上がりコンバージョンにつながるという当たり前の結果ではありますが、マーケ施策やサイト上のコミュニケーションを検討する上で一つの示唆になるのではないかと思います。