BigqueryでGAデータを分析する時のよく使用するクエリ

GAのデータをBigQueryで分析するときのクエリまとめ

最近はどこの会社でもデファルトでGoogleAanlyticsまたはAnalytics360を導入しており、
ウェブマーケティングに使用しているとおもいます。
GAはいろいろウェブの施策を確認したり、ユーザー行動を把握する上で素晴らしいツールではありますが、デファルトUIではユーザーID(cookieベース)ごとの分析はできないのが一つのネックです。
ですが、GA360を導入しているところはGAデータをBigQueryに移行することが可能で、それによってIDベースで分析が可能になります。
GAデータをBQに移行させた上で分析するときによく使用するクエリをまとめました！
※standard sqlを使用しています。
こういうときはどうするのとか質問あればコメントください。ちょこちょこ追加しますー

時間操作でよくつかうもの

UTCをJST（日本時間に変更するクエリ）

--UTCを日本時間に直す
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', created_at, 'Asia/Tokyo') AS JST

--UTCを日本語時間に直す（日付）
FORMAT_TIMESTAMP('%Y-%m-%d', created_at, 'Asia/Tokyo') AS JST

DATE_DIFFのクエリ

時間の差分を出すためのクエリ。日数の差分を出す場合には前処理として使用するカラムをdate型に変換しておく必要があるので注意。
僕はこの変換していないエラーでいつも引っかかるw

--DATE_DEFFを使用する前処理として、前もって使用するカラムの型をDATE型に変しておく
--下記、DATE型にCASTするクエリ
CAST(FORMAT_TIMESTAMP('%Y-%m-%d',hitTime, 'Asia/Tokyo') AS date) AS visit_date

--前処理をした後にやっとこさdate_diffを使用して、日数の差分を出す
DATE_DIFF(purchase_date,visit_date, DAY)

-- （time_jst - purchase)　の解を出してくれる

URLを操作するクエリ

GAではページビューごとにヒットが飛ばされるので、URLを操作する機会がおおい。
またURLをカテゴリごとにまとめて分析することがおおいのでその際にURLをカテゴリの識別子に変換して分析することをおすすめする。

正規表現でURLを変換/操作する

▽GAでURLごとに分析することがおおくなるので、正規表現を使ってURLを操作するクエリは変換や操作するのに使いやすいのでぜひ覚えておいてほしい。

--正規表現にマッチするか判定
REGEXP_MATCH('str','reg_exp') --これは標準SQLでは使用不可かもしれない。。
REGEXP_CONTAINS('str','reg_exp')

--正規表現にマッチする部分を抜き出す
REGEXP_EXTRACT('str', 'reg_exp')

---正規表現でマッチする部分を文字列置換
REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')

URLの一定箇所のみを抜き出すためのクエリ

▽URLである一定の文字列を除外した状態でカラムに収めたいときに使えるクエリ
もしパラメータのみを吐き出したいときや、またhttps://配下のみを抽出したいときに使える

--https://以外の文字列を取得する
REGEXP_EXTRACT(url, 'https://(.*)')

▽パラメータ除外（?配下を除外するクエリ）
パラメータを空白にreplaceするクエリ。これを知って入ればごちゃごちゃしたURLをスッキリさせて分析に使用できるぞー

REGEXP_REPLACE( page.pagePath,"[?](.*)",'')

▽URLのPathをディレクトリごとに分割するためのクエリ

SPLIT(path_all, '/')[SAFE_OFFSET(1)] --pathの１番目のディレクトリを取得,
SPLIT(path_all, '/')[SAFE_OFFSET(2)] --pathの2番目のディレクトリを取得,
SPLIT(path_all, '/')[SAFE_OFFSET(3)] --pathの3番目のディレクトリを取得