ANDREA WU: Hi.
Today, I'm going to tell you about BigQuery, specifically,
integrating Firebase with BigQuery,
and streaming data from Firebase to BigQuery.
Wow, I just said BigQuery a lot of times.
What is it?
BigQuery is Google Cloud's enterprise data warehouse.
And it's designed to help ingest, store, analyze,
and visualize a bunch of data.
It's often used to quickly get insights from that data.
In Firebase, there is a lot of data
across a lot of the products.
And sometimes, you might have some questions about your app
or your users' experiences that can be answered by looking
more closely at this data.
Maybe you're using Google Analytics,
and wants to see the retention rate by country,
or maybe you want to check Crashlytics
to look at the number of crashes per day for the past month,
to ensure a bug bash you just had made
your app more stable over time.
All of these questions can be answered
by integrating Firebase with BigQuery,
where you can perform custom analysis on your data set
per product.
But wait, all these questions can
be answered from within the Analytics dashboards too.
So why integrate Firebase with BigQuery?
Well, when you integrate Firebase with BigQuery,
you can dig into the data to see if there are any patterns
and trends, or perhaps you have questions
that aren't already answered in the Firebase Console.
You can use BigQuery queries to answer
those questions to get more actionable insights,
and get into much deeper exploration of your data
in ways that are far more customized than what's
available in the Console.
For example, maybe you don't just
want to see user retention by country,
but also want to compare different user
groups on a regular basis based on factors,
like when they first started using
the app, or their total app engagement time,
or maybe you want to do something even more customized,
like cross-referencing that data with your own marketing
and other backend data to match analytics data
with other marketing campaigns you recently ran.
Another benefit of exporting Firebase data to BigQuery
is that you can decide how long you want to keep your raw data
history, since the Console doesn't keep your data around
forever.
There are literally endless possibilities.
And this is where using BigQuery can become a big help.
Currently, five Firebase products
integrate with BigQuery, Analytics, Cloud Messaging,
Crashlytics, Performance Monitoring, and A/B Testing.
After integrating with BigQuery, you
can export data from each of these products
and use SQL to query the data to gain insights.
There's also a Firebase extension
for exporting Cloud Firestore collections to BigQuery
as well.
And there's a link below if you want to learn more about that.
You may be wondering whether BigQuery is an entirely no cost
service.
And the answer to that question is that some features are free
of charge, and some features aren't.
While loading and exporting data have no cost,
BigQuery does charge for storing data, streaming inserts,
and querying data.
There's more information in the link
provided in the description.
What if you wanted to try out BigQuery
to make sure it fits your needs?
BigQuery Sandbox is a way to try out BigQuery
without entering a credit card.
And it lets you explore BigQuery capabilities
at no cost to confirm that it does indeed fit your needs.
There are some limits, though, including a default table
expiration time of 60 days, meaning the tables
will get deleted in 60 days.
You also won't have access to some features such as streaming
to BigQuery, which we'll talk about in a bit, data transfer
service, and data manipulation language statements.
If you do want those features, you
can upgrade at any point from the Sandbox
to use BigQuery without these limits.
And when you upgrade, you'll still
have access to the no cost tiers included in the Sandbox.
But operating beyond those limits can incur charges.
And those charges depend on how much
data you store in BigQuery, and the queries you make.
Although loading and exporting data is free.
To check out more details on BigQuery pricing,
there's a link in the description.
As a reminder, with BigQuery Sandbox,
there's a 60 day expiry set on tables used for exporting.
And while upgrading BigQuery means you no longer have the 60
day limit, you'll have to manually set the expiration,
as upgrading doesn't automatically reset it.
Take a look at the link in the description for how to do so.
To export data to BigQuery from Firebase,
first go to the Integrations page in the Firebase Console.
In the BigQuery part, click Link.
And then you'll see a page with some information
about linking Firebase to BigQuery.
As a side note, you'll have to have the required permissions
to view or manage settings for data export to BigQuery.
And you'll find a link with more information
about this in the description.
In Firebase Console, you can click Next,
to choose which Firebase products you'd like
to integrate with BigQuery.
And can click on Link, to BigQuery, from there.
If at any time you'd like to integrate more Firebase
products, or un-link from BigQuery overall,
you can come back to this page.
Once you link a Firebase product,
you can choose which apps in your project
will send data to BigQuery by clicking on the Edit button
here next to Apps Exporting.
If you create a new app for that project,
the new app's data will automatically be exported.
But you can come back to this page at any time
to manage which apps send data.
The details of BigQuery integration
with each of the five Firebase products
are slightly different.
And I'm going to cover the ones that have specific BigQuery
features enabled for them, like Google Analytics
and Crashlytics.
The other products are fairly similar in how
they integrate with BigQuery, but still each
have their own nuances.
These are more minor differences though.
So I won't cover them here.
But in case you're curious, you can check out
the docs linked the description for more details
on each of them.
In general, linking Firebase products to BigQuery
enables you to gain insight into data for the previous day.
That is, today's data will not be available today.
And you'll typically have to wait until tomorrow
to be able to query today's data.
But what if you wanted to check data for today
to get a sense of what's happening right now?
For that, Google Analytics and Crashlytics
support streaming, which makes data for the current day
available by publishing periodic updates to intraday tables
throughout the day.
For Google Analytics streaming, BigQuery
has more recent information you can
analyze about your users and their traffic on your property.
For each day, streaming export creates one new table.
And it's named events_intraday, and then the date, which
is an internal staging table that
includes records of session activity that took place
during the day.
Streaming export is a best-effort operation,
and may omit all data for reasons
such as the processing of late events, or failed uploads.
Data is exported continuously throughout the day.
And this table can include records of a session
when that session spans multiple export operations.
The table is eventually deleted when
the daily export is complete.
It's important to note that no backfill for GA is available,
meaning that until you link GA to BigQuery,
there will be no GA data available in BigQuery.
So if you plan on using BigQuery,
or even if you just suspect that you might use it,
it's probably worth linking it sooner rather than later,
so you have data available to query.
Also as a side note, while there is
no limit on streaming export, standard GA properties
do have a BigQuery export limit of one million events.
If your property consistently exceeds the export limit,
the daily BigQuery export will be paused.
And the previous day's exports will not be reprocessed.
To get rid of this limitation, you can upgrade to GA 360.
And to help operate under the limit,
you can use data filtering to determine
what events get exported.
See the link in the description to learn more
about how to set this up.
You can also include streaming for Crashlytics,
which will result in a Realtime table,
in addition to the Batch table you'd
have for integrating Crashlytics with BigQuery.
The Realtime table will have data exported in real time,
but with no backfill available.
Whereas the Batch table has data exported once daily,
and can provide backfill data up to 90 days prior.
The Batch table is ideal for long term analysis
and identifying trends over time because Firebase durably stores
events before writing them.
When data gets written to a Realtime table,
the data gets immediately returned to BigQuery.
And so it's ideal for live dashboards and custom alerts.
These two tables can be combined with a stitch inquiry
to get the benefits of both.
Streaming Google Analytics and Crashlytics data to BigQuery,
as well as integrating those in A/B testing,
Cloud Messaging, and Performance Monitoring with Firebase
can be quite helpful, as you'll be
able to gain more insights into all of the data that you have,
hopefully giving you a better understanding of your users.
As you dive deeper into BigQuery,
you might be wondering about what sort of data
is being exported to BigQuery, and what you're actually
able to query.
And it might also be helpful to take a look
at some examples of queries.
As if I read your mind, there will be
a video coming soon about this.
Thanks for watching.
And happy querying.
[MUSIC PLAYING]