Dear Analyst

Unlike the NBA, you can't easily pick and choose the top "plays" of 2021 in the data analytics field like you can with basketball games. This list is by no means exhaustive and is just based on my reading of news and trends in the data analytics world. It's easy to talk about big changes to tool and platforms that occurred throughout the year, but I'll try my best to focus on actual job or organizational trends that affect the data analytics profession. The bigger swag will be my predictions for 2022. They might be just a continuation of trends from 2021 or heck...even 2011. Certain things won't change much (like the use of Excel at work) but who uses data and in what forms will constantly change.

What happened in 2021?

1. The "Analytics Engineer" job title

While a data analyst spends their time, well, analyzing data, the analytics engineer helps prepare data for data analysts. The analytics engineer is similar to a data engineer in many ways, but is a bit closer to the business. It's probably common for an analytics engineer to become a business analyst and vice versa. I think the organization that put this role on the map is dbt (read more here).

I used to think an enterprising data or business analyst did the job of an analytics engineer. This may still be true at smaller companies. In most large organizations, I think there is a clear separation now between those who transform data and those who analyze the data. Take a listen to episode #58 with Krishna Naidu of Canva to see how analysts at Canva need to think more like software engineers; further showing why the analytics engineer role is here to stay.

A good video on what an analytics engineer does:

https://www.youtube.com/watch?v=ixyzF4Dy9Us

2. Democratization of datasets and visualizations

I remember working in a role where one of the head business analysts was tired of getting ad-hoc requests to see customer data sliced and diced different ways. This would require the analyst to write SQL queries to answer some C-suite executive's question. While these questions are important for making business decisions, these one-off questions and subsequent SQL writing was not sustainable. The business analyst ended up organizing a 1-hour session for all his business stakeholders to do a crash course on SQL and the data warehouse so the executives would feel empowered to pull the data themselves.

Can you guess what happened during that training?

Eyes were glazed over, people ended up answering emails on their laptops, and the business analyst was still fielding one-off questions after the training.

With the data visualization tools enterprises use today, the C-suite doesn't need to run their own SQL queries anymore. A basic understanding of filtering and sorting means charts can be manipulated to answer the questions you have for your data. Business and data analysts are no longer the bottlenecks for a dataset. Data is permissioned up and down the organization and access is granted as long as you have a business need for the data. I would take a listen to episode #57 with Nadja Jury of Education Perfect to hear how data analysts partner with business stakeholders to deliver the exact data stakeholders need.

I was playing around with a dataset on TikTok recently to show a PivotTable trick. The raw data is a list of all Friends episodes that have aired starting in 1994. I noticed that the raw data requires some cleanup so decided to take the dataset and to some cleaning operations and also try out some exploratory data analysis. I also explored building a simple dashboard using PivotTable Slicers in Excel. This is the process I might follow with cleaning up any raw dataset. The data isn't too complex so it might be a good dataset to practice on. If you want to use the sample data, download the raw data here. This episode might be better for watching than listening, so you can see the full YouTube video below as well.

https://youtu.be/kwPekvEqrPg

A quick view of the data

The dataset is relatively straightforward. It's just a list of all Friends episodes with various attributes associated with each episode:

Issues with the dataset

You'll notice some issues with this raw data that will make it difficult to do any exploratory analysis:

* The Episode column is in dates instead of integers* The Rating/Share column is one value and ideally they should be separated into two unique columns* The U.S. viewers column is actually a text column where the "number" is written out as "21.5 million"

There are some other formatting issues with the dataset but these three issues prevent us from putting this data into a PivotTable where we can filter and sort the data. We want numbers to be actual numbers so that we can apply a SUM or AVERAGE to the column when summarizing the column in a PivotTable. The tutorial walks through solving these issues with the MID(), FIND(), and LEFT() functions.

Simple dashboarding with PivotTable Slicers

In addition to doing some exploratory analysis, we also want to make this data usable for our colleagues. By adding some basic PivotTable Slicers, we can turn the analysis into an interactive dashboard that anyone can filter and sort without having to go into the PivotTable options. Slicers are also available in Google Sheets too!

Other Podcasts & Blog Posts

No other episodes mentioned!

