How I: Get cheap; automatic analytics for my business using BigQuery Video Lecture | "How I" by Google Developers - IT & Software

JAVIER RAMIREZ: Hi, my
name is Javier Ramirez.
I'm one of the
founders of Teowaki.
And Teowaki is a collaboration
tool for software developers
where you can serve interesting
links, best practices,
documentation, mistakes,
gossip with other developers.
AND I'm going to tell you
today how we are using
and how you can use Google
BigQuery to run analytics
over anything which is
interesting for your business.
In our case,
analytics is allowing
us to have insights on how users
are using the tool in a very
economical way without
having to worry about keeping
our own servers upgraded,
maintaining them,
or how much data we are
storing, or if it's going
to be cost efficient
when we grow up.
So once you're in the console,
what you are going to see
is a quite familiar
interface if you know SQL.
Since we are already developers,
for us, SQL was really easy.
For example, here, I'm
getting-- from the date,
I'm getting first the
year, and then the month.
And then they are going to
be counting how many users
have been deleted from the tool.
So SQL is quite
convenient for us.
This query is quite basic.
But I've heard-- for
example, in Khan Academy--
that the businesspeople
are learning
SQL for managing
their own reports.
So they don't have to depend
on the development team, which
is quite interesting,
especially because if you have
to run many reports,
you don't want
to have dependencies
from anyone.
So yeah, when you
run the query, you
can see the results
are straightforward.
And here is one of the metrics
that are important for us,
just how many users are
abandoning the tool.
But we can do something
more interesting.
For example, I told you
before, in Teowaki, we
want to know how users are
interacting with each other.
So in this particular
query, what
we are going to see is
per every different month
and every different team, how
many items they are posting,
how many items they are sharing
between the team members, which
is something that would
be difficult to get
with other tools.
But here, it's just a query.
And as you can see, we have
already some teams are starting
in November, during the winter
period, that were posting,
I don't know, over 100 items.
And that's quite
interesting, because you
can see which teams are
more valuable for you,
which teams are less valuable.
Then, of course, you
can see as new people
are entering into the platform.
They start with no
contents at all.
But in the next month,
the number is increasing.
So to us, it's helping
us to get some insights
of how people are using Teowaki.
But what other things
you can do, apart
from getting aggregates from
how teams are using the tool?
The other input metric for us
is which users individually
are the most valuable?
Who are using the tool the most?
And with this simple query, I'm
getting just the internal ID
of the user which is using
the system the most, which
accidentally in this case is me.
Because I'm one of
the founders, and I've
been using the tool
before it was in public.
But yeah, this just
gives me a number.
It's not really useful.
But it would be much
better if I could
have a way of seeing the
individual navigation
of the user, how
they are actually
using the tool, our application.
So in this other query,
what I am getting is,
give me the date and the
URL from our database
where the user-- it's the
[INAUDIBLE], which in this case
is me.
And the trouble here
is on the results,
I can see the URL list
is all the time the same.
And that's because
in our system,
we have a notification part,
which every few seconds is
going to request if we have
new content from the server.
So if I want to get information,
that's really not useful.
And I can see in this
new version of the query,
I'm telling, OK, give
me all the results,
except the one about
the notifications count.
And here, you can see
all the information.
I can see that the part I've
been using the most lately,
it's the [INAUDIBLE]
interface, categories
for one particular team, some
different best practices.
And this is really
cool, because I
can see if users are using the
tool in one way or another.
For example, we are seeing
that some parts of the tool
are not being used
that much, [INAUDIBLE]
either redesign them or
even stop working on them.
Because if we are working on
different parts of the tool,
but the users are not
actually using those things,
we are wasting the
time and the money.
This is just an example
of what you can do.
Depending on your business,
you will have different metrics
that are more important for you.
But once you have
the queries assigned,
you don't want to
come here all the time
and be writing the queries,
executing the queries.
Because it's wasting time, and
it's not the best way to do it.
So the next thing
I want to show you
is how you can automate
this interface with BigQuery
so you can have your report
sent to you periodically.
In order to work with BigQuery,
you can use different things.
If you're a developer, probably
you want to use the API.
But you can use something
called Google Apps Script, which
is a smaller version
of JavaScript.
If you know JavaScript,
then you already
know how to use Apps Script.
And you can interact with
virtually any API from Google,
like, for example, Google
Analytics, or Google BigQuery
in this case,
Gmail, Google Docs.
Let's see what I'm doing here.
I am creating a new
folder on my Google Docs.
And in this new
folder, I'm going
to create a new spreadsheet
with the date of today.
And I'm going to call
a function to populate
the data from my new users.
Then I'm going to create a
new sheet in the spreadsheet.
I'm going to add
the deleted users,
insert a new sheet
for the unique users,
populate the data, and
just send the email.
And after I execute
this script, I'm
getting an email directly
into my Gmail inbox
with the summary
of how many users
are new in this month, how
many users have removed
from the tool, and how many
unique users we are seeing,
and the link to the spreadsheet
in which you can see directly
all the results, like the report
here for the three metrics
that we have taken every day.
And this is just a
spreadsheet, so you
can do any graphs you want.
Anything you want to
do, it's up to you.
But let's see the
detail of how I'm
getting this data
into the spreadsheet.
The easiest part is
just sending the email.
So if you go to the
Mail tab, you're
going to see sending an
email is as easy as adding
the recipient of the email,
copy anyone you want,
add the subject, as the body
of the email as an HTML,
and that's about it.
Before this function,
what I'm getting
is the reference for the
link to the spreadsheet,
which is a spreadsheet that
you can see later in the email.
I can show you here.
So yeah, we're inserting
the link directly.
So the most difficult
part in this script
is creating the data
for the spreadsheet.
But that's also quite easy.
The first part is we
have three metrics.
I have three functions here.
One is getting the new users per
month, which is a query quite
similar to the one I showed
you before in the console.
Then I have another
one, which is
for the deleted users per month.
And then I have one
for the unique users.
And once you have that, the
only thing you have to do
is just launching
the query, which
is a matter of calling
BigQuery and getting
the reference for the job ID.
And then we are just
going to be waiting
until BigQuery gets
the results for us.
So we are are sleeping
every half a second
until we have the results.
And when we have the
results, we are just
going to populate those results
into the different cells
of the spreadsheet using
a very simple JavaScript.
So that's about it.
Once you have that, you
are ready to get your data
into your spreadsheet.
And yeah, that's how it
looks, as I showed you before.
But the last part
we need to do is
you don't want to
run this manually.
You want to schedule
so periodically,
you are getting this
report to your mailbox.
If you go to the
triggers in Apps Script,
you can see in our case
we have a date timer.
So every morning
between 9:00 and 10:00,
we are getting the
mailing to the inbox.
But if you have
other requirements,
you can, for example, set
time driven every hour,
or even every minute.
And as an example, this year at
Google I/O, I met one person.
And he's using Apps Script,
BigQuery, and Google Docs
to have a live dashboard.
And every 30 seconds, he's
refreshing the queries.
And you can see in real time
how the graphics are changing.
So depending what
you want to do,
you can have different options.
But the interesting part for
us, it's really easy to set up.
OK, so that's how
we're using BigQuery.
And in case you are wondering
why we didn't use something
like Google Analytics, it's
because we are getting data
from different places--
not just from the website,
but also from our API.
And in Analytics, getting
data from other sources
is kind of tricky.
The other thing is
we are interested
not only in the
individual users, how
they are clicking around
links or anything.
We wanted to keep track of
how they are collaborating
in different teams, what we
are sharing with each other.
So we needed a more
powerful solution,
still keeping a
fraction of the cost.
And to us, it's working.
Because we have at the
moment like 10 million rows,
we are running the daily
reports that you just saw,
and we are paying
like $1 a month.