Google BigQueryで数値データをTIME型に変換する(Google Analyticsで取得した時間データを同じように見たい)

#Google BigQuery

しんや

2023.11.30

小ネタです。

こちらのエントリで紹介したように、今年のタイミングでクラスメソッドにおける旧GA(Universal Analytics)のデータを退避、移行する作業を行っていました。

この作業の過程で、「旧GAで時間データとして見えていたものを、BigQuery環境でも同じように見せたい/見たい」という要望が挙がりました。当エントリではその対応の過程をちょっとした備忘録としてまとめておこうと思います。

旧GAで見えていたデータの在り方

旧GA環境へは、担当者はBIツールを介してデータを参照していました。該当する項目はavgTimeOnPage(平均ページ滞在時間)。以下のようにhh:mm:ssの形式で表示されていました。

Google Analytics APIで取得したデータの在り方

ですが、Google Analytics APIを介して取得したデータの形式は以下のような数値形式(小数点を含む形)になっていました。「数値」から「時間」(NOTタイムスタンプ)の形にデータを変換する必要が出てきたのです。

dim_date dim_pagePath met_pageviews met_uniquePageviews met_avgTimeOnPage met_avgPageLoadTime
:
:
2023-xx-xx xxxxxxxxxxxxxxxxxxx.index.html 99999 99999 130.98447893569846 4.84884

実践した事

目的を実現するために実践したこと、手順の流れは以下のようになりました。

とりあえずGoogle Analyticsから抽出したデータはそのままの形式でBigQueryに投入してしまい、SQLで何とかすることにした(生データのままでテーブルに格納)
上記で作成したテーブルとは別に、該当項目部分が時間(TIME)型のテーブルを別途用意
SQL関数を使い「小数点を含む数値データ」を「TIME型のデータ」に変換、別途用意したテーブルに投入
- 小数点以下は切り捨て、整数にキャスト
- 整数値情報をTIME型情報に変換

生データ投入用のテーブル定義。小数点を含む数値情報が入るようにデータ型をFLOAT64としています。

DROP TABLE IF EXISTS `projectxxxx.datasetyyyyyy.tablezzz_rowdata`;
CREATE TABLE IF NOT EXISTS `projectxxxx.datasetyyyyyy.tablezzz_rowdata`
(
  dim_date DATE OPTIONS(description="対象日付"),
  dim_pagePath STRING OPTIONS(description="ページパス"),

  met_pageviews INT64 OPTIONS(description="ページビュー数"),
  met_uniquePageviews INT64 OPTIONS(description="ページ別訪問数"),
  met_avgTimeOnPage FLOAT64 OPTIONS(description="平均ページ滞在時間"),
  met_avgPageLoadTime FLOAT64 OPTIONS(description="平均読み込み時間(秒)")
)
OPTIONS (
  description="旧GA(UA):ページ別PV&エンゲージメント(生データ格納用)"
);

そしてこちらが変換済のデータを格納する用のテーブル定義。該当項目の定義はTIME型となっています。

DROP TABLE IF EXISTS `projectxxxx.datasetyyyyyy.tablezzz_converted`;
CREATE TABLE IF NOT EXISTS `projectxxxx.datasetyyyyyy.tablezzz_converted`
(
  dim_date DATE OPTIONS(description="対象日付"),
  dim_pagePath STRING OPTIONS(description="ページパス"),

  met_pageviews INT64 OPTIONS(description="ページビュー数"),
  met_uniquePageviews INT64 OPTIONS(description="ページ別訪問数"),
  met_avgTimeOnPage TIME OPTIONS(description="平均ページ滞在時間"),
  met_avgPageLoadTime FLOAT64 OPTIONS(description="平均読み込み時間(秒)")
)
OPTIONS (
  description="旧GA(UA):ページ別PV&エンゲージメント(変換済)"
);

検証用に構築したSQL文が以下の内容。対象となる項目(+1)について変換の遷移を確認するために幾つか項目を増やしていますが、最終的にはTIME関数にそれぞれの時間情報(時/分/秒)を渡し、TIME型データを作り上げています。

SELECT
  dim_date,
  dim_pagePath,
  met_pageviews,
  met_uniquePageviews,

  /** [平均ページ滞在時間] */
  /** 生データ値. */
  met_avgTimeOnPage, 
  /** 一旦小数点以下を切り捨て。この時点ではまだFLOAT64型. */
  TRUNC(met_avgTimeOnPage) AS val_float, 
  /** FLOAT型からINT型にキャストして整数値とする. */
  CAST(TRUNC(met_avgTimeOnPage) AS INT64) AS val_int, 

  /** MAKE_INTERVAL関数とEXTRACT関数を使い、それぞれの時間単位に相当する情報(時/分/秒)を作成。
      それらの値をTIME関数に渡してTIME型の値を作成. */
  TIME(
    EXTRACT(HOUR FROM MAKE_INTERVAL(SECOND => CAST(TRUNC(met_avgTimeOnPage) AS INT64))),
    EXTRACT(MINUTE FROM MAKE_INTERVAL(SECOND => CAST(TRUNC(met_avgTimeOnPage) AS INT64))),
    EXTRACT(SECOND FROM MAKE_INTERVAL(SECOND => CAST(TRUNC(met_avgTimeOnPage) AS INT64)))
  ) val_time,
 
  /** [平均読み込み時間(秒)] */
  met_avgPageLoadTime,
  /** はROUND関数を使い小数点第3桁で四捨五入. */
  ROUND(met_avgPageLoadTime,2)　

FROM `projectxxxx.datasetyyyyyy.tablezzz_rowdata`
;

以下が出力結果。データ型の変遷を経て情報がTIME型に変換されていることを確認出来ました。

あとは上記SQL文を活用してINSERT文を構築し、データ投入して完了です。

BigQuery 特集: データ操作（DML） | Google Cloud 公式ブログ

INSERT  `projectxxxx.datasetyyyyyy.tablezzz_converted`(
  dim_date,
  dim_pagePath,
  met_pageviews,
  met_uniquePageviews,
  met_avgTimeOnPage,
  met_avgPageLoadTime)
SELECT
  dim_date,
  dim_pagePath,
  met_pageviews,
  met_uniquePageviews,

  TIME(
    EXTRACT(HOUR FROM MAKE_INTERVAL(SECOND => CAST(TRUNC(met_avgTimeOnPage) AS INT64))),
    EXTRACT(MINUTE FROM MAKE_INTERVAL(SECOND => CAST(TRUNC(met_avgTimeOnPage) AS INT64))),
    EXTRACT(SECOND FROM MAKE_INTERVAL(SECOND => CAST(TRUNC(met_avgTimeOnPage) AS INT64)))
  ) val_time,
  ROUND(met_avgPageLoadTime,2)

FROM `projectxxxx.datasetyyyyyy.tablezzz_rowdata`
;