I haven't seen a good use case or template for the GOOGLETRANSLATE function in Google Sheets. I was inspired by Blake Burge's Top 8 Google Sheets Tweet and thought a bit more about when you might use GOOGLETRANSLATE in Google Sheets. I suppose if you work as a translator or at the United Nations, you need to translate text in a spreadsheet versus a general translator tool. I think you would use GOOGLETRANSLATE when you want to dynamically translate text from one language to another and perhaps do some word analysis on the translated text. I thought it would be interesting to translate famous quotes and lines from The Godfather and create an dynamic translator tool in Google Sheets in the process. Spreadsheet here.

Source: The Guardian

Setting up quotes to translate

In the Google Sheet I have some famous quotes from The Godfather in column A. Column B is where I want to translate the quote into another language. Column C is just for translations into Chinese which we'll talk about later.

Data validation for creating a dropdown menu

To make this a more dynamic tool, I want to turn cell B1 into a dropdown menu. This way you can select any language (from a list) and Google Sheets will automatically translate the text into that language. Off to the side in columns E and F, I wrote the main languages I want to translate into and the two-letter abbreviation. Those abbreviations can be found here.

I've talked about data validation before (fancy name for saying "dropdown"). See episode 46 for another instance where you would use data validation.

I only select cells E2:E7 in this situation but you might want to increase the range by a few or several rows depending on how many languages you want to be able to translate into. Now cell B1 contains a simple dropdown of the languages listed in cells E2:E7:

Setting up the Google Translate formula

In cell B2, we write the following formula:

=GOOGLETRANSLATE(A2,"auto",vlookup($B$1,E:F,2,0))

Let's look at this in context of the spreadsheet and break down each part of the formula:

The GOOGLETRANSLATE function takes in 3 arguments:

* Text you want to translate* Source language of your text (2-letter abbreviation)* Destination language of your text (2-letter abbreviation)

A2 contains our text. For the second argument, I could've written "en" to represent English, but "auto" is a little more flexible as Google will try to detect the source language. This means you can put text in any language and Google should be able to detect it.

The VLOOKUP for the 3rd argument is to find the two-letter abbreviation that represents the destination language we want to translate to. Notice how the lookup value is cell B1 and it's locked. In the previous section, we established cell B1 as the dropdown that contains the languages listed in cells E2:E7. When I drag the formula down, I want cell B1 to stay fixed since that cell always contains the language I want to translate to.

Experimenting with different languages

I love a good deal, especially when it comes to getting a good flight. Ever since I got into churning a few years ago (signing up for credit cards to get rewards), I started booking flights primarily with reward points from these credit cards. I spend more time searching for flights with good point redemptions than I'm proud of, but when you find a great flight it's like finding an undervalued company whose stock is about to pop. Or for the millennials out there, it's like finding a meme-coin for $0.000001 before Elon Musk Tweets about it. This post definitely gets into the weeds of the reward hacking ecosystem, and may be too in-depth for some. But there are some Google Sheets skills you can gain from seeing the tool I built for this episode (copy Google Sheet here).

One of the most coveted seats one can book with award miles: Singapore Airlines suites. Source: The Points Guy

Award flights: from hack to hobby

The inspiration for this episode came from multiple places. Booking award travel started off as a hack I thought was only meant for people 1) with too much time on their hands and 2) who travel for a living (e.g. writers). Over the last few years, I realized my online and newsletter "diet" is slowly being taken over by these award travel blogs.

One Mile At A Time: one of my favorite news sources for award travel.

It first started with trying to understand the lingo in subreddits like /churning and /awardtravel. I even started going to meetups where like-minded award travel hackers congregate (shout out to Spencer Howard from STTP for organizing an NYC meetup earlier this year!). Recently, I've been listening to Chris Hutchins' All The Hacks podcast who I feel brings award travel hacking to the "mainstream." While Chris is an award travel aficionado at heart, he brings this "hacking" mindset to other aspects of his life. This brings me back to knowing rules of the game you play.

Knowing the rules to "win" a game

