GA4のエンゲージメント時間をLookerで見やすく可視化したい！

この記事では、BigQueryに連携したGA4のデータをLookerで可視化・分析する際に、value_formatパラメータをフル活用して、エンゲージメント時間（ミリ秒単位）を「〇時間〇分〇秒」形式にフォーマット変換して表示する方法を紹介します。

色々と試行錯誤を繰り返したので、ぜひ最後まで読んでみてください！

GA4のエンゲージメント指標

まず、可視化対象のGA4のエンゲージメント関連の指標ですが、GA4で新たに追加された新指標で、UI上でもかなり目立つところに配置されているので、まだ使ってないけど気になっているという方も多いのではと思います。

ABCグループでも従来は主にPV数やセッション数を見ていましたが、従来からの指標と合わせてエンゲージメント関連の指標を見る機会が多くなってきています。

これらの指標をLookerでも確認できるようにしようというのが事のはじまりで、BigQueryに連携されたGA4のスキーマを確認したところ、event_paramsのengagement_time_msecの値を使えば良さそうだということがわかりました。

参考：[GA4] ユーザー エンゲージメント - アナリティクス ヘルプ

https://support.google.com/analytics/answer/11109416

早速LookerのLookMLでdimensionとmeasureを準備して、

dimension: engagement_time_msec {
  type: number
  sql: (SELECT value.int_value FROM UNNEST(${TABLE}.event_params) WHERE key = 'engagement_time_msec') ;;
  hidden: yes
}
measure: engagement_time_msec_per_session {
  label: "セッションあたりの平均エンゲージメント時間"
  sql: 
    SAFE_DIVIDE(
      SUM(${engagement_time_msec}), -- エンゲージメント時間の合計
      COUNT(DISTINCT ${session_id}) -- セッション数の合計
    ) ;;
}

Exploreで可視化すると・・・

画像のように桁数の多い数値が抽出されてしまいました。

GA4のエンゲージメント時間はミリ秒単位で計測されているので当然と言えば当然ですが、この数字を見ても何時間・何分・何秒とすぐにイメージできませんよね。

「1時間23分45秒」などとフォーマットを変換して可視化したいところです。

やり方として真っ先に思い浮かんだのがSQLで文字列として整える方法でした。

が、表形式では問題ないものの、折れ線グラフなどでの可視化ができない（文字列形式では量的意味が失われる）ので、早々に別の方法を検討することにしました。

value_formatの活用

次に考えたのが表示されるフォーマット書式のみを変換する方法です。

参考：value_format  |  Looker  |  Google Cloud

https://cloud.google.com/looker/docs/reference/param-field-value-format

Lookerのvalue_formatは、数値データの表示形式を制御するためのパラメータで、数値を特定の書式、例えば通貨記号付きの3桁区切りやパーセント表示などに整形して表示することができます。

ページ内にいくつか基本的な書式の例示がのっているので、これまでは記載があるものぐらいしか使用していなかったのですが、よく読んでみると

value_format accepts an Excel-style formatting string.

とのことで、どうやらエクセルの（より正確に言うと、ECMA-376で標準化されている）書式指定を利用することができるようです。

参考：Review guidelines for customizing a number format - Microsoft Support

https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5

上記のページなどを参考にLookMLに落とし込んでみました。

measure: engagement_time_msec_per_session {
  label: "セッションあたりのエンゲージメント時間"
  type: number
  value_format: "[h]:mm:ss"
  sql:
    SAFE_DIVIDE(
      SUM(${engagement_time_msec} /1000), -- エンゲージメント時間の合計
      COUNT(DISTINCT ${session_id}) -- セッション数の合計
    ) /86400 ;;
}

ポイント

• type: numberを指定する。
• ミリ秒を1000で割って秒単位に変換する。
• さらに86400（1日の秒数）で割る。

value_formatの[h]:mm:ssは[hh]:m:sなどとすることもできます。

1桁の数値の10の位をゼロ埋めするかしないかの違いなので、お好みで選択してください。

hを囲む[]がないと24時間を超えた場合、例えば25時間だと1と表示されてしまうのでご注意ください。

Exploreで確認すると・・・

先程のミリ秒の数値形式より格段に可読性があがりましたね！

数値形式のままフォーマットを変換しているだけなので、量的意味も保持され、グラフも問題なく描写できています。

Y軸の値の部分が変換後のフォーマットで表示されているところもポイントです。

（別のやり方で、htmlパラメータとLiquidで表示フォーマットを変換をすることもできるのですが、その場合は軸の数値は置き換わりません。）

value_formatで日本語を扱う

続いて、「@時間@分@秒」のような日本語の書式への変換を試してみます。

こちらは\でエスケープした”で日本語文字列を囲むことで簡単に実現できます。

measure: engagement_time_msec_per_session {
  label: "セッションあたりのエンゲージメント時間"
  type: number
  value_format: "[h]\"時間\"mm\"分\"ss\"秒\""
  sql:
    SAFE_DIVIDE(
      SUM(${engagement_time_msec} /1000), -- エンゲージメント時間の合計
      COUNT(DISTINCT ${session_id}) -- セッション数の合計
    ) /86400 ;;
}

これでも十分いい感じですが、0時間や00分は意味を持たないので、できれば削りたいところ・・・。

そこで、もうひと工夫。

value_formatで条件分岐を行う

Lookerのベストプラクティスの1つとして、value_formatを使用した条件付き書式設定の方法が紹介されています。こちらも活用して、1時間未満は「@分＠秒」、1分未満は「@秒」と表示されるように条件を設定してみました。

参考：value_format を使用した条件付き書式設定 | Looker | Google Cloud

https://cloud.google.com/looker/docs/best-practices/how-to-conditional-formatting-with-value_format?hl=ja

measure: engagement_time_msec_per_session {
  label: "セッションあたりのエンゲージメント時間"
  type: number
  value_format: "[<0.000694]s\"秒\"; [<0.041666]m\"分\"s\"秒\"; [h]\"時間\"m\"分\"s\"秒\""
  sql: 
    FLOOR(
      SAFE_DIVIDE(
        SUM(${engagement_time_msec} /1000), -- エンゲージメント時間の合計
        COUNT(DISTINCT ${session_id}) -- セッション数の合計
      )
    ) /86400 ;;
}

理想的な形で可視化することができましたね。Looker最高！！

まとめ

今回はGA4のエンゲージメント時間関連の指標をLookerで可視化する際に、ミリ秒単位の値を量的意味を保持したまま可読性の高いフォーマットに変換する方法をご紹介しました。条件分岐も活用したこによりvalue_formatが長くなってしまったので、named_value_formatを定義してvalue_format_nameパラメータで指定するなど、実装の際にはLookMLの可読性にも配慮したいところです。

今回のテクニックはエンゲージメント時間以外にも動画の再生時間やイベントとイベントの間隔などを可視化する際にも活用できそうですね。

value_formatがここまで柔軟性が高いというところが今回の驚きポイントでした。ただし現時点ではLookerのvalue_formatではエクセルの書式を全て使えるわけではない（at this time, date formatting, color formatting, and hexadecimal conversion are not supported in Looker.）そうなので、今後もケースバイケースで最適な可視化方法を探っていければと思います。