LookerStudioで作ったダッシュボードの閲覧数を計測してみた  〜 Google Workspace × BigQuery 〜

## まえがき
- ビットキーでAnalytics Engineerをしている三河内です
    - 元上司が今情シスのManagerやっているので呼ばれました
- 社内のあらゆるデータをGoogle CloudのデータウェアハウスサービスであるBigQueryに連携してきて、それを分析・活用していく業務を担っています
    - 過去のアウトプットや「Analytics Engineerって何？」という話は下記を見ていただければ
        - https://www.wantedly.com/companies/bitkeyinc/post_articles/509149
        - https://qiita.com/i_am_miko/items/e7d622cc74ca3b0db807
- ビットキーでは社内のデータのモニタリングに`Looker Studio`を採用しています
- データ屋の性として、作成したダッシュボードが **「どれぐらい見られているのか（活用されているのか）」** を知りたくなったので`Google Workspace`のログから計測してみました
    - Looker StudioはGoogleのサービスであるため、`Google Workspace`のログから計測できる
- 今回は[情シスアドベントカレンダー](https://qiita.com/advent-calendar/2023/bitkey-corp-eng)にお邪魔する形でそのやり方を紹介しようと思います
    - よってこの記事は[株式会社ビットキー 情シス Advent Calendar 2023](https://qiita.com/advent-calendar/2023/bitkey-corp-eng) の8日目の投稿です

## 想定読者
- Looker Studioの閲覧数を計測したいデータ系人材の皆さん
- Google Workspaceのログをいい感じに見たい情シスの方
    - BigQueryでGoogle Workspaceのログを扱いたい情シスの方
---
それではやり方を説明していきます

## ログの連携〜計測までのステップ

1. Google WorkspaceのログをBigQueryにエクスポートする
2. BigQueryでデータを集計し、テーブルを作成する
3. 作成したテーブルからレポートを作成し、継続的に計測できるようにする

## STEP1：Google WorkspaceのログをBigQueryにエクスポートする

- 前提
    - ここの部分は情シスでやってもらったので想像で書いています
        - 正確にはやっといたから「いい感じに見れるようにして」という無茶振りがありました
    - 基本的に[公式のドキュメント](https://support.google.com/a/answer/9079365?hl=ja)を参考に進めていきます
        - 「Google Workspace 管理者アカウント（特権管理者）をGCPプロジェクトのプロジェクト編集者として追加する」とあるので、基本的にそれに該当する方が以下の作業を行うのがスムーズです
- 連携までの手順
    - ログを連携したいGCPプロジェクトを用意する
        - 例：`gws_audit`
    - そのプロジェクトの`IAMと管理`に移動
        - `gapps-reports@system.gserviceaccount.com` という`サービスアカウント`を作成し`編集者`のロールを適用
    - Google Workspaceの管理画面に移動
        - メニューから「レポート」→「BigQuery Export」の順に移動し、以下を設定していく
            - GCPプロジェクトID：ログを連携したいGCPプロジェクトを選択
            - データセット：ログを連携したいBigQueryのデータセット名です
                - 例： `gws__source__audit`
        - 上記設定後保存　※上記のデータセットが作成され、実際に連携が始まるまでにラグがある場合があるようです
        - GCPプロジェクトに移動し、「BigQuery」に移動し、データを確認します
- 参考
    - https://support.google.com/a/answer/9079365?hl=ja

## STEP2：BigQueryでデータを集計し、テーブルを作成する

- 前提
    - STEP1の手順が完了してしばらくすると実際にデータが連携されてきます
        ![Untitled.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176356/e5851018-3d50-d404-2ede-c86a49f7bad3.png)
        - 連携されてくるデータ（テーブル）には2種類あります
            - 使用状況レポート（activity）<= 今回はこちらを利用
            - 監査ログ（usage）
- BigQueryでテーブルを作成するまでの手順
    - まずはどんなデータなのか確認していきます
        - 一旦ざっくりデータを眺める
            ```sql
            # サンプルクエリ
            SELECT
              *
            FROM
              `projectId.datasetId.activity` AS activity
            -- コスト削減のため参照する範囲を限定する（数字の部分を変更すると読み込む日数を変更できます）
            WHERE
            	(DATE(_PARTITIONTIME, "Asia/Tokyo") >= DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 3 day)
                  OR _PARTITIONTIME IS NULL )
            ```
            ```
            💡 ポイント
            
            - BigQueryの料金を節約しよう
                - コンソールからクエリを書くと、右上に読み込むデータ量が表示されます
                - BigQueryではざっくりこのデータ量をベースに料金が算出されます（設定によりますが、1TB 6ドル）
                - 監査ログはデータ量がかなり大きいのではじめから注意しておかないと、かなりの桁の額が請求されました、なんてことになりかねないです。
                    - 参考：https://qiita.com/itkr/items/745d54c781badc148bb9
                - サンプルクエリのように、WHERE句で読み込む日付範囲を限定することで節約できます
                    - ちなみにLIMIT 10とかは全く料金節約にはならないので注意
                    - 本当は select * をやめて読み込む列を選択すると節約になるが、今回はデータを俯瞰したいので*にする
            ```
    - 次にデータを集計し、テーブルを作成します
        - ざっくりデータを見て気づいたと思うのですが、なかなかハードなデータの持ち方をしてます。これをいい感じに見れるようにしつつ集計していきます
            - まずは`Looker Studioの利用状況データを抽出`します
                ```sql
                SELECT
                  -- timestampで持っているものを、わかりやすくかつ日本時間に加工
                  TIMESTAMP_ADD(TIMESTAMP_MICROS(time_usec), INTERVAL 9 hour) AS event_time_jst,
                  DATE(TIMESTAMP_MICROS(time_usec), "Asia/Tokyo") AS event_date_jst,
                  email,
                  event_type,
                  event_name,
                  record_type,
                  --こんな感じにするとdatastudioの利用状況データの列にアクセスできます（各列の説明はデータを見れば伝わってくるので割愛）
                  data_studio.asset_id AS data_studio_asset_id,
                  data_studio.asset_type AS data_studio_asset_type,
                  data_studio.asset_name AS data_studio_asset_name,
                  data_studio.owner_email AS data_studio_owner_email,
                  data_studio.connector_type AS data_studio_connector_type,
                  data_studio.data_export_type AS data_studio_data_export_type,
                  data_studio.embedded_in_report_id AS data_studio_embedded_in_report_id,
                FROM
                  `projectId.datasetId.activity` AS activity
                WHERE
                  (DATE(_PARTITIONTIME, "Asia/Tokyo") >= DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 3 day)
                    OR _PARTITIONTIME IS NULL )
                  -- ここでLookerStudioのデータに限定
                  AND record_type = "data_studio"
                  -- どうも重複が存在しているので重複を除外する
                  AND TRUE QUALIFY ROW_NUMBER() OVER(PARTITION BY event_time_jst, email, event_name, record_type, data_studio_asset_id) = 1
                ```

            - 次に閲覧数を集計していきます。今回は`直近3日の人×ダッシュボードごととの閲覧数`を出してみます
                ```sql
                WITH
                  import_activity AS (
                  SELECT
                    -- timestampで持っているものを、わかりやすくかつ日本時間に加工
                    TIMESTAMP_ADD(TIMESTAMP_MICROS(time_usec), INTERVAL 9 hour) AS event_time_jst,
                    DATE(TIMESTAMP_MICROS(time_usec), "Asia/Tokyo") AS event_date_jst,
                    email,
                    event_type,
                    event_name,
                    record_type,
                    --こんな感じにするとdatastudioの利用状況データの列にアクセスできます
                    data_studio.asset_id AS data_studio_asset_id,
                    data_studio.asset_type AS data_studio_asset_type,
                    data_studio.asset_name AS data_studio_asset_name,
                    data_studio.owner_email AS data_studio_owner_email,
                    data_studio.connector_type AS data_studio_connector_type,
                    data_studio.data_export_type AS data_studio_data_export_type,
                    data_studio.embedded_in_report_id AS data_studio_embedded_in_report_id,
                  FROM
                    projectId.datasetId.activity` AS activity` AS activity
                  WHERE
                    (DATE(_PARTITIONTIME, "Asia/Tokyo") >= DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 3 day)
                      OR _PARTITIONTIME IS NULL )
                    -- ここでLookerStudioのデータに限定
                    AND record_type = "data_studio"
                    -- どうも重複が存在しているので重複を除外する
                    AND TRUE QUALIFY ROW_NUMBER() OVER(PARTITION BY event_time_jst, email, event_name, record_type, data_studio_asset_id) = 1 )
                SELECT
                  --いつ
                  event_date_jst,
                  --どのダッシュボードを（残念ながらどのページを見たかはこのデータでは判別できないみたいです・・・）
                  data_studio_asset_name,
                  --誰が
                  data_studio_owner_email,
                  --何回見た
                  COUNT(1) AS view_counts
                FROM
                  import_activity
                WHERE
                  --閲覧に絞る
                  event_name = "VIEW"
                  -- 計測するダッシュボードを絞り込みたいとき
                  -- AND data_studio_asset_name IN ("hoge", "fuga")
                GROUP BY
                  event_date_jst,
                  data_studio_asset_name,
                  data_studio_owner_email
                ORDER BY
                  event_date_jst DESC,
                  data_studio_asset_name,
                  data_studio_owner_email
                ```
                
            - 最後にこの`集計結果をテーブル`にしてみます
                - 3日分の結果を見ても寂しいので、ここから直近3ヶ月のデータに対象を広げてみます。
                - Create文を追加したクエリを実行することでテーブルを作成できます

                ```sql
                CREATE OR REPLACE TABLE
                  `projectId.tmp_dataset.activity_counts`
                  --STEP1で説明した節約を可能にする設定です、基本的に日付フィールドをしていします
                PARTITION BY
                  event_date_jst AS
                WITH
                  import_activity AS (
                  SELECT
                    -- timestampで持っているものを、わかりやすくかつ日本時間に加工
                    TIMESTAMP_ADD(TIMESTAMP_MICROS(time_usec), INTERVAL 9 hour) AS event_time_jst,
                    DATE(TIMESTAMP_MICROS(time_usec), "Asia/Tokyo") AS event_date_jst,
                    email,
                    event_type,
                    event_name,
                    record_type,
                    --こんな感じにするとdatastudioの利用状況データの列にアクセスできます
                    data_studio.asset_id AS data_studio_asset_id,
                    data_studio.asset_type AS data_studio_asset_type,
                    data_studio.asset_name AS data_studio_asset_name,
                    data_studio.owner_email AS data_studio_owner_email,
                    data_studio.connector_type AS data_studio_connector_type,
                    data_studio.data_export_type AS data_studio_data_export_type,
                    data_studio.embedded_in_report_id AS data_studio_embedded_in_report_id,
                  FROM
                    `projectId.datasetId.activity` AS activity
                  WHERE
                    (DATE(_PARTITIONTIME, "Asia/Tokyo") >= DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 3 month)
                      OR _PARTITIONTIME IS NULL )
                    -- ここでLookerStudioのデータに限定
                    AND record_type = "data_studio"
                    -- どうも重複が存在しているので重複を除外する
                    AND TRUE QUALIFY ROW_NUMBER() OVER(PARTITION BY event_time_jst, email, event_name, record_type, data_studio_asset_id) = 1 )
                SELECT
                  --いつ
                  event_date_jst,
                  --どのダッシュボードを（残念ながらどのページを見たかはこのデータでは判別できないみたいです・・・）
                  data_studio_asset_name,
                  --誰が
                  data_studio_owner_email,
                  --何回見た
                  COUNT(1) AS view_counts
                FROM
                  import_activity
                WHERE
                  --閲覧に絞る
                  event_name = "VIEW"
                  -- 計測するダッシュボードを絞り込みたいとき
                  -- AND data_studio_asset_name IN ("hoge", "fuga")
                GROUP BY
                  event_date_jst,
                  data_studio_asset_name,
                  data_studio_owner_email
                ```
                
            - テーブルができましたね
                
                ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176356/6884f398-6953-d361-447d-7c4a33f802bd.png)

                
    - 上記を`定期的に更新`できるように設定します
        - STEP2のクエリの右上にあるスケジュールをクリック
            
            ![Untitled (2).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176356/ba82ba23-fd5a-0543-c0d9-0da3da6b0c67.png)

            
        - いくつか入力し保存
            
            ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176356/9a152fbf-47a7-9cb6-ca31-ba659589ebc8.png)

            
        - 保存できてることを確認します
            - 左側のメニューから「スケジュールされたクエリ」を選択、以下のように設定したクエリができていたら完成です
                
                ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176356/267d70ef-16f9-029c-0564-3b85ec66e42c.png)

                
                - ※BigQuery Data Transfer APIが無効の場合、有効化が必要です（有効化するだけなら特に料金面などに影響ないので、心配せずに有効化しちゃいましょう）
                    - https://console.cloud.google.com/apis/library/bigquerydatatransfer.googleapis.com?hl=ja
