Looker Studioで組織内に魔境を作らないための裏技的Tips集

はじめに

Looker StudioはGoogleが無料で提供しているBIツールです。無料でもかなりの機能が揃っており、BIの活用にあたっては大変重宝する存在になっています。ただし、以前「後回しにするとどうしようもなくなる（かもしれない）Looker Studioの権限を理解する」で整理したように、組織管理という観点で難があるのは事実です。

それは例えば、レポートを組織的に管理する機能がなかったり、Google CloudのIAMを利用した権限管理ができなかったりすることが挙げられます。Looker Studio Proという有償版のサービスでは、こうした組織管理を円滑にするような機能拡充がされています。

とはいえ、実は様々なデータをフル活用すると無料版でもできることは多くあります。今回は、そんな裏技的Tipsを5つ紹介しようと思います。

こんな方におすすめ

• Looker Studioの組織的な運用管理に課題感を抱いている
• 「後回しにするとどうしようもなくなる（かもしれない）Looker Studioの権限を理解する」の記載内容のような基本的なLooker Studioの仕組みは把握している
• Google WorkspaceやGoogle Cloudについてはなんとなくわかっている

Tips①：Google Analyticsで利用状況を把握する

BI活用のあるあるが、作ったダッシュボードは本当に使われているのか？という話になります。ダッシュボードは作っておしまいではなく、作ったところがはじまりで、利用状況を把握しながらより改善を進めていくことで活用が浸透していきます。そしてそういったことを進めていくには、活用状況を計測する必要があります。

このとき、一番簡単な方法はGoogle Analyticsをダッシュボードに埋めこむことです。Google Analytics自体の設定については割愛しますが、左上の「ファイル」 > 「レポート設定」をクリックし、「Googleアナリティクスの測定ID」にIDを記入するだけで利用状況が測定できます。

測定したデータはGoogle Analyticsの管理画面で見てもいいですし、Looker Studioでダッシュボードにしてもいいですし、BigQueryにエクスポートして生データを色々探索してみてもいいです。なお、Google Analyticsでの計測になるため、閲覧者の個人特定は難しいという制限があります。

Tips②：監査ログから組織内のレポート／データソース一覧を作成する

Looker Studioは個人で勝手にレポートを作成することが可能になっており、組織内にどのようなアセットがあるのかを網羅的に把握するのが難しいです。

この対応として、Looker Studio APIというものがあり、どうやらこれでアセット一覧を取得できるようなのですが、ドキュメントが乏しく実態はあまりよくわかりません。（組織レベルの権限をもらいたくなかったため私は試しておらず、どなたかわかる方がいれば教えてください！笑）

そこで、代替的な方法として、監査ログのアクティビティログによるマスタデータ作成があります。アクティビティログなので期間内にアクティビティがなければ取得漏れが発生しますが、大枠を掴むためあればこれでも全然問題ありません。

エクスポート設定についてはWorkspaceレベルの権限になるため私は分かっていませんが、「BigQuery での Google Workspace のログとレポート > ログのレポートと BigQuery について」のドキュメントをもとに設定してください。

BigQueryにエクスポートされたデータは下記のようなスキーマになっています。冒頭に共通のデータが格納されており、後半にはサービス別のデータがRECORD型で入っています。Looker Studioに対応するものは旧サービス名であるdata_studioになります。

Looker Studioに関連するログの区分としては、下記のようなものがあります。

これをSQLで加工していきます。

レポートのマスタを抽出する

データの抽出は下記のクエリで行います。

looker_studio_report_master

with user_master as (
  select
    email,
    (select string_agg(o, '/') from unnest(org_unit_name_path) o) as org_unit_name_path
  from
    `project_id.dataset_id.activity`
  where
    org_unit_name_path is not null
  qualify
    row_number() over (partition by email order by time_usec desc) = 1
),

deleted_report as (
  select distinct
    data_studio.asset_id as looker_studio_report_id
  from
    `project_id.dataset_id.activity`
  where
    event_name = 'TRASH'
    and data_studio.asset_type = 'REPORT'
)

select
  a.data_studio.asset_id as looker_studio_report_id,
  a.data_studio.asset_name as looker_studio_report_name,
  a.data_studio.visibility as looker_studio_report_visibility,
  a.data_studio.owner_email as looker_studio_report_owner_email,
  u.org_unit_name_path as looker_studio_report_owner_property,
  'https://lookerstudio.google.com/reporting/' || a.data_studio.asset_id as looker_studio_report_url,
  d.looker_studio_report_id is not null as looker_studio_report_is_deleted,
  timestamp_micros(a.time_usec) as looker_studio_report_checked_at