I wrote and spoke extensively on relating games you play in life to Squid Game in episode #80. I mentioned travel hacking as one of these games, but wanted to spend this episode diving more into the rules of this game. Knowing the rules of booking award flights can help you "win" a nice business class flight. When you explore the edges or extremes of these rules, that's where you can get a really good redemption or benefit. That's what makes award flight hacking so fun, in my opinion. It's kind of like taking a model or some code and testing for the edge cases. You don't want your model to output something that's ridiculous.

You hear about these "error fares" where the airline makes a mistake and you're able to book a flight for a fraction of its normal cost. This is not the same as using the award rules of airlines, their airline partners, travel alliances, and credit cards to find these good deals.

What I like about All The Hacks is that Chris applies this mindset to things like your 401(k), shopping online, getting a low interest rate on your mortgage, etc. These are all little games you can play to maximize and optimize your earnings or savings.

I finished watching Squid Game a few weeks ago and still have all these swirling thoughts about the series. Through a few basic survival games, the show brings to light wealth inequality, capitalism, and other aspects of the human condition. Trying to find the loose threads between the show's deeper meaning and data literacy is a tough exercise. If you are interested in another Excel or Google Sheets tip, this episode ain't for you. But if you spend time analyzing and optimizing your personal and professional lives, then the idea here might be of interest. Or, I could be over analyzing and simplifying these concepts. Either way, hopefully we'll learn something about ourselves afterwards.

Source: POPSUGAR

The most pivotal scene in Squid Game

I can't not mention my most favorite scene in the whole series. Through this short scene, you learn the premise of the whole show: rules. These scene also sparked the direction I wanted to take with this post. You learn why the games exist, why the players decided to stay in the game (after episode 2), and how a strict adherence to rules is the most important strategy to staying alive in the games.

The scene is the start of episode 6 where the players are being led into a room to play the game with the marbles. As they walk through the dollhouse-like stairs, they see the doctor and some of the guards hanging from the ceiling. Then someone comes on the loudspeaker:

Source: Netflix

Players, what you witness before you is what remains of those who broke the rules for their own benefit. They tainted the pure and fair ideology everything here has been built upon. Each and every one of you is considered an equal within the walls of this facility. You must be guaranteed the same opportunities without being disadvantaged or facing any kind of discrimination. We truly apologize for allowing such an unacceptable incident to occur.

No subtleties, subtexts, or posturing. I love how black and white the translation is. Mike Hale, TV critic at The New York Times had this to to say about the show's commentary on socioeconomic classes:

But what probably puts it over the top is the aspect of the series that most makes me dislike it: its pretense of contemporary social relevance, a thin veneer of pertinence meant to justify the unrelenting carnage that is the show’s most conspicuous feature. [...] Its goal, a common one at the moment, is to ingratiate itself with its audience by confirming their accepted ideas.Mike Hale, The New York Times

To be fair, Hale is approaching the show from a TV critic's point of view. I agree with Hale's opinion that the show isn't really saying anything new about capitalism and why we need to "take down the rich." But he misses the effect of the show's direct approach at calling out the issues of today. Perhaps some of us have simple minds and we need to be told things directly and in layman's terms. In my opinion, the more direct the approach, the more it makes you question these "accepted ideas" Hale speaks of.

Rules and norms

We've established that the Squid Game world is a "fair world," and everyone gets a fair shot at winning each game. The front man and his underlings make sure of this by hanging the doctor and some of the guards in the scene above.

While each game's rules are meant to be followed, norms also play a role in how the players behave. You have the woman who drops the marble and the her opponent quickly yells at her...

When you're creating some dashboard or report, one of the most common formatting operations you'll find yourself doing is expanding the column width to fit the text or numbers in a cell. You might also be expanding the row height to fit the size of the text, but I think it's less common than expanding the column width. In concert with the shortcut to select the entire column (CTRL+SPACE), AutoFitting the column width is a super powerful shortcut to show the data that you need. The problem? The shortcut only applies to Windows Excel users. For whatever reason, Microsoft decided not to give a native shortcut for AutoFitting columns in Mac Excel. Until now. Well not really until now, but this is the workaround for all you Mac Excel users who want to AutoFit columns like a boss.

Video tutorial for this episode:

https://youtu.be/xOeMYDFC3rE

AutoFit columns in Excel for Windows keyboard shortcut

As a quick refresher, the keyboard shortcut for AutoFitting columns for Excel on Windows is pretty simple:

