こんにちは、エクスチュアCTOの権です。
今回はGA360のBigQueryエクスポートデータについてです。
GA360データをBigQueryからサードパーティDWHに入れようとしたりする時は、hitsデータなど「入れ子」になっているJSON形式のデータを、フラットな1行に変換してやると扱いやすくなります。
というわけで、お馴染みのLondonCycleHelmetのサンプルテーブルを変換してみます。
#standardSQL
SELECT
visitorId,
fullVisitorId,
visitNumber,
visitId,
visitStartTime,
PARSE_DATE('%Y%m%d', date) AS hit_date,
trafficSource.referralPath,
trafficSource.campaign,
trafficSource.source,
trafficSource.medium,
trafficSource.keyword,
trafficSource.adContent,
device.browser,
device.browserVersion,
device.operatingSystem,
device.operatingSystemVersion,
device.isMobile,
device.flashVersion,
device.javaEnabled,
device.language,
device.screenColors,
device.screenResolution,
hits.hitNumber,
hits.time,
TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)) AS hit_timestamp,
hits.hour,
hits.minute,
hits.isSecure,
hits.isInteraction,
hits.referer,
hits.page.pagePath,
hits.page.hostname,
hits.page.pageTitle,
hits.page.searchKeyword,
hits.page.searchCategory,
hits.transaction.transactionId AS hit_transactionId,
hits.transaction.transactionRevenue,
hits.transaction.transactionTax,
hits.transaction.transactionShipping,
hits.transaction.affiliation,
hits.transaction.currencyCode AS hit_transaction_currencyCode,
hits.transaction.localTransactionRevenue,
hits.transaction.localTransactionTax,
hits.transaction.localTransactionShipping,
hits.item.transactionId,
hits.item.productName,
hits.item.productCategory,
hits.item.productSku,
hits.item.itemQuantity,
hits.item.itemRevenue,
hits.item.currencyCode,
hits.item.localItemRevenue,
hits.contentInfo.contentDescription,
hits.appInfo.name,
hits.appInfo.version,
hits.appInfo.id,
hits.appInfo.installerId,
hits.exceptionInfo.description,
hits.exceptionInfo.isFatal,
hits.eventInfo.eventCategory,
hits.eventInfo.eventAction,
hits.eventInfo.eventLabel,
hits.eventInfo.eventValue,
(SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension1,
(SELECT MAX(IF(index=2, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension2,
(SELECT MAX(IF(index=3, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension3,
(SELECT MAX(IF(index=4, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension4,
(SELECT MAX(IF(index=5, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension5,
(SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metrics1,
(SELECT MAX(IF(index=2, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metrics2,
(SELECT MAX(IF(index=3, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metrics3,
(SELECT MAX(IF(index=4, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metrics4,
(SELECT MAX(IF(index=5, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metrics5,
hits.type
FROM
`google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`,
UNNEST(hits) AS hits
このクエリを実行すると、ヒット毎に一行ずつフラットになった結果が返ります。
hitsというネストされたデータをunnestして一行ずつに展開するとこうなります。
ヒットタイムスタンプ
また、ヒット毎のタイムスタンプはhits.timeカラムに、セッション開始からの経過時間がミリ秒で格納されるので、この「ミリ秒」を「秒」に直した上でvisitStartTimeに足せばヒットタイムスタンプになります。
ついでにタイムスタンプを扱いやすくするためにtimestamp_seconds関数で数値→タイムスタンプに変換しておきます。
※2018年11月26日訂正 hts.timeをミリ秒のままvisitStartTimeに足してしまってた誤りを修正しました。
同名のカラム
transactionIdとcurrencyCodeは同じ名前の別カラムが存在するので、別名をつけて抽出します。
カスタムディメンションとカスタム指標
あとメンドクサイのはカスタムディメンションとカスタム指標ですね。
元のデータでは、インデックス別に縦方向に格納されてます。
hits.customDimensions/hits.customMetricsをunnestした上で、各ディメンション/指標のインデックス番号別に別々のカラムとして横方向に展開して行きます。
こうする事で、一つのヒット＝1行＝カスタムディメンションもカスタム指標も横方向に1行となります。
このようにフラットな一行に変換すれば、CSVファイルに書きだしたり、サードパーティDWHとの連携がしやすくなります。
今回はGoogle Anallytics 360のBigQueryエクスポートデータをフラット変換する方法についてでした。
弊社では各Web解析ツールのデータを様々なビッグデータ分析ツールを使って分析する業務を行っております。
お問い合わせはこちらからどうぞ。
ブログへの記事リクエストはこちらまで
