Google Analytics 360 + BigQueryのド基本を学ぶ

#対象の人
これから会社などでGoogle Analytics 360 + BigQueryを使っていく事になった人が最初に任されがちな依頼例を実際に
Google Analytics 360用のSQLを書いて紹介します。

#目標
では今回はこんな感じのものが出せるようになるのを目指してみましょう

行	Date	PV	Sessions
1	2021-01-01	2518630	341160
2	2021-01-02	2617295	351097
3	2021-01-03	2517898	346030
4	2021-01-04	2624950	351061
5	2021-01-05	2515262	342054
6	2021-01-06	2417454	331461

#作業
##1.PVを出す

standardSQL

SELECT
 COUNT(fullVisitorId) AS PV
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`, UNNEST(hits) AS hits
  --パブリックデータセットの bigquery-public-data:google_analytics_sample を使ってます
where
  _TABLE_SUFFIX BETWEEN '20210101' AND '20210106'
  AND hits.type = 'PAGE'
;

###結果

行	PV
1	24652378

hits.type = ‘PAGE’ に絞って
fullVisitorId(ユニーク ユーザー ID)をCOUNTすることによってPVを出せます。
2021-01-01～2021-01-06までの期間の全てのPVを出してます。
hitsはネストされたRECORD型のカラムになってるので、UNNEST関数でフラットにします。

##2.日付を出す

standardSQL

SELECT
 CONCAT(SUBSTR(Date, 0, 4), '-', SUBSTR(Date, 5, 2), '-', SUBSTR(Date, 7, 2)) AS Date,
 COUNT(fullVisitorId) AS PV
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`, UNNEST(hits) AS hits
  --パブリックデータセットの bigquery-public-data:google_analytics_sample を使ってます
where
  _TABLE_SUFFIX BETWEEN '20210101' AND '20210106'
  AND hits.type = 'PAGE'
GROUP BY
  Date
ORDER BY
  Date ASC
;

###結果

行	Date	PV
1	2021-01-01	2518630
2	2021-01-02	2617295
3	2021-01-03	2517898
4	2021-01-04	2624950
5	2021-01-05	2515262
6	2021-01-06	2417454

さっきのは2021-01-01～2021-01-06までの合計のPVでしたが今回は日別のPVになります。
GROUP BY句でDateをグループ化することにより日別に分けられたPVが出せます。
日付はCONCAT関数を使って年-月-日という形にしてます。
昇順、降順はORDER BY句を使います。
今回はDate(日付)を昇順(ASC)にしてます。

##3.セッションを出す

standardSQL

SELECT
 CONCAT(SUBSTR(Date, 0, 4), '-', SUBSTR(Date, 5, 2), '-', SUBSTR(Date, 7, 2)) AS Date,
 COUNT(fullVisitorId) AS PV,
 COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitNumber AS string))) AS Sessions
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*`, UNNEST(hits) AS hits
  --パブリックデータセットの bigquery-public-data:google_analytics_sample を使ってます
where
  _TABLE_SUFFIX BETWEEN '20210101' AND '20210106'
  AND hits.type = 'PAGE'
GROUP BY
  Date
ORDER BY
  Date ASC
;

###結果

行	Date	PV	Sessions
1	2021-01-01	2518630	341160
2	2021-01-02	2617295	351097
3	2021-01-03	2517898	346030
4	2021-01-04	2624950	351061
5	2021-01-05	2515262	342054
6	2021-01-06	2417454	331461

fullVisitorId(ユニークユーザーID)とvisitNumber(ユーザーのセッション数)を文字列にしたものを
CONCATすることによりユニークユーザーIDとセッション数を紐づき
ユニークユーザーIDあたりのセッション数がわかります。そのユニーク数をカウントしたのがセッション数になります。