Introduction to the Google Analytics Spreadsheet Add-on Video Lecture | Google Analytics: For Developers - Data & Analytics

hey everybody my name is philip bolton
and today i'm going to demonstrate how
to use the google analytics spreadsheet
add-on now if you're unfamiliar with
add-ons for Google Docs and sheets they
basically allow you to extend the
existing functionality of those
applications with some new features so
in the Google Analytics spreadsheet
add-ons case it allows you to query the
API and get the results back right into
your spreadsheet this is great for doing
advanced data insulation and all the
things that you would normally want to
do with your data in a spreadsheet so
let's go ahead and install the add-on
from the add-ons menu choose get add-ons
and then look for the Google Analytics
add-on in the list and here it is and
then press the plus button to install it
now it's going to need your permission
to do some basic things obviously it
needs access to your analytics account
data and it also needs the ability to
you know write that data to your sheet
so go ahead and choose accept to allow
it to do that and then it will install
the add-on you'll notice once the add-on
is installed there's now a Google
Analytics sub menu item from the add-ons
menu and there are three tasks you can
create reports run reports and schedule
reports so let's go ahead and create a
report so imagine we have a website and
we're curious to learn the relationship
between our new users and our returning
users so let's create a report called
users and I'm just going to choose an
account here and then we'll make the
metric users and since we're curious
about how this is happening over time
let's make the dimension date so what
this will do is it will create a report
configuration sheet and this stores all
of our report data so we just create a
report called users and as you can see
that's in its first column here if we
create additional reports they'll be in
the second third fourth columns and so
on on the Left these values are the
query parameters these are the values
that are sent to the API and then you
know our report values kind of represent
the values for those keys you'll notice
that the default date range is last
seven days you can change that instead
an explicit started
if you want but I think for our case the
last seven days is it's perfectly fine
so let's go ahead and run this report
and then what will happen is it will
create a new sheet and the sheets name
will be users which just corresponds to
the name of our report and then it has
the data that was returned from the API
so the metadata is up here kind of the
totals are here and then the breakdowns
are at the bottom so I said you wanted
to compare users to new users so let's
go ahead and create another report for
new users so I could choose create new
report from the add-ons menu but since I
already know these values it's often
easier just to kind of copy and paste
change the title to new users and then
the metric also to GA new users let's go
ahead and and run these reports so now
you see we have another sheet called new
users corresponding to our second report
and what we need to do is we need to
combine these the data from these two
sheets to make a visualization so I'm
going to go ahead and put some column
header placeholders up here and then we
need to just pull the data from these
other sheets so if you if you look
looking at cells kind of a 16 and B 16
and below so we just need to reference
those a 16 users B 16 and then this one
was new users B 16 then we can just kind
of click and drag as you would normally
do in a spreadsheet to fill this out so
if we select these cells and then click
the insert chart icon we can create a
visualization from this data and since
this is the timeline let's choose an
area chart and go ahead and choose
insert so there you go now we have our
first visualization we can share this
visualization with people we could
publish it to the web we can do you know
embed it on a third-party dashboard
whatever we want and since this
visualization represents the values in
these cells
when these values change the
visualization will automatically update
so that means whenever we run these
reports the values will change and then
and then our visualization will change
what we can do here
which is great is we can create a
schedule to have a reports run
automatically so then we don't have to
manually update these reports kind of
every time you want new data so what I
can do is I can choose enable reports
are automatically and I can set the
schedule to be whatever I want so let's
say that I want mainly to show these at
a recurring meeting I have every Monday
at 9 a.m. so I could choose every week
on Monday and I'll say sometime between
8:00 and 9:00 a.m. run these reports and
then I'll know that at my Monday 9:00
a.m. meeting my report data will be as
fresh as possible so that's it for the
spreadsheet add-on if you're looking to
go a little bit deeper I have another
video that explains from start to finish
how to make a more complicated dashboard
and I would definitely check that out if
you're interested in also look at the
spreadsheet add-on documentation on our
website and that's it for now thanks for
watching the video