ALT, O, C, A or ALT, H, O, I

This is a "sequential" keyboard shortcut where you hit each key one at a time. ALT, O, C, A actually comes from Excel 2003 and ALT, H, O, I is the more modern shortcut. When you press these keys, you'll see the ribbon light up in Windows Excel:

Source: O'Reilly Media

This makes learning shortcuts on Windows Excel pretty easy because you can just press letters to open up menus and buttons on the ribbon. The AutoFit columns shortcut automatically expands the column to fit whatever you've selected in the column. This means you don't have to drag-and-drop the column anymore like this:

Source: Spreadsheeto

AutoFit columns in Excel on Mac keyboard shortcut

Mac Excel users have probably come to learn (and hate) that you can't use keyboard shortcuts in the ribbon. I don't think Microsoft just overlooked this feature for Mac Excel users. Rather, it's a limitation of the Mac OS in general. So what can Mac Excel users do to AutoFit columns? There are a few solutions/workarounds:

Method 1: Open the ribbon with F6 and TAB keys (worst method)

There is a way to open the ribbon in Mac Excel and that's with the F6 key. The big caveat is is you don't have function keys turned on in your Mac OS settings, then you'll have to press the function key and the F6 key. So the shortcut for most folks is FN+F6. Once you press the F6 key, you'll see the green highlight show up over the "Home" option on the ribbon. You may have to press F6 a few times until the green selection goes over the "Home" tab. Once the "Home" tab is highlighted, you can press SPACE to get into the "Home" tab:

Once the Home tab is open, the goal is to get to the Format button button near the right of the ribbon because that's where the AutoFit Column Width option is located:

How do you get there? From what I know the only way is to hit the TAB key several times. As you hit the TAB key, you'll see the green selection go through every option in the Home tab. You would think that you could do SHIFT+TAB to cycle backwards (start from the end of the ribbon) since the Format button is near the end of the ribbon. No dice. You literally have to hit TAB up until the green highlight hits the Format button:

Heaven forbid you hit TAB too many times and you end up shooting past the Format button. This means you have to cycle through all the options on the Home tab again to get to the Format button.

Following up from episode #77 about mental models, I wanted to take things one step further and tie mental models to one of my favorite shows. If you came here hoping for the latest Excel trick or keyboard shortcut, apologies in advance. If there's one theme you should take away from these posts, it's being a good analyst means you're able to connect differing ideas, trends, and frameworks together.

One show that has informed my thinking over the years is Billions. For those uninitiated, Billions is a show about a hedge fund manager (Bobby Axelrod) who tries to gain wealth and power through unscrupulous means while evading the regulatory arms of Chuck Rhoades, the U.S. Attorney. The show is loosely based on real-life events where then U.S. Attorney for the Southern District of New York Preet Bhara prosecuted hedge fund manager Steve Cohen of S.A.C Capital Advisors in 2013. The show is filled with esoteric references, finance jargon, and yes, some mental models that are worth noting. Here are three mental models from three different episodes.

Source: Showtime

1) Survivorship bias and Axe's bad trade on BioLance

This is an example of not necessarily a mental model, but one of the many cognitive biases that can cloud our judgement. In addition to survivorship bias, there's a bit of confirmation bias and the hot hand effect at play here.

Setting up the scene: Axelrod is sitting in his office and waiting to hear the news about a company called BioLance he's invested in called BioLance. Before the CEO of the company goes on the conference call to discuss the status of their inhibitors for fighting type 2 diabetes, the trader (Mafee) who is executing the trade on behalf of Axelrod tries to talk Axelrod out of the trade. Mafee believes BioLance won't have approval from the FDA to move forward with manufacturing the inhibitor, and that Axelrod should short BioLance instead given the current competitive market. Axelrod claims he's done the research and believes BioLance's inhibitors will get approved, leading to a stock jump. The BioLance CEO gets on the conference call and announces the FDA has not approved their inhibitors. Axelrod is dumbstruck and is trying to figure out how his decision-making led to this $1B mistake.

The reason for Axelrod's flummoxed decision-making was he felt guilty about a previous misdeed he did and was punishing himself. Notwithstanding the real reason for the bad trade and the need for the plot to make sense, there is some interesting biases to take note of as Axelrod reflected on his trade.

