Tal shares his views on industry trends, the rise of influencers and UA in new, original ways.

BlueThrone | Tal Turpaz, our Head of Acquisitions, at the Israeli Mobile Summit 2022

1. Accessing multiple tables in one query



Firebase event data is saved in BigQuery in multiple tables, one for each date. Thankfully, a convenient naming convention `events_YYYYMMDD` allows access to all such tables in a single query using a wildcard in the table name:

You can also get data for a particular year or month as follows:

Get all events for 2023


Moreover, you can apply a more granular filter with `_TABLE_SUFFIX`, a pseudo column like this to get all events for the first seven days of March 2023


Applying filters to your wildcard queries allows you to limit your requests to particular subsets of raw event data and save on Bigquery processing costs.


`event_name` can be one of either standard events or custom events your app is sending to Firebase.

`event_timestamp` specifies a timezone-independent representation of the time when the event happened at the user's device (not when data was uploaded to the server). The `event_timestamp` is in microseconds so that it can be converted to Bigquery's `TIMESTAMP` format using the `TIMESTAMP_MICROS` function.

`user_pseudo_id` is a unique identifier for a particular app installation. If the app is reinstalled, a new `user_pseudo_id` is generated. This does not apply to app updates, though, so `user_pseudo_id` can be quite useful if you do not include custom user ids to your events yourself.

`event_value_in_usd` indicates gross revenue for a particular `in_app_purchase` event, conveniently converted into US dollars.

`platform` specifies the device platform (iOS / Android).

`geo` contains data about the user's geographical location, with subfields such as `geo.country` or `geo.city` providing different levels of detail. Note that geographical data is included based on the user's location during each particular event, so it can differ between events.

`device` and its subfields contain a lot of useful information on the device model, OS, language, etc.


Let's see how we can use some of the available fields.



If you have an app with a global audience, you must consider time zone differences, which gets tricky very quickly. Fortunately, Firebase provides a field `device.time_zone_offset_seconds` that specifies a timezone offset from the GMT in seconds for each particular event. Then it is easy to calculate the local date time for each event as follows. First, convert `event_timestamp` to `DATETIME` in GMT timezone:

Then, convert `TIMESTAMP` to `DATETIME` at GMT timezone:

Then, add time zone offset to arrive at the local `DATETIME`:


For each event, a `user_first_touch_timestamp` field is available. Calculating the difference between `user_first_touch_timestamp` and `event_timestamp` allows filtering events within a particular time range since each user's first opening. 

For example, we can learn how many users are subscribing within the first 10 minutes, 60 minutes, one day, etc. of first opening the app. To do that, we calculate the time difference between `user_first_touch_timestamp` and `event_timestamp` for each `in_app_purchase` event using the `TIMESTAMP_DIFF` function:

With this data, we can build a "time-to-purchase" histogram with any convenient tool (Python, Google Sheets, etc.).



A lot of useful information about the events is included in the columns named `event_params` and `user_properties`. The specific list of such attributes depends on the integrations you use, but many useful things can already be inferred from default Firebase and Google Analytics attributes. Custom attributes you assign to particular events or users can also be found in these columns. Note that attributes can differ for different events. For example, attributes `ga_session_id` are included for every event type, while `price` is understandably included only for `in_app_purchase` events.
Some of the useful attributes are:

`ga_session_number` contains a sequential number of the session for a specific user;

`engagement_time_msec` provides data on each particular user's engagement time. Sum over all `engagement_time_msec` values for a user to get their total engagement time (note that this attribute is not unique to the `user_engagement` event type, so make sure to sum over all event types to get your engagement time data);

`product_id`, `price` and `currency` for `in_app_purchase` events.

`firebase_exp_NN` provides Firebase A/B testing data;

6. Working with columns that are arrays of structs


Each of the `event_params` and `user_properties` columns has a type of repeated STRUCT/RECORD, so you can think of it as an array of key-value pairs. Since each value in this column is an array, you won't be able to access their attributes directly by key. For example, you cannot get the price of your `in_app_purchase` events like this:

Will not work.

