はじめに
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で加工していきます。
レポートのマスタを抽出する
データの抽出は下記のクエリで行います。
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で切るような予感もします。
データソースのマスタを抽出する
データの抽出は下記のクエリで行います。
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に関する詳しいデータはないわけですが、今や私たちには監査ログ由来のマスタデータがあります。ということで、マスタデータと照合することでジョブ履歴にレポート／データソースの情報を付与できます。
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か月のジョブ履歴をもとに依存関係を積み上げていくための処理です。
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のどのようなアセットがあるか
- それらのオーナーが誰で、どのような公開状態にあるのか
- レポートの利用状況がどうなっているのか
- レポートに由来するコストがどの程度か
- レポートが依存しているデータがどうなっているか
を理解することができるようになります。なかなか強力な味方になりそうですね。再帰クエリも含めてかなりマニアックな内容になりましたが、よろしければ参考にしてもらえると嬉しいです！