- In this video David is gonna tell you
what formulas to use to analyze your data in Google Sheets.
All and more coming up right after this.
(upbeat music)
Hi there and welcome to another video
of MeasureSchool.com where we teach you
the data-driven way of digital marketing.
My name is Julian and on this channel
we do marketing tech reviews, tutorials,
and give you tips on better analysis just like this one.
So if you haven't yet, consider subscribing.
Now, you might know I am a big fan of Google Sheets
because I can take data from different datasets,
such as Google Analytics, Facebook, AdWords,
and put them all together in one Google sheet,
then analyze my data there
and build a dashboard of it as well.
Now, once you pull in all that data
from these different sources,
you will probably have a spreadsheet of raw data
and then you need to work with that data
in order to get it in the right format,
get insights out of it, and maybe also
build a dashboard of that.
And the tools that you are using
to do all this are formulas.
Now, I've asked my friend David from Coding For Losers,
he also has a YouTube channel right over there,
to come up and answer the question
what are the most used formulas for marketers nowadays
to do analysis within Google Sheets,
and here's what he came up with.
David, take it away.
(guitar playing)
- I'm not that good at guitar, just started learning,
but to me learning guitar and learning Google Sheets,
you know, when I first started learning it
five or six years ago, they're really similar,
similar to learning anything.
You have to put in time practicing,
or else you won't get any better.
And there's learning curve.
You really have to kind of learn things in a specific order,
otherwise you really end up confusing yourself,
more than you really have to, and having less fun.
And that's the third thing.
If you don't have fun doing it, if you don't have fun
practicing and you don't have fun
moving up that learning curve, you won't enjoy doing it
and it's, honestly, not even worth doing.
So, stick to Excel if you feel like you get it
and you don't want to get into another learning curve
and have fun with it.
But Google Sheets, over the last couple of years,
has really blossomed in terms of what it can do.
You can now use all kinds of add-ons, like Blockspring
or Supermetrics, or the Google Analytics add-on.
It seems like every service now,
there're so many different add-ons
that you can use to pull data into sheets
and really make use of it there,
without having to do CSV exports
and all that kind of clunky stuff.
And on the other side, in terms of like display of data,
Google Data Studio is now this really great free,
sexy dashboarding tool and reporting tool.
So, you can use Google Sheets as this kind of hub
to sit in the middle of the work that we do
as digital marketers, pull data in using add-ons,
use sheets as your hub to calculate all your metrics
and all your changes overtime and all of your stats,
all the stuff you want to look at,
and then push them up to Data Studio
so you have a nice, clean dashboard there
to share with your team or other kinds of reports.
So, Google Sheets has really become the Swiss Army knife,
I would say, for us, digital marketers,
anyone working in startups or tech,
recently, over the last couple years.
But, there is that learning curve.
There is a fair amount that you need to learn
to be comfortable and be fluent working in Google Sheets.
So, let's talk about Google Sheets formulas specifically
and kind of how you can limit your vision
in Google Sheets formulas, just learn the ones
that you really need to know upfront
and potentially never even expand your vision
outside of this set of, I would say,
about 10 formulas that you really need to know
to kick ass in Google Sheets.
The first one to narrow your focus,
when thinking about sheets formulas,
are to think about the jobs they do,
because they really only perform, as I've seen, three jobs,
and those three jobs are: they're wrangle data
in terms of plucking out just the pieces of data
that you need removing everything else.
They snipe data for you, so something like VLOOKUP
would be sniping out just the value that you need.
And they count, they help you come up
with all of the averages and sums and counts
for all the metrics that you need to calculate,
the kind of aggregation functions.
So those are the three jobs really,
and once you think about formulas in that way
you can pick a few formulas per job
and forget about all the rest of them.
So, if you have QUERY you don't need COUNTIF
and examples like that.
So the first job of formulas, wrangling data.
Those are doing things like processing text and dates,
turning a date string into just a year or a month,
filtering data that you don't need,
and merging data together so taking data
that's in two tabs and putting it into one tab.
And there're a few formulas that we use to do that.
For processing text we'll use things like,
formulas like LEFT and RIGHT, the TEXT function itself
for working with dates frequently, date formulas like TODAY.
So if you want to calculate the last week
you can always do like TODAY minus seven, stuff like that.
For filtering data I really frequently use IF and IFERROR,
which are kind of logic functions but let you,
say you have a referring URL path
and you want to boil it down to a channel,
you can use IF statements to classify certain texts
in URL as a channel in the UTM tags.
Just one kind of offhand example.
Merging data, I'll often use formulas like ARRAYFORMULA
to pull together large datasets into one tab,
and also these curly braces that you see here
are really really helpful in Google Sheets,
and we'll get into why later.
So the second job of formulas is sniping.
That's you have a specific value you're looking for
from a table of other values and you want to look it up.
So, the most common usage of sniping is the VLOOKUP formula
and I also, in times where VLOOKUP doesn't work,
say you're looking, you want to look left in a table
versus to the right, you can use INDEX for that instead.
So VLOOKUP and INDEX are really
kind of the same type of formula.
And the last job of formulas is counting.
Now, this is where you can get kind of crazy
with all the different formulas
you want to use, you can use.
You can combine things like FILTER with COUNT or SUMIF,
but I honestly choose to just use queries for counting.
For all averages, counts, sums, max, min,
all of this aggregation work I hire query to do that job.
So now you're up to speed on the three jobs
that formulas do, wrangling, sniping, and counting,
and about 10 formulas that you can use
to do those jobs in Google Sheets.
So hopefully, this will really help narrow your focus
when you're learning sheets formulas
and make it a lot more fun to get started.
There is a cheat sheet that Julian will link to
in this video in the description
and you can use that cheat sheet to really dive
into the needy-greedy of learning all these formulas,
work through a bunch of examples, and get some of that
fun practice that I talked about with guitar.
Take care.
- Alright, so there you have it.
These are the 10 formulas that you should know
as a marketer to analyze your data in Google Sheets.
Pretty interesting, right?
Now, we've got the links that David mentioned
in the description below, but he also did a video
on how to use these formulas on his YouTube channel
right over there, and you can also probably click
right here right now to view this video.
Now if you haven't yet, consider subscribing
to our channel over there, because we'll bring you
new videos every Wednesday.
My name's Julian.
Till next time!