Advanced Google Sheets for Data Analysis
4.5 (35 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
340 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Advanced Google Sheets for Data Analysis to your Wishlist.

Add to Wishlist

Advanced Google Sheets for Data Analysis

Using Google Sheets and apps like Zapier to save hours at work
Best Seller
4.5 (35 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
340 students enrolled
Created by David Krevitt
Last updated 10/2016
English
English [Auto-generated]
Current price: $12 Original price: $100 Discount: 88% off
4 days left at this price!
30-Day Money-Back Guarantee
Includes:
  • 1 hour on-demand video
  • 4 Articles
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion

Training 5 or more people?

Get your team access to Udemy's top 2,000 courses anytime, anywhere.

Try Udemy for Business
What Will I Learn?
  • 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
View Curriculum
Requirements
  • 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)
Description

‘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.  

Who is the target audience?
  • 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.
Compare to Other Data Analysis Courses
Curriculum For This Course
24 Lectures
01:04:05
+
Intro to the Lazy Way
3 Lectures 06:55

Learn how you'll use Google Sheets to cut the time you spend working with data.  

Preview 01:59

Why working with data in spreadsheets is the same as baking a cupcake.  

Preview 02:13

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.

Preview 02:43
+
The Lazy Toolbelt
4 Lectures 06:06

Why you're better off letting software take this part of your job.  

Preview 01:56

Dive into Blockspring, a Google Sheets Add-On that lets you pull data from roughly 100 sources (including Google Analytics, Mailchimp and Twitter) directly into your Sheet.  

WTF is Blockspring?
01:17

Dive into Supermetrics, a Google Sheets Add-On designed primarily for pulling Google Analytics and Adwords data into Sheets - it's a favorite of many SEO and PPC experts out there. 

WTF is Supermetrics?
01:31

Meet the simplest way to pass data around the web.  

Zapier allows you to send data between any two apps based on a specific trigger - for example, sending Tweets from a list to a Google Sheet, as we'll do in this course.  

WTF is Zapier?
01:22
+
10 Sheets Formulas to Know
2 Lectures 03:03

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.  

Preview 02:50

This lesson will take place entirely in a Google Sheet - work your way through tabs 1-10 to learn each of the formulas. 

Make a copy of the Google Sheet from the attached resource link.


Learn the 10 Formulas
00:13
+
Answering Basic Questions
4 Lectures 12:54

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.


 

Copy the Example Sheet
00:20

What are the top questions (by retweets) being asked by this Twitter list

We'll find out the answer using a simple QUERY function.

Question 1: Top Questions
02:55

Who tweets the most often on the list? What about just in the last day?

To answer it, we'll use the QUERY function, combined with the TEXT function to process dates.  

Question 2: Top Tweeters
04:28

What's the most common day of week to tweet?

We'll answer using a handful of functions: QUERY, ARRAYFORMULA, IF and WEEKDAY.

Question 3: Top Day of the Week
05:11

Make a copy of the quiz here:

https://docs.google.com/spreadsheets/d/1Y1heFQPYiYYE3TfLFrZMR83BxtoH6JdOJf8ZKl24IeY/copy

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 help@codingisforlosers.com and include a link to your spreadsheet.  

Quiz 1: Basic Querying
3 questions
+
Answering Advanced Questions
3 Lectures 17:23

What's most common hour of the day to tweet?

We'll answer using a combination of the formulas QUERY, ARRAYFORMULA, LEFT and RIGHT.

Question 4: Top Hour of the Day
04:40

What were the most frequently used hashtags?

We'll find out using a combination of QUERY, SPLIT, LOWER and ARRAYFORMULA.

Question 5: Top Hashtags
07:07

What domains were shared most frequently by this list?

We'll make a leaderboard using the QUERY, INDEX and SPLIT functions.

Question 6: Top Domains
05:36

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:

https://docs.google.com/spreadsheets/d/1TSBleuX1EzMp-1b_sIUnnuQRGba4GAtNN3Ed3806CeA/copy

If you're stuck at any point, drop a note to help@codingisforlosers.com and include a link to your quiz sheet.  

Quiz 2: Advanced Querying
4 questions
+
Visualizing your Answers in Data Studio
6 Lectures 17:05

We'll walk through how you can visualize data within spreadsheets, and why Google Data Studio is the future of lazy visualization. 

Intro to Google Data Studio
02:28

Walking through the process of adding your spreadsheet to Data Studio as a data source, in order to visualize it in charts.  

Connecting your Sheet to Data Studio
02:27

Pulling your Sheet data to build a simple table - presented in a much more user-friendly format than viewing data within a spreadsheet.

Building a Simple Table
02:11

Learn about the power of calculated columns, which will allow you to do much of your work with data from within Data Studio instead of a Sheet.

Calculating Metrics in Data Studio
03:57

Data Studio allows you serious flexibility with creating charts for different date ranges.  We'll walk through how to take advantage of it, and some of the pitfalls to watch out for.  

Building Time Series Charts
03:48

You can go wild with styling in Data Studio, but I prefer the lazy way: styling the entire dashboard with one click.  

Styling Charts and Reports
02:14

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:

https://datastudio.google.com/u/1/#/org//reporting/0B7-PuiNCTLO8NGVnU1JxenlrUWM/page/m9Y


​Quiz 3: Building your own Dashboard​
1 question
+
Cupcake Data Analysis and You
2 Lectures 00:41

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.


Your Turn!
00:37

Crack the Trello board (from the attached resource link), where I store every spreadsheet data analysis I've ever built.

Bonus: Access the CIFL Template Vault
00:04
About the Instructor
David Krevitt
4.5 Average rating
35 Reviews
341 Students
2 Courses
Growth Marketer

I'm currently the head of growth at Coach, a venture-backed education startup in New York City.

I publish everything I know about code-free automation on my blog, Coding Is For Losers, including a vault of Google Sheets templates. 

I teach advanced Google Sheets, how to use Sheets add-ons like Blockspring, Supermetrics, and Zapier for data analysis, and SQL for beginners.