Building a dashboard with 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 show you how to
build a dashboard using the google
analytics spreadsheet add-on now if
you've never used the spreadsheet add-on
before i definitely recommend checking
out my previous video where i explain
the basics of how to use and install the
add-on this video is going to be a
little bit more advanced so like I said
if you've never used it before
definitely check that other video out
first so what we're going to do today is
I'm going to show you how to build a
dashboard we're then going to embed that
dashboard on a third-party website so
you can share it with whoever you want
we're going to schedule a report to
update automatically in the background
so that our dashboard data is always
correct and current and you don't have
to manually update this yourself and
we're going to do all of this without
writing any code so let's go ahead and
get started the dashboard that I want to
build I wanted to see if we can recreate
an existing dashboard to kind of you
know make it more of a challenge and on
our demos and tools site we have this
dashboard using chart j/s and the embed
API a lot of people really like it one
question we get a lot is people are
confused at on how to how to use this
dashboard themselves on their own
website and we kind of explain with the
code here and show you exactly how you
can do that but I totally understand if
you're not a developer this can be a
little bit intimidating so I wanted to
see if we could recreate this entirely
just using the spreadsheet add-on to
kind of demonstrate its power so what is
this dashboard showing us well we have
four visualizations the top left one is
comparing this week's traffic versus
last week's traffic to the right we have
a similar thing with this year versus
last year you know both of these kind of
just see how we're trending and then
below we have information about our top
browsers and the top countries that our
users are coming from so let's go ahead
and start making our our reports we know
we're going to need this week versus
last week so let's call our first report
this week and I'm just going to go ahead
and use my personal account here I don't
want to expose any sensitive data
belonging to anybody else so for our
metrics I'm going to choose sessions and
then Fordham
I'm going to choose date because we know
we're going to be comparing week to week
okay so if you notice that by default
the add-on uses last end days and sets
it to seven and that might seem like
what we want because we're doing week to
week data but if you look closely at the
visualization you'll see that it's going
from Sunday to Saturday and today is
Thursday if we were just new to the last
seven days we would be getting Friday to
Thursday and so that's not quite what we
want we want to always be showing this
week compared to last week where this
week maybe a partial week depending upon
what day it is so that means we know
that end date needs to be the current
day so we can use the spreadsheet two
day function and to get start date we
need to get whatever the most recent
Sunday is and that can be a little bit
tricky but spreadsheets has a function
called a weekday where you can pass in a
date and it will return a number
representing the day of the week so
today is a Thursday and that's a
cheaters turning five you know where
sunday is one Monday is two and so on
and so forth what we need is we need to
say okay whatever date today is subtract
whatever day of the week it is to get
the previous Sunday and actually we need
this weekday function to return us zero
based index where you know sunday is
zero Monday is one instead of what it
does
where Sundays one and Monday is two but
we can fake that by just subtracting one
it's pretty easy so this will return us
whatever the the most recent Sunday is
now we know we also need a report called
next week or I should say last week and
the date range here is where the end
date is whatever the previous Saturday
is and we can get that by we know we
already know where the previous Sunday
is and we just subtract one we can get
the previous Saturday and we can do a
similar thing here where we get the end
date and then subtract seven to get the
Sunday prior to that okay so we have our
first two reports let's go ahead and run
them and see what data we get back
so here's our last week report and this
other tab shows this week now you'll
notice that this is only five days which
is exactly what we want it's a partial
data set so let's combine these two in
this other sheet we have down here to
create our visualization and so I'll
call the the headers this week and last
week and the the x-axis will be the day
of the week so let's just start out with
Sunday and we'll just let spreadsheets
automatically fill this in now for last
week we need to just reference the other
sheet that like the data cells in the
other sheet so we could do that by using
this notation here and I just happen to
know that the cell number is B 16 and
then we'll just same thing for last week
okay so now we just select these cells
and click the visualization button and
we can create a visualization so let's
go ahead and choose an area chart and
insert that into our sheet and it's just
changing these values around to make it
look more like the other the other
visualization let's just kind of change
the color here and we'll move this below
set our title to this week versus last
week I'll just say sessions all right
there we go we have our first
visualization so let's do a very similar
thing for this year versus last year I'm
just going to copy these values
again we want our end date to be today
that's correct for a start date here we
want to be whatever the first day of the
year was so January 1st of the current
year a spreadsheet has a date function
where you can pass in a year a month in
a day and we turn the date for that and
so in this case we need to figure out
whatever the year is for the current
year so we can use the year function for
that and just pass in today and we'll
that will return the current year and
then we know we want January the first
month and when you want the first first
day so this will give us January 1st of
the current year and then here we do a
very similar thing where we just
subtract 1 to get the end date for last
year and then here we subtract 365 to
get the start date for last year just
will ignore the possibility of a leap
year just to keep this simple and then
the dimensions we need to change this
from date to month because we want to
bucket this by by month all right let's
run these reports we should get two more
sheets at the bottom representing our
new reports ok so we have this is our
last year report and this is our this
year we're going to go ahead and go to
our combined sheet and do a very similar
thing where you say this year last year
and then let's set our x axis labels to
the months one to any again just
referencing the previous sheets here B
16
here be sixteen and select these cells
and create another visualization so to
be consistent with the example we'll use
a column chart and I'll just move this
around so you can see it better let's go
ahead and choose the same color we did
before put this below will remove this
axis label and let's see if we can
shrink these a little bit or angle them
so they all fit kind of in a in the same
line better and then we'll call our
chart this year versus last year
sessions alright so we have our first
two let's go ahead and create the last
ones so this one is going to be top
browsers for the date range let's just
go ahead and say the last 30 days to
keep it current will keep sessions as
our metric and for our dimension will do
GA browser now this will just give us
all the browsers we only need a top 5 so
we'll say max results is 5 and we want
this to be sorted by you know the the
top browser like to the least frequently
used browser so we need to sort it in
descending order by sessions and then
we're going to do a very similar thing
for top countries where we just have to
change the dimension to GA country now
we'll run these reports and we should
get again two new sheets in the bottom
that have this data
okay so there we see top countries let's
go ahead and create this visualization
will use a donut chart and then four top
browsers will do the same thing
okay so now we have all our
visualizations now what we need to do is
embed them onto a third party website so
I have a Google Sites site that I've
kind of set up and just a blank page
this doesn't have to be Google Sites you
can just use you know an HTML document
or a WordPress site or any kind of see a
master anything that you can enter HTML
code into so this is going to give us
our HTML code window and then all we
have to do is go over here and and then
share these visualizations or I should
say publish them and then just copy the
embed code so we just publish this chart
click embed publish and then we get an
iframe HTML sent but we just have to
copy that and then bring it over here
and paste it in as HTML then I'll do
that for all of the other charts publish
this
get our top browsers
and then lastly we'll get our top
countries
so this renders these visualizations
stack on top of each other that's not
quite what we want we can fix this by
creating a table 2x2 table I'm just
going to drag these into the table
all right so here we have it here is our
dashboard so we still need to set this
to automatically update because this
shows us the data for right now we just
ran as reports but if you come back
tomorrow it's just going to show the
exact same thing we get it we need to be
updating automatically so we can go we
can do that by going back to our
spreadsheet and from the add-ons menu
clicking scheduled reports and then we
can pick our schedules and Google sheets
will run these reports in the background
based on the schedule that we choose so
to enable this we just click the check
box to enable the reports to run
automatically and let's just have this
you know for simplicity sake let's just
have this run every hour so now our
schedule is scheduled to run every hour
I don't want to wait an hour to show you
this so I can I can demonstrate how this
would look by just changing the view ID
and then running the reports manually
and then you'll see that the dashboard
does in fact update when the report data
changes and this is just another random
view I I know I have access to so we've
run all of these and as you can see if
we look you know at these sheets you can
see that you know this this
visualization looks different this one
also looks different and so when you go
back to our dashboard all we have to do
is refresh and the data will appear over
here because these visualizations are
live it's actually pulling in the data
that is currently in the spreadsheet so
let's refresh and as you can see here is
the updated visualizations so so had we
just waited for the schedule to run we
would have also got our new data so
that's it for today I hope you enjoyed
this showing you how to create a
dashboard with the Google spreadsheet
add-on please send us any feedback that
you may have if you build any dashboards
ourselves we'd love to kind of see what
you guys can do and also check out the
spreadsheet add-on documentation if you
have any more questions about how to use
it alright thanks for watching