BigQueryでGoogleアナリティクスデータを差分更新をする

BigQueryでGoogleアナリティクスデータの差分を更新する

クエリのスケジュール化の方法は「クエリの定期的実行を自動化する」で紹介させていただきましたが、Googleアナリティクスデータを日次で差分取り込みしたい場合は、前日分だけ取得するように書く必要があります。

　select
　date,
　count(distinct fullvisitorid) as user,
　sum(totals.visits) as session,
　sum(totals.newvisits) as new_session,
　sum(totals.pageviews) as pageviews,
　round(sum(totals.pageviews)/sum(totals.visits),2) as pv_per_visit,
　round(sum(totals.bounces)/sum(totals.visits),2)*100 as bounece_rate,
　round(sum(totals.timeonsite)/sum(totals.visits),2) as avg_timeonsite,
　from `{bigqueryプロジェクトID}.{データセット}.ga_sessions_*`
　where _table_suffix = format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
　group by 1
　;

 このようにwhere句で前日分だけ取得するように書いて、前述のエントリーのようにスケジュール化すれば日次の差分更新が可能です。

週次で更新していきたい場合は、where句をこのように書きます。

　where _table_suffix
　between
　format_date('%Y%m%d',date_sub(current_date(), interval 7 day))
　and
　format_date('%Y%m%d',date_sub(current_date(), interval 1 day))

例えば、2020年1月1日から4月30日までの日次テーブルを作成しておいて、5月1日から日次のスケジュールを有効にする場合、作成したテーブルを書きこみ先に指定して「宛先データテーブルの書きこみ設定」は「テーブルに追加する」にチェックをいれてください。すると前日分だけが同じテーブルに追加されます。

「テーブルを上書き」してしまうと、テーブルを上書いて前日分しか残りませんので、ご留意ください。