05.12.2022 | Nikolay Valiotti

Simple Mobile Analytical Stack: Firebase + BigQuery

Mobile Analytical Stack BigQuery Firebase

Developing reliable and high-quality mobile and web applications requires a lot of dedication and, more importantly, a powerful and feature-rich development platform. Firebase, provided by Google, is one platform that has gained a strong foothold among developers worldwide. With this platform, you can quickly develop high-quality applications, attract new users, and increase revenue. The platform offers several integrated features, such as a mobile app server, mobile analytics, app promotion, and monetization tools that can be used individually or in any combination.

Firebase allows you to collect event data from your mobile apps and provides functionality for their analytics. But what if this functionality is not enough? What if you have several data sources (for instance, not only Firebase) from which you want to aggregate information (i.e., import data) and build custom mobile analytics?

In this guide, we will show you how to set up a custom mobile analytics environment for your data using popular tools.

Setting up Data Export from Firebase to BigQuery

To analyse data from Firebase, a user must first import data from BigQuery:

  1. Make sure you have the required level of data access to Firebase for viewing and managing data from BigQuery data export settings. If you don’t have data access, ask the owner of the Firebase project to assign you the appropriate role in the IAM settings of the Firebase console
  2. Go to the project settings on the Integrations tab:
  3. Select Link in the BigQuery card to go to the configuration page:
  4. Set up exports for those Firebase products whose data you want to interact with in BigQuery:
  5. Select Link to BigQuery to complete the connection setup.
  6. BigQuery offers users to change the configuration on the integration page as per users’ needs.
  7. The screenshots above are from the Free Tier project. Here is an example of a configured connection of a GoogleAnalytics product to BigQuery for mobile apps in production:
  8. And here is how it looks in BigQuery:

The number 467 represents the number of days for which there is event data.

Now you have a repository in BigQuery with data from Firebase; you can work with its different features and build custom reports for business insights. To do this, you can use two types of tools – on-premise or cloud.

Setting up Metabase

Let’s look at the first type, on-premise, and briefly describe how to deploy a tool like Metabase for data analysis. Let’s deploy Metabase using Docker (for the example, it is assumed that Ubuntu 22.04 LTS is the working system):

  1. Make sure you have Docker installed and running.
  2. Get the latest Metabase Docker image:
    sudo docker pull metabase/metabase:latest

  3. Run container:
    sudo docker run -d -p 3000:3000 --name metabase metabase/metabase
  4. If you want to use a different port, change the value of the following parameter:
    -p <port>:3000
  5. Don’t forget to check the accessibility of the port on your machine.
  6. To view container logs, you can run the following command:

    docker logs -f metabase
  7. After the Metabase initialization is completed, the UI will become available at
    <machine IP>:<port>

  8. Select Let’s get started and set up Metabase:
    1. Choose language
    2. Fill in your account details
    3. Add a data source:

In our case, we need to configure the connection to BigQuery.

In the Service account JSON file field, upload the key in JSON format, which can be created in the Keys tab on the settings page of your service account in Google Cloud.

4. Allow/deny Metabase to collect tool usage information and complete the setup by selecting Finish.

5. The screen will inform you that the setup is complete. Select Take me to Metabase (you can also subscribe to the newsletter to learn about new features, get deeper insights, etc. while you are there!).

9. It’s worth noting that the above method has a drawback: Metabase ships with an embedded H2 database that uses the file systems to store its own application data. This means that if you remove the container, you’ll lose your Metabase app data (your questions, dashboards, collections, and so on).

If you want to run Metabase in production, you’ll need to store your app data in a production-ready database. You can read about how to run the production version of Metabase here in the section “Production installation”. If you have already set up the production database, you can read here how to migrate data.

10. After setting up Metabase, you will be taken to a workspace for managing data where you can analyze your data and build custom reports.