A perfect example of survivorship bias is news about companies and startups who are raising millions in funding or doing an IPO. As an aspiring startup founder, you see this news and believe that it must be easy to create a company and have it be successful. The problem is you are overlooking the status of the majority of other companies who are failing our going bankrupt since there isn't as much news about these floundering companies.

Source: Google

Most of Axelrod's employees (including Axelrod himself) believe that Axelrod is never wrong. His hedge fund has billions under management and he wouldn't get to this position if he didn't make the right bets. There is survivorship bias here in terms of people focusing on the trades that made the fund millions of dollars, so this trade on BioLance shouldn't be any different.

Then there is the potential for confirmation bias, or interpreting and seeking information that confirms your current beliefs. Axelrod walks through his decision-making process with...

The promise of mental models is that it helps you bridge the gap between what's in your mind and what happens in real life. They are interesting thought exercises in how you think. But that's where it ends for me. How often do you approach a big decision in life and decide to pull from your little bag of mental models to make the best decision? Life (and the decisions you face) are messy. Yet, we see countless bloggers and authors provide their own takes on mental models (I've referenced Shane Parrish's mental models multiple times, to be fair). An entire industry of leadership and career coaches use mental models to describe a person:

Source: Amateur Coach

I'm sure this coach, does some great work with his clients, but something about having my entire being defined with a few lines and phrases doesn't sit right. Perhaps mental models are best left for VCs and thinkbois on Twitter. The issue I have with mental models is that they are easy to write and talk about, but the examples people cite seem too farfetched and "clean" to work in real life. With that said, here are a few mental models I like to read and think about, but rarely use in real life (because life is messy).

1) Power laws and the 80/20 principle

The classic Pareto or "80/20" principle. No doubt this mental model has come across your Twitter timeline in the last week. Via Wikipedia:

The Pareto principle states that for many outcomes, roughly 80% of consequences come from 20% of causes. 

Thinkboi thoughts on Twitter:

https://twitter.com/shaneaparrish/status/1312735218776772608

One example a really like from history is from WWI and a German fighter pilot named Manfred von Richthofen (aka the "Red Baron"). Unlike other fighter pilots at the time, Richthofen approached aerial combat with conservatism. This meant less aerial acrobatics and what you might otherwise see in Top Gun.

Source: History.com

His fleet was constantly outnumbered by Allied forces and he knew if his men entered into combat head-to-head, he would always lose. Instead, he created a special formation for his fleet called "The Flying Circus." This formation would just hammer the hell out of a specific point in the Allied's defenses. This led to him taking down more Allied planes than he would have otherwise using traditional aerial combat techniques. In the context of combat, Manfred was able to 80/20 his results by taking advantage of the multiplier effect whereby one side can create outsized impact just from having a more numbers than the other side in a battle. I could be conflating two different mental models here, but the effect of dominating a small part the enemy's defenses day after day must have a huge impact on their strategy and morale.

80/20 principle and data

Back in the real world where you have numbers to crunch and spreadsheets to format, how might one use this mental model at work? Again, I don't actively think about using the 80/20 principle before I go and pull some data, but the simplest way for this model to manifest itself in a spreadsheet is sorting. What does this mean?

You're doing some analysis and trying to find the key driver for why sales are spiking or why customers are churning. Pull the data you need and instantly sort on the column that contains your key metric. 80% of the time (see what I did there?), that simple operation of sorting the data set will help you find the answer you're looking for because the product or customer that is driving the issue will s...

Taking a slight detour off of the data analysis/spreadsheet path and getting into the world of productivity hacks with this episode. A productivity hack can mean different things, and in this case it's a hack to improve your workflow at, you guessed it, work. This hack gives you the ability to create a Google Calendar event by sending yourself an e-mail in Gmail on your phone. The goal of this productivity hack is this: avoid switching contexts (apps) to schedule a calendar event. I get into the problem and solution below, but if you want to just start using the Google Apps Script go to this repo. Watch a quick tutorial on how this script works below:

https://www.youtube.com/watch?v=DbbT0zPGzVY

Your calendar is what you actually do

