Google Sheet Formulas Every Marketer Needs (feat. David Krevitt)

4
2


– 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!

4 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here