エクスチュア株式会社ブログホームエクスチュア株式会社コーポレートサイトお問い合わせエクスチュア総合研究所ブログへのリクエストエクスチュア株式会社ブログマーケティングテクノロジーの情報やノウハウ・TIPS、エクスチュア社の情報を発信。【ブログネタ募集】ご興味のある分野を教えてください!ご要望の内容を記事に起こします!メニューの「ブログへの」リクエストよりお送りください。360のネストされたデータをフラット変換するSQLこんにちは、エクスチュアCTOの権です。今回はGA360のBigQueryエクスポートデータについてです。GA360データをBigQueryからサードパーティDWHに入れようとしたりする時は、hitsデータなど「入れ子」になっているJSON形式のデータを、フラットな1行に変換してやると扱いやすくなります。いうわけで、お馴染みのLondonCycleHelmetのサンプルテーブルを変換してみます。hitsこのクエリを実行すると、ヒット毎に一行ずつフラットになった結果が返ります。hitsというネストされたデータをunnestして一行ずつに展開するとこうなります。ヒットタイムスタンプまた、ヒット毎のタイムスタンプはhits.timeカラムに、セッション開始からの経過時間がミリ秒で格納されるので、この「ミリ秒」を「秒」に直した上でvisitStartTimeに足せばヒットタイムスタンプになります。ついでにタイムスタンプを扱いやすくするためにtimestamp_seconds関数で数値→タイムスタンプに変換しておきます。hts.timeをミリ秒のままvisitStartTimeに足してしまってた誤りを修正しました。同名のカラムtransactionIdとcurrencyCodeは同じ名前の別カラムが存在するので、別名をつけて抽出します。カスタムディメンションとカスタム指標あとメンドクサイのはカスタムディメンションとカスタム指標ですね。元のデータでは、インデックス別に縦方向に格納されてます。hits.customDimensions/hits.customMetricsをunnestした上で、各ディメンション/指標のインデックス番号別に別々のカラムとして横方向に展開して行きます。する事で、一つのヒット=1行=カスタムディメンションもカスタム指標も横方向に1行となります。このようにフラットな一行に変換すれば、CSVファイルに書きだしたり、サードパーティDWHとの連携がしやすくなります。360のBigQueryエクスポートデータをフラット変換する方法についてでした。弊社では各Web解析ツールのデータを様々なビッグデータ分析ツールを使って分析する業務を行っております。お問い合わせはこちらからどうぞ。2019レポート:初日目こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。「生Webビーコン」をBigQu...こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。Storage間でファイルを並列コピ...こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。BigQueryにロードしたデータフィードをDataStudioで...こんにちは、エクスチュアCTOの権です。サンプリングの有無こんにちは。AAとGAの違いの2段目にいきます。今月のGCP課金額をslackに自動的に書き込むこんにちは、エクスチュアCTOの権です。