Instead, we have to use Bigquery's UNNEST operator, which takes an array and converts it into a set of rows that can be joined or filtered in a usual way. For example, to get all events for which the event_params array contains the `price` key, we can use the following query:

Notice that the above query will exclude all rows that do not have the `price` key in `event_params`. If you want to return ALL rows and add price information only to the rows that have the `price` property (and NULLs everywhere else) your query can look something like this:

Note that each attribute has predetermined value fields: string, int, float, and double. Usually, only one of these fields is used, so double-check which type is used for your particular field value.



Data that allows mapping events to Firebase A/B experiments is stored in the `user_properties` field. If a user was part of the experiment `N` with variant `V`, a key-value pair `firebase_exp_N`-`V` is added to the `user_properties` array for each relevant event. Variant id is specified in the corresponding `string_value` field (even though the variant id looks like an integer).
Applying the approach from the previous section, we can query the events from experiment 42 as follows: -- All events in experiment 42

With this as a starting point, you can calculate all kinds of stats about users in your experiments.


8. Calculate basic stats for an experiment


One basic question you can ask about an experiment is how many events of each type are there for each variant and how many unique users had such events. The following query achieves this:
WITH data AS (...) -- All events in a particular experiment, as described above

We can infer many things from this straightforward calculation alone.

For example, we can understand how many users were included in each variant of the experiment, and compare conversion rates from `session_start` to any chosen event between variants. 

To do that, just divide `dcount` of your chosen event by `dcount` of the `session_start` event.Note that this query will not give you conversion rates between arbitrary events, for which we have to write a more sophisticated query with joins or window functions. 
We leave it for the future. Total revenue by variant can be most easily accessed via `event_value_in_usd` field as follows:
WITH data AS (...) -- All events in a particular experiment, as described above

Combining this query with the result of the previous one, you can easily calculate revenue per user by variant.



We’ve looked at some of the tasks that you might find useful in your own exploration of Firebase events in Bigquery. This is undoubtedly just scratching the surface of what's possible with direct access to raw events so that we will look at more sophisticated examples in future posts.



Firebase / Google Analytics allows you to export raw event data to Bigquery, Google's data warehouse platform. Bigquery supports a SQL dialect called GoogleSQL (previously known as StandardSQL). Having access to full unabridged data of your users' events is great if you want to customize your analytics layer and obtain in-depth insights into your users' behavior.

Recipes for Preparing Raw Firebase Events in Big-query

When setting up an experiment in Firebase, targeting is one of the key parameters. 
Targeting fields and Activation events can limit your experiment to a particular subset of users. 

With targeting, users who do not satisfy selected criteria are excluded from the experiment and see no experiment variants.
 
Activation event works differently – users are exposed to experiment variants regardless of whether they've triggered the selected activation event or not; however, only those users who triggered it are counted by Firebase when presenting results.

Therefore, if your Activation event is rare, it should not be surprising that many more users will be exposed to the experiment than counted towards the result in the Firebase console. 

For example, if you want to serve a paywall with 90% price discount to users who invited more than 90 friends to the app, it might not be a good idea to target them using the Activation event set to "invited_90_friends".
 



2. Remote config may not load when you think it does

Experiments in Firebase are powered by remote config. 

A specific implementation of fetching and activating the remote config within the app can directly impact the experiments since a user enters the experiment only after the remote config is fetched and activated. 

There are a few remote config loading strategies, however, for the purposes of A/B testing, it is usually beneficial to always have the most up-to-date config upon app startup.

One common issue related to this arises when the Activation event happens before the remote config is fetched and activated. In this case, the variant config is still being served to the user, but they are not taken into account by Firebase when presenting results. 

For example, this will happen if you try to target new users by setting the Activation event to "first_open”. 
First open is an automatically-collected event that is generated when a newly installed app contacts Firebase server for the first time. 

In this case, the first_open event will most likely be triggered before your app can load any remote config parameters, and you won’t be able to gather any sizable sample size for your experiment.

For filtering new users, as an alternative to first_open, you can set the activation event to the start of onboarding (assuming the remote config is loaded by this point) or target by first open time.




