LoginSignup
5
1
株式会社ビットキー 情シスAdvent Calendar 2023Day 8bitkey
@i_am_miko(Takuya Mikouchi)in株式会社ビットキー / Bitkey Inc.

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

Last updated at Posted at 2023-12-08

まえがき

  • ビットキーでData Engineer/Analytics Engineerをしている三河内です
    • 元上司が今情シスのManagerやっているので呼ばれました
  • 社内のあらゆるデータをGoogle CloudのデータウェアハウスサービスであるBigQueryに連携してきて、それを分析・活用していく業務を担っています
  • ビットキーでは社内のデータのモニタリングにLooker Studioを採用しています
  • データ屋の性として、作成したダッシュボードが 「どれぐらい見られているのか（活用されているのか）」 を知りたくなったのでGoogle Workspaceのログから計測してみました
    • Looker StudioはGoogleのサービスであるため、Google Workspaceのログから計測できる
  • 今回は情シスアドベントカレンダーにお邪魔する形でそのやり方を紹介しようと思います

想定読者

  • Looker Studioの閲覧数を計測したいデータ系人材の皆さん
  • Google Workspaceのログをいい感じに見たい情シスの方
    • BigQueryでGoogle Workspaceのログを扱いたい情シスの方

それではやり方を説明していきます

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

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

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

  • 前提
    • ここの部分は情シスでやってもらったので想像で書いています
      • 正確にはやっといたから「いい感じに見れるようにして」という無茶振りがありました
    • 基本的に公式のドキュメントを参考に進めていきます
      • 「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」に移動し、データを確認します
  • 参考

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

  • 前提
    • STEP1の手順が完了してしばらくすると実際にデータが連携されてきます
      Untitled.png
      • 連携されてくるデータ（テーブル）には2種類あります
        • 使用状況レポート（activity）<= 今回はこちらを利用
        • 監査ログ（usage）
  • BigQueryでテーブルを作成するまでの手順

    • まずはどんなデータなのか確認していきます

      • 一旦ざっくりデータを眺める 
        # サンプルクエリ
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の利用状況データを抽出します

          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日の人×ダッシュボードごととの閲覧数を出してみます

          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文を追加したクエリを実行することでテーブルを作成できます
          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

    • 上記を定期的に更新できるように設定します

      • STEP2のクエリの右上にあるスケジュールをクリック

        Untitled (2).png

      • いくつか入力し保存

        image.png

      • 保存できてることを確認します

        • 左側のメニューから「スケジュールされたクエリ」を選択、以下のように設定したクエリができていたら完成です

          image.png

  • 参考

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

  • 今回はLooker Studioでレポートを作成します
    • 色々やり方はありますが、今回は最も簡易的な方法を紹介します

      • まずSTEP2で作成したテーブルを選択します。

      • 選択したら以下のスクショのように「Looker Studioで調べる」を選択します

        image.png

      • 選択するとデフォルトのグラフが作成された状態でダッシュボードが開かれるます

        • 以下のように、編集したりして作業していきますが、今回は一旦ここまでにしておきます

          スクリーンショット 2023-12-08 16.30.46.png

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

さいごに

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
1