[BigQuery]Custom Dimensionsに対するQuery

GoogleのhelpでもBigQueryのCookbookとしてCustom Dimensionsに対するQueryの書き方があります。

■ヒットレベル

SELECT fullVisitorId, visitId, hits.hitNumber, hits.time,
MAX(IF(hits.customDimensions.index=1,
hits.customDimensions.value,
NULL)) WITHIN hits AS customDimension1,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

■セッション・Userレベル

SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

カスタムディメンションはBigQueryだとヒットレベル・セッション/ユーザレベル・プロダクトレベルの3つあります。

hit : hits.customDimensions
session/user : customDimensions
product : hits.product.customDimensions

上の例だとcustomDimension2には数値が入りMAXを取得する感じですが入ってる文字列を抜き出してjoinに使いたいとなった場合どう取得すれば良いかと少し悩みました。

パット見て最初わからなかったのは `customDimensions.value` の書き方の部分なのですが。

国内・海外含めコミュニティやブログを参照しつつ色々ためしてみて、一部抜粋&改変でちょっとイマイチな部分がありますが、最終的にこんな感じで書きました。

  (
  SELECT
    t2.id AS id,
    t2.query AS query
  FROM (
    SELECT
      cd.value AS id,
      hits.page.searchKeyword AS query
    FROM
      `xxxxxxxxxxxxxxxxxxxxxx.xxxxxxxxxxxga_sessions_2017092*`,
      unNEST(customDimensions) cd,
      unNEST(hits) hits
    WHERE
      cd.index = 1
      AND cd.value IS NOT NULL
      AND hits.page.searchKeyword IS NOT NULL
    GROUP BY
      cd.value,
      hits.page.searchKeyword) t2 ) t1

WITHIN RECORDを使うやり方やFLATTENを使うやり方、UNNESTを使うやり方などもあり、FLATTEN/UNNESTはRECORDタイプをフラット化して取り扱うやり方ですね。
結構customDimensionsをBQから抜いてる人は多そうですがネット上にはあまり情報が無いなという印象。普段からQueryを書き慣れている人からするとRECORDタイプとかもそんなに苦もなく処理できるんですかね。