3. Mind the A/B test-related user experience

If you run price experiments or experiments with different paid packages (e.g., monthly/yearly subscriptions vs. weekly/quarterly), there is a good chance that some of your users will see different offers at different points in time. This can sometimes lead to users’ confusion or even frustration. 

To avoid that, you can frame the alternative variants in experiments in a manner that is consistent with the base variant.

For example, if you just go from $9.99 to $8.99 in an experiment variant, some new users who were only exposed to the $8.99 offer are set up for a surprising price hike once the experiment ends. 
In this case, serving them a 10% limited-time discount makes sense.




Firebase automatically analyzes the experiment and presents results in two panels – Observed and Modeled data. 

While Observed data is pretty self-explanatory, Modelled data is more opaque since it is based on Bayesian modeling. 
When it comes to statistics, most people have better familiarity with the frequentist approach that uses such concepts as hypothesis testing, confidence intervals, and p-values.

Firebase mainly uses the Bayesian approach because frequentist interpretation of experimental results is less intuitive than Bayesian interpretation IF you want to be precise in your statements. 
For example, when applying the frequentist approach to the comparison of conversion rates between two variants, the resulting p-value is NOT the probability that conversion is the same between variants. 

It is also NOT the probability that the observed conversion rates happened purely by chance. 
Correct frequentist interpretation gets a bit technical and can be easily misinterpreted.

Bayesian inference that is used by Firebase, on the other hand, provides a direct answer to the question: “What is the probability that variant X is better than variant Y?”. 
This probability metric can be used as a measure of confidence in the experiment results. 

To summarize, if you do sufficiently many experiments, each of them having a winning variant with a 90% probability of beating the base, you can expect, on average, to correctly select the winning variant 9 out of 10 times.




5. When Firebase console is not enough, integration with BigQuery can give you more details

Firebase / Google Analytics has strong integration with BigQuery, which is Google's data warehouse solution, allowing you to save raw events to BigQuery. 
A free tier permits exporting up to 1 million events per day.
 
With a smart approach to event setup and limited export to the most relevant event types, this quota can go a long way, even for medium-sized apps. However, beware of additional charges you may incur on the BigQuery side.

The data from A/B testing can be easily integrated into the data saved in BigQuery. Having detailed experiment data in BigQuery is excellent if you want a more granular view of your users' behavior. 

You can add extra dimensions to your analysis, such as comparing the performance of your variations by state/city, device model, or day of the week. Additionally, with raw data in BigQuery, you can dissect each step of your funnel, which provides in-depth insight into user behavior and more ideas for your next experiments.



6. Deal with BigQuery integration BEFORE running experiments


It's important to note that you cannot save data from previous experiments in Firebase to BigQuery, so it's necessary to set up data export beforehand. 

Exported raw events data provides many otherwise unavailable possibilities, but using it requires additional effort. 

The exported data only includes raw events, so nothing is pre-computed, including conversion rates, probabilities, and model outputs. 

The user needs to create their own methods to derive insights from raw data, which requires understanding Firebase event schemas and writing SQL queries to obtain even the most basic experiment data. 
Looker Studio is useful for analyzing raw events data, particularly because of its native BigQuery connector.

Here at BlueThrone, we believe that running A/B tests is a must if you want to truly maximize your app’s potential. 
However, doing it right can be a challenging and time-consuming task that requires knowing your users and having experience with the best practices and tools. 
We’ve addressed a few of the gotchas of Firebase A/B testing, but there is so much more to it, so we will continue looking into A/B testing in more detail in the upcoming articles.

A/B testing is a powerful tool for mobile app developers who want to optimize their user experience and increase conversions.  
Firebase, Google's mobile and web app development platform, makes it easy to run A/B tests on your app and measure their impact on user behavior. 

However, there are several key things to remember when running A/B tests with Firebase to ensure that your results are accurate and meaningful.

Blog

Tal Turpaz, our Head of Acquisitions, at the Israeli Mobile Summit 2022

Tal Turpaz, our Head of Acquisitions, at the Israeli Mobile Summit 2022

Amit Carmi

More Posts: