Firebase Analytics

Firebase AnalyticsのデータをフラットなCSVに変換するETL処理

こんにちは、エクスチュアCTOの権です。
今日はFirebase Analyticsのデータについてです。

Firebase AnalyticsをBigQueryエクスポートすると、user_dimとevent_dimの2列だけのスキーマになりますよね。
Google Analyticsのデータもそうですが、JSONのようなネストになったレコードで構成されてます。

これらのネストになったデータを、サードパーティや自前のデータウェアハウスやデータベースにインポートしようとするなら、FLATTEN関数やUNNEST関数をつかってフラットな1行に展開して、つまりCSVデータに変換するようなETLを挟むと扱いやすくなります。

参考になるのは公式ブログですね。
BigQuery 活用術: UNNEST 関数
標準SQLのUNNEST関数を使う例。

あとは、レガシーSQLのFLATTEN関数を使う例もこちらのブログに載ってます。
Using BigQuery and Firebase Analytics to understand your mobile app

しかし私はどうもSQLじゃなくて他のプログラムで片付けるのが好みなので、Compute Engine上でNode.jsを使ってフラットなCSVデータに変換してみました。

 

BigQueryからJSON形式でCloud Storageにエクスポートする

まずはbqコマンドを使ってJSON型で抽出します。

$ bq extract --destination_format NEWLINE_DELIMITED_JSON foobar:firebasetest_ANDROID.app_events_20170702 gs://hoge/firebase/test20170702.json

 

Cloud StorageからCompute Engineにコピーする

次に、gsutilコマンドを使ってcompute engineのインスタンスにコピーします。

$ gsutil cp gs://hoge/firebase/test20170702.json ./

 

ネストされたJSONをフラットなCSVに変換

Node.jsで簡単なコードを書いて実行します。
JSONファイルをReadStreamで読み込みながら、ネストになったJSONレコードをフラットに1行にして、WriteStreamに書き出して行きます。

var fs = require("fs");
var rs = fs.createReadStream("test20170702.json");
var readline = require("readline");
var rl = readline.createInterface(rs, {});
var of = "test20170702.csv";
var ws = fs.createWriteStream(of, "utf-8");

rl.on("line", function(json) {
    var data = JSON.parse(json);

    //user_dimの展開
    var first_open_timestamp_micros = data.user_dim.first_open_timestamp_micros ? data.user_dim.first_open_timestamp_micros : "";
    var device_category = data.user_dim.device_info.device_category;
    var mobile_brand_name = data.user_dim.device_info.mobile_brand_name;
    var mobile_model_name = data.user_dim.device_info.mobile_model_name;
    var mobile_marketing_name = data.user_dim.device_info.mobile_marketing_name;
    var device_model = data.user_dim.device_info.device_model;
    var platform_version = data.user_dim.device_info.platform_version;
    var resettable_device_id = data.user_dim.device_info.resettable_device_id;
    var user_default_language = data.user_dim.device_info.user_default_language;
    var limited_ad_tracking = data.user_dim.device_info.limited_ad_tracking;
    var continent = data.user_dim.geo_info.continent;
    var country = data.user_dim.geo_info.country;
    var region = data.user_dim.geo_info.region;
    var city = data.user_dim.geo_info.city;
    var app_version = data.user_dim.app_info.app_version;
    var app_instance_id = data.user_dim.app_info.app_instance_id;
    var app_store = data.user_dim.app_info.app_store;
    var app_platform = data.user_dim.app_info.app_platform;
    var app_id = data.user_dim.app_info.app_id;
    var bundle_sequence_id = data.user_dim.bundle_info.bundle_sequence_id;
    
    //event_dimの展開
    for (var i = 0; i < data.event_dim.length; i++) {
        var date = data.event_dim[i].date;
        var event_name = data.event_dim[i].name;
        var timestamp_micros = data.event_dim[i].timestamp_micros;
        var firebase_screen = "";
        var firebase_screen_class = "";
        var engagement_time_msec = "";
        for (var j = 0; j < data.event_dim[i].params.length; j++) {
            if (data.event_dim[i].params[j].key == "firebase_screen") {
                firebase_screen = data.event_dim[i].params[j].value.string_value;
            } else if (data.event_dim[i].params[j].key == "firebase_screen_class") {
                firebase_screen_class = data.event_dim[i].params[j].value.string_value;
            } else if (data.event_dim[i].params[j].key == "engagement_time_msec") {
                engagement_time_msec = data.event_dim[i].params[j].value.int_value;
            }
        }
        ws.write(first_open_timestamp_micros + ",");
        ws.write(device_category + ",");
        ws.write(mobile_brand_name + ",");
        ws.write(mobile_model_name + ",");
        ws.write(mobile_marketing_name + ",");
        ws.write(device_model + ",");
        ws.write(platform_version + ",");
        ws.write(resettable_device_id + ",");
        ws.write(user_default_language + ",");
        ws.write(limited_ad_tracking + ",");
        ws.write(continent + ",");
        ws.write(country + ",");
        ws.write(region + ",");
        ws.write(city + ",");
        ws.write(app_version + ",");
        ws.write(app_instance_id + ",");
        ws.write(app_store + ",");
        ws.write(app_platform + ",");
        ws.write(app_id + ",");
        ws.write(bundle_sequence_id + ",");
        ws.write(date + ",");
        ws.write(event_name + ",");
        ws.write(timestamp_micros + ",");
        ws.write(firebase_screen + ",");
        ws.write(firebase_screen_class + ",");
        ws.write(engagement_time_msec + "\n");
    }
});