from
  `project_id.dataset_id.activity` a
left join
  user_master u
  on
    a.data_studio.owner_email = u.email
left join
  deleted_report d
  on
    a.data_studio.asset_id = d.looker_studio_report_id
where
  a.data_studio.asset_type = 'REPORT'
qualify
  row_number() over (partition by looker_studio_report_id order by looker_studio_report_checked_at desc) = 1

下記のようなデータが取得できます。そうそう、欲しかったのはこれですね。

オーナーが誰なのかを把握できるのはもちろん、共有設定もPRIVATE／SHARED_EXPLICITLY／PEOPLE_WITHIN_DOMAIN_WITH_LINK／PUBLIC_ON_THE_WEBのどの状態になっているかが分かるので、不用意な公開がないかも監視できます。

SHARED_EXPLICITLYの具体的な対象者はわかりませんが、おそらくこれは前述のLooker Studio APIで切るような予感もします。

データソースのマスタを抽出する

データの抽出は下記のクエリで行います。

looker_studio_datasource_master

with connector_type as (
  select
    data_studio.asset_id as looker_studio_datasource_id,
    data_studio.connector_type as looker_studio_datasource_connector_type
  from
    `project_id.dataset_id.activity`
  where
    data_studio.asset_type = 'DATA_SOURCE'
    and data_studio.connector_type is not null
  qualify
    row_number() over (partition by looker_studio_datasource_id order by time_usec desc) = 1
),

user_master as (
  select
    email,
    (select string_agg(o, '/') from unnest(org_unit_name_path) o) as org_unit_name_path
  from
    `project_id.dataset_id.activity`
  where
    org_unit_name_path is not null
  qualify
    row_number() over (partition by email order by time_usec desc) = 1
),

deleted_datasource as (
  select distinct
    data_studio.asset_id as looker_studio_datasource_id
  from
    `project_id.dataset_id.activity`
  where
    event_name = 'TRASH'
    and data_studio.asset_type = 'DATA_SOURCE'
)

select
  a.data_studio.asset_id as looker_studio_datasource_id,
  a.data_studio.asset_name as looker_studio_datasource_name,
  c.looker_studio_datasource_connector_type,
  a.data_studio.visibility as looker_studio_datasource_visibility,
  a.data_studio.owner_email as looker_studio_datasource_owner_email,
  u.org_unit_name_path as looker_studio_datasource_owner_property,
  'https://lookerstudio.google.com/datasources/' || a.data_studio.asset_id as looker_studio_datasource_url,
  a.data_studio.embedded_in_report_id as looker_studio_datasource_embedded_in_report_id,
  'https://lookerstudio.google.com/reporting/' || a.data_studio.embedded_in_report_id as looker_studio_datasource_embedded_in_report_url,
  d.looker_studio_datasource_id is not null as looker_studio_datasource_is_deleted,
  timestamp_micros(a.time_usec) as looker_studio_datasource_checked_at
from
  `project_id.dataset_id.activity` a
left join
  connector_type c
  on
    a.data_studio.asset_id = c.looker_studio_datasource_id
left join
  user_master u
  on
    a.data_studio.owner_email = u.email
left join
  deleted_datasource d
  on
    a.data_studio.asset_id = d.looker_studio_datasource_id
where
  a.data_studio.asset_type = 'DATA_SOURCE'
qualify
  row_number() over (partition by looker_studio_datasource_id order by looker_studio_datasource_checked_at desc) = 1

下記のようなデータが取得できます。

レポートと同様のデータに加えて、データソースが何かもわかります。非常に便利ですね。なお、ベースがアクティビティログなので、短期間のデータではレポートよりも取得漏れが発生しやすいのにご注意ください。

Tips③：監査ログからレポートのアクセス状況を把握する

先ほどレポートのマスタデータを作ったので、そのデータをもとのアクティビティログと組み合わせることで、ログデータの分析が行えるようになります。

with user_master as (
  select
    email,
    (select string_agg(o, '/') from unnest(org_unit_name_path) o) as org_unit_name_path
  from
    `project_id.dataset_id.activity`
  where
    org_unit_name_path is not null
  qualify
    row_number() over (partition by email order by time_usec desc) = 1
)

select
  datetime(timestamp_micros(a.time_usec), 'Asia/Tokyo') as event_datetime_jst,
  a.email as viewer_email,
  u.org_unit_name_path as viewer_property,
  l.*
from
  `project_id.dataset_id.activity` a
left join
  user_master u
  on
    a.email = u.email
