質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.06%

[SQL][Google Analytics] NEST されたカラムで複数のキーと値を取得したい

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 335

score 5

Google Analytics 4 のイベントを BigQuery テーブルに流し込んでいます。
ひとつのキーをもとにデータを取得することはできるんですが、そのキーと同じレコードにある別のキーに格納されている値を取得するにはどうしたらいいのでしょうか?

具体的なイメージで言うと、記事名でアクセス数ランキングをだして、その補助データとして著者名を別カラムで出したい(著者名は同じレコードの中に別のキーで格納されている)といったような用途です。

出力したいデータのイメージ

  • article_name の閲覧数を集計して cnt を出し、降順に並べる
  • どの著者が書いた記事かをわかりやすくするために、author_name カラムを横に出す
  • article_name と author_name は同一レコードの同じカラムの中にネストされて入っていて、それを取り出したい
        ↓集計対象       ↓補助データ
+-----+--------------+-------------+-----+
| Row | article_name | author_name | cnt |
+-----+--------------+-------------+-----+
| 1   | My Article A | Author A    | 20  |
| 2   | My Article D | Author A    | 18  |
| 3   | My Article C | Author C    | 11  |
| 4   | My Article B | Author B    | 9   |
  ...
+-----+--------------+-------------+-----+

環境

Google Analytics 4
Google Tag Manager でイベントを設定

このようなテーブルスキーマになっていて、event_params.key の中に article_name author_name などのキーがあり、event_params.value.string_value の中に取得したい値が入っています。

イメージ説明

テーブルの中身はこのようになっています。

+-----+------------+-----------------+--------------+------------------+---------------------------------+
| Row | event_date | event_timestamp | event_name   | event_params.key | event_params.value.string_value |
+-----+------------+-----------------+--------------+------------------+---------------------------------+
| 1   | 20201127   | 160394324324231 | view_article | article_name     | My Article A                    |
|     |            |                 |              | author_name      | Author A                        |
|     |            |                 |              | hogehoge key1    | hogehoge value1                 |
|     |            |                 |              | hogehoge key2    | hogehoge value2                 |
| 2   | 20201127   | 160394324324112 | view_article | article_name     | My Article B                    |
|     |            |                 |              | author_name      | Author B                        |
|     |            |                 |              | hogehoge key1    | hogehoge value1                 |
|     |            |                 |              | hogehoge key2    | hogehoge value2                 |
 ...
+-----+------------+-----------------+--------------+------------------+---------------------------------+

試したこと

記事名をもとにしたアクセス数ランキングは出すことができました。

#standardSQL

WITH _data AS (
    SELECT 
        value.string_value AS article_name 
    FROM 
        `my-new-project.analytics_000000000.events_*`, 
        UNNEST(event_params) 
    WHERE 
        key = 'article_name'
        AND event_name = 'view_article'
)

SELECT
    article_name,
    COUNT(*) AS cnt
FROM
    _data
GROUP BY
    1
ORDER BY
    2 DESC

↓結果

+-----+--------------+-----+
| Row | article_name | cnt |
+-----+--------------+-----+
| 1   | My Article A | 20  |
| 2   | My Article D | 18  |
| 3   | My Article C | 11  |
| 4   | My Article B | 9   |
  ...
+-----+--------------+-----+

ここの article_name の横に author_name のカラムを追加したいので、CASE WHEN を使うのがいいのかなと思いました。
しかし、結果としては author_name はすべて null になってしまっているようでした。おそらく別のレコードとして扱われてしまっているのでしょうか。。?

#standardSQL

WITH _data AS (
    SELECT 
        CASE WHEN key = 'article_name' THEN value.string_value
        END AS article_name,
        CASE WHEN key = 'author_name' THEN value.string_value
        END AS author_name
    FROM 
        `my-new-project.analytics_000000000.events_*`, 
        UNNEST(event_params) 
    WHERE 
        event_name = 'view_article'
)

SELECT
    article_name,
    MAX(author_name),
    COUNT(*) AS cnt
FROM
    _data
GROUP BY
    1
ORDER BY
    3 DESC

↓結果

+-----+--------------+-------------+-----+
| Row | article_name | author_name | cnt |
+-----+--------------+-------------+-----+
| 1   | My Article A | null        | 20  |
| 2   | My Article D | null        | 18  |
| 3   | My Article C | null        | 11  |
| 4   | My Article B | null        | 9   |
  ...
+-----+--------------+-------------+-----+

追記:author_name で group by して降順にすると、著者名はちゃんと現れるのですが今度は article_name がすべて null になってしまいました。同じレコードに、article_name と author_name が両立して入っている状態を作れていないのでしょうか・・


追記2:各レコードのいずれかのカラムが null になってしまうのは UNNEST してしまってるからなと思い、このようなクエリでただカラムとキーを指定できるかなと試してみました。

SELECT 
    CASE WHEN e.event_params.key = 'article_name' THEN e.event_params.value.string_value
    END AS article_name,
    CASE WHEN e.event_params.key = 'author_name' THEN e.event_params.value.string_value
    END AS author_name
FROM 
    `my-new-project.analytics_000000000.events_*` as e
WHERE 
    event_name = 'view_article'


しかし、BigQuery でこのようなエラーになり、クエリを回せませんでした。

Cannot access field key on a value with type ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64, float_value FLOAT64, ...>>> at [5:24]

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

check解決した方法

0

サブクエリの FROM でいきなり UNNEST をつかうこのようなやりかたで解決しました

SELECT 
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'article_name') AS article_name,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'author_name') AS author_name
FROM 
    `my-new-project.analytics_000000000.events_*`
WHERE 
    event_name = 'view_article'

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.06%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る