簡単なテストアプリのサンプルデータなので、カスタムのイベントとプロパティのデータの処理は入ってません。
なので実際はプロパティの数だけカラムは増えます。
あとはset_timestamp_usecやら、previous_timestamp_microsなど他にもデータはあるのですが、そこらへんは今回は省略してます。

このNode.jsを実行すると、このようなCSVデータになりました。

ネストされてないフラットなCSVなので、各データウェアハウスやデータベースに取り込みやすくなりました。

弊社ではFirebase Analyticsの他、Google AnalyticsやAdobe AnalyticsのデータをGoogle Cloud Platformを用いて分析する業務支援を行なっております。
お問い合わせはこちらからどうぞ。

関連記事

  1. GA 360 Suite

    BigQuery: Google Analytics 360のネストされたデータをフラット変換するS…

    こんにちは、エクスチュアCTOの権です。今回はGA360のBi…

  2. Google BigQuery

    Tableau : BigQueryでLOD計算が使えない場合の対処法

    こんにちは、エクスチュアの渡部です。Tableau×BigQu…

  3. Adobe Analytics

    Adobe Analytics: データフィードをGoogle Compute EngineのLin…

    こんにちは、エクスチュアCTOの権です。もはやWeb解析だけの時代…

  4. Adobe Analytics

    Adobe Analytics: SegmentsAPIを使って大量のセグメント設定を作成・更新する…

    こんにちは、エクスチュアCTOの権です。今回はAdobe Ma…

  5. Cloud Dataproc

    BigQueryテーブルをAVRO形式でエクスポートしてHiveで扱う

    こんにちは、エクスチュアCTOの権です。今日も普段あまり使わな…

  6. Adobe Analytics

    Adobe AnalyticsからDWHレポートをGoogle Cloud Storageにアップロ…

    こんにちは、エクスチュアCTOの権です。以前、AdobeAna…

最近の記事

  1. Google Compute EngineのUbuntu V…
  2. Firebase Analyticsの新しいBigQuery…
  3. Adobe Cloud Platform Auditor (…
  4. Google Analytics Standardのデータを…
  5. Google Tag Manager上でGoogle Ana…
  1. Adobe Analytics

    Adobe Analytics: Legacy s_code.jsからAppMe…
  2. プログラミング

    Node.jsでCSVファイル内のダブルクオートで囲まれたカラム内のカンマを除去…
  3. Ad Hoc Analysis

    Adobe Analytics: Ad Hoc Analysisでセグメントの条…
  4. Adobe Analytics

    Adobe Analytics: カスタムリンク計測を1行で書く
  5. Adobe Analytics

    Adobe Analytics:ワークスペース:セグメント比較機能の紹介
PAGE TOP