CODEWITHSUNDEEP
firebasegoogle-sheetsfirebase-analytics
nucci
nucci

Reputation: 137

Automate Google Firebase Analytics Data Export

After a solid load of searching I'm still not able to find a way to automate the export of Google Firebase Analytics data.

I need to import a few KPIs in an existing Google Sheet every month.

Isn't there any option to query the Firebase API through the Google Sheet and GET the KPI I need into a cell somehow every month?

There are ways (and even plugins) to do that for Google Analytics data. So I was sure it should be possible for firebase aswell.

Upvotes: 2

Views: 3021

Answers (2)

Eugene Sorokin
Eugene Sorokin

Reputation: 1

Let me help you to deal with the OWOX BI BigQuery Add-on and simplify steps 3 to 6 from the answer above.

First of all, you really need to link BigQuery to your apps (Ian wrote good instruction how to do that in steps 1 and 2).

Then:

  1. Add OWOX BI BigQuery Reports Add-on to your Chrome browser,
  2. Open your Google Sheet, run add-on (Add-ons -> OWOX BI BigQuery Reports -> Add a new report),
  3. Provide the add-on with the access to your BQ tables,
  4. Select your Google BigQuery project in a drop-down list,
  5. And create a new query (once again, Ian provide you with a good example of the query)

You can find some more details about OWOX BI BigQuery add-on in our Help Center. And feel free to write to us via email ([email protected]) or in chat - we’ll be happy to answer any of your questions.

Best regards, Eugene

Upvotes: 0

Ian D. Miller
Ian D. Miller

Reputation: 313

To import Firebase analytics data into a Google Spreadsheet, you'll need to go through BigQuery. There's a Google Spreadsheet add-on called OWOX but I couldn't get it to work, so here's how to do it via creating a Google Apps Script:

  1. In Firebase under Project settings (under the gear icon) -> Account Linking : find the BigQuery tile and sign up if you haven't already. This DOES require moving to the Blaze pay-as-you-go plan, but there's a generous amount of free usage in this tier so you're likely to avoid costs, but you do need to provide the usual billing info.
  2. After signing up for Blaze, you'll go back under this Account Linking section and you'll see that BigQuery is now linked to your apps. Click on "Manage Linking" and you'll likely see "Syncing dataset" for awhile... like several hours (note, this page doesn't seem to refresh on its own so you'll have to refresh manually to check status). Once this is complete, you'll be able to access your data via BigQuery on the Google Spreadsheets side.
  3. In your Google Spreadsheet, select Tools -> Script Editor. There's a good starter script here: https://greenido.wordpress.com/2013/12/16/big-query-and-google-spreadsheet-intergration/
  4. In Google Script editor, select Resources -> Advanced Google services and ensure you turn on BigQuery API. Now your script will have access to BigQuery data.
  5. For my needs, I was just trying to get a count of some events in Firebase, so I wrote a function to invoke multiple queries like this:
    function runQueries() {
        var sql;

        // fetch bigQuery data for Firebase app Create Wildlink (bottom row app)
        sql = "#standardSQL\nSELECT count(event_dim) as event_count FROM `PROJECTID.TABLENAME.app_events_*`, UNNEST(event_dim) as event WHERE event.name = 'target_event_name'";
        runQuery(sql, 1, 1, 'Label 1');

        // fetch bigQuery data for Firebase app Share Extension (top row app)
        sql = "#standardSQL\nSELECT count(event_dim) as event_count FROM `PROJECTID.TABLENAME.app_events_*`, UNNEST(event_dim) as event WHERE event.name = 'target_event_name_2'";
        runQuery(sql, 3, 1, 'Label 2');
        Browser.msgBox("Finished updating the results");
    }
  1. And then I changed the runQuery function to accept and use those params:
    function runQuery(sql, insertRow, insertCol, label) {

        ...

        sheet.getRange(insertRow, insertCol + 1, resultCount, tableRows[0].getF().length).setValues(resultValues);
        sheet.getRange(insertRow, insertCol).setValue(label);

A few important notes about this method:

  • Events don't appear to be imported retroactively. From the moment you tie Firebase to BigQuery you start getting event data, but it doesn't import any old data.
  • Events are imported into day-parted tables. Note in the example above the wildcard in the table reference to span tables.
  • The example above uses StandardSQL (which was new to me). Note the #standardSQL\n in the above SQL strings. That changes from the default mode (LegacySQL). I ran into challenges using legacy SQL to get at the data.

Upvotes: 0

Related Questions