left join
  `project_id.dataset_id.looker_studio_report_master` l
  on
    a.data_studio.asset_id = l.looker_studio_report_id
where
  event_name = 'VIEW'
  and data_studio.asset_type = 'REPORT'

このデータをダッシュボードにすることによって、誰がどのレポートをどの程度見ているのかまで確認できるようになります。

Tips④：BigQueryのジョブ履歴からレポートの利用状況を把握する

BigQuery native integration in Looker Studio previewの要素の1つとして、Looker StudioがBigQueryを参照すると、INFORMATION_SCHEMAのジョブ履歴のlabelsにlooker_studio_report_idとlooker_studio_datasource_idが含まれるようになりました。

機能全体としては2023年10月のPrivate Preview機能になりますが、labelsへの連携はPreviewに申し込んでいない弊社環境でも12月頃から利用できるようになっていました。

ジョブ履歴自体にはLooker Studioに関する詳しいデータはないわけですが、今や私たちには監査ログ由来のマスタデータがあります。ということで、マスタデータと照合することでジョブ履歴にレポート／データソースの情報を付与できます。

jobs_looker_studio

select
  *  -- スキャン量に注意
from
  `project_id`.`region-region`.INFORMATION_SCHEMA.JOBS j
inner join
  `project_id.dataset_id.looker_studio_report_master` l
  on
    (select value from unnest(j.labels) where key = 'looker_studio_report_id') = l.looker_studio_report_id
where
  creation_time >= timestamp(date_sub(current_date(), interval 1 day))

ジョブ履歴にはクエリ実行時のスキャン量データがあるので、Looker Studioのレポート別にどの程度のクエリコストがかかっているのかを把握することができます。なお、クエリの実行者はデータソースの認証情報に由来するため、レポートの閲覧者≠クエリの実行者となることがあるのにはご注意ください。

Tips⑤：ソーステーブルからレポートまでの依存関係を把握する

最後はここまでの集大成です。再帰クエリを使うので一気に難易度が上がりますが、よろしければ試してみてください。

ダッシュボードを管理運用していこうとすると、作られたダッシュボード自体が適切に利用されているのかはもちろん、そのダッシュボードに利用するデータが健全に管理されているのかも重要になってきます。そこで、データが加工されてくる依存関係を把握することが役立ちます。

BigQueryのジョブ履歴にはreferenced_tablesとdestination_tableというカラムがあり、クエリ実行時に参照元テーブルと作成／更新したテーブルの関係性が判別できます。これは、データの加工にあたっての依存関係を示すものになります。

それに加えて、以前の話からBigQueryのテーブル→Looker Studioのデータソース→Looker Studioのレポートという依存関係も把握できるので、それらの依存関係を積み上げていくと全体の流れが掴めるようになります。

このデータ処理のために、何度も同一の処理を繰り返す再帰クエリという仕組みを活用します。ということで、下記が直近1か月のジョブ履歴をもとに依存関係を積み上げていくための処理です。

looker_studio_lineage

with recursive latest_jobs as (
  select
    creation_time,
    referenced_tables,
    destination_table,
    (select value from unnest(labels) where key = 'looker_studio_report_id') as looker_studio_report_id,
    (select value from unnest(labels) where key = 'looker_studio_datasource_id') as looker_studio_datasource_id
  from
    `project_id`.`region-region`.INFORMATION_SCHEMA.JOBS
  where
    creation_time >= timestamp(date_sub(current_date('Asia/Tokyo'), interval 1 month))
),

report_datasource_dependency as (
  select distinct
    coalesce(
      dm.looker_studio_datasource_name || ' [' || dm.looker_studio_datasource_id || ']',
      j.looker_studio_datasource_id
    ) as reference,
    coalesce(
      rm.looker_studio_report_name || ' [' || rm.looker_studio_report_id || ']',
      j.looker_studio_report_id
    ) as destination,
    cast(null as bool) as is_probably_custom_query
  from
    latest_jobs j
  left join
    `project_id.dataset_id.looker_studio_datasource_master` dm
    on
      j.looker_studio_datasource_id = dm.looker_studio_datasource_id
  left join
    `project_id.dataset_id.looker_studio_report_master` rm
    on
      j.looker_studio_report_id = rm.looker_studio_report_id
  where
    j.looker_studio_report_id is not null
),

datasource_custom_query as (
  select
    looker_studio_datasource_id as looker_studio_datasource_id,
    max(array_length(referenced_tables)) > 1 as is_probably_custom_query  -- 2つ以上のテーブルを参照しているものをカスタムクエリとみなす
  from
    latest_jobs
  where
    looker_studio_report_id is not null
  group by
    all
),

