Advanced Google Sheets for Data Analysis
- 1 hour on-demand video
- 4 articles
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to Udemy's top 3,000+ courses anytime, anywhere.Try Udemy for Business
- Pull data from services like Google Analytics, Twitter, and more into Google Sheets
Use Blockspring, Supermetrics and Zapier to automate your data pipeline
Perform data analysis in Google Sheets, using a limited number of formulas
- Build charts and dashboards in Google Data Studio
- Familiarity with spreadsheets and writing basic spreadsheet formulas
- Desire to pull data from services like Google Analytics, Twitter, etc
- Have a Google Apps account (free with every GMail address)
‘Data analysis’ probably isn’t in your job description - it’s something you do only out of necessity.
You get asked a question about your work, and you’re expected to present the answer.
You build reports, export data from tools like Google Analytics, and make charts - not the most fun part of your job.
But what if it was?
This course presents a new approach to data analysis - the lazy way.
Google Sheets has become a weapon of mass laziness - when combined with tools like Blockspring and Zapier, it allows you to drastically cut your time spent wrangling and analyzing data.
In this course, you’ll learn the three-part process we use at Coding is for Losers to analyze data:
- Pulling data into Sheets using apps like Blockspring or Zapier
- The 10 key Google Sheets formulas we use to wrangle data
- Build beautiful charts and dashboards in Google Data Studio
If you’ve ever used a spreadsheet, you’re closer than you think to becoming a data analysis master.
*Warning: This course is _not_ for spreadsheet beginners.
- This course is for you if: you use spreadsheets to work with data, but want to take your skills to the next level. If you're doing marketing, sales, analytics, finance or ops, that probably means you.
- This course is *not* for spreadsheet beginners.
- As a matter of motivation, you'll get the most out of this course if you have a specific dataset you'd like to dive into. Whether that's Google Analytics data for a website, YouTube data, Salesforce data, it doesn't matter, but it'll help you visualize how you'll make use of these skills in your day-to-day.
Learn how you'll use Google Sheets to cut the time you spend working with data.
Why working with data in spreadsheets is the same as baking a cupcake.
Data analysis only exists to answer questions.
You can avoid wasting tons of time by asking good questions, or by not bothering to answer bad questions.
Find out the one key attribute that separates the good from the bad, and learn the questions we'll focus on answering in our analysis of Twitter data.
Why you're better off letting software take this part of your job.
Spreadsheet formulas only perform three jobs - wrangling, sniping, and counting.
Learn what each of those are, and the handful of formulas you'll master in the worksheet this video.
Now that we're up to speed with how to pull data into Sheets, and the 10 formulas we'll use to analyze it - it's time to dive into the nitty-gritty of answering questions.
Over the next 6 videos (and 2 quizzes), we'll answer a bunch of questions to hone in on how we can best engage with our sample Twitter list, Analytics is for Losers.
Follow along with the video by making a copy of the example sheet from the attached resource link.
Make a copy of the quiz here:
Just as we're doing in the sample questions, you'll want to use the 'tweets_zapier' tab as your dataset for the 3 quiz questions.
Stuck on a question? Holler to email@example.com and include a link to your spreadsheet.
Time to take it up a notch. Answer three questions that require advanced querying and aggregation, similar to the last few examples here.
Make a copy of the quiz here:
If you're stuck at any point, drop a note to firstname.lastname@example.org and include a link to your quiz sheet.
Now it’s your turn to take Data Studio for a spin.
Your goal is to chart the total count of tweets by date, using data from the ‘tweets_blockspring’ tab of your copy of the example worksheet (make a fresh copy here).
Count of tweets isn’t a metric anywhere in the raw data - you’ll have to calculate it by creating a calculated column (you can count any other metric or dimension as a ‘count column’).
Let’s make two charts:
- A simple table, showing date and count of tweets (to check your calculated column)
- A time series Bar chart, showing count of tweets from August 16-22nd. Also turn on the date comparison in your chart, populating tweet count data from the week before the August 16th.
For extra credit:
Using data from the text_hashtags tab of the same sheet, make a pie chart of the top 10 hashtags used.
Ready to check your work? Compare your report against the demo dashboard at:
Congrats on making it to the end of the course - I know working with data can be a slog :)
I'm sure you're already envisioning how you can apply your newfound Google Sheets superpowers to your work: questions you're curious to answer, and sexy dashboards you'll build to share those answers.
As I mentioned at the beginning of the course, the best way to ultimately learn is to practice on data that's important to you. Time to get out there and do it!
Make a copy of this Cupcake Data Analysis worksheet from the attached resource, which will walk you through the questions I ask myself anytime I'm setting out on a data analysis project.