- 参考
    - https://support.google.com/a/answer/9079364?hl=ja
    - https://support.google.com/a/answer/9079965?hl=ja&ref_topic=9079469&sjid=13584563935533791211-AP#zippy=


## STEP3：作成したテーブルからレポートを作成し、継続的に計測できるようにする

- 今回は`Looker Studioでレポートを作成`します
    - 色々やり方はありますが、今回は最も簡易的な方法を紹介します
        - まずSTEP2で作成したテーブルを選択します。
        - 選択したら以下のスクショのように「Looker Studioで調べる」を選択します
            
            ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176356/c8956962-e3ef-ec4b-3ce9-33cc0ce39694.png)

            
        - 選択するとデフォルトのグラフが作成された状態でダッシュボードが開かれるます
            - 以下のように、編集したりして作業していきますが、今回は一旦ここまでにしておきます
                
                ![スクリーンショット 2023-12-08 16.30.46.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/176356/6d811a13-1bd6-7647-56bd-ef3f867bdffe.png)

                
        - ちなみに`Google Sheets`などからもアクセス可能なのでそちらを活用するのもいいと思います

## さいごに

- 今回はGoogle WorkspaceのログをBigQueryに連携して、集計したデータをダッシュボードで確認できるまでを紹介しました
    - もちろんLooker Studioの閲覧数だけでなく、Google Sheetsの閲覧数など他にも様々な利用状況を把握できるので、ぜひやってみてください
- **ビットキーのデータ活用に興味がある方は是非devトークしましょう！**
    - https://jobs.qiita.com/employers/240/dev_talks/441