‘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:
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.
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.
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.
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.
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.
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.
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.
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.
What are the top questions (by retweets) being asked by this Twitter list
We'll find out the answer using a simple QUERY function.
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.
What's the most common day of week to tweet?
We'll answer using a handful of functions: QUERY, ARRAYFORMULA, IF and WEEKDAY.
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.
What's most common hour of the day to tweet?
We'll answer using a combination of the formulas QUERY, ARRAYFORMULA, LEFT and RIGHT.
What were the most frequently used hashtags?
We'll find out using a combination of QUERY, SPLIT, LOWER and ARRAYFORMULA.
What domains were shared most frequently by this list?
We'll make a leaderboard using the QUERY, INDEX and SPLIT functions.
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.
We'll walk through how you can visualize data within spreadsheets, and why Google Data Studio is the future of lazy visualization.
Walking through the process of adding your spreadsheet to Data Studio as a data source, in order to visualize it in charts.
Pulling your Sheet data to build a simple table - presented in a much more user-friendly format than viewing data within a spreadsheet.
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.
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.
You can go wild with styling in Data Studio, but I prefer the lazy way: styling the entire dashboard with one click.
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:
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.
Crack the Trello board (from the attached resource link), where I store every spreadsheet data analysis I've ever built.
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.