datasource_table_dependency as (
  select
    r.project_id || '.' || r.dataset_id || '.' || r.table_id as reference,
    coalesce(
      dm.looker_studio_datasource_name || ' [' || dm.looker_studio_datasource_id || ']',
      j.looker_studio_datasource_id
    ) as destination,
    dc.is_probably_custom_query
  from
    latest_jobs j
  left join
    unnest(referenced_tables) r
  left join
    `project_id.dataset_id.looker_studio_datasource_master` dm
    on
      j.looker_studio_datasource_id = dm.looker_studio_datasource_id
  left join
    datasource_custom_query dc
    on
      j.looker_studio_datasource_id = dc.looker_studio_datasource_id
  where
    r.project_id is not null
    and looker_studio_report_id is not null
  qualify
    row_number() over (partition by destination order by creation_time desc) = 1
),

tables_dependency as (
  select distinct
    r.project_id || '.' || r.dataset_id || '.' || r.table_id as reference,
    j.destination_table.project_id  || '.' || j.destination_table.dataset_id || '.' || j.destination_table.table_id as destination,
    cast(null as bool) as is_probably_custom_query
  from
    latest_jobs j
  left join
    unnest(referenced_tables) r
  where
    j.destination_table.project_id is not null
    and r.project_id is not null
    -- 参照テーブルのデータセットが、TEMPテーブル以外の一時テーブルではない
    and (not starts_with(r.dataset_id, '_') or starts_with(r.dataset_id, '_script'))
    -- 参照テーブルのテーブルが、TROCCOの転送先BigQueryまたはスキーマ追従のテーブルではない
    and not regexp_contains(r.table_id, r'^LOAD_TEMP_[^_]*_[^_]*_[^_]*_[^_]*_[^_]*_.*|___trocco_schema_sync_[^_]*_.*')
    -- 作成テーブルのデータセットが、TEMPテーブル以外の一時テーブルではない
    and (not starts_with(destination_table.dataset_id, '_') or starts_with(destination_table.dataset_id, '_script'))
    -- 作成テーブルのテーブルが、TROCCOの転送先BigQueryまたはスキーマ追従または転送元BigQueryのテーブルではない
    and not regexp_contains(destination_table.table_id, r'^LOAD_TEMP_[^_]*_[^_]*_[^_]*_[^_]*_[^_]*_.*|___trocco_schema_sync_[^_]*_.*|^trocco_[0-9]{8}_[0-9]{6}_.*')
),

upstream_dependency as (
  select
    *
  from
    datasource_table_dependency

  union all

  select
    *
  from
    tables_dependency
),

root as (
  (
    select
      reference,
      destination,
      reference as destination_for_join,
      is_probably_custom_query,
      destination as final_destination,
      reference || '/' || destination as lineage_path,
      null as lineage_layer
    from
      report_datasource_dependency
  )

  union all

  select
    u.reference,
    u.destination,
    u.reference as destination_for_join,
    u.is_probably_custom_query,
    r.final_destination,
    u.reference || '/' || r.lineage_path as lineage_path,
    coalesce(r.lineage_layer + 1, 0) as lineage_layer
  from
    root r
  inner join
    upstream_dependency u
    on
      r.destination_for_join = u.destination
      and r.lineage_path not like '%' || u.reference || '%'
)

select distinct
  rm.* except (looker_studio_report_checked_at),
  r.reference,
  r.reference not in (select distinct destination from upstream_dependency) as is_root_table,
  r.destination,
  r.is_probably_custom_query,
  r.lineage_path,
  r.lineage_layer
from
  root r
left join
  `project_id.dataset_id.looker_studio_report_master` rm
  on
    r.final_destination like '%' || rm.looker_studio_report_id || '%'
order by
  looker_studio_report_name

下記のようなデータが取得できます。

これによって、ベースとなるテーブル/加工されてできたテーブル/加工されてできたテーブル/Looker Studioのデータソース/Looker Studioのレポートという依存関係を追えるようになります。

さいごに

ここまでにご紹介した5つのTipsを実行すると、

• 組織内にLooker Studioのどのようなアセットがあるか
• それらのオーナーが誰で、どのような公開状態にあるのか
• レポートの利用状況がどうなっているのか
• レポートに由来するコストがどの程度か
• レポートが依存しているデータがどうなっているか

を理解することができるようになります。なかなか強力な味方になりそうですね。再帰クエリも含めてかなりマニアックな内容になりましたが、よろしければ参考にしてもらえると嬉しいです！