TODD KERPELMAN: So let's talk about BigQuery--
why you might want to use it with Firebase Analytics, why
it's kind of awesome, and how you get started with it.
And to do that, we need to explain a little about how
Firebase Analytics works.
Now, as you know, Firebase Analytics
gets data constantly from your app,
along with thousands of other apps from all around the world.
And every few hours, it runs a batch
of reports on all this data.
These reports turn into the nice graphs and charts you
see in the Firebase console.
And while these reports are kept around
for you to view whenever, that raw data is not.
That means that if you wanted to, say, run specific ad
hoc queries on historical data, or export
your data to be used with another analytics tool,
or simply run some specialized reports or charts that Firebase
Analytics doesn't include by default,
you'll need access to that raw data.
And the best place to do that is with BigQuery.
BigQuery is basically a data analytics warehouse
in the cloud.
It's service designed to run very fast queries
over very large amounts of data, like the analytics data
that your app is generating.
Now, it's important to note that BigQuery is not
completely free.
If you want to make use of BigQuery,
you'll need to update your Firebase project
to the pay-as-you-go Blaze plan.
But keep in mind, this is a feature
that's only available to Google Analytics customers
if they've signed up for the Premium Analytics 360 service.
So yes, you do have to pay for BigQuery usage, but in my mind,
it's kind of like getting a car for free
and then finding out you still have to pay for the gas.
I mean, sure, paying for the gas is kind of a bummer,
but come on, man, free car.
Of course, the big question you might actually have
is, well, how much is this actually going to cost?
And as with most things in life, the answer is it depends.
There are three main areas you'll
be concerned with-- storing data, loading
in the data in real time, and querying the data.
If you want an example, let's take a look at Bingo Blast.
This is an app with about 24,000 monthly active users
across iOS and Android.
It records about 70 different user events, but only
three user properties.
And right now, we have about 6 and 1/2 months
of historical data, which equals roughly 19 gigabytes of data.
So our data storage charges are about $0.38 a month
to store that data.
We're also paying about $0.12 a month
to insert that data into BigQuery in real time.
Running a very simple query across, say,
a month's worth of Android data costs about 1/10 of a penny.
If we were to run 50 queries like that every day
across both Android and iOS, we'd be talking about $3
a month.
Although in our case, that would actually
fall under the 1 terabyte free tier that we get from BigQuery,
so this part would actually cost me nothing.
Now obviously, huge disclaimer, these figures
can vary considerably depending on how much
data you're collecting, how long you keep it around for,
and how often and how complex you're making these queries.
But still, at least for Bingo Blast,
these charges ended up being pretty minor.
Plus I should also note that there are some tools in place
to set quotas or alert you if pricing starts to go over
what you're expecting.
So with all that said, let's go back to my sample app
that I've used in the last few videos.
As you recall, I've been recording an analytics event
any time the user adjusts that slider.
Now, with that event, I've been storing a custom parameter that
lets me see what value this slider ended up
with after users are done adjusting it.
But until now, I haven't been able to query that value.
This isn't something I get automatically
with the Firebase Analytics standard event reports.
So this sounds like a job for BigQuery.
So the first thing I'm going to do
is link up my Firebase account with my BigQuery account.
I'm going to go into my project here,
and under Project Settings, I'll select Account Linking.
Now, at this point, I'll click the Upgrade Project and Link
button.
Now, this account already has billing set up,
but if it didn't, I'd need to do that too,
where I'd supply my credit card and billing address.
But for now, all I need to do is click Confirm Purchase
and I'm in.
Oh yeah, and I should click this Continue button too.
There, better.
So let me jump over to BigQuery, which
I can do by heading to bigquery.cloud.google.com.
And here on the left are a list of Firebase projects
that I can select from.
On top here, you can see my project.
Below that are some free public datasets,
things like baby names in the US, and "Hacker News" posts
that are available for you to experiment with if you want to.
Now, I'll select my project.
And right now there are no tables.
That's because there's no data.
BigQuery starts collecting my raw data from the point
that I turn it on, which I did just 30 seconds ago.
So let's add some data, shall we?
I'll go back to my app and I'll trigger a few events,
then I'll move map to the background
to make sure the data gets sent to Firebase.
And I'll do that a few times.
All right.
Now we'll switch back to BigQuery.
Notice that we now have this app events intraday table.
This is basically like a temporary table
that gets created to hold the day's events.
At the end of the day, these get moved
into a more permanent app events table, which you see here.
The reason I have one of these already
is that it's actually been a day since I
did all that stuff in my app.
The magic of video.
You might not have this, but that's OK.
So let's take a look at what I have here.
Now obviously, a full deep dive into BigQuery
would probably take many hours and quite
possibly a book or two, but let's give you the basics.
First off, this schema button lets
me see what my data looks like.
You can basically think of this as a giant database table,
although it's a little more sophisticated than that.
You'll notice that some of these sections
are described as records, things like this Device Info section
here.
I tend to think of these as rows from other smaller tables that
get pulled into this one.
More importantly, a few of these records
here are listed as a record with type repeated.
User Properties, for example, is one of them.
This is sort of like grabbing multiple records
from these mini tables and putting them
all into the same record without my
having to do any kind of joins.
In this example, for instance, I have multiple user properties,
all of which are associated with this event.
And I can see them all in the same entry
without my having to do some kind of join
with a link table or anything.
You might also notice that this Event Dim
is a repeated record itself.
It turns out each row in my table
is usually several events from the same user
all bundled together.
Or I guess if JSON trees are more your thing,
you're going to get these records as child
objects inside our tree, and these repeated records
are more like child arrays inside our tree.
So if I go up here and click Preview,
this gives you a better example of what this data actually
looks like, or at least it should.
But sometimes on intraday tables,
it won't have had enough time to add data
into this preview buffer, so you'll get
something that looks like this.
So I'll just go ahead and preview yesterday's data
and it'll have the same structure.
So here's my first event.
And right away, you can see the user property data
that's being collected with it.
It's a repeated record and I happen
to have two of these properties associated with this event,
or I guess, more accurately, the user who triggered this event.
So I've got First Open Time-- that's
something Firebase Analytics collects right away--
as well as my Custom Dog or Cat Person user property.
So it looks like I'm checking out
the events from a dog person.
Let's move over to the event data.
I can see that this event up here was a button click.
And if we move over to the event parameters here,
well, there's not a whole lot of interest.
But let's move down to a slider event.
Here's one.
And here in our Event Parameters Value,
I can see the new value entry that I
have been reporting from the client, which now means
I can start analyzing these events by clicking this Query
Table button and entering in some SQL queries.
Now, it turns out there are two ways to enter these queries.
There's the legacy SQL, which is what BigQuery supported before,
and there's the new standard SQL,
which might look more familiar to you
if you've done a lot of SQL work in the past.
At the time of this recording, standard SQL
has been out for about a month, so legacy SQL
is still the default option.
But I'm going to switch to standard SQL,
partly because everybody keeps telling me
this is the new hotness, but also because it
makes some of these more complex queries a little easier.
So I'm going to do that by selecting Options and then
unchecking Legacy SQL here, although by the time
you see this video, who knows, maybe this will be the default.
So I'm going to go ahead and reclick this Query Table
button to bring up the starter query
in the nice new standard SQL format.
And now at this point, I can create a simple query
to list all the new value parameters for the slider
events I've seen so far.
You'll notice with standard SQL, I
have to do this unnest call with every repeated
record I want to query, which includes these Event Dim
and Event Params fields.
Once I've done that, it's easy to ask for just the new value
parameters of the Adjust Slider events.
Note that to get the actual values,
I have to call the Double Value property of the value record.
So now I'll run this query.
And look at that, I got a bunch of values
that I can kind of scroll through.
Now, if I want to get the overall average of this value,
I can then change this line here,
and that will give me the average.
There we go.
So let's do a slightly more complicated one
to give me the average of the slider events for dog
people compared to cat people so I
can start to see how these two groups differ
in their treatment of sliders.
So basically, you'll notice that once I call unnest
on both the events and the user properties,
I can then just ask for the Adjust Slider
events and the New Value Custom Event property,
and the Dog or Cat Person user properties.
And yes, it's totally weirding me out
that I'm not using double equals here.
So we'll run this and find out how our dog and cat
people differ in their slider behavior.
So it looks like our cat people are dragging to the right
a lot more than our dog people.
Incidentally, you'll notice that I'm
doing all this on the intraday table that was created today.
In a real life scenario, you'd probably
want to run this across many weeks worth of data.
The query to do this would actually look pretty similar.
It's just that instead of selecting an individual table
like this, I'd add in a little wildcard
here at the end of the tablename and specify the tables
that I want with this special table suffix
line in my WHERE clause.
So I'd create something like this.
And now I can compare all my data
over the last 30 days, which in my case,
just happens to be yesterday's data.
Notice that BigQuery keeps a history of queries for you,
and you can save individual queries like this
and share them with others in case
there is a report that you want to run fairly often.
Also note that this little green checkbox here
is telling me if my query looks valid before I run
it, which is nice.
And when I click on it, it'll tell me
about how much data this query will process.
If you're more of a visual person
and wish you could see this data in pretty graphs or dashboards
or something, there are number of third party tools out there,
things like Tableau, Qlikview, and Google's own Data Studio
that you can set up to run on top of BigQuery
to see this info a little better.
So that there is the whirlwind tour of BigQuery.
There is a lot more information that I haven't covered,
so if this has got you hankering for more information,
feel free to check out the documentation.
And yes, there really is a whole book all about it.
But if you don't feel like reading an entire book,
this blog post is also a pretty good way to get started.
And if you follow these two links here,
you can get access to some sample Firebase Analytics
data for iOS and Android so you can play around
with some actual analytics data and get
used to the way your data is going to be structured.
So thanks again for watching, and we'll see you soon
on the next episode of Firecasts.