Like many of you out there, I live by my Google Calendar (for better or worse). The app just gets one thing done really well and integrates with all your other apps. I have my personal Google Calendar overlaid on my work calendar so I can see personal appointments and such. Over the years, I've started using my calendar for more than just events and appointments. It's almost become a to-do list, reminder tool, and calendar all bundled in one.

For instance, I'll typically have all-day events scheduled to act as a reminder for when I need to pay a bill. I may have an event called "follow up with so-and-so" just to remind me to send an email to that person. This starts to getting into my Gmail workflow of snoozing emails to remind myself to follow up with someone. But "seeing" that event on my calendar gives me more visibility into what I actually need to do a given day or week whereas the snoozed email just pops up when it's scheduled to.

If you're interested in this gentle dance between your calendar, email, and to-do list, check out this Coda template from Des Traynor I worked on a few years ago. This is the money Tweet which shows why our calendars dominate our lives:

https://twitter.com/destraynor/status/1143853719135621121

Boiling the frog

This productivity hack solves a problem I didn't really know existed until I reflected on how much time I spend using the Google Calendar app on my phone (I have an Android). When I'm going through emails on the Gmail app, I'll inevitably need to schedule an event or reminder to do something in the future. This is how some emails may potentially turn into a Google Calendar event:

* Get an email from a friend to get lunch -> Create a calendar event with something like "Hold for lunch with X"* Promotional email from Doordash to get 15% only if you use by X date -> Calendar event on X date that says "use Doordash promo"* Email comes on the weekend showing random fees on my Internet bill -> Schedule event on Monday that says "call Spectrum to get rid of fee"

It's not a perfect system, but it works for me. The issue is that I'm constantly closing the Gmail app and opening the Google Calendar app to add these events to my calendar.

The Google Calendar app is easy to use and I've been content with doing this Gmail <-> Google Calendar dance over the years. A few weeks ago I realized how much I've been switching back and forth between these apps and how much time I waste context switching. Why do I need to leave Gmail to create these quick Google Calendar events? Once I made this realization, I cringe a little bit every time I have to open the Google Calendar app just to schedu...

In episode 52, I talked about how to extract text from a cell. You might use this formula to extract a certain value from a cell when your database or CSV export contains a bunch of miscellaneous prefix data. This formula relies on finding a certain character in the cell and then using the MID formula to get the data you need. Turns out this formula doesn't solve all use cases. In particular, when it comes to getting first names, middle name(s), and last names (surnames) from a cell. Evert Scholtz left the following comment on episode 52's YouTube tutorial:

You're in luck Evert! While the formula discussed in this post won't account for salutations, the solution should get you on your way to getting salutations to work too. This formula trick allows you to extract the first name, middle name(s), and last name from a cell where there are multiple spaces in between names. The reason I like the formula (in particular for the middle names) is because it builds on the formula from episode 52, the previous episode on counting the number of words in a cell, and the idea that formulas are composable (also discussed in the previous episode). Link to the Google Sheet for this episode is here.

Watch a tutorial of this episode:

https://youtu.be/d2CPXF1CtdE

Formulas for first name, middle name(s) and last name

If you just want to see the formulas, take a look below. The formulas assume your data starts in cell A2 with the output looking like this (make a copy of the Google Sheet to use formulas directly):

Formula to extract the first name

=LEFT(A2,FIND(" ",A2)-1)

Formula to extract middle name(s)

=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,"",MID(A2,FIND(" ",A2)+1,FIND("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(" ",A2)-1))

Formula to extract last name

=RIGHT(A2,LEN(A2)-FIND("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

As with all my posts, I like to dig into why and how these formulas work, so read on if you want to to see the step-by-step tutorial. The second sheet on the Google Sheet also contains the intermediate steps to get to these formulas so you can see formula composability at its finest.

Creating intermediate columns to test your formulas

I didn't experiment in one cell to get any of the above formulas (especially the formula for extracting middle names). A common practice is to create what I call "intermediate" columns to test out the final formula you want to use.

Columns in Google Sheets and Excel are "cheap." They cost nothing to create, and give your colleague or end user the ability to see how you came to your solution. Typically you would hide these intermediate columns from the final output so that it doesn't clutter your report,