ANDREA WU: BigQueryis Google Cloud'sfully-managed serverlessdata warehousethat enables you to ingest,store, analyze, and visualizea bunch of data.And it's often used to quicklygain insights from that data.When you're usingFirebase, there'sa lot of data acrossa lot of the products.And you can useBigQuery to answermany questions you haveabout your app or your users'experiences.If you've neverheard of BigQueryor want to learn more aboutits integrations with Firebase,check out the videolinked in the description.In this video,we're going to divedeeper into what we cando with the data exportedto BigQuery from Firebase.Currently, fiveFirebase productsintegrate with BigQuery--Analytics, Cloud Messaging,Crashlytics, PerformanceMonitoring, and A/B testing.Each of these productshave its own schema model,and the documentation for eachone is in the description.I'll be using publicdemo data in BigQuery.And the data comes from anactual game called Bingo Blastand contains anonymizeddata from a few years ago.So you can followalong if you'd like to.The link to this public dataset is also in the description.So say you've builtthis amazing game,and you have usershappily playing it.You might wonder abouthow user retention is,because you've probablyspent a lot of time acquiringnew users.There is a retentionreport that you get outof the box when you addGoogle Analytics for Firebaseto your project.But you can get more detailsand do some custom analysiswith BigQuery's integrationwith Google Analytics.Let's see some queries we canwrite with the public demodata.To do that, we'll use thelink in the description thatwill take us straightto the BigQuery data setin Google Cloud Console.Let's hit the querybutton and write a queryto analyze weekly retentionand compare retention groups.And we'll do this overa series of queries.Our first goal will be to gathera list of players who firststarted playing your game duringthe week of, say, August 1,2018.To do this, we'lluse user_pseudo_id,which is a stringthat is randomlygenerated per app instance,to represent a unique player.We'll select fromthis table, and we'llwant the users who executeda first_open event, whichis an event that getstriggered when a player firstopens the app.We'll also want to get theusers between August 1,2018, and August 8, 2018.If we hit Run, we'll see alist of all users who firstused the app during that week.If we select thecount from this query,it looks like there's 408 users.So with that, let's calculateour week one retention.We want to findout how many userswho used the app betweenAugust 8 and August 14were among the groupof players who startedusing the app the week before.Luckily, this is easy todo with the IN clause.IN is a function that willreturn true if a value existsin the list of values.So, for example, selectone in one, three, fiveevaluates to true sinceone is in that list.In our case, we're not goingto use a hard-coded listlike this.We're going to look forusers in the resultsfrom that week zeroquery as our list.We'll then query the datafor users for week oneand have the query returnwhich of those usersare in our week zero query.When we run that,we'll see this.And if we run the query againusing counts, we'll get 93.So if we do 93divided by 408, weget a week oneretention rate of 22.8%.Now we can go aheadand repeat this queryby going back a week at a timeand dividing those resultsby the original number of 408to get our results for weektwo and week three and so on.But as programmers, thatsounds a little too manual.Let's see how we cando that automatically.We're going to useanother bit of SQLto help us out here,the WITH AS statement.Essentially, this lets us referto some results from a queryby creating a labelthat I can use later.For example, we could say,WITH executives AS select allfrom employees where levelis greater than the nine.Select all from executiveswhere first name equals Sundar.Essentially, we're savingthe results of the first lineSELECT statement intothe variable executives,and we're selecting in thesecond query from executives.So let's look at how we canconvert our previous statementinto somethingusing a WITH clause.We'll save week oneusers into week_1_users.And we'll save week zerousers into week_0_users.We'll then selectfrom week_1_usersand join the resultswith week_0_usersby their user_pseudo_id.The join statementis an inner jointby default, which basicallysays select the user IDs thatappear in both of these tables.When we run this, wecan see the resultshave 93 users, same as above.At this point, we can copyand paste the modifier wayto several weeks worth of data.We can just copy andpaste week_1_users,change week two to weekone, and update the dates.Same with week three.We can then writeour SELECT statementsto get the counts for each week.If we run that,this is what we see.We can then make this wholething a little subqueryto calculate percentages.So if we run that, we'll see theretention rate for each week.This works perfectlyfine, but thisseems to be a bit ofa mess, doesn't it?What would happen if we suddenlywanted to report the changedate of our report?We would need to change 16different dates in that query,and I'd probably get atleast one of them wrong.Let's see how we canclean this up a bit.The trick is to once againuse our friend, WITH AS,to define our data set as wellas a few values that basicallyact like variables.For instance, we cango back and reviseour get all usersin week zero queryto look a littlesomething like this.When we run it, we getthe same results as above,but it's much easier to change.If a teammatedecides she suddenlywants a report startingon October 31 instead,we just need to adjust thethree values at the beginning.Yay!Now, what if we'reinterested in seeingwhat retentionlooks like for onespecific version of the app?We can add the app versiondata into the analytics dataset, which will then selectweek zero users who areusing that specific version.When we run that, we'llget these results.We can similarlybreak out retentionby device type or family.Let's go over one morequery, breaking out retentionby events.For instance, what ifwe wanted to see whatour retention willlook like among userswho ever tried a level.We could do that bychanging our queryto grab a list of userswho ever encountereda level retry quick-play event.When we run that,this is what we get.So that was a lotof query examples,and this was only forretention for Google Analytics.Imagine what you can do withquerying your data with A/BTesting, Cloud Messaging,Crashlytics, and PerformanceMonitoring as well.If you need some helpgetting those juices flowing,the description haslinks to query examplesfor each of these products.With Firebase'sintegration with BigQuery,you'll be able to gaininsights from the datafrom each of these products,hopefully giving you a betterunderstanding of your users.Thanks for watching,and happy querying.[MUSIC PLAYING]