11. For example, you can view the contents of each table and perform quick mobile analytics without using SQL:

    1. Open the sidebar
    2. In the Data section, select Browse Data
    3. Select a data source
    4. Select a table
    5. Perform filtering and/or aggregation (optional)
    6. Build a data visualization (optional)

12. A similar result can be obtained if you click on the top right + New and select Question:

13. You can also build a data visualization based on a SQL query:

    1. Select +New
    2. Select SQL query
    3. In the editor that opens, select the data source
    4. Write a request
    5. Adjust variables as needed
    6. Select Run query
    7. Select a data visualization type (optional)
    8. If desired, save the result by selecting Save

14. To create a dashboard, select +New, then Dashboard.

 

Using Preset Cloud

There are cloud solutions for those who do not want to or cannot deploy the tool on their machine. The same Metabase can be used in the cloud, but you will have to pay for it (flat-rate pricing for 5/10 users; although there is a 14-day trial period):

However, there are other cloud tools and platforms that have free plans for users, such as Preset Cloud (which, by the way, has an on-premise counterpart–Apache Superset).

Let’s see how to connect data from BigQuery to Preset Cloud.

  1. Register on the tool’s website if you don’t have an account.
  2. Data can be connected during the account setup process, or after. (We will describe the second case.)
  3. Go from the working environment to the Data tab and from there to Databases.
  4. Select +Database.
  5. Select BigQuery from the list of data sources:
  6. Download the service account key JSON file from Google Cloud:
  7. Perform additional configuration if necessary (this can be done later):
  8. Next, you can create a dataset from a specific table with events:
    1. Select Data tab
    2. Select Datasets
    3. On the page that opens, select + Dataset
  9. You can also use SQL Lab, write a custom query, and then create a virtual dataset, based on which you can build various data visualizations:
    1. Select the SQL Lab tab
    2. Select SQL Editor
    3. In the editor that opens, write a query
    4. Select Run
    5. Select Create Chart
    6. Enter dataset name and select Save & Explore
    7. Build the data visualization
  10. To create a dashboard, go to the Dashboards tab and select + Dashboard.

Accessing Multiple Tables With Events

Since there is a separate table for each day in the data warehouse, it can be inconvenient to write queries manually specifying table names. Table suffixes come to our aid, which can be accessed via TABLESUFFIX.

Let’s take a simple example. The following query will return all dates for events from the last week:

SELECT DISTINCT event_date
FROM `analytics_< ... >` . `events_*`
WHERE TABLESUFFIX BETWEEN
 FORMAT_DATETIME ( '%Y%m%d' , DATE_SUB ( CURRENT_DATE (), INTERVAL 7 DAY ))
  AND FORMAT_DATETIME ( '%Y%m%d' , CURRENT_DATE ())
ORDER BY event_date DESC

We can check the request, for example, in Preset Cloud:

You can see that there are only six results – the table with data for 20.06 has not yet been created.

The same process can be done in Metabase as well:

Connecting Other Data Sources

For more constructive mobile analytics and business intelligence, data from Firebase alone may not be enough. But you can connect additional data sources to build merged reports and unlock business intelligence capabilities.

For example, if you have an active Facebook ad account, you can use Airbyte to access and export data on ad insights to your storage. Read about how to do this here.

It is worth noting that collecting data from different sources and storing them in one database is recommended for most use cases.

  • Firstly, it is convenient for data analysts, marketing teams, and other departments to have everything in one place.
  • Secondly, if you want to write complex queries for data from different sources, you will most likely not be able to access several databases simultaneously or work with several complex datasets interactively.

 

You need this guide, because it includes:

  • evolution of data stack
  • reasons why many well-known tools do not meet the challenges of the new world
  • vital structure of modern data stack
  • the newest tools for every part of modern data stack
mail

Turn it on to get exclusive guide on modern data stack

Emails suck. This newsletter doesn’t

Subscribe to the newsletter and get the most useful guide on modern data stack

The newsletter

You will also receive other useful materials on data analysis hacks with case examples from our company.