Dear Analyst: Recent Episodes

View Details

When you think of your data warehouse, the “semantic layer” may not be the first thing that pops in your mind. Prior to reading Frances O’Rafferty‘s blog post on this topic, I didn’t even know this was a concept that mattered in the data stack. To be honest, the concept is still a bit confusing to me since I’m not building data warehouses and data products all day. Frances grew up in northern England studying mathematics during the recession. The decision to jump into data was a function of what jobs happened to be available at the time. Frances worked through a variety of data warehousing, BI, and ETL roles before looking more into the data management space like data modeling and cataloguing. This conversation is a deep dive into the world of data warehousing, data catalogues, and of course, the data semantic layer.

Enforcing data warehouse conformity for an insurance companyImagine an insurance company where the policies are in two different systems. Which database contains the “right” policy for a customer? This is the mess Frances had to deal with when she helped build out the insurance company’s data warehouse. What I thought was interesting is Frances’ team looked at the source data and then interviewed people in the business to understand how the data is generated and how the data is being used. The questions she was asking were pretty high-level:

1. What do you do on a day-to-day basis?
2. What works well and doesn’t work well?
3. What would you like the data to do?

Source: LinkedInData quality validation checks and global lookups were set up so that if a new piece of data entered the warehouse and it didn’t match, then the administrator would get an alert. They would then have to figure out what to do with that rogue piece of data to fit the rules that have been set up.

A methodology Frances brought up I’ve never heard before is the Kimball methodology for setting up a data warehouse or BI system. The main tenets of the methodology are basically how modern data warehouses are setup: add business value, structure data with dimensions, and develop the warehouse iteratively. This is an image of the lifecycle from their website:

Source: Kimball GroupFocusing on different layers of the warehouse “stack”Frances’ team first focused on the data source layer and tried to figure out where all the data came from. After that, then came the consolidation layer. That consolidation layer is where the data gets split into facts and dimensions.

I figured even for a data warehouse project, Excel must come into play at some point. Excel was used fro all the modeling to figure out what the dimensions and facts were. It wasn’t a core part of the warehouse but it was still a one-time use tool in the development of the warehouse.

The final layer is the target layer where we are getting more into the business intelligence realm. There are different ways the insurance company wanted to see the data. So Frances team had to create different views of the data to answer questions like: What premiums have we received? What transactions have come through? The actuarial team wanted to see what the balance was on an account so another view was created for them.

Frances noticed that different regions would call the data different things but they were all still referring to the same concept. There wasn’t a system to translate what a metric like gross revenue, for instance, meant in one region would mean in another region. This foreshadows the semantic layer concept Frances wrote about.

Source: MyReactionGifsCataloging 5,000 data attributes for an investment bankData catalog tools can get expensive (and rightly so) if you are managing thousands of data attributes and definitions. As I discussed in episode #129 with Jean-Mathieu, if you only have a handful of attributes, using Excel or Goole Sheets is completely doable as a data catalog.

The investment bank Frances was working for had many different source systems, KPIs, and measures that the entire investment bank was trying to get alignment on. Span this across a variety of financial products and the team came back with 5,000 attributes to put into a data catalog. The challenge was understanding the requirements from the finance, risk, and treasury departments to create a catalog that could be shared internally within the entire bank.

Frances’ team looked at the taxonomy first across loans, customers, and risk. They had an original glossary and compared the glossary with the new taxonomy. The main tool they used for the data catalog was Collibra. With this new catalog, new publishers of data had to abide by a strict format dictated by the catalog.

After one year and talking with 150 people, they finally launched the data catalog to the entire investment bank. I asked Frances how her team was able to best understand what different data attributes meant. The answer is just as you would expect: she asked people within the bank to send an example of the data attribute and how it’s being used.

Source: TumblrTranslating data for enterprise consumers with the semantic layerBack to the Frances original post about semantic layer: it historically is “trapped” in a BI tool, according to Frances. When Frances first started using SAP, there was a business objects universe in SAP which allowed you to create joins between tables and define data attributes. But these rules and definitions only existed in SAP.

Today, the semantic layer can show up in places like dbt, Collibra, graph databases, and more. There isn’t a “semantic layer vendor” that does it all (which is the first question I asked Frances about her blog post). The key takeaway is that the raw data in the data warehouse needs to be translated/converted into something usable by consumers within the enterprise. Frances said this translation is usually needed with legacy applications.

Source: G2 Learn HubThis is a good diagram of where the semantic layer sits within the data stack:

Source: Modern Data 101The next question to answer is: who owns the semantic layer? According to Frances, this also depends on your data team. It could be the data governance team, data management team, or even the data visualization team. If you’re looking at ownership from the perspective of product management, it would be the product owner. At the end of the day, it’s the team that is working with the people who are consuming your organization’s data.

Other Podcasts & Blog PostsNo other podcasts or blog posts mentioned in this episode!

The post Dear Analyst #132: How the semantic layer translates your physical data into user-centric business data with Frances O’Rafferty appeared first on .

View Details

If you could only learn one programming language for the rest of your career, what would be it be? You could Google the most popular programming languages and just pick the one of the top 3 and off you go (FYI they are Python, C++, and C). Or, you could pick measly #10 and build a thriving career out of it. Ergest Xheblati didn’t just pick SQL and decide to bet his career on it. He started as a software engineer, held various roles in data, and even became a product manager. After trying these different roles, Ergest would still find his way back to data engineering and has dedicated 15+ years to writing SQL for a living. In this episode he talks about why he loves SQL, reducing the number of dashboards in your company, and best practices for writing SQL.

Why Ergest loves writing SQLThe reason why Ergest loves SQL is also the reason how most things get invented: laziness. As that Stack Exchange thread points out, a lazy developer tends to find shortcuts and automations to make repetitive and tedious tasks less onerous. You could also argue that Excel shortcuts are a result of analysts being lazy and not wanting to use their mouse to do mundane formatting tasks.

Source: Invisible BreadAs it pertains to programming, Ergest saw that a standard framework might require 20-30 lines of code to pull some data from a database. Ergest could handle do that same operation by writing a few lines of SQL with a simple SELECT statement.

Solving business problems with technologyErgest was a data analyst, data engineer, and also what we call an analytics engineer. When Ergest was a data analyst, he didn’t have tools like dbt which prevented him from succeeding as an analyst. As with many data roles, Ergest still straddles multiple roles today. He still considers himself a blend between a data analyst and data engineer with SQL being his main tool of choice. At a high level, Ergest talks about “solving business problems with technology.”

Source: dbt LabsI think it’s important to emphasize this point which many other guests on Dear Analyst have pointed out as well. Learning tools like Excel and SQL are great, but if you cannot communicate your findings and solve real business problems with these tools, then what’s the point? I think data professionals get caught up with how to utilize a data tool’s features when time should really be spent on what can be done to solve your customer’s problems.

I recently had a conversation with a technical program manager who had an opportunity to sit on a few customer meetings with her sales team. She was amazed to learn about the actual problems her company’s customers face every day. It gave her a new perspective on the backend infrastructure her team supports.

Mining open source data with SQLMost of the projects Ergest works on are focused on business intelligence. For instance, he had to work on a project where the company wanted to build robust customer profiles. You typically want to see all these different aspects of a customer so you know how to best market to and retain the customer. From a data perspective, Ergest was writing SQL to transform and merge different data from different sources.

Some data source might have names of the customers while another source might have numbers. You then have to look at the session logs of what these customers are doing on your website and create tables based on this customer activity. Ergest is a proponent of the One Big Table (OBT) approach for this customer activity data to make querying and management easier. This graphic below shows the main structural difference between the standard star schema and OBTs:

Source: Databricks SQL SMEHow to stop building dashboards and answering ad-hoc questionsErgest wrote a great blog post a few months ago called Transforming a Data Culture. The blog post talks about how data teams can prevent the deluge of one-off data questions being asked by the business and to shift to being a more strategic partner. Does this sound like a goal or OKR your data team is striving for?

Source: iFunnyErgest did an audit at a company that had 17,000+ dashboards! Talk about not knowing which metrics matter. Ergest believes in going back to first principles when it comes to dashboarding. There are 4 questions Ergest believes you need to answer when creating a dashboard:

1. What’s happening?
2. Why did it happen?
3. What are you going to do?
4. What’s your prediction?

The blog post goes in-depth on how getting executive buy-in is the most important step in reducing the number of questions coming at the data team.

Best patterns for writing SQLErgest reviews a lot of SQL queries and saw mistakes and anti-patterns in how his fellow analysts and data engineers were writing queries. Surely, there must be a book about the best patterns for writing SQL, Ergest thought. There are many books on best patterns for coding and how to debug code. The only books Ergest could find on SQL were anti-patterns. He ended up writing a book called Minimum Viable SQL Patterns based on his experience reviewing other people’s queries. He breaks the patterns down into 4 buckets:

1. Query composition patterns – How to make your complex queries shorter, more legible, and more performant
2. Query maintainability patterns – Constructing CTEs that can be reused. In software engineering, it’s called the DRY principle (don’t repeat yourself)
3. Query robustness patterns – Constructing queries that don’t break when the underlying data changes in unpredictable ways
4. Query performance patterns – Make your queries faster (and cheaper) regardless of specific database you’re using

These 4 patterns are pulled directly from this workshop Ergest gave about SQL patterns:

According to Ergest, what separates his book apart from other books about SQL is that the patterns he discusses are based on writing professional/production-ready SQL for cloud environments. He assumes you are writing SQL to query data warehouses in AWS, Azure, or some other public cloud platform.

Other Podcasts & Blog PostsNo other podcasts or blog posts mentioned in this episode!

The post Dear Analyst #131: Key insights and best practices from writing SQL for 15+ years with Ergest Xheblati appeared first on .

View Details

This is a replay of an episode from the Make Sense podcast with Lindsay Tabas.

In the 1990s, large enterprises typically bought software in a tops-down approach. IT teams would get get Oracle software or Microsoft Office and get their entire organization to use the software. Since these tools are the default IT “blessed” tools, people start putting everything in these tools. This is why I think most people decide to push everything into Excel even though Excel is primarily meant for financial analysis. When it’s already installed on your computer and everyone knows how to use it, Excel becomes the crutch that we turn to regardless of the use case.

Source: xkcdShadow IT and the swinging pendulum of SaaS tools vs. Excel spreadsheetsIn this episode, Lindsay Tabas and I talk about why large enterprises rely so much on Excel. This is part of a bigger movement of shadow IT and citizen development where individuals build business-critical workflows without needing an engineer or developer to step in. We talk about the shift from the 1990s of big monolithic software platforms to the explosion of workplace SaaS tools going into the 2000s and 2010s. The pendulum keeps on swinging back and forth as the SaaS tool sprawl gets too wide for IT departments to handle.

Despite the ebb and flow of teams having freedom to pick up their own tools vs. IT shoving software down everyone’s throats, we talk about why Excel will never die. We talk about how to get off the crutch of using Excel and one of the strategies I mention is to have a curious mind and be willing to learn new to tools. Every week we see new tools launched on Product Hunt that are supposed to replace some feature in Excel. These tools were borne out of the frustration that comes with trying to do something in Excel that Excel was not meant for. Nevertheless, you need to keep an open mind to see what these new tools are all about. You never know which one of these tools just might replace how you use Excel.

Source: Not Boring by Packy McCormickOther Podcasts & Blog PostsNo other podcasts or blog posts mentioned in this episode!

The post Dear Analyst #130: What happens when we rely too much on Excel spreadsheets and shadow IT takes over? appeared first on .

View Details

When you’re organization is small, a centralized data team can take care of all the internal data tooling, reporting, and requests for all departments. As the team grows from 100 to thousands of people, a centralized data team simply cannot handle the number of requests and doesn’t have the domain knowledge of all the departments. Jean-Mathieu Saponaro (JM) has experienced this transformation at Datadog. He first joined Datadog in 2015 as a research engineer. He was part of the inaugural data analytics team which now supports 6,000+ employees. In this episode, he discusses scaling a self-serve analytics tool, moving from ETL to ELT data pipelines, and structuring the data team in a hybrid data mesh model.

Building a data catalog for data discoveryAccording to JM, creating a data catalog is not that hard (when you’re organization is small). I’ve seen data catalogs done in a shared Google Doc where everyone knows what all the tables and columns mean. When the data warehouse grows to hundreds of tables, that’s when you’ll need a proper data cataloging solution to store all the metadata about your data assets. This is when you move to something like Excel (just kidding)! In all seriousness, a shared Google Sheet isn’t a terrible solution if your data warehouse isn’t that large and the data structure isn’t very complicated.

Source: North Shore Data ServicesJM discussed a few strategies that helped them scale their internal data discovery tool:

Strong naming conventionsA pretty common pattern for data warehouses containing “business” data is using dim and fact tables. All tables in the data warehouse have to be prepended with dim or fact so that it’s clear what data is stored in the table. There are also consistent naming conventions for the properties in the table. Finally, the “display” name for the table should be closely related to the actual table name itself. For instance, if the table is dim_customers, the display name for the table would just be customers.

Snowflake schemaAnother common pattern is using a snowflake scheme to structure the relationship between tables. This structure makes it easy to do business intelligence (e.g. reports in Excel) later on.

Source: WikipediaCustomizing the data discovery experienceDatadog switched BI tools a few years ago so that the tool could be used by technical and non-technical users alike. They ended up going with Metabase because it didn’t feel as “advanced” as Tableau.

In terms of their data catalog, one of the key decisions going into picking a tool was being able to quickly answer the question: where do I start? Where do I go to learn about our customer data? Product data? This is where the discovery experience is important. JM said the entry point to their catalog is still just a list of 800+ tables but they are working on a custom home page.

JM’s team thought about the classic build vs. buy decision for their data cataloging tool. Given the size of their organization, they went with the building the tool internally. If the number of users was smaller, it would’ve been fine to go with an off-the-shelf SaaS tool. JM’s team set a goal to build the tool in a few months and it took them 3.5 months exactly. Building the tool internally also meant they could design and re-use custom UI components. This resulted in a consistent user experience for every step of the data discovery process.

Should you migrate data pipelines from ETL to ELT?When JM joined Datadog, he found that all the ETL data pipelines were done in Spark and Scala. If you were to ask me a year ago what “ETL,” “data pipeline,” and tools like Spark and Scala mean I would’ve thought you were speaking a different language. But once you hear the same terms over and over again from various analysts and data engineers, you’ll start to understand how these different data tools and architecture work together. If you are new to Apache Spark, this is a quick intro video that I found useful:

As Datadog grew, so did the number of data pipelines. JM saw the number of data pipelines grow from 50 to hundreds and Spark didn’t make sense as a data processing framework anymore. Every time you wanted to add a new field to a table or change a workflow, it required an engineer to submit a pull request and deploy the change to the data warehouse.

Eventually tools like dbt and came onto the scene which prevented the need for relying on engineers to make changes to the data pipeline. Analysts who are not on the core data engineering team could develop and test data pipelines by writing SQL. One might saw dbt is like the no-code/low-code data processing framework democratizing who can create data pipelines. As the data team scaled, their data pipelines migrated from ETL to it cousin ELT. The team uses Airbyte for the “extraction” step and dbt does all the data cataloging.

Source: AirbyteSince dbt opened up the data pipeline development process to more people outside the data team, it became even more important to enforce best practices for naming conventions for tables and fields.

Pros and cons of data meshingAnother term I didn’t learn about until a few years ago: data mesh. A definition from AWS:

A data mesh is an architectural framework that solves advanced data security challenges through distributed, decentralized ownership. Organizations have multiple data sources from different lines of business that must be integrated for analytics. A data mesh architecture effectively unites the disparate data sources and links them together through centrally managed data sharing and governance guidelines.

When JM first started working at Datadog, there was a central data team that did everything for every department at Datadog. The data team did the extraction, ingestion, dashboarding, and even recruiting. This is totally reasonable when you’re a small organization.

As the organization grew to thousands of people, it became harder for this centralized data team to cater to all the departments (who were also growing in size and complexity). The data team simply didn’t have the domain knowledge and expertise of these business units.

Source: Trending GIFsIf Datadog were to go full on data mesh, silos would form in each department. This is one of those situations where the data mesh sounds good in theory, but in practice, Datadog hired and structured their data teams to meet the needs of their data consumers. Having each team manage their own data extraction and ingestion would lead to a big mess according to JM.

Start with one team to prove the semi-data mesh modelJM’s team started with the recruiting team to prove this data mesh hybrid model would work. The recruiting team started hiring its own data analysts who understood the business priorities of the team. The analysts would help clean and process the data. An example of domain-specific data for the recruiting team might be engineering interview data. The analysts helped make sure that interviews were properly distributed among engineers so that no engineer was overloaded.

To see JM’s journey in more detail, take a look at this talk he gave in 2023 at the Compass Tech Summit:

Find something you like and have funPeople have given all types of great advice on this podcast in terms of how to switch to a career in data. Sometimes the advice goes beyond data and applies to life in general. Perhaps this is getting too touchy-feely but JM’s advice for aspiring data professionals is to “find a domain that you like and have fun.” Life is finite, after all. This is one of my favorite visualizations when I need to remind myself about the finiteness of life (read the full blog post from Wait But Why):

Source: Wait But WhyJM also talked about the ability to be flexible once you’re in the data world because tooling changes a lot. The definition of roles change a lot and new roles pop up every year the redefine what working in “data” means. Case in point is the analytics engineer role. JM’s advice is that you should feel empowered to follow wherever the industry may go.

Other Podcasts & Blog PostsNo other podcasts or blog posts mentioned in this episode!

The post Dear Analyst #129: How to scale self-serve analytics tools to thousands of users at Datadog with Jean-Mathieu Saponaro appeared first on .

View Details

This is a replay of an episode from the Citizen Development Live podcast with Neil Miller.

Citizen development is a relatively new term I learned about a year ago or so. To me, it’s using no-code tools at scale within a large enterprise. It’s a term that covers the population of people who are not developers, programmers, and software engineers by trade but know how to build apps and workflows to accomplish business-critical tasks. This is the definition of a citizen developer from PMI (Project Management Institute):

Low-code or no-code development is the creation of applications software using graphic user interfaces or minimal basic code instead of large strings of complex coding. This term is often used to describe citizen development processes and technology. Low-code and no-code technology provides visual drag-and-drop or point-and-click user interfaces, making them easy for anyone to use.

Source: PMI

In this conversation on the Citizen Development Live podcast, Neil and I discuss various spreadsheets I’ve built in the past, when to move beyond spreadsheets, and why citizen development is a growing trend within the enterprise. I referred to a talk I gave at the 2019 No-Code Conference where I spoke about building tools with spreadsheets (and why the spreadsheet is the real first no-code tool):

Other Podcasts & Blog PostsNo other podcasts or blog posts mentioned in this episode!

The post Dear Analyst #128: What is citizen development and how to build solutions with spreadsheets? appeared first on .

View Details

I wasn’t sure if this topic should be it’s own episode but it’s been on my mind ever since I came back from Atlassian Team ’24 (Atlassian’s annual conference). At the conference, I had the opportunity to meet with a few people who are just as interested in spreadsheets as I am. We talked specifically how Jira can best work with spreadsheets (Excel or Google Sheets) and different workflows that result from the combination of these two tools. It was fascinating to hear how company culture and old ingrained way of doing things leads to the usage of spreadsheets when Jira and its add-ons can accomplish 80-90% of what the business need is. This episode highlights some of the things we discussed at the conference and implications for the future for teams using Jira and spreadsheets.

Source: AtlassianWhat is Jira?Since most people following the newsletter are data analysts, I thought it would be relevant to first share what Jira is. Most would say Jira is issue-tracking software used by engineering and product teams to track software projects. The software aims to mirror agile and scrum methodologies for accomplishing tasks versus traditional waterfall techniques. The rituals behind agile and scrum are codified in Jira’s features, so that’s why the software is loved by thousands of engineering teams around the world. This is a good video from Atlassian on what a scrum project in Jira looks like. Near the end, you’ll see a backlog of tasks. The backlog is one of the most foundational principles of the scrum methodology and will serve as the launching pad for this discussion on Jira and spreadsheets.

Why do teams export Jira issues to Excel spreadsheets?One theme for why teams would want to export Jira issues into spreadsheets is reporting. We also talked about using other tools like Power BI for reporting purposes, but the intermediary step between Jira and Power BI is still a CSV export.

There are built-in reporting and charting capabilities in Jira. There are also a plethora of add-ons in the Atlassian marketplace for custom charts. The issue with the add-ons is they can get quite costly since you are paying on a per-seat basis. So even if the Jira admin is the one creating the charts, you still have to pay for the other Jira users who are simply viewing the charts. This charting add-on below is one of the most popular add-ons for Jira with 10,000+ downloads. Looks a bit like Excel, no?

Source: eazyBIDigging a little deeper, we also discussed how the Jira backlog is kind of like a datastore for what the product and eng teams are working on. You can think of this almost like another table of data in your data warehouse. What does this mean for a regular business user who doesn’t work on the eng or product team and still needs the data? Traditionally, they would write a SQL query to get the data they need, do their analysis, and call it a day. With Jira, they would need the Jira admin to export the backlog to a CSV and then they can go off into Excel and do their custom reporting, PivotTables, and dashboarding to show how the product and eng team’s work aligns with the rest of the work of the company.

Story points, finance, and HRIS systemsExpounding on the above point, being able to merge your Jira backlog with other business data is why teams are exporting from Jira into spreadsheets. During the conference, I brought up the point that other business data might just be other worksheets in your Excel workbook. Perhaps one tab has data from your customer support team and another tab has data from your sales team. Through a series of VLOOKUPs and INDEX/MATCHes, a product owner may be able to get a full P&L for their area of work. Perhaps ERP software can do this but can it get to the level of fidelity that a Jira backlog has? This is why it’s easier to just export all your data (not just Jira) into one Excel file and do the custom analysis in that workbook.

How to export Jira backlog to CSV after writing a JQL query. Source: QuoraRelating to this topic, one use case our group discussed was figuring out how much work was actually completed by the engineering team. To get an accurate picture of this, story points are included in the export. For those new to agile, story points are a unit of measurement for estimating the effort required to complete an item in the backlog.

The CSV export now contains the entire backlog, the engineer assigned to each item, and the story point estimate for the task. You can then combine this Jira data with data from an HRIS system like Workday to understand the output for each engineer taking into account PTO, holidays, etc. Furthermore, engineers might self-report how much time or capacity they are spending each project. Perhaps 50% of time is spent on Project A and 50% on Project B. These ratios (probably also tracked in a spreadsheet somewhere), can then be applied to the story points to get an accurate picture of how much effort was actually spent on the project as a whole.

Source: PlakyYou can take this one step even further by combining your Jira backlog data with costs and salaries from your finance system. Then you can start seeing the actual dollar costs for different software projects. This might be important for accounting teams as they may be interested in software capitalization and being able to compare software projects with other assets in the company.

The key takeaway is that these questions and answers start with exporting data from Jira into spreadsheets.

Benefits of exporting Jira backlog into spreadsheetsIf you’re a follower of this newsletter and podcast, you already know why spreadsheets are the preferred tool for business users. Stepping outside of Jira for a second, Excel is still one of the best analysis and prototyping tools for businesses of all sizes. Our group talked about why Excel and Google Sheets is still used within companies and why it’s the first thing you even think about exporting to. We all already have practice doing this in our personal lives. Think of the first time you were able download transactions from your bank statement into a spreadsheet. What did that moment feel like? Is magical a stretch?

Source: AmazonThere are other benefits for exporting your Jira backlog into spreadsheets beyond reporting. If other team members don’t have a Jira license, they can still “see” the data in a spreadsheet format (assuming the organization is a Microsoft Office or Google Suite shop). It’s not ideal, but emailing that spreadsheet around or storing it on Sharepoint makes that Jira backlog collaborative. Now others beyond the engineering team can get visibility into what the engineering team is doing.

Jira add-ons for niche use casesI mentioned the plethora of add-ons for custom reports in Jira. It’s amazing to me how many add-ons exist for very niche use cases in Jira.

One topic that came up during our discussion is how to calculate the time a backlog items spends in different statuses. When the item moves from “Not Started” to “In Progress,” you may want to know how much time has elapsed. This cycle time is important to understand how long it takes to complete tasks once they’ve started. There are add-ons for this in Jira but there are times when you may want to calculate the time in status according to your business rules. This means–surprise surprise–exporting to Excel and writing a formula to calculate the time it takes for items to move through statuses.

Snapshot of the Time in Status add-on for Jira CloudThe issue is that this granularity of data doesn’t exist in the native export in Jira. To get this granular level of data, you would need another export that would have duplicate entries of a task and the timestamp for when that task moved to a certain status. This data is available through the API, but that would require additional work beyond doing a simple export from Jira.

Importing a spreadsheet into JiraI didn’t consider the “other” direction of importing a spreadsheet into Jira until I met with people at the conference. To be precise, you can only import a CSV into Jira. The reason for importing from a CSV to Jira is when you want to make bulk changes or additions to your Jira backlog or perhaps you’re migrating from another issue tracking platform.

Another edge case I had not considered is that data entry into Jira is not straightforward for non-engineering teams. If you’re trying to crowdsource ideas for new projects to tackle for next quarter, do you ask your key stakeholders to simply create projects in Jira? What are the proper settings for the project? From the stakeholder’s point of view, all they care about is being able to add a new project quickly and perhaps a description of the project.

To make the data entry easier, you could use a Google Form to collect ideas from various business stakeholders. The form has standard fields like project name, project description, type of project (using a dropdown of pre-filled values), team, etc. Now you’ll have a Google Sheet of all the projects sourced from various parts of your organization in a standard format that works for submitting to your Jira workspace. Even after people submit projects, however, the Jira admin or DRI would have to clean up the submissions to make sure that only valid projects get imported into Jira.

Maintaining workflows with spreadsheets and JiraOnce your Jira backlog is exported out of Jira into a spreadsheet or the data is prepared in a spreadsheet to be imported into Jira, there is a whole separate set of issues that arise. We know that the spreadsheet is the easiest interface for non-engineering teams to use when it comes to Jira data, but someone still has to maintain that spreadsheet. Usually it’s the person who has the domain or business knowledge for why they need the spreadsheet in the first place and they happen to know enough formulas to get the analysis done.

In the moment, the business user gets the job done and goes on with their day. Inevitably, someone will ask the question: can we get the data for next month? Or next week? Now that business user has to think about a long-term solution for maintaining this spreadsheet or push it off to IT. This is where the spreadsheet can really hinder the progress of non-engineering teams. At the conference, we talked about a few different directions this could go.

Just do it manually each timeNot the most ideal scenario, but it gets the job done. After you’ve exported from Jira and your other tools, you brute force to get the job done. Some parts of the file might be automated or you set it up so that all you need to do is just paste in the raw data from the Jira export. Once you get good at doing the manual work, it becomes muscle memory. Thinking through an automated solution or moving off of spreadsheets entirely is an opportunity cost because you have become so fast at doing it manually. We talked about how this method is usually the preferred method when the analysis is ad-hoc or doesn’t need to be done frequently (e.g. quarterly).

Scripting with VBA or Google Apps ScriptYou decide you have some time to come up with an automated solution or you’re tired of people asking you for the analysis. So you decide to learn a little VBA for Excel or Google Apps Script for Google Sheets. I recently spoke with someone who figured out a way to write a Google Apps Script that pulls from Jira and dumps the backlog into a Google Sheets directly (using the Jira API).

While this solution does remove the manual work from the exporting from Jira and downstream analysis, the question remains: who will maintain this script? What happens when there are new custom fields in Jira that need to be pulled into the spreadsheet? If you are the only person that knows how the script works, you may find yourself scripting and gathering requirements from stakeholders on what data from Jira they want to pull.

Isn’t there an add-on for this?For Jira Cloud and Google Sheets, yes. It’s just called Jira Cloud for Google Sheets. In the Atlassian marketplace, it only has a few reviews but in the Google Workspace Marketplace, it has 2M+ downloads and over 100 reviews. It’s actually supported by Atlassian and it’s free. But according to the Atlassian Marketplace listing, the add-on was released in 2022 and hasn’t been updated since then.

The reviews are mixed and for those who gave 1-2 stars, the add-on just stopped working altogether. So try at your own risk and I wouldn’t implement it in any business-critical workflows. This review shows what happens when you depend on the add-on and it stops working all of a sudden:

This review speaks to the point I made about embedding a spreadsheet or an add-on in your workflow and then having that spreadsheet or add-on go down. You (in this case, Atlassian) is in charge of fixing the issue and other downstream stakeholders have to come up with workarounds. This Jira user now needs to do a regular CSV export from Jira and get it in the format that he had set up with the Google Sheets add-on.

Build vs. buy: manage the spreadsheet yourself or let Atlassian do it for you?The classic build vs. buy decision can be applied to the stance your organization takes on the usage of spreadsheets and Jira. You could rely on your TPMs and data analysts to pull data out of Jira and manage the scripts and spreadsheets themselves. This is the “build” scenario.

Atlassian knows that spreadsheets are a problem within organizations because they are brittle, prone to human error, and most importantly, no one typically takes ownership of the maintenance of that spreadsheet. So Atlassian gives you the opportunity to let them replace that spreadsheet at your organization with Jira Align. The product marketer for Jira Align did a great job of framing the problem on this landing page:

Saying Atlassian is giving you the “opportunity” to let them solve the spreadsheet problem for you is a bit of a euphemism. Atlassian sees a huge revenue opportunity with Jira Align which is why the pricing is on the higher side. It’s meant for large enterprises with 500+ Jira users. For 100 seats, Jira Align costs $155,000 annually (or around $130/mo/user). This includes “integrated” users who are still doing work in Jira Cloud and don’t need access to all Jira Align features.

Is Atlassian’s Jira Align worth it?With an enterprise license, your organization will also most likely get all kinds of human support. Maybe a few account managers, CSMs, etc. to make sure you’re feeling happy and getting the most out of the tool. The main question is what happens when you want to customize something about a chart or metric that is outside of Align’s current feature set? Do you also get an on-call developer who build that feature for you or prioritize your feature requests on Align’s own product backlog?

Source: I Am DeveloperI think you know where I’m going with this. Instead of not only exporting from Jira Cloud, some analyst may need to now export from Jira Align and merge that with other business data. I don’t necessarily agree with this paragraph on the Jira Align landing page:

Spreadsheet solutions are not designed for the rapid collaboration and information sharing that an innovative enterprise needs. A connected enterprise can quickly and easily answer questions like: Will we deliver on time? What are my team’s dependencies? Are we building the right things? How do we know?

I think the spreadsheet is exactly the “rapid collaboration” tool that most enterprises use because it’s not cost-prohibitive and knowledge workers know how to navigate a spreadsheet. However, if no team maintains the spreadsheet or the various workflows surrounding it, then Jira Align is probably worth it given it’s a managed service by Atlassian. Jira Align was actually an Atlassian acquisition back in 2020 and was formerly called AgileCraft. So there may still be some legacy integration issues between Jira Cloud and Align. But if you’re tired of exporting out of Jira into spreadsheets and would rather have this be someone else’s problem, Jira Align might be worth exploring.

Other Podcasts & Blog PostsIn the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• EconTalk: How to Avoid Lying With Statistics (with Jeremy Weber)

The post Dear Analyst #127: Spreadsheets vs. Jira: Which one is better for your team? appeared first on .

View Details

With an undergraduate degree in zoology and a master’s in public health, you wouldn’t expect Amanda Makulec to lead a successful career in data analytics and data visualization. As we’ve seen with multiple guests on the podcast, the path to a career in data analytics is windy and unexpected. It was the intersection of public health and data visualization that got Amanda interested in data visualization as a career. In one of her roles, Amanda was supporting USAID by analyzing open data sets and creating charts and graphs for publishing content. Her team consisted of graphic designers and developers. Designers would basically take her charts from Excel and add more color and add on text to the chart. Amanda found that large enterprises were facing the same challenges as the organizations she was supporting in public health (and enterprises have more money to throw at this problem). Thus began Amanda’s career in data viz.

How do you tell a data story?We’ve talked a lot about data storytelling a lot on this podcast. If there is one person who can crisply define what data storytelling is, it would be Amanda. This is Amanda’s definition according to this blog post:

Finding creative ways to weave together numbers, charts, and context in a meaningful narrative to help someone understand or communicate a complex topic.

We talked a bit about how data storytelling can mean different things to different people (this blog post in Nightingale talks more about this). You might work with a business partner or client who says they want a data story, but all they really want is just an interactive dashboard with a filter. Amanda cites Robert Kosara’s definition of data storytelling in 2014 as one of her favorites:

• ties facts together: there is a reason why this particular collection of facts is in this story, and the story gives you that reason
• provides a narrative path through those facts: guides the viewer/reader through the world, rather than just throwing them in there
• presents a particular interpretation of those facts: a story is always a particular path through a world, so it favors one way of seeing things over all others

Amanda stresses the 3rd bullet point as the most important part of data storytelling. If the audience has to walk away with one analytics fact from the story, what is that fact you want to get across?

Source: Effective Data StorytellingGetting feedback on your data stories and visualizationOne point Amanda brought up during the conversation which I think is worth highlighting is feedback. After you’ve published of launched an analysis, dashboard, or data story, you rarely get feedback on how effective the product was at telling a story. You might get some qualitative feedback like the dashboard answers specific questions or that the findings are “interesting.” But was the visualization actually effective at telling a story?

Amanda likes to ask people what they like and don’t like about her data stories and visualizations. Often people will get frustrate because the key takeaway from the data story is simply counter to what they believe. This leads them to questioning the validity of the data source. But you as the storyteller are simply conveying the signal from the noise in all the data.

During the pandemic, Amanda worked with the John Hopkins Center for Communications to create charts around COVID. Talk about telling an important data story! Amanda is presenting data about a worldwide pandemic while working with an organization that was at the core of reporting on the stats on the pandemic. Needless to say, the data stories and visualizations drew a variety of feedback. Remember seeing stories like this questioning how different entities and organizations were collecting and disseminating data about COVID? Being able to concisely present dense survey data about COVID is probably the toughest data storytelling job I can think of.

Applying principles of user-centered design to data visualizationBefore Amanda starts working on a new dashboard or visualization, she asks several questions about the project:

• Who is going to use the dashboard?
• When are they going to use it?
• What are their needs?

Before designing the dashboard, Amanda likes to borrow from the world of user-centered design to make sure her data visualization meets the goals of the end user. She creates mindset maps to make sure the dashboard is serving the right group. Journey maps also helps with figuring out how often the target audience will engage with the dashboard.

Source: The Interaction Design FoundationWe chatted about data exploration and data explanation. The explanation step is sometimes overlooked by analysts because so much time is spent on the nuts and bolts of creating the visualization. But data explanation is just as important because this helps lead the end user to the key analytical fact of the data story. This means having clear chart titles and annotations so that you’re guiding the user to the key takeaway of the story.

Data tools for building effective data visualizationsI love talking about tools so we spent some time talking about the tools Amanda uses to build her data stories and data visualizations. Amanda talked about understanding the constraints of the data tools so that you know what you can and cannot build with the tool. For instance, Amanda talked about Power BI not supporting dot chart plots before so she didn’t consider Power BI as tool in her toolbelt for telling data stories (if it involve dot chart plots). Other tools like Tableau and Ggplot are great for adding annotations to different parts of the data visualization.

Did you really mean story-finding?Amanda talks about how some people want more data storytelling but what they really want is “story-finding.” Coupled with data exploration, story-finding is all about finding the trends and outliers in a dataset before doing the actual data storytelling. This graphic from Amanda’s blog post neatly plots some of these common terms we hear in the data viz world and shows how important words are in describing what we want:

Source: NightingaleI asked Amanda what story-finding projects she’s actively working on and she talked about a project she’s working on with the Data Visualization Society (where she is the Executive Director). Her team has been trying to learn more about the membership (31,000+ people) so that they can create better programming for members. They partnered with the The Data School at The Information Lab to create a survey for members. As Amanda explored the data, she found that members are requesting information about professional development and work in a digital analytics capacity. The Data Visualization Society also issues a challenge to the community to come up with interesting visualizations using the survey data (see results from 2022 here). I really like this one done in Figma which shows which tools are most used by the members (surprise surprise Excel is on the map):

Source: James WoodAdvancing the profession of data visualizationWe talked about how Amanda got involved with the Data Visualization Society and how it keeps her connected with the broader data viz community. It’s a volunteer board and the the organization has its roots in the Tapestry Conference in 2018. Elijah Meeks (then a senior data viz engineer at Netflix) gave the keynote about a “3rd wave” of BI tools becoming popular like Tableau and Jupyter notebooks. The talk is definitely worth a watch if you’re interested in the history of data visualization:

The Data Visualization Society is a space for people working in the data visualization profession to connect. The main goals of organization are to celebrate the profession, nurture cross-functional connections, and advance the practice of data visualization. Their annual conference is aptly called Outlier and is coming up in June.

Landing your next data visualization roleAs with all episodes, I asked Amanda on advice she has for aspiring data visualization professionals. She had a lot to say on the topic. One thing that stood out to me is that all of us have the skills that translate well into data analytics and data visualization. Whether you are a writer or elementary school teacher, your communication and collaboration skills to produce a deliverable are the skills you need to be a data visualization expert.

Source: Global Investigative Journalism NetworkAside from joining organizations like the Data Visualization Society, Amanda suggested mastering fundamental design skills. Understanding how to declutter charts is one important aspect of being a data visualization expert. Of course, the Data Visualization Society’s journal has a bunch of great resources like this article on starting out in the world of data visualization and questions to ask when starting out in data viz. In the starting out article, I really liked this line about making things simple:

Simple is also beautiful in data visualization, and as long as what you’re creating is meeting the needs of your audience, you’re succeeding in making data more accessible to more people, which is an incredible talent in itself.

Source: Nightingale

Other Podcasts & Blog PostsNo other podcasts or blog posts mentioned in this episode!

The post Dear Analyst #126: How to data storytelling and create amazing data visualizations with Amanda Makulec appeared first on .

View Details

Sometimes pop culture and data analysis meet and the result is something interesting, thought-provoking, and of course controversial. How can one use data to prove definitely which Taylor Swift songs are the most underrated? Isn’t this a question for your heart to answer? Andrew Firriolo sought to answer this question over the last few months and the results are interesting (if you’re a Taylor Swift fan). As a Swiftie since 2006 (moniker for Taylor Swift fans), Andrew wanted to find a way to bridge his passions for Taylor Swift and data analysis. He’s currently a senior data analyst at Buzzfeed, and published his findings on Buzzfeed to much reaction from the Swiftie community. In the words of Taylor Swift, Andrew’s methodology and analysis just “hits different.”

From comp sci to data analyticsAndrew studied computer science at New Jersey Institute of Technology but realized he liked the math parts of his degree over the engineering parts. Like many guests on this podcast, he made a transition to data analytics. Interestingly, it wasn’t a job that propelled him into the world of data analytics. But rather, going to graduate school at Georgia Institute of Technology (Georgia Tech). GIT has some really affordable online technical programs including data analytics. After getting his master’s degree, he worked at Rolling Stone as a data analyst. This is the beginning of Andrew’s exploration into the Spotify API to see the data behind music. You can see some of the articles Andrew published while at Rolling Stone here.

Source: PocketmagsAfter Rolling Stone, Andrew landed his current role at Buzzfeed building internal dashboards and doing internal analysis. In both of his roles, he talks about using a lot of SQL and R. A big part of his job is explaining the analyses he’s doing to his colleagues. This is where the data storytelling aspect of a data analyst’s job comes into play. I call this the “soft” side of analytics but some would argue that it’s the most important part of a data analyst’s job. In most data analyst roles you aren’t just sitting at your desk writing SQL queries and building Excel models. You’re a business partner with other people in the organization communication skills are more important than technical skills.

Answering a Taylor Swift question with dataAndrew became a Taylor Swift fan through his sister in 2006. They both listed to the world premier of Taylor’s first album. Given his background in data, Andrew decided to answer a question about Taylor Swift that’s been on his mind for a while: what are Taylor Swift’s most underrated songs?

To read Andrew’s full article, go to this Buzzfeed post.

Andrew’s hypothesis was that there’s a way to use data to prove which songs in Taylor’s discography are most underrated. When I classify something as “underrated,” it’s usually a decision you make with your gut. But it’s always interesting to see the data (and the methodology) for determining if something is truly “underrated.”

Multiple iterations in song streaming analysisAs mentioned earlier, Andrew made good use of Spotify’s API. The API gives you a plethora of information about songs such as how “danceable” or “acoustic” a song is. Each characteristic is measured on a scale of 0 to 1.

For the first iteration of Andrew’s analysis, he simply compared a given song’s streaming performance to the album’s median streaming performance. The hypothesis here is that the less-streamed songs are considered the underrated songs. The result of this analysis was a lot of Taylor’s deluxe tracks.

Source: GeniusThe second iteration was to look beyond the streaming performance of the album the song is on. Andrew compared the song’s performance relative to album’s released before and after the current album. This surfaced some more underrated songs.

Getting the opinion of SwiftiesWhile Andrew’s analysis so far yielded some interesting songs, he found that these songs weren’t all that loved by other Swifties.

In his final iteration, Andrew implemented a quality score to his analysis. This is a more subjective number that would take into account the opinion of experts.

At Rolling Stone, they had a rolling list of expert opinions that were published in various places. He had a data set of 1,000 opinions on different Taylor Swift songs that he could use to qualify a song. The big question is, how much weight do you give the quality score? In the end, Andrew decided on a weight od 33% to each metric he tracked:

1. Percent difference between its lifetime Spotify streams and the median streams of its album
2. Percent difference between its lifetime Spotify streams and the median streams, including neighboring albums
3. Average of six rankings of Taylor’s discography from media publications (quality score)

The quality score basically took into account the wisdom of the Swifty community.

Source: Know Your MemeGetting to the #1 most underrated song: Holy Ground (Red)Andrew was able to use R–a tool he’s already using every day on his job–to do this analysis. After dumping all the data from the Spotify API into a CSV, he used the Tidyverse R packages do crunch the numbers. One of the most commonly used packages for data visualization in Tidyverse is ggplot. But superimposing the images of Taylor Swift’s albums onto the charts created by ggplot was a new script Andrew had to write in R. I asked Andrew if he had to learn any new skills for this Taylor Swift analysis, and the main skill Andrew said he had to learn was data visualization. Here’s an example of a visual from Andrew’s blog post for the #1 most underrated Taylor Swift song:

Source: Republic Records / Tidyverse / Andrew Firriolo / BuzzFeedTo make sure he was on the right track, Andrew asked other Swifties what their #1 most underrated Taylor Swift song was. To Andrew’s delight, two co-workers said Holy Ground. Getting this qualitative feedback let Andrew know he was on the right track.

On the Buzzfeed article, half of the commenters agree that Holy Ground is indeed the most underrated song. The other half talk about other songs that should on the list. When Andrew posted his analysis on LinkedIn, most people commented on his methodology and thought process (like we did in this episode).

Using science to see which re-releases of Taylor’s songs most resemble the original songOf course, “science” is used a bit loosely here. But similar to Andrew’s underrated song analysis, this analysis utilized the Spotify API to see which Taylor’s Version song most closely matches the original song. This was Andrew’s first analysis on Taylor Swift published late last year.

Read the Buzzfeed article for the full details on the methodology. Andrew also used R and various packages like the HTTP request package to pull the data from Spotify. To skip right to the results: the #1 song where Taylor’s version is most similar to the original is Welcome to New York.

Source: Republic Records/Big Machine Records/Tidyverse/Andrew Firriolo/BuzzFeed
Euclidean Pythagorean distance scores and Taylor SwiftWhen Andrew first brought up this concept I just scratched my head. Sounds advanced and if someone is bringing up Euclid in a Taylor Swift analysis, you trust that it must be thorough and accurate.

In reality, this concept harkens back to your high school geometry/algebra days. The distance formula simply measures the distance between two points on an X-Y plot:

Source: HowStuffWorksIn this analysis, Andrew utilized 7 metrics from the Spotify API for each version of Taylor’s songs. So each song could be plotted on an X-Y plot where the X might be the acousticness of the original song and the Y would be the acousticness of Taylor’s Version. The beauty of this formula is that it can find the distance between N points in N dimensions. I definitely went down the rabbit hole on this one to learn more about this formula I originally learned in high school. Here’s an explanation of the distance formula in 3-D space (something we can comprehend visually):

But in this analysis, there are 7 points. That means there are points in 7 dimensions. How do we even visualize that many dimensions? This explanation discusses a solution to this problem of how to think about plotting points beyond three dimensions. Math and linear algebra for the win!

I asked Andrew what the next Taylor Swift analysis will be. He said once he sees enough people asking a question about Taylor Swift that can potentially be answered by data, he’ll start an exploratory analysis (most likely with the Spotify API).

Getting your big break in data analyticsAndrew’s #1 advice for landing a job in data analytics or transitioning to a career in data is getting your master’s degree. We haven’t heard this advice too much on the podcast, but Andrew is a shining example of how a master’s degree in data can help. Especially at a university like GIT where the cost is quite low relative to a traditional university.

Andrew also discussed the importance of knowing SQL as the key technical skill for a data analytics role. Who knew that a database query language from 1970 would still be in high demand today?

Source: Medium / Çağatay KılınçThe final piece of advice Andrew gave regarding skills you need for a career in data analytics is communication. Specifically, knowing how to communicate your analysis to a non-technical audience. At the beginning of his career at Buzzfeed, Andrew received feedback that his explanations were too technical. He realized that everyone didn’t need to know how the SQL query was constructed and people just cared about the trends and final results.

Other Podcasts & Blog PostsNo other podcasts or blog posts mentioned in this episode!

The post Dear Analyst #125: How to identify Taylor Swift’s most underrated songs using data with Andrew Firriolo appeared first on .

View Details

We sometimes forget that a large organization is composed of groups and divisions. Within these groups, there are teams and individuals looking to advance their careers. Sometimes at the expense of others. When your advancement depends on the success of your project, the benefits of that project to your company may be suspect and the tools you use to complete that project may not be the best tools for the job. Alex Kolokolov started his journey in data like many of us: in Excel. He moved on to Power BI, PowerPivot, PowerQuery, and building data visualizations for the last 15 years. In this episode, he talks through consulting with a company as the analytics expert only to find out that the the underlying forces at play were company politics. He also discusses strategies to make your line charts tell a better data story.

The state of analytics at companies in traditional industriesAlex consults with large companies in “traditional” industries like oil, gas, and mining companies. The state of analytics and knowledge of analytics is not equal in these companies, according to Alex. You’ll come across data science and AI groups at these companies who are, indeed, working on the cutting edge. But then when you approach other departments like HR or operations, they are still quite far from this digital transformation that everyone’s talking about.

Alex worked with a mining company where there are cameras that can ID employees using facial recognition when they walk through the door. But when you sit down with the folks who are actually doing the work at the plant, they are still humming along on Excel 2010. Excel 2010! What a time…

Source: dummies.comIn terms of creating dashboards, teams from these companies would consult their IT or tech team to create a report. But then the IT team comes back and says it will take three months to create this report given their current backlog. Hence the reason these companies outsource the analytics training, metrics collection, and dashboarding to people like Alex.

Internal battles for power and platformsAlex once worked with a government institution and they were building an internal SQL data warehouse before Power BI came on the scene. This specific project was driven by IT as a warehouse solution for the finance department. a few years later, the head of this SQL project became the CIO, but started getting some pushback from the heads of the finance department. It turns out the finance department heads already had their own platform in mind and claimed Microsoft’s technology was outdated for their purposes (the finance team wanted to go with Tableau to build out pretty dashboards).

Source: reddit.comThe finance department proceeded to roll out their solution in Tableau and the CFO eventually became the Chief Digital Office and pushed the CIO who was spearheading the SQL project out. The project wasn’t about Microsoft vs. Tableau at all. It was all about who was better at playing the game of internal politics and fighting for the resources to get your project across the line.

When digital transformation is 10 years too lateLarge companies Alex has worked claimed they went through “digital transformation” but this was back in 2012. When Alex started working with these companies over the last few years, he found that individuals were still using SAP and Excel 2010. It’s as if the digital transformation didn’t go past 2012, and whatever tools were brought in at the time were meant to carry the organization for another 20 years. We’ve all seen this story. Large companies and enterprises move slow and digital transformation sounds nice and warm, but execution is where organizations may lose their place.

Source: marketoonist.comIn my own experience, teaching someone an Excel keyboard shortcut that saves them X number of hours per week of manual work is a pretty awesome feeling. It’s a visceral feeling of knowing you are having a direct impact on the person’s productivity. Alex has done the same thing at these large companies which, at the heart of it, is explaining somewhat “technical” concepts in an approachable way. If there’s one lesson Alex has learned over the years from helping people stand up dashboards, the one advice he always gives is: don’t insert new columns. Adding new columns may ruin the way the data is laid out (if its a time series) or affect the look and feel of a dashboard.

When your line charts look like spaghettiAlex published a blog post late last year called When Charts Look Like Spaghetti, Try These Saucy Solutions where he provides different strategies for “untangling” your messy line charts. The goal is to have your audience walk away with the key message from the line chart. For instance, you have a line chart where it’s hard to detect trends (given the number of series on the chart) so you can selectively highlight a line (and gray out the rest) to make a point:

Source: nightingaledvs.comAnother option is to simply break out each line into its own mini chart:

Source: nightingaledvs.comThe one skill Alex believes analysts and dashboard creators should learn is compromise. If your visualization is overloaded with elements and colors and your target audience says they want to see all the data, you’ll have to find ways to give them what they need and highlight the story. Imagine this scenario:

An analyst is tasked with plotting more data on a chart by their superiors and so the analyst goes off and makes more charts. Eventually, the analyst realizes there are too many charts and decides to make the dashboard an interactive dashboard with interactive filters and Slicers. This allows the target audience to manipulate the data however they see fit. But does the target audience even know how to use the filters in the first place? Do they know it’s something they are supposed to interact with. There’s a mismatch between what the analyst wants the dashboard to do and what the target audience expects (consume vs. interact).

Other Podcasts & Blog PostsNo other podcasts or blog posts mentioned in this episode!

The post Dear Analyst #124: Navigating people, politics and analytics solutions at large companies with Alex Kolokolov appeared first on .

View Details

When you think of data journalism, you might think of The New York Times’ nifty data visualizations and the Times’ embrace of data literacy for all their journalists. Outside of The New York Times, I haven’t met anyone who does data journalism and data storytelling full-time until I spoke with Ben Wylie. Ben is the lead financial journalist at a financial publication in London. Like many data analysts, he cut his teeth in Excel, got his equivalent of a CPA in the UK, and received his master’s degree in journalism. In this episode, we discuss how his side passion (sports analytics) led him to pursue a career in data journalism and how he approaches building sports data visualizations.

Playing with rugby data on lunch breaksWhen Ben worked for an accounting firm, he would pull rugby data during his lunch breaks and just analyze it for fun. One might say this started Ben’s passion in data storytelling because he started a blog called The Chase Rubgy to share his findings. The blog was a labor of love, and at the end of 2019 he had only focused on rugby. After building an audience, he realized data journalism could be a promising career path so he did some freelance sports journalism at the end of his master’s course. At the end of 2022, he started Plot the Ball (still a side project) where the tagline is “Using data to tell better stories about sport.”

Learning new data skills from writing a newsletterBen spoke about how writing Plot the Ball forced him to learn new tools and techniques for cleaning and visualizing data. All the visualizations on the blog are done in R. A specific R package Ben uses to scrape data from websites is rvest. Through the blog, Ben learned how to scrape, import, and clean data before he even started doing any data visualizations. Sports data all came from Wikipedia.

I’ve spoken before about how the best way to show an employer you want a job in analytics is to create a portfolio of your data explorations. Nothing is better than starting a blog where you can just showcase stuff you’re interested in.

How the NBA became a global sportOne of my favorite posts from Plot the Ball is this post entitled Wide net. It’s a short post but the visualization tells a captivating story on how the NBA became global over the last 30 years. Here’s the main visualization from the post:

Source: Plot the BallBen first published a post about NBA phenom Victor Wembanyama in June 2023 (see the post for another great visualization). Ben talks about this post being a good data exercise because there is no good NBA data in tabular form. This “waffle” chart was Ben’s preferred visualization since it allows you to better see the change in the subgroups. A stacked bar chart would’ve been fine as well, but since each “row” of data represents a roster of 15 players, the individual squares abstracts the team composition each year.

Home Nations closing the gap with Tri Nations in rugbyBen talked about another popular post from his blog entitled Heading South. The post started as a data exploration exercise where Ben was simply trying to find trends instead of telling a story. For some background, rugby has traditionally been dominated by a few teams (e.g. Australia, New Zealand, and South Africa). The most recent finals was between New Zealand and South Africa and these two clubs have won a majority of World Cups.

Ben was interested in seeing how these elite teams and other teams were trending over time. Ireland and France have started doing well over the last few years but there is not bird’s eye view of how these teams are performing as a whole. So Ben decided to create this visualization:

Source: Plot the BallCognitive overload is a concept many data visualization professionals care about. When a visualization has more information than an individual has the mental capacity to process, the message and story gets lost. A few factors about the visualization above eases the path for understanding the story:

1. Gridline color is muted
2. Data labels only show up at the end of the line charts
3. The colors of the lines match the series name in the title of the chart

If it’s not clear what the trend is, the main header of the chart even tells you the key takeaway from the chart.

Other Podcasts & Blog PostsNo other podcasts or blog posts mentioned in this episode!

The post Dear Analyst #123: Telling data stories about rugby and the NBA with Ben Wylie appeared first on .

View Details

There are only so many ways to make Excel “fun.” If you’ve been following this blog/podcast, stories about the financial modeling competition and spreadsheet errors that lead to catastrophic financial loss are stories that make a 1980s tool somewhat interesting to read and listen to. There are numerous tutorials and TikTok influencers who teach Excel for those who are actually in the tool day in and day out. Meet Matt Basta, a software engineer by trade. He published a story on his own blog called No sacred masterpieces which is worth reading in its entirety as its all about Excel. In this episode, we discuss highlights from Matt’s time at Uber, how he built a version of Excel online to help Uber China compete with DiDi, and how Uber completely scrapped the project weeks later after DiDi acquired Uber China.

Business intelligence at Uber through the eyes of a software engineerI don’t normally speak with software engineers on the podcast, but Matt’s story during Uber will resonate with anyone who works at a high-growth startup and lives in Excel. Matt’s story has everything. Tech, cutthroat competition, drama, and of course, Excel.

Matt has worked at a variety of high-growth startups like Box, Uber, Stripe, and now Runway. He joined Uber in 2016 and worked on a team called “Crystal Ball.” The team was part of the business intelligence team. The goal of this team was to create and develop a platform that analysts and business folks could use to figure out how much to charge for rides, how much incentives to provide to drivers, etc. All the core number crunching that makes Uber run.

As per Matt’s blog post, employees were working on one of two major initiatives at Uber in 2016:

1. Redesigning the core Uber app
2. Uber China

As Matt told his story, it reminded me of all the news articles that came out in 2016 about Uber’s rapid expansion in markets like China. The issue is that a large incumbent existed in China: DiDi. This comes up later in Matt’s story.

Getting data to the city teams to calculate driver incentivesFrom the perspective of the Crystal Ball team, all they wanted to do was set up a data pipeline so that data about the app could be shared with analysts. Analysts would then download these files and crunch numbers in R and this process would take hours. In 2016, Uber was competing directly with DiDi to get drivers on the platform. The city team would use the data provided by the Crystal Ball team to figure out how much of an incentive to offer a driver so that the driver would choose to drive with Uber instead of DiDi for that ride.

Source: ForbesThe problem was that the city team in China was using these giant Excel files that would take a long time to calculate. In order to compete with DiDi, Uber China would need a much faster way to calculate the incentives to offer drivers. This is where Matt’s team came in.

The only other “tool” the city team had at their disposal was the browser. The city team still wanted the flexibility of the spreadsheet, so Matt’s team strategy was to put the spreadsheet in the browser. Now at this point, you might be wondering how in the world did this become the solution to the problem? Matt’s blog post goes into much more detail as to the stakeholders, constraints, and variables that led his team to go in this direction.

Luckily, Matt had worked on a similar tool while at Box, so he re-used code from that previous project. During this time at Box, Box had Box Notes and Dropbox had Dropbox Paper. Both of these products were based on the open source tool Etherpad for real-time collaborative document editing. Matt thought, why not build something similar for spreadsheets?

Source: DropboxDiscovering nuances about ExcelIn the blog post, Matt talks about discovering Excel’s circular references. We all know that circular references can break your models, but Excel’s calculation engine also allows for continually calculating if the computed value of the cell converges. I think this is how the Goal Seek function works in Excel to a certain extent.

Source: MicrosoftWhen Matt’s online version of Excel was released internally, the head of finance was upset since you could see how the formulas were calculated in the tool. To Matt’s team, they did what they were supposed to do. They put Excel in the browser and figured you should be able to see the formulas in the cells.

According to the head of finance, there were spies from DiDi who would apply for internships at Uber China just to get competitive data. Needless to say, Matt removed the ability to see formulas in his tool.

DiDi buys Uber ChinaMatt and the Crystal Ball team spent 6 months helping the Uber China team with their data needs. Internally, Matt’s team didn’t get an all-hands invite or anything regarding the acquisition of Uber China by DiDi. People just found out through the news. Eventually, then CEO of Uber Travis Kalanick sent out a message regarding the acquisition. Matt’s tool would get scrapped immediately.

Matt open-sourced the code for this WebSheets tool and the calculation engine lives on GitHub here. We chatted about the feedback Matt’s received about his blog post and you can see the comments on HackerNews. As usual, there are people chiming in saying Matt could’ve done this or that better. Whenever there is a mention of Excel on HackerNews, you’ll inevitably see people talking about how $XX billions of their company is still run off of someone’s Excel file. Interestingly, one of the resources Matt used to learn about Excel is Martin Shkreli’s YouTube channel where Shkreli walks through building out a financial model. Putting aside misgivings about Shkreli’s character, the videos are actually super educational:

Excel’s fast feedback loopThis is where the Matt’s story turns into takeaways and learnings that make this story more than a story about Uber China and Excel. Matt built something from scratch and had to come to terms with that it wouldn’t have a business purpose anymore. The tool is just a way to achieve the business objective. If the business objective changes, then the tool may become obsolete.

Hearing Matt’s perspective about Excel was quite refreshing since prior to this Crystal Ball project, he wasn’t an analyst and in the weeds of Excel every day. However, he worked with said analysts every day to understand their requirements and more importantly, whey they were so tied to Excel. Excel allows you to create a fast feedback loop to test an idea or an assumption. The reason the city team stuck with Excel and put up with the hours of calculation time is because building similar functionality with code would’ve been too difficult.

Founders will use Excel before writing code.

To the analysts and data scientists Matt worked with, writing formulas was their version of programming. Unlike traditional programming, Excel users don’t have to develop unit tests, build integrations, and deal with piping data in/out. Another interesting tidbit Matt brought up about the internal workings of the city team at the time is that there was no expectation that a given Excel file would live for more than a week. Each file would solve a specific problem at that point in time, and then get discarded as it too became obsolete.

Planning and forecasting on IBM softwareFollowing this Crystal Ball project, Matt started working on the financial engineering team within Uber. His next project as trying to figure out how much revenue Uber would make in 2017. The tool they used was a self-hosted version of Anaplan called IBM TM1. I’ve never heard of this tool from a FP&A perspective, but my guess is that it’s similar to Oracle Hyperion (the tool I used back in the day).

Source: Lodestar SolutionsThere were analysts working with this tool who would turn Excel spreadsheet data into TM1 code for planning purposes. The problem is TM1 code is not strongly typed, so analysts would constantly break the tool when trying to write code for it. It was just one guy who created TM1 and the platform was acquired by IBM. Uber even invited one of TM1’s chief architects to talk to Uber’s analysts about the tool. According to the creator of TM1, Manny Perez, TM1 was the first “functional database” in the 1980s which exploited in-memory computing. Apparently there’s a cult following around Manny and the creation of TM1. So much so that a documentary was released a few years ago aptly named Beyond the Spreadsheet: The Story of TM1:

Not gonna lie, this seems like a super interesting documentary given the foundation of the story discusses spreadsheets at length. How about this description from the film’s website to incite some excitement around corporate planning software:

But as long ago as 1983, a light-bulb idea went off in the head of an employee at oil distributor Exxon. Manny Perez realized he could give business users the freedom to create at scale but also the control and collaboration prevalent in other technologies today. He thought his solution to the problem was so elegant and obvious, it would become instantly ubiquitous. It didn’t. To achieve his ultimate aims, he would need to pioneer and master many facets of technology, staying true to the spirit of user freedom whilst battling waves of competitors selling solutions that enriched themselves but not their customers. Eventually, with thousands of companies globally using his solution, and with a passionate community of followers, his inspiration and perspiration was validated when IBM acquired his technology in 2008.

Source: tm1.film

Back to Matt’s work with TM1. His goal was to make it easier for analysts to work with the software. He built a programming language on top of what the analysts were coding. The new language had type inference and checking to prevent errors from occurring in TM1.

Tips for Excel usersGiven Matt’s extensive experience building on top of Excel and working with analysts all day at Uber, I thought it would be interesting to get tips he has for us Excel users. A key question that is worth pondering is when the business evolves to a point where Excel doesn’t make sense to be the tool of record anymore. I’m sure many of you have worked with files that handle business critical processes at your company and have wondered: this data should probably be in a secure database or something more secure than Excel.

Source: KaiNexus BlogRealistically, moving the data and process off of Excel involves a team of engineers writing code where everything is hosted on a server. The resourcing for this speaks to to the speed and immediacy of Excel’s value when your team needs to work fast. Should your team go down this route and create code instead of spreadsheets, Matt encourages all analysts to do one thing: provide good documentation.

This helps with the migration process when you have to work with a team of engineers. Tactically, this can mean something as simple as adding a comment to a cell in your file, leaving notes in the cell itself, or even creating a text box with the notes in the box. How many times have you inherited a file and spend hours spelunking around trying to figure out how it was constructed? Good documentation helps everyone.

Other Podcasts & Blog PostsNo other podcasts or blog posts mentioned in this episode!

The post Dear Analyst #122: Designing an online version of Excel to help Uber China compete with DiDi on driver incentives with Matt Basta appeared first on .

View Details

One of the more popular courses you could take at my college to fulfill the finance major requirements was Behavioral Finance. The main “textbook” was Inefficient Markets and we learned about how there are qualitative ways to value a security beyond what the efficient market hypothesis purports. During the financial crisis of 2008, psychology professor and behavioral economist Dan Arielypublished Predictably Irrational to much fanfare. The gist of the book is that humans are less rational than what economic theory tells us. With the knowledge that humans are irrational (what a surprise) when it comes to investing and other aspects of life, the capitalist would try to find the edge in a situation to get a profit. That is, until, recent reports have surfaced showing that the results of Dan Ariely’s experiments are fabricated (Ariely partially admits to it). This episode looks at how the data was potentially fabricated to skew the final results.

Dan Ariely. Source: WikipediaBackground on the controversy surrounding Dan Ariely’s fabricated dataIn short, Ariely’s main experiment coming under fire is one he ran with an auto insurance company. The auto insurance company asks customers to provide odometer readings. Ariely claims that if you “nudge” the customer first by having them sign an “honesty declaration” at the top of the form saying they won’t lie on the odometer reading, they will provide more accurate (higher) readings.

I was a fan of Predictably Irrational. It was an easy read, and Ariely’s storytelling in his TED talk from 15 years ago is compelling. I first heard that Ariely’s experiments were coming under scrutiny from this Planet Money episode called Did two honesty researchers fabricate their data? The episode walks through how Ariely a thought leader and used his status to get paid behavioral economics consulting gigs and to give talks. Apparently the Israeli Ministry of Finance paid Ariely to look into ways to reduce traffic congestion. In the Planet Money episode, they talk about how other behavioral scientists like Professor Michael Sanders applied Ariely’s findings to the Guatemalan government by encouraging businesses to accurately report taxes. Sanders was the one who originally questioned the efficacy of Ariely’s findings. Here is part of the abstract from the paper Sanders wrote with his authors:

The trial involves short messages and choices presented to taxpayers as part of a CAPTCHA pop-up window immediately before they file a tax return, with the aim of priming honest declarations. […] Treatments include: honesty declaration; information about public goods; information about penalties for dishonesty, questions allowing a taxpayer to choose which public good they think tax money should be spent on; or questions allowing a taxpayer to state a view on the penalty for not declaring honestly. We find no impact of any of these treatments on the average amount of tax declared. We discuss potential causes for this null effect and implications for ‘online nudges’ around honesty priming.

Professor Michael SandersIf you want to dive deeper into Dan Ariely’s story, how he rose to fame, and the events surrounding this controversy, this New Yorker article by Gideon Lewis-Kraus is well researched and reported. NPR also did a podcast episode about this a few months ago. This undergraduate student only has one video in his YouTube account, but it tells the story about Ariely quite well:

Instead of discussing Ariely’s career and his character, I’m going to focus on the data irregularities in the Excel file Ariely used to come up with the findings from the auto insurance experiment. This podcast/newsletter is about data analysis, after all.

Instead of dissecting the Excel file myself, I’m basically going to re-hash the findings from this Data Colada blog post. Data Colada is a blog run by three behavioral scientists: Uri Simonsohn, Leif Nelson, and Joe Simmons. Their posts demonstrate how “p-hacking” is used to massage data to get the results you want.

Irregularity #1: Uniform distribution vs. normal distribution of miles drivenThis is the raw driving dataset from the experiment (download the file here). Each row represents an individual insurance policy and each column shows the odometer reading for each car in the policy before and after the form was presented to the customer.

The average number of miles driven per year irrespective of this experiment is around 13,000. In this dataset, you would expect to see a lot of numbers around 13,000, and a few numbers below 1,000 and a few numbers above 50,000 (as an example). This is what normal distribution or bell curve looks like:

Source: Math Is FunIn Ariely’s dataset, there is a uniform distribution of miles driven. This means the number of people driving 1,000 miles per year is similar to those who 13,000 miles/year and those who drove 50,000 miles/year.

Source: Data ColadaNo bell curve. No normal distribution. This by itself makes the dataset very suspect. One could argue that the data points were cherry-picked to massage the data a certain way, but the other irregularities will show that something more sinister was at play. You’ll also notice in the chart created by Data Colada is that the data abruptly stops at 50,000 miles per year. Although 50,000 miles driven per year is a lot, it’s highly unlikely thatthere are no observations above 50,000.

Irregularity #2: Mileage reported after people were shown form are not rounded and RANDBETWEEN() was usedPeople in the experiment were asked to recall their mileage driven and write the number on a piece of paper. If you were to report on a large number, you’d probably round the number to the nearest 100 or 1,000. In the screenshot below, you’ll see how some of the reported mileage are indeed rounded. What’s peculiar is that mileage reported after people were shown the form (Column D) were generally not rounded at all:

Did these customers all of a sudden remember their mileage driven down to the single digit? Highly suspect. Data Colada suggests that the RANDBETWEEN() function in Excel was used to fabricate the mileage in Column D. The reasoning is that RANDBETWEEN() doesn’t round numbers at all.

Even the numbers in Column C (mileage reported before shown the form) seem suspect given how many places most numbers go to. If Ariely or members in his lab did in fact use RANDBETWEEN() to generate the mileage in Column D, they could’ve at least tried to hide it better using the ROUND() function which would allow them to round the numbers to the 100 or 1,000th place. This is just pure laziness.

This chart from Data Colada further shows how the last digit in the baseline mileage (before people were shown the form) is disproportionately 0. This supports that these numbers are indeed reported accurately. The last digit in the updated mileage (after people were shown the form) again has a uniform distribution further adding to the evidence that the numbers were fabricated.

Source: Data ColadaIrregularity #3: Two fonts randomly used throughout Excel fileThis is by far the most amateur mistake when it comes to judging the validity of any dataset. When you open the Excel file, something instantly feels off about the data. That’s because half of the rows have Calibri font (default Excel font) and the other half have Cambria font (in the same font family as Calibri).

Were some of the rows copied and pasted from another Excel file into the main file and then sorted in some fashion? Did someone incorrectly select half the data and set it to Cambria?

According to Data Colada, the numbers probably started out in Calibri and the RANDBETWEEN() function was used again to generate a number between 0 and 1,000 to be added to the number in Calibri. The resulting number is in Cambria:

Source: Data ColadaTo recap what the data hacking looks like with this irregularity:

1. 13,000 baseline car readings are composed of Calibri and Cambria font (almost exactly 50/50)
2. 6,500 “accurate” observations have Calibri
3. 6,500 new observations were fabricated in Cambria
4. To mask the new observations, a random number between 0 and 1,000 was added to the original numbers in Calibri to form the fabricated numbers in Cambria

In the screenshot above, this pattern of the Cambria number being almost identical to the Calibri number is what leads Data Colada to believe that the Cambria numbers (half the dataset) are fabricated.

To put the cherry on top of this font irregularity, very few of the numbers in Cambria font are rounded. As discussed in irregularity #2 above, using RANDBETWEEN() without using ROUND() will lead to numbers not being rounded. Not having rounded numbers is again, highly suspicious when you consider that these mileage numbers are reported by humans who tend to round large numbers.

Source: Data ColadaWhy did Ariely allegedly fabricate the numbers? Easy. Fame, notoriety, and consulting gigs. Again, I’d read the New Yorker piece to learn more about Ariely’s background and character. The narrative Ariely wanted to tell was that nudges have an outsize impact on behavior, and the data was skewed to prove this.

Source: ResourceaholicAriely actually acknowledged Data Colada’s analysis and basically responded with “I’ll check my data better next time” over email. The New Yorker article talks about maybe someone at the auto insurance company fabricating the data before it was sent to Ariely, which means Ariely can claim he had no hand in fabricating the data.

Even if that were the case, you wouldn’t at least scroll through the dataset to see–I don’t know–that the data is in two different fonts? Your future TED talks, published books, and paid consulting gigs are dependent on your findings from this Excel file and you don’t bother to check the validity of it? The file is just over 13,000 rows long so it’s not even that huge of a dataset. While not on the same scale, this narrative feels similar to what happened with Theranos. Similar to Elizabeth Holmes, Ariely claims he can’t recall who sent him datasets or how the data was transformed (as reported in the New Yorker).

Excel mistakes are different from fabricating dataI’ve dissected a few Excel blunders on the podcast such as the error that led to a $6.2B loss at JPMorgan Chase, Enron’s spreadsheet woes, the DCF spreadsheet error leading to a mistake with a Tesla acquisition, and many others. In these cases, the pilot simply misused the instrument which led to a massive mistake.

With the fabricated data in Ariely’s experiment, Ariely, members of his lab, or someone at the auto insurance company knowingly massaged the data with the intention of not getting caught. Better auditing or controls cannot prevent data drudging to this magnitude.

Perhaps Ariely (or whoever fabricated the data) knew that if they could tell this narrative that “nudging” does indeed lead to changes in human behavior, there would be a size-able financial payout somewhere down then line.

Source: GetYarnBlowing the whistle on ArielyIn the Planet Money episode referenced earlier, Professor Michael Sanders is credited with first calling bullshit on Ariely’s findings after his own failed project with the Guatemalan government. Data Colada’s blog post really made clear what issues exited in Ariely’s spreadsheet.

Data Colada kind of reminds me of the European Spreadsheet Risks Interest Group (EuRpRIG), a group of individuals who document all these Excel errors in the hopes that analysts won’t make the same errors. By detailing Ariely’s spreadsheet tactics, hopefully it will be easier to spot issues like this in the future.

The New Yorker article shows that it’s hard to evaluate the true intentions of each party in this case. It’s easy to point fingers at Ariely and say he committed spreadsheet fraud for his own personal gain. But what about Data Colada? While the behavioral scientists behind the blog seem like upstanding citizens, who knows what benefit they stand to gain from uncovering these issues and calling out fraud? Simmons, Nelson, and Simonsohn also get their share of the limelight in this recent WSJ article highlighting the impact of the group’s research.

Leif Nelson, Uri Simonsohn, and Joe Simmons. Source: WSJLike Ariely, maybe more consulting gigs get thrown their way based on their ability to take down high profile authors and scientists? Remember when Hindenburg Research came out with the hit piece on Nikola leading to the resignation of the CEO? Not only did Hindenburg stand to gain from short-selling the stock, they also drew more attention to their investment research services. They also probably got more inbound interest from people who have an axe to grind with some other company CEO and want to take down the company.

Open source wins the dayI’ve been a fan of open source ever since I got into software since, well, the whole fucking Internet runs on it. One of my favorite data cleaning tools (OpenRefine) is completely free to use and is just as powerful as Microsoft Power Query for cleaning data.

Source: Rocket.ChatThe beautiful thing about open source is that anyone can analyze and investigate how the code really works. There is no narrative about what the tool or library can do. These same values should also be applied to researchers and scientists. I really like how the Data Colada team ended their post on Ariely’s spreadsheet issues:

There will never be a perfect solution, but there is an obvious step to take: Data should be posted. The fabrication in this paper was discovered because the data were posted. If more data were posted, fraud would be easier to catch. And if fraud is easier to catch, some potential fraudsters may be more reluctant to do it. Other disciplines are already doing this. For example, many top economics journals require authors to post their raw data. There is really no excuse. All of our journals should require data posting. Until that day comes, all of us have a role to play. As authors (and co-authors), we should always make all of our data publicly available. And as editors and reviewers, we can ask for data during the review process, or turn down requests to review papers that do not make their data available. A field that ignores the problem of fraud, or pretends that it does not exist, risks losing its credibility. And deservedly so.

Hopefully this episode nudges you in the right direction.

Other Podcasts & Blog PostsIn the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• Data Visualization Society’s 2022 State of the Industry Report

Source: Rachel E CinelliThe post Dear Analyst #121: Fabricating and skewing Excel survey data about honesty with behavioral economists Dan Ariely and Francesca Gino appeared first on .

View Details

Data analytics and business analytics are still relatively new areas of study (in terms of academics). The subject borders business and computer science. When I went to school, the only data analytics classes available were special electives offered through our school’s continuing education department. In this episode, I spoke with Zach Wilner who currently leads data and analytics at Pair Eyewear. Zach is a “classically trained” in data analytics (if one can call it such) since he studied business analytics at Boston College. He has worked at various DTC (direct-to-consumer) companies like Wayfair and Bombas before landing at Pair (also a DTC company). In addition to discussing marketing attribution and pricing projects, Zach also talks about building Pair Eyewear’s data infrastructure from 0 and how to build the team around it.

Scaling a data stack in a step-wise approachWhen Zach joined Pair, there wasn’t really much of a data infrastructure in place. People wanted to analyze and visualize data but didn’t know where to pull the data from. The classic multiple data silos problem.

The easy thing to do would’ve been to take the data stack at Bombas or Wayfair and try to implement it at Pair. Instead, Zach asked what if we started with a blank slate? With the help of a consultant, Zach spent 6 months building out a data warehouse with dbt, Stitch, and other ETL tools. After the foundation was placed, he then focused on BI and implemented Looker and Heap. The goal is to make analytics as self-service as possible. Today, 60%-70% of the company use Looker actively.

From a marketing analytics perspective, most DTC companies have similar marketing channels (e.g. Shopify, Facebook, TikTok). This means Zach could set up similar telemetry for tracking all of Pair’s marketing initiatives. One area the team spent some time on is health data and they decided that they wouldn’t be HIPPA compliant or deal with PHI data.

Customer centric vs. marketing attribution modelMarketing attribution. A never-ending battle between marketing channels and data to figure out which channel gives your company the best bang for your buck. The reason I know this problem hasn’t been solved yet is because new marketing attribution vendors pop up every year claiming to be the end-all-be-all omnichannel tracking tool. If you work in martech, we’ve seen the industry evolve from last-click to multi-touch models.

Source: WordStreamZach worked with Pair’s head of marketing to figure out what model would work for the company. Surprise surprise, they started with the data. Using the data, they answered questions like how many sessions does it take before a customer makes a purchase? How many ads does the customer need to see before they make a purchase?

The team decided to build out a home-grown attribution model and called it a customer-centric attribution model. They basically looked at how individual customers viewed Pair’s different marketing messages and optimized spend based on the customer. They were able to properly attribute conversions by comparing their results with lift studies from Facebook.

Using a sensitivity model to experiment with pricingPair’s business model is doing limited-edition drops. This means a lot of one-unit orders when the drops happen. With the longevity of the business in mind, the team asked what would happen if they encouraged customers to to purchase two items with less frequency between them instead of just these one-time higher-priced drops?

Source: SoundCloud (Mokos)Again, they started with the data. They looked at a distribution of their order values. As expected, they saw a normal distribution of orders and could see the average order value across all customers. Using this data, they could figure out what the order minimum customers were reaching for. Then came the sensitivity model to find the tradeoff between a lower conversion % and higher order value.

Hiring the right people for your data teamThe sequencing of how Zach went about hiring members to join his data team might sound familiar to folks. The first hire was an analytics engineer, the Swiss army knife of the data world. The analytics engineer can help build the tech stack and do analysis. This breakdown of data engineer, analytics engineer, and data analyst is always good to know:

Source: LearnSQLOnce the data infrastructure is in place, Zach then hired the data analysts who do the more traditional exploratory analysis and dashboarding. From there, Zach built out a consumer insights team. The analytics team is now doing full-stack stuff which goes beyond Excel and Tableau. They are diving into dbt and machine learning as well.

Zach talked about encouraging data analysts to be generalists. One reason people leave their current job or employer is simply being bored with the work. If an analyst is a generalist, they can grow and learn and be excited about other aspects of their role. They will have the opportunity to touch multiple departments. More importantly, they can approach company problems from multiple angles.

What keeps Zach up at night: building in-house vs. managed servicesBuild vs. buy. No matter how trite this debate may seem to some of you, I think it’s always interesting to hear how different companies view this problem. There’s always a new set of constraints, contexts, and tools to consider this tradeoff. What doesn’t change, however, is that there is never a clear answer. Even when you think if you’d made the right decision, that all can change next quarter.

Source: Customer Success MemesOne of the things that keeps Zach up. at night is whether a certain task should be delegated to a managed service like Stitch or Fivetran. These tools make it easy to tap into APIs. They also allow teams to move quicker and get to impact faster. The problem is that it opens up your company to more risk. If one of the APIs or providers happens to go down, you’re at the mercy of the provider. Zach talked about an issue that Stitch had with the Shopify API and that there was nothing his team could do about it.

The other side is you build in-house and everything is under your control. This requires more resources and you move slower. According to Zach, this tradeoff is something. he revisits often and the work is never quite done even when you think it’s done.

Other Podcasts & Blog PostsNo other podcasts or blog posts mentioned in this episode!

The post Dear Analyst #120: Marketing attribution, sensitivity models, and building data infrastructure from the ground up with Zach Wilner appeared first on .

View Details

Future Dear Analyst episodes will get more sporadic since, well, life gets in the way. Unfortunately curiosity (in most cases) doesn’t pay the bills. Nevertheless, when I come across an idea or person that I think is worth sharing/learning more about, I’ll try my best to post. In this episode, I interview the Chief Scientist of a data startup who did his PhD at Stern NYU and was on track go becoming a professor. Then he got an internship at Facebook and everything changed. The speed of learning at a tech company outpaced what the academic was used to at university. Over the years, Sean Taylor has worked with and spoken to hundreds of data analysts and statisticians. We’ll dive into his data science work at Lyft, his notion of “hidden analytics,” and why he’s obsessed with user journeys in modern applications.

Modeling the Lyft marketplace and creating the GRAIL modelSean worked at Facebook for 5 years as a research scientist and worked on general data problems. Eventually he joined the revenue operations science team at Lyft. His team’s goal was to help grow the marketplace of riders and drives on the platform. One of the most important aspects of the marketplace is the forecast. As Lyft runs promotions and enters new cities, how do you ensure there are enough drivers for the riders and vice versa?

The team ultimately decided that a simple cohort methodology would be best to help set the forecast for both drivers and riders. Every rider, for instance, would belong to a cohort based on when they first signed up for Lyft, when they booked their first ride, etc. There’s a “liquidation curve” for each cohort that eventually hugs the x-axis. There is much more detail about the cohort methodology in this blog post by the Lyft Engineering team from 2019.

Despite being such a simple model, the model worked surprisingly well. Goals of this model taken from the blog post mentioned in the previous paragraph:

1. Forecast the behavior of each observed cohort and use it to project how many rides are taken or driver hours are provided within a specific cohort
2. Forecast the behavior of the cohorts that are yet to be seen.
3. Aggregate all the projected rides and driver hours to make forecasts for both the demand and supply side of our business.

Sean talked about how there were flaws in the model, and one of those flaws is that a marketplace is ver fluid and evolves over time. When a rider is exposed ot high prices, this may lead to churn and this was also not included in the model. Sean’s team tried building a better model called GRAIL but Sean left Lyft before completing the model.

Source: SymposiumsSpeaking of Lyft’s data team, I had mentioned Amundsen, an open source data discovery platform Lyft released in 2019 (blog post). It’s great to see the data team at Lyft giving back to the ecosystem to help data analysts and data scientists do their job better!

Discovering a bug that cost the company $15M per yearOne of the best feelings as a data analyst is using data to uncover the root cause or underlying trends in a given business situation. One might say this is like Moneyball where the Oakland As realize that On-base percentage (OBP) is the best predictor for player performance.

Source: Hire an EsquireSean believes there is a lot that data analysts do that is not necessarily taught in school or on the job. You’re expected to understand the business and how every day business operations are translated into the numbers on the dashboard.

When you’re working on a project because your are curious about the project rather than being forced to come up with an analysis, you are able to come up with the bigger wins that really move the needle. Sean calls this type of work “hidden analytics,” or as I like to say, there is much more behind the numbers.

Sean’s colleague at Lyft cam across some anomaly in the data and just started pulling on the thread some more. His colleague ultimately found a bug in the marketplace in how Lyft was dispersing driver incentives. Sean talks about how his colleague’s curiosity led them to discover this bug in the first place and squashing the bug led to saving Lyft $15M per year.

Why the systems for collecting user journey data are brokenModern websites and applications collect a ton of data, but the actual user journey is harder to quantify. A customer signs up for a tool or service, goes through an onboarding process, and might engage with the tool at various times in the future. Modeling and visualizing this data on a spreadsheet or in a SQL database can be difficult. With these tools, you are aggregating data and parts of the user journey might be improperly reduced down to a single number when there is much more nuance to a user’s journey on a website.

Source: WikipediaUsers are in different states when using a website or app. Sessionizing data has become the default way to capture the path a user takes but there are still many micro-sessions in just one experience like registering your account on a website.

Sean discusses this concept in the context of a rider taking or not taking a ride booked on Lyft. The customer requests the ride, and perhaps declines the first ride and books the second ride. The basic conversion rate would be 50%, but that statistic doesn’t answer why the customer didn’t book the first ride. Perhaps the customer couldn’t find the right address with the first ride, and just gave up. Perhaps the driver was too far away.

Balancing usability and expressivity in data toolsBrowse any Hacker News article and you’ll inevitably see devs talking about why you should just build your own tool on-prem with code. The main reason is that you can fully customize the app if you know how to code. I’ve discussed at length on this podcast and through content I’ve created for my company how the need for low-code and no-code tools redefines who a “builder” is in a company.

Sean’s current company (Motif Analytics) is trying to strike that balance between giving data analysts and data scientists the ability to express their data question without diving right into the code. In terms of user journey data, Sean says most people use Amplitude, Mixpanel, or other similar tools. While these tools allow you to execute common data tasks, there are certain things these tools block you from doing. Python notebooks, for instance, are very expressive. But you kind of need to be an expert to use them to their full potential.

Source: JupyterSean talks about how he drew inspiration from Ruby on Rails in terms of how the creators had strong opinions about how to do web development. I also first learned about web development through a Ruby on Rails book and it’s interesting to see how many of the patterns from Rails are still seen in frameworks using PHP or Javascript.

As we discussed the platform Sean and his team are building, we got into the weeds about a little-known SQL command called MATCH\_RECOGNIZE(). There apparently isn’t much documentation about this function and the creators behind SQL rushed this pattern-matching function into the language because of competitors coming out with similar functionality. Nothing like real-world drama impacting the open source world!

Start with the questions instead of the toolsWe ended the conversation with a bit of career talk. Sean talks about intrinsic motivation being the number one driving force in his career. While tools come and go, he said domain expertise is something that can give budding analysts a leg up when searching for their next role. Technical skills, unfortunately, are slowly becoming a commodity. What never goes out of style? Asking the right questions.

Other Podcasts & Blog PostsNo other podcasts or blog posts mentioned in this episode!

The post Dear Analyst #119: Developing the holy “grail” model at Lyft, user journeys, and hidden analytics with Sean Taylor appeared first on .

View Details

No, this isn’t an episode about how Facebook’s algorithm and feed works. The data science function is popping up in companies small and large given the amount of data swimming around. No other company understand the power and influence that data science can have on the customer experience than Facebook (Meta, to be exact). Akos […]

The post Dear Analyst #118: Uncovering trends and insights behind Facebook News Feed, Reels, and Recommendations using data science with Akos Lada appeared first on .

View Details

The Google Workspace team announced a slew of Google Sheets functions a few months ago (February 2023). These functions look familiar and that’s because Microsoft Excel released most of them two years ago. I never had a chance to play around with the new functions in Excel since I don’t have the latest Office 365 version. Now that they are live in Google Sheets, I played around with them and find them pretty interesting for data manipulation purposes. I think what’s interesting about these new functions is that they help with both super basic data organization use cases but also more advanced data cleaning use cases too. Here’s a rundown of some of the new functions and more importantly, examples of real-life use cases. If you want a copy of the Google Sheet I use in this episode, go here.

Watch a tutorial showing all the new Google Sheets functions in 2023:

What’s interesting about these “new” Google Sheets functions?Here’s a quick rant on these “new” Google Sheets functions. They aren’t new. They are basically a direct copy of what exists in Excel already (if you have Office 365). I think Google Sheets has some pretty awesome features that differentiate it from Excel (auto-fill, collaboration features, it’s free, etc.) But I’ve always viewed Google Sheets as a tool that is playing catchup to Excel. These functions are an example of Google playing catchup with Excel’s features versus coming up with something new.

These “new” functions in Google Sheets also highlight something Microsoft discovered a few years ago about how people are using spreadsheets: data is not organized in a structured way. You have time periods across the columns and the rows. You have headers and sub-headers. People don’t typically organize and clean their data for the purposes of a PivotTable but rather for ease of use. With this in mind, I think these new Google Sheets functions are targeted at the beginner spreadsheet user who may just be using Google Sheets to show who’s sitting at different tables at a banquet dinner or showing a shift schedule.

Next to each function, I also put a usefulness rating (🌶 being not useful and 🌶🌶🌶🌶🌶 being really useful) based on what I think would be useful for a beginner Google Sheets user.

1) EPOCHTODATE() – Turn computer-generated dates into a human-readable date formatUSEFULNESS RATING: 🌶

This is a pretty basic one. You’ll typically get epoch dates when getting some output from a database or any type of computer-generated date/time. It’s usually a long string of numbers and EPOCHTODATE simply converts that “computer time” into a date and time that us humans can comprehend.

Gave this a rating of 1 because I don’t see many instances where you’ll have the epoch time format in your spreadsheet save the rare occasion you have a a Unix export of data that has these epoch times.

2) TOROW(), TOCOL() – Arrange a bunch of cells into a single row or columnUSEFULNESS RATING: 🌶🌶🌶🌶🌶

Also a pretty simply formula that helps with basic data manipulation tasks. Big fan of this one because it removes the need to cut and paste ranges of data on top of each other. I think TOCOL() will be used more often just because you typically want to get a continuous list of values in one column. Here’s an example where you have a bunch of names arranged by groups (perhaps groups of students in a class) and you just want to get all the names in one column:

There are also some interesting options that let you remove errors and blanks as well as how the data should be “scanned” and put together. Someone just asked me how to do a data manipulation task similar to this and using TOCOL() with the scan_by_column flag set to false does the trick.

3) CHOOSEROWS(), CHOOSECOLS() – Choose which rows or columns you want from a data setUSEFULNESS RATING: 🌶🌶🌶🌶

I would put these new functions in the camp of “making it easier to filter out the data I don’t need.” I find this useful when you know when you want to quickly get the top 3 scores or maybe the top score and bottom score from a list of test scores, for instance. There are probably a bunch of other use cases I’m not able to think of, but in general it’s a really useful function to quickly “pull out” the rows or columns of data you need from a data set. CHOOSEROWS() in action:

While we’re at it, I’d say CHOOSECOLS() is equally as useful because you can just pull out the columns of data that matter for you. In this case, you can just pull out the list of students and just the scores from the subjects that matter for you. This feels like a more user-friendly version of the {} syntax for concatenating different ranges to create a custom range (typically used for creating a custom VLOOKUP formula with multiple conditions)

4) WRAPROWS(), WRAPCOLS() – Turn a bunch of cells into a specified number of rows or columnsUSEFULNESS RATING: 🌶🌶

Kind of an interesting formula for a specific use case (I think). You put in a list of cells, and then the number of rows or columns you want to turn the list into. I don’t find these formulas that useful because your data has to be in really bad shapeto warrant using these formulas. Then again, I may not be thinking of all the use cases where one would use these formulas.

For instance, you might have a list of employees with their location, job, etc. all listed out versus properly arranged in columns. This is where you would use the WRAPROWS() formula:

A more realistic use case is you have a list of names and you want to put them into 3 groups. You would use WRAPROWS() to quickly put this list of names into 3 columns:

In this case the number of names don’t fit perfectly into 3 columns so there are two N/As at the end. There’s this handy pad_width parameter which kind of acts like an IFERROR() function where you can just put in a placeholder value for those extra cells:

5) VSTACK(), HSTACK() – Stack rows from different sheets on top of each otherUSEFULNESS RATING: 🌶🌶🌶

I think the reason why VSTACK() might be useful is when you have data coming in on multiple sheets. The data is also structured the same across those three sheets. Then you can have one primary sheet that aggregates all the data using VSTACK().

Not sure when you might use HSTACK() but the example Google shows is when you’re combining dates together. Kind of a weird scenario, but sure whatever.

In this Google Sheet, I have 3 sheets called shows1, shows2, and shows3. Each sheet has the same columns in the same order, but the data is different between the three:

Then with VSTACK(), you can “add” or concatenate all these data sources together on one page:

Again, this assumes your data is structured exactly the same across sheets or even on the same spreadsheet. If the data is, then using VSTACK() could be a nice way to put together these “disparate” data sources compared to using the bracket syntax {}. This feels like an alternative to CHOOSEROWS() where Google Sheets is just making it easier to use the {} syntax.

6) LET() – Assign the result of a formula to a variable to use in the futureUSEFULNESS RATING: 🌶🌶

I have mixed feelings about the usefulness for this formula. It technically already exists using named ranges. But this is the formula version of named ranges. I also wouldn’t say it’s that much easier to understand compared to a named range hence the 2-pepper rating. It’s also not a “beginner” function.

Say you have a bunch of product ratings like in the table below. In the Average Score column, you want to put the word “High” if the average rating for a product is greater than 4. If the average rating is between 3-4, then you want the word “Medium.” 3 or below should say “Low”:

Today, you might write a simple formula like this to get this output of “High” and “Low”:

=if(average(B44:D44)>4,"High",if(average(B44:D44)>3,"Medium","Low")) A typical nested IF statement. Now with the LET() function, you simple are assigning the average(B44:D44) “result” to a variable. The formula below would output the same exact thing as the nested IF statement above:

=LET(avg_rating, average(B44:D44), if(avg_rating>4,"High",if(avg_rating>3,"Medium","Low"))) Here’s a look at the formula in the context of the example:

The formula doesn’t look that much “easier” compared to writing out the nested IF statement. But for more complicated formulas beyond a regular average, this could make the formula much more readable and easier to debug.

One reason I like this function is that it starts to bridge the gap between working in a spreadsheet and using Google Apps Script (or Office Script if you’re in Excel). Starting to treat things like variables might make the learning curve to scripting in Google Apps Script easier and more approachable to a Google Sheets user who has never touched an Apps Script.

Other Podcasts & Blog PostsIn the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• The Defiant: Synthetix, DeFi Summer 2, FTX, 3AC and Crystal Meth with Crypto OG Kain Warwick
• The Forward Thinking CFO #8: Brian Jones – Microsoft Excel

The post Dear Analyst #117: New 2023 Google Sheets functions for data manipulation that already exist in Excel appeared first on .

View Details

This news is a bit old but I figured it’s juicy enough to talk about its future implications on Excel and artificial intelligence in general. Mid-March 2023, Microsoft announced Copilot, it’s artificial intelligence bet that will supposedly change the way we work. The video discusses how Copilot integrates with Office 365 and all your Microsoft apps including Excel. Around minute 18:00, they show a demo of how Copilot helps you find trends, make adjustments to your models, and more. It’s quite impressive. You can watch just that segment from the presentation below. I watched the video a few times and wondered: will Copilot eliminate the need for entry-level data analysts? Only time will tell.

Breaking down the features in Copilot for ExcelThis is the corporate marketing blurb from the Microsoft blog post announcing Copilot for Excel:

Copilot in Excel works alongside you to help analyze and explore your data. Ask Copilot questions about your data set in natural language, not just formulas. It will reveal correlations, propose what-if scenarios, and suggest new formulas based on your questions—generating models based on your questions that help you explore your data without modifying it. Identify trends, create powerful visualizations, or ask for recommendations to drive different outcomes. Here are some example commands and prompts you can try:

• Give a breakdown of the sales by type and channel. Insert a table.
• Project the impact of [a variable change] and generate a chart to help visualize.
• Model how a change to the growth rate for [variable] would impact my gross margin.

The video shows the above 3 bullet points using a dataset of product sales by country:

Finding key trends with Copilot for ExcelThe first demo involves giving Copilot a prompt like “analyze the data and give me 3 trends.” The output is something you might expect if you’ve done anything with ChatGPT:

This feature in Copilot is table stakes and a version of this came out in Google Sheets in 2017. The Explore panel in Google Sheets can provide similar summary trends on your data and suggest charts you should add to your analysis. Google Sheets has slowly been adding AI-like features over the last few years, so don’t sleep on Google Workspace’s own AI announcement. Below is a dataset of hotels and their locations and I simply clicked on the Explore option in the bottom-right of the Google Sheet:

The trends don’t come in a free-form text format but the different widgets are interesting. The first widget shows additional questions you might ask of your dataset (and Google Sheets spits out the answer). Then the most common visualizations like Pivot Tables and charts are displayed afterwards which makes it easy to analyze and visualize your data. This leads into the next feature in Copilot for Excel: visualizing your data.

Visualizing your data with Copilot for ExcelWhat’s old is new. As I explained in the previous section, Google Sheets’ Explore panel already has a flavor of this feature. The next prompt for Copilot is “Show me a breakdown of Proseware sales growth.” Yes, it’s natural language. Yes, humans are lazy and it’s easy just to ask a question in plain English and get an answer back. But the summary and data and charts already exist in Google Sheets. This just happens to be Excel’s implementation of the Explore feature and the AI is the entry point to this feature:

I like Copilot responds to the prompt by saying:

Remember to check for accuracy.

That doesn’t inspire much confidence in you, Copilot! Nonetheless, Copilot does a few things that are interesting:

• Created a chart with a title and the title has selective formatting (assuming the AI made the “Sales” word foreground color green)
• The tables are nicely formatted with clear headers, formatted percentages, and growth rates
• The background colors for all the cells are white (common formatting trick for making your visualizations stand out more)
• Columns are re-sized to fit the width of the products and the growth rates
• Column A and Row 1 are very narrow in width and height, respectively (another common trick to making dashboards look cleaner)

Was this all AI or just smoke and mirrors?It’s hard to say which of the above formatting operations were done by the AI versus a human who just cleaned up the spreadsheet for a demo.

Does the AI know that a summary table looks better when the background color cells are all white?

Does the AI know that analysts like to make column A and row 1 super narrow/short so that the charts and tables are flush against the edges of the spreadsheet?

If Copilot knew all this, that’s pretty slick. But this just so happens to be the vanilla formatting you’ll see in a dashboard devoid of any custom coloring or branding. It will be interesting to see how an analyst would train Copilot to create visualizations that match the theme and brand guidelines for existing reports.

The next prompt is “Help me visualize what contributed to the decline in sales growth?” The interesting leap that Copilot makes here is translating a very simply business question into a feature (conditional formatting to highlight what contributed to the decline):

But simply applying conditional formatting to a table of numbers is not nearly as impressive as all the formatting steps the AI did in the previous step to create the table in the first place.

What-if scenario analysis with Copilot for ExcelThis is probably the most interesting part of the demo. The next prompt is:

What would have happened if Reusable Containers had maintained the prior quarter’s growth rate?

Before Copilot, you’d have to start thinking about duplicating your summary table and start setting up cell references to replace the current growth rate with another number. Assuming this is not some human playing around with data for the demo, Copilot does the whole thing for you:

What’s impressive is that Copilot was able to copy the original summary table and paste it directly to the right of it. This makes comparing the growth rates easy. It was also able to change the title to reflect the answer to the original prompt. Finally, the step-by-step bullet points tell you exactly what Copilot did to create the analysis.

Perhaps this type of analysis is “easy” for Copilot since you have a relatively simple summary table with clearly spelled out products and growth rates. What if there are more variables involved or there are other one-off factors that would impact the analysis? According to the longer Copilot demo, Copilot has access to the full corpus of data for your organization so it should have the domain expertise that someone who works in the business knows. This means you could ask Copilot questions whose answers are tucked away in some Outlook email, Teams thread, or PowerPoint slide. That’s pretty freaking cool.

The question still remains: Will Copilot replace the need for data analysts?

Source: The Wall Street JournalIf the analysis is as simple as what Microsoft showed in this demo, I think the answer is yes.

If you’re an entry-level analyst, this type of task is not very uncommon. You have dataset where you need to build summary tables and put them into PowerPoint decks to present during meetings. Your manager tells you: “Hey, what would growth look like for Reusable Containers if we didn’t completely tank last quarter and used historical growth rates?” You would probably follow a similar step-by-step process as the above screenshot shows. Copilot appears to be able to do the basic analyst grunt work and format the analysis in a clear visualization.

Why Copilot won’t replace analysts at large enterprisesWhile Copilot does look impressive, it definitely won’t replace human data analysts who understand nuance, context, and business knowledge at large enterprises. If you are a startup and building a model from scratch, Copilot might be a good solution to get something off the ground and running. The Microsoft demo clearly shows that this is possible. I can foresee a few situations where Copilot would not be used in a large enterprise:

1. A lot of money is on the line – The Copilot prompt already tells you to “check for accuracy.” If you are working on a multi-million dollar deal, you best be sure you have a human taking a look at the numbers.
2. Company culture may not be captured in Microsoft applications – As much as our knowledge is “written down” in Word, Outlook, and Teams, there is a lot that is not formally written down in these applications. Humans understand the nuances about company culture and how that can impact the analyses and dashboards analysts create.
3. Existing templates have already been created – In a large enterprise, you are most likely copying an existing file to build a model or dashboard. That institutional knowledge has resulted in well-formatted dashboards where Copilot may not add much value (if formatting is a big part of the task).

Long story short, I’d love to see Copilot tackle a more complicated task that can’t be solved with a simple template. If you’re well versed in Excel, doing what this demo did by “hand” might take all of 15 minutes and you build the knowledge on how to do this analysis in the future. This knowledge makes debugging and troubleshooting models easier.

Other Podcasts & Blog PostsIn the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• People Make Games – We Entered the World of Microsoft Excel eSports & Got Our Asses Beat

The post Dear Analyst Episode #116: Will Microsoft’s AI Copilot for Excel replace the need for analysts? appeared first on .

View Details

Counting the number of colored cells or formatted cells in Google Sheets or Excel seems like it should be a basic operation. Unfortunately after much Googling, it doesn’t seem as easy as it looks. I came across this Mr. Excel forum thread where someone asks how to count the number of rows where there is a colored cell. The answers range from VBA to writing formulas that indicate whether a cell should be colored to the usual online snark. I think the basic issue is this. A majority of Excel or or Google Sheets users will have a list of data and they will color-code cells to make it easier to read or comprehend the data. No fancy formulas or PivotTables. Just coloring and formatting cells so that important ones stick out. I thought this would be a simple exercise but after reading the thread, I came up with two solutions that work but have drawbacks. The Google Sheet for this episode is here.

Video walkthrough:

Color coding HR dataIn the Mr. Excel thread, the original poster talks about their HR data set and the rules their team uses to color-code their data set. Many people in the thread talk about setting up rules for conditional formatting (which I agree with). But it sounds like people just look through the data set and manually color code the cells based on the “Color Key” mentioned in the post:

I think this manual color coding of cells is very common. Yes, someone could write conditional formatting logic to automate the formatting and color coding of these cells. But for most people, I’d argue just eyeballing the dataset and quickly switching the background or foreground color of the cell is easier, faster, and more understandable for a beginner spreadsheet user. If there isn’t that much data, then manually color coding cells feels less onerous.

I put a subset of the data into this Google Sheet and manually color-coded some of the cells into column B below:

Method #1 for counting colored cells: Filter by color and the SUBTOTAL formulaThe quickest way to count the number of cells that have a certain color format is to filter the column by color. After applying the filter to all the column headers, you can filter a column by the cell’s background color through the column header menu. Filter by color -> Fill color -> Desired color:

Let’s say I filter this column by the yellow background color. You’ll see this results in a filtered data set with 9 rows remaining:

In order to actually count the number of cells in this filtered data set, you might be tempted to do a COUNTA() formula, but let’s see what happens when I put this into cell B51:

The formula counts all the rows in the data set including the rows that have been filtered out. Instead, you can use the SUBTOTAL() formula which magically returns the sum, count, etc. for a filtered data set. The key is to use the value “3” for the first parameter to tell Google Sheets to count only the cells in the filtered data set:

I don’t think this is the usual use case for the SUBTOTAL formula. But like many formulas in Google Sheets/Excel, it works! To recap on this method:

Pros* Easy to use and implement * Doesn’t require the use of VBA or Google Apps Script * Since it’s a formula, it’s dynamic and can change as your data changes (with caveats)

Cons* Requires a few steps to get it to work (e.g. filter your data set by a color) * Each time you want to count the number of formatted cells, you need to re-filter by a different color * Since your data is filtered, you can’t easily update the source data and requires you to re-filter by a color

Method #2: Filtered views to allow for dynamic updating of data with the SUBTOTAL formulaThis is an extension of method #1. One of the cons of method #1 is that once you’ve filtered your data set, you need to un-filter the data set if you want to add or remove formatting from your cells. For instance, in column B we have a bunch of yellow colored cells. If you want to highlight another cell as yellow and then re-count the number of cells that are colored yellow, you have to un-filter the data set, highlight the cell that needs to be colored yellow, re-filter the column, and re-write the SUBTOTAL formula (assuming you put it at the bottom of column B):

To avoid filtering and un-filtering the data set, you can create a filtered view of the data set. Additionally, you can put the SUBTOTAL formula somewhere that’s not at the bottom of the data set. Let’s first create a a filtered view just on the background color yellow and we’ll call it “Yellow Cells”:

Now you can quickly switch between the filtered view of yellow-colored cells and the unfiltered data set:

Then we can put the SUBTOTAL formula somewhere below the bottom of the data set. Notice now how when we switch between the filtered view and the unfiltered data set, the SUBTOTAL formula automatically updates:

While this method is an improvement on method #1, it still has some drawbacks. A recap of this method:

Pros* Easily switch between the filtered and unfiltered data set * Update cells with new colors and have that flow into the SUBTOTAL formula dynamically

Cons* Filtered views are not an easily discoverable feature in Google Sheets * Still requires you to go through the Data menu and flip back and forth when you want to count the number of colored cells

Method #3: A macro to count the number of colored or formatted cells in a rangeAlmost all the other solutions for counting the number of colored or formatted cells on the Internet refer to a VBA script for Excel. This is a macro for Google Sheets using Google Apps Script. You can copy and paste the script from this gist. When you run the CountFormattedCells macro in Google Sheets, it counts all the cells that have a background color in column B below. It then outputs the count of cells in cell 52 after you’ve selected a range of cells where you want to count the colored cells:

If you want to specify a color to count, you can color cell C53 with color you want to count. Let’s say I want to count only the green cells. I would color cell C53 with green, select all the cells where I want to find the color green, and then run the macro:

The key to making this work is setting some variables up in the script. The two variables you have to set in the script are outputNumberOfFormattedCells and cellWithFormatToCount. The cells you pick will depend on the specific spreadsheets you’re working with. In the script below, you’ll see that you have to edit the first two variables fit the needs of your Google Sheet:

function CountFormattedCells() { **// Output the number of formatted cells somewhere in your spreadsheet var outputNumberOfFormattedCells = 'C52' // Cell that contains the color you want to count. Default is blank. var cellWithFormatToCount = 'C53'** var spreadsheet = SpreadsheetApp.getActive(); var currentRangeColors = spreadsheet.getActiveRange().getBackgrounds(); if (cellWithFormatToCount !== '') { var cellWithFormat = spreadsheet.getRange(cellWithFormatToCount).getBackground(); } var formattedCellCount = 0 for (var i in currentRangeColors) { for (var j in currentRangeColors[i]) { if (currentRangeColors[i][j] !== '#ffffff' && cellWithFormatToCount == '') { formattedCellCount++ } else if (cellWithFormatToCount !== '' && currentRangeColors[i][j] == cellWithFormat) { formattedCellCount++ } } } if (outputNumberOfFormattedCells != '') { spreadsheet.getRange(outputNumberOfFormattedCells).setValue(formattedCellCount) }}; The macro is very easy to use but it does require you knowing how to add macros to your Google Sheet and editing the script in Google Apps Script. The recap for this method:

Pros* Script is easy to copy and paste into Google Apps Script and works right out of the box * Just two variables to customize * Doesn’t require any filtering of your data set or any formulas * Can assign a keyboard shortcut to the macro to quickly run the macro * Could assign a time-based trigger to the macro so that it runs every minute or hour to give you a “dynamic” count

Cons* Requires knowledge of macros and editing a Google Apps Script * May need to change the location of the cell where you output the count of colored cells if your data changes a lot over time * Requires running the macro each time you want to get an updated count of the colored cells

Bottom lineNone of these methods are that simple or easy to use in my opinion. Usually I have a preferred method for solving some Google Sheets or Excel problem, but in this case I can’t say I like or dislike a method over another one. If I had to pick one, I’d use method #3 since I’m comfortable with macros and editing Google Apps Scripts. But the Google Apps Script solution is far from easy to use for a beginner to Google Sheets.

The SUBTOTAL formula is indeed much easier to implement, but also comes with the added inconvenience of constantly filtering and unfiltering your data set.

Other Podcasts & Blog PostsIn the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• Google Cloud Platform Podcast: Top 5 Data & Analytics Launches from Next 2022 with Bruno Aziza and Maire Newton

The post Dear Analyst #115: How to count the number of colored cells or formatted cells in Google Sheets appeared first on .

View Details

When you think of data pipelines, data warehouses, and ETL tools, you may be thinking about some large enterprise that is collecting and processing data from IoT devices or from a mobile app. These companies are using tools from AWS and Google Cloud to build these complex workflows to get data to where it needs to be. In this episode, you’ll hear about a relatively small company who is using modern cloud and data tools rivaling these aforementioned enterprises. Elite Development Group is a real estate investment and construction company based in York, Pennsylvania and is less than 50 employees. Doug Walters is the Director of Strategy and Technology and Elite and he discusses how data at Elite was trapped in Quickbooks and in their various tools like property management software. He spearheaded projects to build data connectors to aggregate various data sources to help build a modern data stack to help make real estate decisions.

Data is stuck in silosElite Development Group consists of a few divisions: HVAC, home performance, energy efficiency, etc. All the typical functions you’d expect a real estate company to have. Doug first started working in IT support and realized their company didn’t have easy access to their data to make data-driven decisions. You’ve probably heard this phrase over and over again:

Data is trapped in silos.

You buy some off-the-shelf software (in this case property management) that is meant for one specific use case. Over time, that data needs to be merged with your customers data or sales data. You end up exporting the data in these silos to CSVs to further combine these data sources down the line. For Elite, data was trapped in property management software, Quickbooks, you name it.

Starting the process to export data After doing a survey of their tools, Doug realized that there weren’t many APIs to easily extract data from the source. So he helped set up data scrapers to get data off of the HTML pages. He also used tools like Docparser to extract data from Word docs and PDFs.

Most data was either in XLS or CSV format, so Doug was able to set up an automated system where every night he’d get an email with a CSV dump from their property management system. This data then ended up in a Google Sheet for everyone to see and collaborate on. After doing this with property management, Doug started exploring getting the data out from their work order tracking system.

Creating accurate construction cost estimatesOne activity Doug wanted to shine the data lens on was cost estimates as they relate to construction. Hitting budgets is a big part of the construction process. You have multiple expenditures from a job and each job needs to have a specific estimate tied to it. This could all be done in Excel or Google Sheets, but given the importance of this data, Doug decided to create something more durable. He created an internal database where each cost estimate and a specific Estimate ID. A unique identifier to give to a cost estimate.

Since Elite uses Quickbooks for their accounting, each project had to be tied to a unique Estimate ID established previously. Then each work order had a unique Work Order ID. Now Elite is able to run reports on all their projects to see what the cost estimates and actual expenditures were for a job. Now they could do a traditional budget to actual variance analysis.

The result? Project teams could start to see when they were about to hit their budgets in real time.

More importantly, this started Doug down a journey of seeing how far he could automate the data extraction and reporting for his company. With the current implementation, the data could only get refreshed every 24 hours. He eventually set up the system so that any user could click a button to refresh a report. The data workflow started from exporting data into Excel and Google Sheets and into complex data connectors and using software for business intelligence.

Income lost due to vacancy metricWhen Elite prioritizes which projects to work on, they look at a metric called “income lost due to vacancy.” Without the different data connectors and systems Doug help set up, this metric wouldn’t exist. This metric essentially helps a property owner figure out how much income they are losing due to vacancies.

When looking at a portfolio of properties to improve, Elite can use this metric to figure out which project would have more high-rent units available. Previously, they would have to rely on intuition to figure out where to invest more time and money into projects.

Building out the data stackThe list of tools Elite uses to extract and process data rivals that of large enterprises. Here is a rundown of Elite’s data stack:

• Fivetran for data loading and extraction
• AWS Redshift as the data warehouse
• Google Cloud functions to run one-off tasks
• dbt for transformation and for pushing data into a datamart
• Sisense to create actionable insights

There are multiple data connectors involved for doing the ETL process as well. With all these modern tools, Elite is able to get the most up-to-date data every 5-15 minutes.

As Elite went through this data journey, Doug and his team started to ask some of their vendors to develop an API so they could get more data out. Their data vendors would push back and say they’ve never seen these requests from such a small company. Typically these data requests are coming from their large customers which shows how deeply Doug’s team has thought about automating their data workflows.

Advice for small companies working with big dataDoug gives some practical advice on how to use some of these tools that are supposedly meant for large enterprises. The first thing is to experiment with spreadsheets before diving deep into a complicated workflow. Doing your due diligence in a spreadsheet is low stakes and helps you uncover all the various relationships between your data.

In terms of learning how to use these tools, Doug mentioned that most of these vendors have their own free or paid workshops and tutorials. I’m always surprised by how much general data training these vendors provide that many not even be about their software. You can learn about databases, SQL, and data analysis from these vendors.

At a high level, Doug says that the data you collect and visualize needs to be tied to some business strategy. These overall goals might include increasing revenue, increasing customers satisfaction, or ensuring your employees are developing new skills. At Elite, the data has allowed the team to look at their portfolio of real estate at the 30,000-foot level all the way down to individual transactions. Data is actually helping them solve real business problems.

And one last plug for Google Sheets: Doug talked about how you would have to hire someone who was an “Excel guru” or a data analyst to help you decipher your Google Sheets files. Now Google Sheets has become so robust, extensible, and–dare I say–easy to use that anyone in the company can pick it up and mold it to their needs. No one ever gets fired for using a Google Sheet 😉.

Other Podcasts & Blog PostsNo other podcasts mentioned in this episode!

The post Dear Analyst Episode #114: How a small real estate investment company uses modern data and cloud tools to make data-driven decisions appeared first on .

View Details

It’s that time of the year again where data professionals look at their data predictions from 2022 and decide what they were wrong about and think: “this must be the year for XYZ.” Aside from the fact that these type of predictions are 100% subjective and nearly impossible to verify, it’s always fun to play armchair quarterback and make a forecast about the future (see why forecasts are flawed in this episode about Superforecasting). The reason why predicting what will happen in 2023 is that my predictions are based on what other people are talking about, not necessarily what they are doing. The only data point I have on what’s actually happening within organizations is what I see happening in my own organization. So take everything with a grain of salt and let me know if these predictions resonate with you!

1) Artificial intelligence and natural language processing doesn’t eat your lunchHow could a prediction for 2023 not include something about artificial intelligence? It seems like the tech world was mesmerized by ChatGPT in the second half of 2022, and I can’t blame them. The applications and use cases are pretty slick and mind-blowing. Internally at my company, we’ve already started testing out this technology for summarizing meeting notes and it works out quite well and saves a human from having to manually summarize the notes. My favorite application of AI shared on Twitter (where else do you discover new technologies? Scientific journals?) is this bot that argues with a Comcast agent and successfully gets a discount on an Internet plan:

Here it is! The first ever Comcast bill negotiated 100% with A.I and LLMs.

Our @DoNotPay ChatGPT bot talks to Comcast Chat to save one of our engineers $120 a year on their Internet bill.

Will be publicly available soon and work on online forms, chat and email. pic.twitter.com/eehdQ5OXrl

— Joshua Browder (@jbrowder1) December 12, 2022

These examples are all fun and cute and may help you save on your phone bill, but I’m more interested in how AI will be used inside organizations to improve data quality.

Data quality is always an issue when you’re collecting large amounts in real-time every day. Historically, analysts and data engineers are running SQL queries to find data with missing values or duplicate values. With AI, could some of this manual querying and UPDATE and INSERT commands be replaced with a system that intelligently fills in the data for you? In a recent episode with Korhonda Randolph, Korhonda talks about fixing data by sometimes calling up customers to get their correct info which then gets inputted a master data management system. David Yakobovitch talks about some interesting companies in episode 101 that smartly help you augment your data using AI.

We’ve also seen examples of AI helping people code via Codex, for example. I think this might be an interesting trend to look out for as the demand for data engineers from organizations outpaces supply. Could an organization cut some corners and rely on Codex to develop some of this core infrastructure for their data warehouse? Seems unlikely if you ask me, but given the current funding environment for startups, who knows what a startup founder might do as runways shrink.

2) Enforcing data privacy and regulation in your user databaseThis trend has been going in since the introduction of GDPR in 2018. As digital transformation pushes all industries to move online, data privacy laws like GDPR and CCPA force these companies to put data security and governance as the number one priority for all the data these companies store. In particular is user data. Any company that has a website where you can transact allows you to create a user account. Most municipalities have a dedicated app where you can buy bus and metro tickets straight from the app. Naturally, they ask you to create a profile where your various payment methods are stored.

When it comes to SaaS tools, the issue of data privacy becomes even more tricky to navigate. Many user research and user monitoring services tout their abilities to give organizations the ability to see what your users and customers are “doing” on these organizations’ websites and apps. Every single click, mouseover, and keystroke can be tracked. How much of this information do you store? What do you anonymize? It’s a cat and mouse game where user monitoring software vendors claim they can track everything about your customers, but then you have to temper what information you actually process and store. The data team at my own company is constantly checking these data privacy regulations to ensure that we implement data storage policies that reflect current legislation.

Source: DIGITA closely related area to data privacy is data governance. Data governance vendors who help your organization ensure your data strategy is compliant have increased dramatically over the years as a result of data regulation and protection laws.

To bring this back to a personal use case, type in your email address in haveibeenpwned.com. This website basically tells you which companies have had data breaches and whether your personal information may have been compromised. To take this another step, try Googling your name and your phone number or address in quotes (e.g. “John Smith 123-123-1234”). You’ll be surprised by how many of these “people finder” websites have your personal information and of your family members. One of the many websites you’ve signed up for probably had a breach and this information is now out there being aggregated by these websites, and you have to manually ask these websites to take your information out of their databases. Talk about data governance.

3) Data operations and observability tools manage the data lifecycleI’m seeing this happen within my own company and others. DevOps not only monitors the health of your organization’s website and mobile app, but also databases and warehouse. It’s becoming more important for companies who undergo the digital transformation to maintain close to 100% uptime so that customers can access their data whenever they want. Once you give your customers and users a taste of accessing their data no matter where they are, you can’t go back.

I think it’s interesting to think about treating your “data as code” and apply concepts of versioning from software engineering to your data systems. Sean Scott talks about data as code in episode #96. The ETL process is completely automated and a data engineer or analyst can clone the source code for how transformations happen to the underlying data.

I’m a bit removed from my own organization’s data systems and tooling, but I do know that the data pipeline consists of many microservices and dependencies. Observability tools help you understand this whole system and ensure that if a dependency fails, you have ways to keep your data flowing to the right endpoints. I guess the bigger question is whether microservices is the right architecture for your data systems vs. a monolith. Fortunately, this type of question is way beyond my pay grade.

Source: DevCamp4) Bringing ESG data to the forefrontYou can see this trend happening more and more, especially in consumer transportation. Organizations are more conscious about their impact on their environments with various ESG initiatives. In order to ensure organizations are following new regulations, the SEC and other regulatory bodies rely on quality data to ensure compliance.

One can guess which industries will be most impacted by providing this ESG data, but I imagine other ancillary industries will be affected too. Perhaps more data vendors will pop up to help with auditing this data so that organizations can meet compliance standards. Who knows. All I know is that consumers are asking for it, and as a result this data is required to be disclosed.

Google Flights showing CO2 emissionsWe know that cloud computing and storage gets cheaper every year (e.g. Moore’s Law). Cheap from a monetary perspective, but what about the environmental impact? An interesting thought exercise is tracing the life of a query when you open Instagram on your phone and start viewing your timeline of photos. The storage and compute resources are monetarily cheap to serve that request, but there is still a data center that runs on electricity and water that needs to process that request. Apparently 1.8% of electricity and 0.5% of greenhouse gas emissions are caused by data centers in the United States (source).

When I think about all the cronjobs and DAGs that run to every second to patch up a database or serve up photos to one’s Instagram feed, I wonder how much of these tasks are unnecessarily taxing our data centers? I have created a few Google Apps Scripts over the years (like creating events from email or syncing Google Sheets with Coda). You could have these scripts run every minute or 5 minutes, but is it necessary? Considering that Google Apps Script is a 100% free service, it’s hard to understand the “cost” with running a script that hits a Google data center somewhere which may be moving gigabytes of data from one server to another. I started thinking about the cost of keeping these scripts alive for simple personal productivity hacks like creating calendar events from email. Sure, my personal footprint is small, but when you have millions of people running scripts, that naturally becomes a much bigger problem.

I still have a lot to learn about this area and my views are influenced by simple visualizations like the one above. It all starts with quality ESG data!

5) Organizations help employees acquire data literacy and data storytelling skillsThis trend is a bit self-serving as I teach various online classes about Excel and Google Sheets. But as a result of data tools like Mode, Looker, and Google Data Studio pervading through organizations, not just the analysts are expected to know how to use and understand these tools. Unfortunately, data skills are not always taught in middle school or high school (they certainly weren’t taught when I was growing up). Yet, the top skills we need when entering the workforce are related to using spreadsheets and analyzing data (I talk about this subject in episode 22 referencing this Freakonomics episode). This episode with Sean Tibor and Kelly Schuster-Paredes is also worth a listen as Sean and Kelly were teachers who incorporated Python into the classroom.

In 2019, The New York Times provided a “data bootcamp” for reporters so that they could better work with data and tell stories with data. The Google Sheets files and training material from this bootcamp are still publicly available here. You can read more about this initiative by Lindsey Cook–an editor for digital storytelling and training at The Times–here. The U.S. Department of Education also believes that basic data literacy skills should be introduced earlier in the curriculum and they created this whole deck on why these skills are important. This is one of my favorite slides from that deck:

Source: U.S. Department of EducationWhat does this mean for organizations in 2023? Upskilling employees in data literacy and storytelling could mean online classes or simple a 1 or 2-day training with your data team. Interestingly, data vendors provide a ton of free training already. While some of this training can be specific to the data platform itself (like Google’s Analytics Academy), other platforms provide general training on databases, SQL, and Excel. So if you don’t pay for the training, at least utilize the free training provided by Mode, Looker, Google Data Studio, Tableau, etc.

Other Podcasts & Blog PostsIn the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• Making Sense #299: Steps in the right direction – A conversation with Russ Roberts

The post Dear Analyst #113: Top 5 data analytics predictions for 2023 appeared first on .

View Details

This is a re-broadcast of an episode of the Secret Ops podcast hosted by my friend Ariana Cofone. Ariana is an operations consultant and her podcast is all about business operations. While I’ve never officially held a “business operations” role, I’ve worked in roles that are related to the operations world. In this conversation, we dive into what operations is, how I approach building workflows and solutions for people, and of course, why knowing Excel and SQL are crucial to getting into the world of business operations.

Check out the Secret Ops podcast here if you’re interested in learning more about operations!

Other Podcasts & Blog PostsNo other podcasts mentioned in this episode!

The post Dear Analyst #112: Greatest lessons learned from building solutions and processes for humans (re-broadcast of Secret Ops) appeared first on .

View Details

One topic that hasn’t been covered on Dear Analyst is master data management (MDM). I’m surprised it took this long before someone brought it up. I’ve never heard of the term before and it looks like it’s a core strategy for many large corporations for manager their data. Korhonda Randolph studied systems engineering at the University of Pennsylvania and started her career in engineering. She started specializing in master data management at companies like AutoTrader, Cox Automotive, and SunTrust/BB&T (merger). In this episode, Korhonda discusses what master data management is, data cleansing the CRM at AutoTrader, and the various data issues you have to work through during a merger between two banks.

A “master” record in master data managementThe definition of master data management according to Wikipedia is pretty generic:

Master data management (MDM) is a technology-enabled discipline in which business and information technology work together to ensure the uniformity, accuracy, stewardship, semantic consistency and accountability of the enterprise’s official shared master data assets.

After doing some quick research, MDM is closely associated with data quality and data governance. The cynical side of me says this is one of those disciplines that was created by data vendors way back when. But given the size and scope of the projects the MDM discipline is used in, it’s very likely I just have never had any experience with people who have utilized this discipline.

Source: Info-Tech Research GroupAt a high-level, the goal of MDM is very simply. Create a “master” record for a customer, product, or some other entity that doesn’t change very much. Korhonda discusses working on customer data where properties like the first and last names of a customer would be an output of MDM. This data should stay consistent no matter what team or department is looking at the customer data.

Data cleaning CRM data at AutoTrader AutoTrader was trendsetting in the field of data. Early on, data architects created their own MDM systems to manage customer data. If the MDM system is not created properly, then other systems would not function correctly. Korhonda’s team was using Hadoop because AutoTrader works with many car dealerships who need data to help them with their businesses.

Korhonda started as a project manager at AutoTrader helping coordinate all the moving parts of AutoTrader’s MDM system. Eventually she became a solutions architect on the data side.

I’ve talked about data cleaning in multiple episodes and I’ve discovered a few things about the process over the years:

1. Excel and SQL are still the main tools used for data cleansing
2. The same type of data problems exist at startups and large corporations alike

At AutoTrader, they were trying to figure out if client A in the sales system was also client A in another system. There is missing data across systems, and AutoTrader would try to find 3rd-party data sources to fill the gaps in the customer data. They may even contact the customer directly to get the data the need. At the end of the day, this type of data problem is not unique to AutoTrader. To this day, it still surprises me how simple and universal these data quality issues are.

Korhonda also discusses “systems of engagement.” These are the interfaces (e.g. a form on a website) where data is entered by a customer. These systems of engagement have to ensure that all the required information is captured such as birthdays. It’s like Amazon validating you entered your address correctly before shipping you a package.

“Analysts make the data flow”Once the MDM system was in place, AutoTrader had a single source of truth for things like customers and dealerships. There was no more duplicate data. According to Korhonda, this had profound operational impact on the business. That feeling when your data is all cleaned up can only be summed up as:

Korhonda talks about how data analysts are becoming more important at organizations where there are tons of data that needs to be analyzed. She says data analysts are just as important as the data engineers who are creating the back-end systems.

Analysts make the data flow.

Engineers are great at building systems, but knowing the right data to include in the system is where business owners come into play. Business owners are subject-matter experts who know about the business rules in the organization, and what type of data would make sense to include in the system.

Merging client data between SunTrust and BB&TIn 2019, BB&T Corporation and SunTrust Banks merged to become Truist Financial Corporation. SunTrust and BB&T were banks based primarily in the southeast of the U.S. These two banks had an overlapping footprint, so there were many customers who belonged to both banks. Behind the scenes, Korhonda was in charge of merging the customer data between these two banks. The customer data had missing birthdays, missing names, and overall there were a lot of legacy processes creating dirty data. Needless to say, it was a mess.

There are a variety of bank regulations that I don’t care about getting into, but it’s interesting to note how these regulations impact the data processes Korhonda dealt with. For instance, there are federal rules about how much a customer can deposit at a bank. If the customer deposits too much, they get added to a special report. As a result, a clean list of customers was needed for the regulators before the merger could go through.

Korhonda acted as the project manager and worked with business stakeholders to sign off on all the rules for the MDM system that was being developed. Each bank had thousands of processes for collecting and storing data, and small differences had a large impact on the project.

For instance, one system might have a 40-character limit for an address but the other system had a 50-character limit. Do you increase the field size to the larger 50 characters? Do you truncate longer addresses? Korhonda and her team had to make decisions like this thousands of times taking into feedback from a variety of stakeholders.

Advice for companies working with dirty dataWe ended the conversation on advice Korhonda has for organizations working with a lot of data that needs to be queried and cleaned up. Data lineage is a hot buzzword in the data infrastructure world (see episode #59 for a list of some companies in the data lineage space). In a nutshell, data lineage tools help you visualize how your data flows from the source all the way to when it gets consumed (typically by data analysts and business users). Referring to the merger example, Korhonda said having a robust data lineage platform would help you with issues like field lengths changing.

Source: OctopaiIn addition to maintaining these data flow diagrams, Korhonda made a final plug for having MDM professionals maintaining an organization’s MDM systems. Sometimes the MDM systems are owned by a systems architect or a DBA, but these people may not see or know the overall picture of the data system.

In terms of advice for data analysts, Korhonda said that it’s more than just knowing how to write SQL. You have to know how to tell the story if you want to make an impact. The data storytelling skill has been repeated quite a few times in previous episodes.

Be a visionary and display data in way that’s easy to understand.

Other Podcasts & Blog PostsNo other podcasts mentioned in this episode!

The post Dear Analyst #111: Master data management at AutoTrader and working with data in a merger with Korhonda Randolph appeared first on .

View Details

Have you ever faced a spreadsheet where one column contains values for each “section” of the spreadsheet but you want to fill those values down through the rest of the column? This is a common problem when you get a data dump from a database or perhaps a copy/paste from a PivotTable. You have values […]

The post Dear Analyst # 110: A tutorial on how to fill values down with Excel VBA and Google Apps Script (Vancouver Power BI/Modern Excel re-broadcast) appeared first on .

View Details

Johnson & Johnson is one of the largest corporations in the world and they produce everything from medical devices to baby powder. They were also on the front lines of developing a vaccine during the pandemic. Internally, J&J is also at the forefront of digital transformation. Sarfraz Nawaz studied computer science and built data analytics […]

The post Dear Analyst #109: Data strategy and optimizing the vaccine supply chain at Johnson & Johnson with Sarfraz Nawaz appeared first on .

View Details

This episode is a re-broadcast of a podcast I did with Bassem Dghaidi, a senior software engineer at GitHub. Bassem has a podcast and YouTube channel called Glich. On his show, he covers moderate to advanced engineering topics on coding, architecture and management. Data analytics is a little outside of the topics he generally talks […]

The post Dear Analyst #108: Skills needed for a successful data analytics career (Glich podcast re-broadcast) appeared first on .

View Details

Matthew Brandt didn’t know analytics was a potential career option until he started managing websites with Google Analytics. Matthew is Canadian, spent most of his life in Switzerland, and went to high school in Japan. He attended the EHL hospitality school at Lausanne, and was part of the 70% who left the hospitality field after […]

The post Dear Analyst #107: Using Twitch to teach people about analytics and launching a food tech startup with Matthew Brandt appeared first on .

View Details

Gift Ojeabulu is a data science professional, community organizer, and data practitioner from Nigeria. He studied biochemistry at university but eventually switched to computer science. He first started in web and Android development before moving to the data science field. By attending developers conferences in Lagos, he started learning more about data science and started […]

The post Dear Analyst #106: Democratizing data knowledge and community building in Africa with Gift Ojeabulu appeared first on .

View Details

Once you learn the VLOOKUP formula, your world opens up in terms of being able to analyze and manipulate data. There are hundreds if not thousands of tutorials on how to use the VLOOKUP formula since it’s such a powerful formula for finding the data you need in a long list. Comparable formulas include the […]

The post How to do a VLOOKUP with multiple conditions or criteria (3 methods) appeared first on .

View Details

I’m always fascinated by how different people on the podcast find their way into data, and this episode is no exception. Austin Dowd has always enjoyed photography. He was associated with the American Marketing Association and was always curious in the metrics for his photos. After pestering the analytics person in the AMA in terms […]

The post Dear Analyst #104: Creating a single source of truth by cleaning marketing analytics data with Austin Dowd appeared first on .

View Details

I used to create a monthly 30-slide report and each slide had a different table or chart that I copied and pasted from Excel. As a naive analyst, I literally filtered my list of data using regular dropdown filters on each column to get the numbers I needed. I would filter, sum or average the […]

The post Dear Analyst #103: How to use one of the best features in PivotTables to filter your data (Slicers) appeared first on .

View Details

Experimentation is a valuable activity in a variety of functions. A product team should be constantly experimenting with features to see which variant leads to the most engagement, sales, or some target metric. But what about on a data analytics team? Mel Restori is the former Director of Analytics & Analytics Engineering at Trove, a […]

The post Dear Analyst #102: Building a culture of experimentation on a data analytics team with Mel Restori, former Director of Analytics at Trove appeared first on .

View Details

Outside of Excel, you’ve seen and heard multiple data platforms on this newsletter and podcast. Everything from commercial data platforms to open-source platforms driven by communities. In this episode, you’ll hear the other side of the data platform ecosystem. David Yakobovitch is a general partner at DataPower Ventures, a venture capital firm that invests in […]

The post Dear Analyst #101: How to invest in modern data startups with David Yakobovitch appeared first on .

View Details

Holy cannoli. Somehow we’ve hit 100 hundred episodes of the Dear Analyst podcast. This podcast started off as an experiment because I was lazy and got tired of writing. I figured speaking about Excel and data analytics would be easier than coming up with prose. Ironically, I’m still writing because all the show notes for […]

The post Dear Analyst #100: Hitting a hunned! A look back at the top 5 episodes of Dear Analyst appeared first on .

View Details

MLOps, neuromorphic computing, next-generation enterprises. These are concepts you generally don’t hear about in the field of data analytics. This episode shows that you can parlay a career in data analysis into any field you want. That’s exactly what Ryan Cunningham did to become a Senior Associate Builder at AI Fund. Ryan studied finance and […]

The post Dear Analyst #99: Hyperscaling at Uber Eats and using VBA to automate M&A models with Ryan Cunningham appeared first on .

View Details

When you’re working in a marketing or advertising agency, you’ll work with clients across various industries. The data you analyze will also vary client to client, giving you exposure to various datasets and the business logic that drives these datasets. Wacarra Yeomans started her career as a graphic designer in the agency world. She always […]

The post Dear Analyst #98: How a career in graphic design helps you with data storytelling and visualizations with Wacarra Yeomans appeared first on .

View Details

As a flex player on a data team, you might play the role of a data scientist, data analyst, or data engineer. Sarah Krasnik is one of those people who has held all these roles. In this conversation, Sarah gets into the weeds of what most data analysts do: helping business partners make better decisions […]

The post Dear Analyst #97: Becoming a data Swiss army knife for marketing, operations, and customer support data problems with Sarah Krasnik appeared first on .

View Details

What did the developer say to the DBA? It doesn’t matter, the answer is “no.” I’ve never worked with a database administrator (DBA) before but know they play an important part in the data lifecycle at a company. Sean Scott stumbled into the DBA world and has been in this field for 25+ years. He […]

The post Dear Analyst #96: Treating data as code and the new frontier for DBAs with Sean Scott appeared first on .

View Details

The motivation for this post/episode is a selfish one (scroll to the very bottom or skip to the end of the podcast to see why). As I thought about the framing of this post during my normal “thinking” moments (commuting, on the toilet, during useless meetings), I realized I’m going to take a different approach […]

The post Dear Analyst #95: Nobody ever got fired for choosing Google Sheets appeared first on .

View Details

A common theme I’ve noticed from talking with many data analysts and data engineers is that they didn’t come from a “data” background. Helen Mary Barrameda someone who exemplifies this theme. She is based in the Philippines and started her career as a freelance writer in 2004 writing lifestyle pieces. She used her earnings from […]

The post Dear Analyst #94: Helen Mary Barrameda on having a “portfolio career” prior to being a data analyst, winning the NASA Space Apps challenge, and wfh tips appeared first on .

View Details

This episode is quite different from other episodes for a few reasons. One, it’s the first time I’ve had two guests on the show at the same time. Second, it’s the first time I’ve had educators on the show. Third, the guests have a podcast about Python so they taught me a thing or two […]

The post Dear Analyst #93: How to bring data literacy to schools and teaching Python with Sean Tibor and Kelly Schuster-Paredes appeared first on .

View Details

Data can come from different places, and one area I don’t hear about too often is from vehicles. Victor Rodrigues is from Brazil and transitioned into a career in data six years ago. Before that, he was working in various IT including network and infrastructure administration. He eventually relocated to Dublin working as a cloud […]

The post Dear Analyst #92: Generating insights from vehicle telemetry data and crafting a data strategy with Victor Rodrigues appeared first on .

View Details

I first heard Elena speak on another podcast and was shocked to hear an analyst talk about one of the biggest companies to emerge during the pandemic: Peloton. Someone from the inside, as it were, is talking about topics that Peloton would likely want to keep confidential. Due to PR and the restrictions that come […]

The post Dear Analyst #91: Growing Peloton’s product analytics team and growth funnel experimentation at Superhuman with Elena Dyachkova appeared first on .

View Details

You go to a family gathering and everyone is fawning over you cousin who has a cushy stats job at Harvard. Knowing your cousin, you think to yourself: if my cousin can do it, so can I. Next thing you know, you are a research fellow at Harvard University. Tyler Vu was studying applied math […]

The post Dear Analyst #90: Biostatistics, public health, and the #1 strategy to land a job in data with Tyler Vu appeared first on .

View Details

Most episodes I have the privilege of speaking with analysts who are in the trenches using tools and doing analyses. In this episode, we look at the role of data from a manager/director of data’s perspective. David Jayatillake is currently the Chief Product & Strategy Officer at Avora, an augmented analytics solution that helps companies […]

The post Dear Analyst #89: Leading high performing data teams and deciphering the data stack with David Jayatillake appeared first on .

View Details

Companies are generating more big data these days, so dumping the data into a CSV for analysis just doesn’t cut it anymore. Sure you could use Power Query or Power BI, but more analysts are turning to Python and platforms built for big data processing. The next step is to use machine learning to help […]

The post Dear Analyst #88: How to learn data science and machine learning from scratch with Santiago Viquez appeared first on .

View Details

Everyone is probably familiar with the 600,000 emails released by Enron after their scandal right at the turn of the century. A lot of different analyses was done on those emails, but there’s one interesting analysis that I didn’t see until recently: the emails with spreadsheets as attachments. Felienne Hermans, a computer scientist at Delft […]

The post Dear Analyst #87: What we can learn about Enron’s downfall from their internal spreadsheet errors appeared first on .

View Details

Nothing like a click-baity headline to get your spreadsheet emotion all riled up amirite? Earlier in my data analysis career, I thought knowing advanced Excel formulas and writing macros made you a good analyst. If you’ve been following this podcast/newsletter, you’ve probably discovered that there is no one magic Excel feature that automatically makes you […]

The post Dear Analyst #86: One Important Excel Feature to Know to Do Your Best Data Analysis appeared first on .

View Details

You might think not having a data background means it’ll be tougher for you to get a career in data. For Preksha Kaparwan, co-founder and CMO at Alphaa AI, the journey into data was anything but traditional. In this episode, Preksha talks about starting her career in the hospitality industry, falling in love with data […]

The post Dear Analyst #85: Preksha Kaparwan talks about her journey as a chef To CMO of a data analytics company appeared first on .

View Details

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 […]

The post Dear Analyst #84: Top 3 data analytics trends in 2021 and top 3 predictions for 2022 appeared first on .

View Details

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.

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!

The post Dear Analyst #83: Cleaning up and doing an exploratory data analysis of all Friends episodes raw data in Excel appeared first on .

View Details

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:

1. Text you want to translate
2. Source language of your text (2-letter abbreviation)
3. 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 As you change the dropdown in cell B1, Google Translate automatically changes the translation:

Pretty neat seeing these famous quotes in different languages and being able to add new quotes over time to this spreadsheet.

Adding Chinese pinyin to Chinese translations Unfortunately I can’t read a lot of Chinese characters and rely on pinyin to help sound out the characters. Unfortunately Google Translate in Google Sheets doesn’t have a built-in pinyin translation for Chinese characters, so I looked for one in the Extensions menu. Aside from the XLMiner Analysis Toolpak extension (mimics Excel’s Analysis Toolpak add-on), I haven’t used another extension before.

When you search for pinyin add-ons, this Mandarin Cantonese API add-on is probably the best one (if not only one) that does pinyin translation from Chinese characters:

In column C, the PINYIN function simply takes one argument (Chinese characters):

I’ve always wanted to learn how to say “leave the gun, take the cannoli” in Chinese. Unfortunately cannoli doesn’t translate well into Chinese. Google Translate does do a good job of taking names and giving you the phonetic translation (see Fredo).

Other Podcasts & Blog Posts No other episodes mentioned!

The post Dear Analyst #82: Translating the best lines from The Godfather with Google Translate in Sheets appeared first on .

View Details

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 whereyou 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. Another community that really embodies this mindset is the FIRE community (Financial Independence/Retire Early). The best part about all these games is that you can encode them into a spreadsheet to find the path to winning, whatever that means for you. I’ll leave you with this:

Playing by the rules of the game can get you far. But if you take the rules to the extreme, you will go farther.

Booking mixed-cabin award flights on Avianca Airlines’ LifeMiles program I’m currently looking up flights for a potential trip next year, and have points in various points “ecosystems” like Ultimate Rewards from Chase. One program I’m looking into more is Avianca Airlines’ LifeMiles program. Avianca Airlines is an airline based in Colombia, but through their online portal, you can book flights from their partners in the Star Alliance. When booking an award flight with LifeMiles, you also never pay fuel surcharges which can add up to several hundreds of dollars per ticket.

Did some research and came across this post from FrequentMiler. There is an interesting hack with LifeMiles’ program where you book a mixed-cabin ticket, and the number of miles you pay is less than the miles you would pay for booking a direct flight to the destination. Let’s break this down.

A one-way ticket from New York to France in business class costs 63,000 miles Now this depends on availability and all that, but according to the LifeMiles award chart (pulled from the FrequentMile post), a ticket from New York (United States 1 region) to France (Europe 2 region) costs 63,000 LifeMiles:

Source: FrequentMiler I can’t confirm if this award chart is still accurate. Nonetheless, it’s still a good starting point to calculate potential costs for flights.

A multi-city ticket from New York to France in business class then from France to Turkey in economy class only costs ~50,000 miles That’s a 20% savings in points for basically getting the same flight (New York to France in business class). You also get this extra leg in economy class from France to Turkey. If your final destination is Turkey, this might be a great way to get there. How is this possible?

Unlike other airlines, Avianca does this weighted-average approach to using miles based on the total miles flown in each segment. So here’s how the math works:

• EWR to CDG = 3,649 miles
• CDG to IST = 1,379 miles
• Total miles flown = 5,028 miles
• EWR (US1) to CDG (Europe2) in business class = 63,000 LifeMiles
• CDG (Europe2) to IST (Europe3) in economy class = 15,000 LifeMiles
• “Weighted average” actual LifeMiles cost = 3,649 / 5,028 X 63,000 + 1,379 / 5,028 X 15,000 = 49,835 LifeMiles

You would think that the total cost should just be 63,000 + 15,000 = 78,000 LifeMiles. This is an example of how knowing the rules of the game can help you save a decent amount of money (let’s face it, award miles are a form of currency these days).

Creating a tool to calculate multi-city routes to take advantage of this mixed-cabin hack As you can imagine, this hack allows you to fly first class to one city, then business or economy class to another city, and the resulting cost will be less than a direct flight. The distance in that 2nd leg where you’re in a lower cabin basically determines how much you save.

Those screenshots in that FrequentMiler post tells me that the author (or Avianca) originally had this award chart in Excel. I copied over some of the relevant numbers for a flight originating from New York (United States 1 region) into this Google Sheet. Here’s a screenshot of the tool with relevant sections called out:

Structuring the data Before building the actual miles calculator, we have to lay out the data in a way that makes sense for the tool. This was the original “structure” of the country to region mapping in the FrequentMiler post:

Source: FrequentMiler While this layout above is great to quickly see which region your country resides in the award chart, it’s not a great structure for creating a dynamic tool or model. We just need a simple two-column table mapping the country to the region to do lookups. You’ll notice I left out United States 2 and United States 3 since I’m only interested in flights originating from New York (United States 1).

The actual award chart is also in a format that is easy to read to find a specific number, but not great for our model. So we need to transpose that award chart into a four-column table that looks like this. This table contains all the different combinations of a “from” and “to” region that make sense for this tool:

Calculating LifeMiles for a direct flight The first step is to quickly find the number of miles it takes to go from US1 to any country in Europe for a given cabin. The first thing to do is make the input for country and cabin as error-free as possible. These two inputs are dropdowns so that the user can only select specific values for these two cells:

To create these dropdowns, you can click on Data->Data Validation and just reference all the countries that we want as selectable options:

Once we have the destination country and the cabin, this formula gives us the number of LifeMiles for that flight:

=filter(L2:L10,J2:J10=vlookup(B4,F2:G41,2,0),K2:K10=B5)

Breaking down this formula:

1. The vlookup pulls the region for a given country based on our country->region table in columns F and G
2. The filter formula pulls the number of miles only from the range J2:L10
3. The first condition in the filter formula matches the region in J2:J10 with whatever the vlookup finds
4. The second condition in the filter formula matches the class in column K2:K10 with the class the user enters in cell B5

Once you have the unique combination of the “to” region and the class, the filter formula return the number of miles in L2:L10.

We could’ve done an INDEX/MATCH to find the miles, but the FILTER formula was the first one that came to mind. I’d say this is not a normal use of the FILTER formula because you usually use this when the data that is returned is an array. You then would apply a SUM or AVERAGE or some other aggregate function on the data that comes back in a format like this:

[20000, 60000, 87000]

Regardless, we got our LifeMiles needed for a direct flight!

Calculating LifeMiles for the mixed-cabin hack What we really care about is how many miles we can save if we do the mixed-cabin hack discussed above. The functionality is similar as the direct flight portion. The main difference is that we need to get the number of miles flown between two regions:

Finding distance (miles) between two cities Originally I tried finding some Google Sheets add-on for geo-coding to automatically calculate the number of miles (flying) if you enter in two cities:

Unfortunately, all these add-ons utilize the Google Maps API which means you’ll only get the number of driving miles between two cities. Not really useful when you’re trying to find the distance between New York and Paris.

I even considered using something like the SkyScanner API and creating a Google Apps Script to pull in the miles I need. Figured it was too much work for a simple tool to calculate LifeMiles savings, so you have to Google the distances between the two cities you’re interested in.

Surprisingly, Google Maps gives you the option to select an airplane as the mode of travel, but it doesn’t tell you the number of miles between the two cities. Ended up using this random Air Miles Calculator site to get the number of flying miles between two cities.

Formula for calculating mixed-cabin miles With the inputs from the user, we now can create this formula to calculate the LifeMiles cost for a mixed-cabin multi-city flight:

=B12/(B12+B16)*filter(L2:L10,J2:J10=C10,K2:K10=B11)+B16/(B12+B16)*filter(L11:L37,I11:I37=C10,J11:J37=C14,K11:K37=B15)

The formula looks super long but when you break it down, it does something similar to the formula for calculating a direct flight:

1. The first filter formula is pretty much the same as the direct flight formula. Only difference is that the first condition J2:J10=C10 references cell C10 (Europe 2 in this case). Decided to just do the vlookup in cells C10 and C14 for simplicity.
2. Before the first filter formula, the B12/(B12+B16) handles the proportion of the flights that happen in business class. We take this percentage and multiply it by the first filter formula to get the proportion of flown miles that happen in business.
3. The second filter function has conditions that match the “from” region in column I and the “to” region in column J to where the second flight happens. The user only has the option to select another city in Europe since it’s unlikely you’ll do another transatlantic flight for your second leg (I guess it might be possible if you really want to lower the cost of the trip, but Avianca probably doesn’t allow this in their multi-city search).
4. The B16/(B12+B16) does the same thing as step 2 for the second filter function: it finds the proportion of miles flown in the second leg in the lower class (in this case economy).

Label shows European cities you are from from and to A small user experience feature I added to help the user know which cities they are flying from and to is in cell A16:

This cell simply changes text based on what cities the user selects. This just helps the user know which cities to look up online to find the number of miles between the two cities

Final notes on this calculator The big drawback to this tool is that you can’t enter in cities where you want to travel and the number of miles between two cities doesn’t automatically calculate. It also doesn’t account for other regions in the United States since I (selfishly) only care about the United States 1 region.

From a modeling perspective, I think it highlights a few best practices for analysts:

1. Structuring your data (columns F to L) is the first thing you should do before writing any formulas
2. Inputs should be in yellow and use data validation to prevent human error
3. Helper cells (or comments) can inform the user how to use the tool (column C)
4. The filter function can be used to find a specific value when you don’t want to use multiple vlookups or index/matchs

There are probably a bunch of other thing we could do to improve this tool like showing a summary table that shows how many LifeMiles you are saving. For small tools like this that are meant to help you do one thing and one thing well, making the tool “good enough” is all that matters.

Using crypto to buy flights? Holding (excuse me, hodling) award miles is a lot like holding crypto. I suppose the big difference is that you eventually do spend your award miles on flights once you find a sweet spot redemption.

But what if miles worked more like crypto? What if airlines gave you a way to stake your miles like you can in many defi platforms? Let’s say you know you won’t be traveling for the next 1-2 years, so you decide to “lock up” your miles in a smart contract that earns interest every week or month. For the airline, they might be able to convert your miles to cash and add this to their balance sheet. Or maybe they can act like banks and lend out the miles at a higher rate to other travelers.

Transferring your rewards from any of the big points ecosystems to travel partners feels a lot like converting your ETH into other ERC20 tokens or bridging them over to other layer 2 protocols like Polygon. I think there’s a startup idea in here somewhere on being able to convert your award miles into crypto and vice versa. If that happens, there will be a lot more people flying business and first class, so maybe the airlines are averse to this idea.

Or perhaps a new airline emerges that only accepts crypto as a form of payment like how Gary Vaynerhuck is opening an NFT restaurant where “members” can only access it if they own the NFT:

Whatever the case may be, travel hackers will be ready to play the game.

Other Podcasts & Blog Posts In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• The Tim Ferriss Show #538: How I Built The Tim Ferriss Show to 700+ Million Downloads

The post Dear Analyst #81: How to hack award flights on Avianca Airlines with Google Sheets appeared first on .

View Details

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 to throw the marble again in the spirit of fairness. The old man let’s Gi-Hun win the game with marbles feeling consoled by the fact they were friends (gganbu). In the race to win each game, players are still confronted by these classic norms of what’s right and wrong, fairness, and believing in humanity.

I spoke a bit about societal norms in episode 74 where Sam Harris interviews Michele Gelfand about “tight” and “loose” cultures. “Tight” cultures have strong norms where a community of people feel compelled to abide by the norms. In a “loose” culture (like in the United States), individualism is prioritized over the community.

While most games we play in real life are not life and death, I think there are similar rules and norms we abide by to “win” or “get ahead.” In these real-life games that follow, I think you’ll notice some common patterns:

• You never win
• You come into the game with a pre-existing advantage or disadvantage
• Those who break the rules may get rewarded handsomely (and spur innovation)

On to the games!

1. Data analysis and cleaning up data game Had to find a way to tie Squid Game back to data analysis and Excel, so here you go. When you think about common Excel or data analysis tasks, your goal is usually to find a number, show trends, or build a tool for others to use. In order to do this, you have a variety of techniques you can use to clean and manipulate the data before it’s presented. These are what I consider the “rules” of the data analysis game. These rules typically boil down to a few functions for cleaning up text and math formulas.

There are some norms, however, around how you do these tasks in a way that don’t piss off your co-workers. I’m sure you’ve come across files that look like this:

Source: Sportscidata Or this:

Source: lgatto.github.io You probably won’t get too far as an analyst if your Excel files look like this. There are some norms around how you structure and format your files like using a blue font for all hard-coded values or using one worksheet to store all your inputs.

Every Excel file start the same with blank rows and columns. You can do whatever you want with this canvas, but in the working world, your output will pretty much look the same because of these rules and norms around data visualization and good user interface design. Every once in awhile, you come across rule-breakers like Tatsuo Horiuchi who uses Excel to create these amazing works of art.

1. University game The university game has changed so much due to the pandemic. With many universities moving to remote or hybrid teaching models over the last year and a half, many students started questioning if their tuition was worth it. Additionally, half of students find that online education is about the same or better than attending classes in person:

Source: Research.com Pre-pandemic, the game was pretty simple. Do the minimum amount to get good grades and maintain a good GPA (at least in the U.S.) The tuition goes towards the “education” and the name on the diploma once you graduate. With classes moving online, the calculus is different. Is the tuition actually covering the “skills” you can gain in the classroom? Why can’t I gain the same skills from taking any other MOOC?

What the tuition and housing costs really cover are the friends you make during the process. When you’re in the middle of the semester cramming for a mid-term, you may not realize the whole point of this game is to walk away with a community or network that you carry into your professional life.

1. Negotiating job offer game Negotiating a job offer is like any other transaction where you are trying to extract as much value as possible. There’s an initial offer, some back and forth, and eventually both sides agree on a “price.” One of my favorite posts on how to negotiate a job offer is from Haseeb Qureshi (I wrote about this in episode 50 as well). The rules for this game imply that once terms are agreed upon, both sides should be happy with the result (otherwise no deal would’ve been made).

Source: 101Productivity The main rule to follow: always negotiate (aka ask for more money, equity, or both). While there are some norms around negotiating a job offer, there are few rules when it comes to how you can increase your value during the negotiation. For instance, having a bunch of exploding offers where the other companies are offering you higher than what Company X is offering is a sure-fire way to get Company X to magically increase their offer.

Another rule for this game is feeling a bit of buyer’s remorse after accepting an offer that you negotiated heavily. Did I ask for enough? Is my value to this company worth more? This becomes especially clear when you hear about the salaries of your colleagues. Guess what changed this rule of the game? Websites like Glassdoor where salary and comp are submitted anonymously. This gives you a better sense of what you should offer or counteroffer during the job negotiation, but the question still remains: what is your true value? It’s a game you will never win, but rather a story you tell yourself over and over agin that you made the right decision.

1. Health and fitness game When I was at university, an acquaintance of mine saw a picture of an obese woman and said something along the lines of: “I don’t understand how people get so fat. You just eat less food and work out and that’ll solve your problem.” At first, I agreed with her simplistic advice. From a pure biological perspective, eating less and working out more should help you lose weight. Unfortunately, this strategy doesn’t consider people with disorders, physical disabilities that prevent someone from exercising, and other reasons for not pursuing this stringent lifestyle.

Source: Giphy Fast forward several years, I have fully bought into the workout culture. I’ve even tried hitting the “1,000 club” (combined weight on squat + deadlift + benchpress = 1,000 pounds). There are all these rules about how you can lift heavier or jump higher, but the end result is still the same. Disappointment with what you’ve gained and always longing for more.

If you go back to the basics, being healthy does boil down to these two simple rules of eating less and being more active. It’s so frighteningly simple that exercise coaches and gurus need to complicate the rules to sell their next fad diet or class.

The only way I’ve been able to game the system is setting up mental tricks to push me to exercise when my body is telling me to go back to sleep. I still don’t understand how someone gets up at 5AM to go running, but if you put your shoes, gym clothes, and a full water bottle by the door, the activation energy to get yourself out the door is lowered (see episode 78 on the activation energy mental model).

1. Credit card points game One of my favorite games to play because there are clear rules (usually) for how to gain, maximize, and spend your reward points. Since the U.S. uses credit scores as a way to determine your ability to meet financial obligations and banks are too big to fail, the U.S. is one of few countries I believe that have these elaborate credit card points systems.

Sure there are ways to game the system. But if you just follow the rules for spending a certain amount to receive a signup bonus or remembering to use that grocery store credit by the end of the month, you can reap most of these rewards.

There are countless websites and communities like the awardtravel and churning subreddits to maximize the benefits of your points. How do banks justify giving out these huge sign up bonuses and rewards worth thousands of dollars? For one, I think they hope that you forget about the amount you were supposed to spend by a certain date (thereby forfeiting the signup bonus). Since I like tracking everything, I created this template to stay on top of due dates and spend thresholds.

When you go deeper into the rules of this game, you have to process the cognitive dissonance that comes with who really pays for all these points. This Planet Money TikTok video summarizes it quite well:

@planetmoney Credit cards are a tax on the poor. #creditcard #tax #economics #tiktokpartner #learnontiktok

♬ original sound – planetmoney

If you care to go deeper into the economics of the credit card points game, this reddit post does a fantastic job of laying out all the key players.

Bringing this back to the premise of Squid Game, one could argue that you come into the credit card game with an advantage or disadvantage, thereby ruining the “purity” of the game. Sadly, credit card reward points can be reduced down to another example of poor people paying for the rich people.

1. Work game Not having worked in large corporations (except one), I’ve been lucky to having avoided office politics. Playing the “corporate game” usually entails politics to be on the “right track” for a promotion or raise. Playing office politics may also be the path to enjoying working with your colleagues.

Source: XKCD The work game I’m thinking of is the daily value you bring to your company. In theory, some company is paying you a certain salary to provide more than the skills you’ve listed on your resume. You add to the company culture, you gain new skills and perspectives as you work, and year after year your contributions may 2X while your salary only 1.5Xs.

So if you were to play this game by the rules, wouldn’t you do just enough to not got fired? Perhaps many people already have this mindset and are doing just enough to scrap by. Not you, though. You are looking for your “calling” or your “life’s work.” Your occupation is more than just a job to you. So you put in the extra hours and 1:1s to have this job live up to the the story you’ve told yourself.

And that, is the work game.

1. Raising kids game This game might be a result of being in an age group where many of my friends are starting to have kids. It may also be a result of the number of apps you can use to track every movement of your newborn. It feels like raising kids is another optimization game where there are rules around when to feed, burp, diaper change, etc. The goal, of course, is to raise a healthy child.

A baby log like this may look familiar to some of you:

Source: BabyCenter Tracking all these activities and metrics is supposed to benefit the parent and the baby. You can find patterns for when and where your baby likes to feed, sleep, and a variety of other bodily activities.

This is just me playing armchair parent, but does following a specific protocol for your baby at 1 month, 3 months, 6 months, etc. supposed to yield some outcome? Or is this mostly for the benefit of the parents? Again, I could easily be the one tracking every single activity down to the second once I have kids of my own, but this seems like a lot of work (as it should be) to raise a kid the “right” way.

I think the result of this game can lead to helicopter parenting. When there are no set rules for how much a parent should “hover” over their kid, is the default to be more conservative and hover more often than not? Let’s assume you follow all the rules and the kid turns out not as you expected, who do we blame? I’m pretty sure the answer to this game is “wait until you have your own.” As of now, I’m still not sure how to play this game.

1. Productivity software game Now that we’ve completely gone off the deep end, it’s time to bring this back to something in the work realm. Productivity software used to be reserved for the CIO or CTO who got wined and dined by vendors selling multi-million dollar deployments. Once the C-suite buys the software, then the ordinary plebs in the organization have to deal with using some clunky piece of shit.

Not anymore with the consumerization of B2B productivity software! Now anyone can whip out their credit card and start a trial of productivity software and decide if their team should adopt the software. In the biz this is known as product-led growth. Now that the rules for how software is bought has changed, the homepages of this software has changed as well. The design is meant to feel more “human” (whatever that means) and to make you feel like you are a “superhero” and saving hours and hours of your time every day.

Remember when homepages used to look like this?

Source: Web Design Museum Oh the humanity! What normal operations manager would want to sign up for a tool that had a homepage that looked like this? Today, productivity software homepages need to look more welcoming to the average employee tired of using Excel for everything on their job. The homepage needs to make using their tool look fun, like playing a game on your phone. The problem is once one company does it, all the rest follow, and the uniqueness of the design is lost.

Look how fun tools like Lattice look:

Or Airtable:

Or Figma:

I have a horse in this race since I work for a productivity software tool. The brand and user experience is obviously important to how people perceive the product. But at the end of the day, the product experience will trump any warm and fuzzy feelings the website conveys.

All productivity software have similar beginnings. A few people who saw the shortcomings of using Microsoft, Oracle, or whatever shitty behemoth wanted to build something better. The software is built from the founding employees’ own work experience and perspectives. How different can each piece of software really be from one another?

The answer is not a lot. Unfortunately, building features is no longer a competitive advantage for these tools. Throw enough engineers, designers, and money at the feature and it will get built. There is one place where productivity software companies can break from the rules of the game: community. The human relationships built with users and between users simply cannot be another goal for the upcoming sprint. When productivity software companies can break through the rules with a thriving community of users, the real innovation begins.

Other Podcasts & Blog Posts In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• SyntaxFM #382: Advice for New Devs

The post Dear Analyst #80: Applying the rules of Squid Game to real-life games like negotiating a job offer appeared first on .

View Details

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:

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.

This method is obviously not ideal since it requires so many key presses to get to the right menu. The good news is that if you happen to need to use the ribbon, F6 opens it up and if the button in the tab is one of the “early” buttons near the left of the ribbon, maybe hitting the TAB key a few times isn’t too onerous. It’s still pretty clunky because when you press F6 the green selection might land on the Quick Access Toolbar or in the active cell form box first. This means you have to hit F6 a few times just to get to the right tab in the ribbon. Again, not ideal.

Method 2: Access toolbar with CTRL+F2 and go through Format menu One of the biggest confusions I have about Excel in general is the need for the ribbon plus the toolbar. There are many duplicates between the two which just means more confusion for the users. Nonetheless, you can access the toolbar by pressing CTRL+F2 (if you don’t have function keys turned on, you’ll have to press CTRL+FN+F2). When you apply this shortcut, you’ll notice a slight gray highlight over the main apple icon in the top-left of the toolbar:

This shortcut is the same thing as if you hovered your cursor over the Apple icon. From here, you can either start typing “Fo” to get to the Format menu or just start using the arrow keys to get to the Format menu. I prefer to use the arrow keys and SPACE to access the “AutoFit Selection” option in the Format menu. The full sequence of shortcuts looks like this:

1. CTRL+F2 to highlight Apple icon
2. RIGHT ARROW 6 times to get the Format menu highlighted
3. SPACE or DOWN ARROW 4 times to get the Column menu highlighted within the Format menu
4. SPACE or RIGHT ARROW once to get into the Column menu
5. DOWN ARROW once to get to the AutoFit Selection option
6. ENTER to apply the AutoFit Selection operation

This is what this all looks like in Excel:

Still quite a few keystrokes to get to the AutoFit operation, but much better than method 1. Now you know you can at least get to any menu in the toolbar with CTRL+F2 and access the menu with a combination of the arrow keys and SPACE.

Method 3: Use App Shortcut in Mac OS keyboard settings This method is pretty good but the reason I don’t think it’s the best method is because it requires you to use Mac OS settings versus Excel settings. On Mac OS, click on System Preferences->Keyboard->Shortcuts tab, and you’ll get a menu that looks like this:

You can set application-specific shortcuts to any application on your mac. After you click on the App Shortcuts option on the left side (last option), click on the plus sign to add a new app shortcut:

Scroll through all the applications until you get to Microsoft Excel. The tricky thing about the “Menu Title” field is you have to type out the operation in Excel word-for-word and it’s case-sensitive. In this box, you have to type out “AutoFit Selection.” Unfortunately Mac isn’t smart enough to get all the potential menu options to autofill here, so just watch your spelling. After that, you just need to type in the shortcut you want to use for this option. I would use something with the letter “A” in it so that you remember it’s AutoFit. Something like SHIFT+COMMAND+A could work:

Back in Excel, you now have the super simple COMMAND+SHIFT+A shortcut to expand the column width!

Method 4: Using Mac Excel’s Customize Keyboard menu (recommended) This method and method 3 are pretty much the same thing. You’ll still use the COMMAND+SHIFT+A shortcut to AutoFit columns. The main difference is that you use Mac Excel’s native settings for defining keyboard shortcuts instead of your general Mac OS settings. I don’t like messing with my Mac OS settings too much since, well, it applies to everything you do on your Mac. If you have a bunch of keyboard shortcuts in the App Shortcuts settings for your Mac OS, it may become difficult to find the one you want to delete or edit. With Mac’s native keyboard shortcut settings, this hopefully won’t be an issue.

The Customize Keyboard menu is one of the under-utilized menus in Mac Excel. It allows you to seta keyboard shortcut to any operation in Excel. Forget not being able to use the ribbon, just set your own shortcuts here. First step is to click on Tools in the toolbar (not the ribbon) an then Customize Keyboard to get this menu:

Remember how the Format button is on the Home tab? After you click on the Home tab selection, just start searching for “AutoFit” in the Commands section and you’ll see a few options pop up:

There is an inconsistency between the name of the operation “AutoFit Selection” and what is actually in the Home tab->Format menu (“AutoFit Column Width”). In any event, “AutoFit Selection” is the right option, and you just need to type in the new shortcut where it says “Press new keyboard shortcut” and then click “Add.” Again, I like SHIFT+COMMAND+A for my AutoFit column width shortcut:

Now you can use COMMAND+SHIFT+A (just like method 3) to AutoFit your columns. The nice thing here is that your custom keyboard shortcuts are stored in your Excel settings versus your Mac OS settings.

Other Podcasts & Blog Posts In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• This Week In Startups #1268: Robinhood earnings breakdown, Amazon launching department stores + Mark Suster deep dives VC
• The Changelog #455: Building software for yourself

The post Dear Analyst #79: How to finally AutoFit column widths in Excel for the Mac (and PC) appeared first on .

View Details

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 his fund’s full-time performance coach, Wendy:

Somehow the idea that all the guys…I asked Mafee. And the moment he said that everyone else agreed that it was the wrong move, I had to stick it out. Had to prove that I was the difference maker. – Axelrod

Knowing that others thought he was making the wrong trade probably led Axelrod further down the path of confirmation bias. Not only did his belief in the trade go higher, but his pride got wrapped up in the trade too.

From a trading perspective, this could also be an example of the hot hand fallacy where a person’s previous positive outcomes leads to them believing the next outcome will be positive as well.

Shooter’s gotta shoot. Unwavering belief in our own capabilities. It’s essential to a point. Keeps us functioning at a high level. – Wendy

Key takeaways Spend more time trying to find information you don’t see, or isn’t readily available. I think survivorship bias and confirmation bias go hand-in-hand. When I’m researching some new crypto or defi platform to invest in, I’ve started conditioning myself to do Google searches like “is X legit” or “negative reviews about X” to get the full story. A normal Google search for “X” will generally yield the positive news you’re looking for. To bring this back to being a good analyst, what questions are people not asking? What other internal or external input are we overlooking?

Source: Product Placement Blog 2) First principles and the Chicken Man Would you rather be a chef or a cook? A cook is able to follow a recipe word for word and deliver a consistent meal as long as they have the right ingredients. What if one of the ingredients is missing? This is where the chef shines. To a chef, the raw ingredients are the most important part of the process rather than the recipe. The ingredients are the first principles for the chef and they can create their own recipes and meals no matter what ingredients they have in front of them.

Billions shows some of the darker sides of the hedge fund industry which leads to the drama on the show. Axelrod and his traders frequently get involved with insider trading in order to make profit on their trades. Let’s be clear, insider trading is illegal and wrong. But in the due diligence process for snuffing out a good trade, Billions teaches us about first principles thinking and going beyond conventional wisdom.

“Dollar Bill” Stern is a trader who frequently presents insider trading opportunities to Axelrod. One such opportunity happened to be going long on the Arkansas Chicken Index (no such index actually exists). This is “pitch,” as it were, from Dollar Bill to Axelrod and Wags, Axelrod’s right-hand man:

Dollar Bill: There’s plenty of chickens. And the early chicken action is counting on that. But Chicken Man will say that there’s very few chickens. The price will skyrocket.

Wags: Why will he say that?

Dollar Bill: Because he’s supposed to drag his ass out to multiple farms and productions house to get an accurate count. But the guy’s Otis of Mayberry. So he’ll go to one or two to make an appearance, but that’s it. He’s not counting shit. The guy sits there and calls a few of the major poultry producers and just asks them for their numbers. It’s like a teacher letting a kid grade his own tests.

Axe: And they say the numbers are low because it keeps the prices high.

Wags: If the Chicken Man is wrong, why does he get to stay the Chicken Man?

Axe: Because he’s wrong in the way the producers want him to be. They installed him. And they keep him in place.

In the process of doing research or due diligence on a company for the purposes of investing, first principles thinking comes out. In the case of the Arkansas Chicken Index, an analyst could treat the price of chickens as sacrosanct news and assume it reflects an accurate count of chickens. Why? Because we know (or we think) that other indices reflect the true price of something (e.g. S&P, CPI). Via analogy, the chicken index should behave the same way, until you break down the index into its component parts and build your own conclusions on–forgive me–counting chickens.

Source: UPROXX The Georgia Dock and the real chicken man Turns out there is a real chicken man who used to publish a newsletter called Georgia’s Poultry Marketing News. Arty Schronce would also just call some major chicken producers for their numbers. Arty’s numbers would then go into the Georgia Dock, the main wholesale chicken price index created by the Georgia Department of Agriculture.

Source: Gainesville Times Wall Street analysts did some first principles thinking and decided to investigate Arty’s chicken numbers. A Washington Post article led to the elimination of the Georgia chicken index as well as some chicken producers getting sued for price manipulation.

Did chicken prices go back to normal after the index was abolished? Surprisingly no. One theory is that the buyers of the chickens–big supermarkets–disregarded the chicken index when they found out the index wasn’t reliable. Turns out there was actual price-fixing and the chicken buyers’ lawsuit against the chicken producers held water.

Arty, like the chicken man in Billions, was just a guy caught in the middle of supply and demand.

Key takeaways Not going with your intuition may be a good thing. Our intuition is influenced by our experiences, history, and other people’s opinions. First principles thinking brings you back to the truths and reality in front of you. When other opinions guide your way of thinking about a problem, you are less likely to come up with a creative solution.

3) Activation energy and catalysts to short the Nigerian Naira Getting started on a big project is the hardest part. Writing the first word in this post was difficult right up until I removed distractions and had a cup of coffee to wake me up. Activation energy is the initial push to get a chemical reaction started. You can equate this with the “tipping point,” as fans of Malcolm Gladwell may be familiar with.

Setting the scene: Axelrod meets with a talented trader named Everett who works at a different hedge fund. Axelrod is trying to poach Everett to his hedge fund, and Everett tells Axelrod about some news he heard about the Nigerian currency:

The have to devalue. Could be a month for now. And with the right pressure, could be tomorrow, if somebody takes a massive short position against it. – Everett

Everett’s research (and inside information) indicates that the Nigerian currency will be devalued, but it’s a question of when. With a little activation energy in the form of a short position on the currency, Axelrod can control the series of events that leads to the official proclamation by the Nigerian government that the currency is getting devalued.

The entire show is a great example of the activation energy mental model at play. Small actions and nudges here and there lead to the big “reaction” the originator of the action is looking for. An anonymous tip, a rumor, or an unexpected appearance can lead to larger events down the road.

For this play on the Naira to work, Axelrod needs additional capital to take a large enough short position to cause the currency to spiral. He decides to disclose this information to some of his competitor hedge fund enemies in the hopes that they will join him in shorting the currency:

Axelrod: Nigeria is going to devalue its currency.

Malverne: How could you know that?

Axelrod: Because we are going to make it happen. If each of us take a monster position against the Naira, we dictate the timing. This deal only works if we’re all in it together. The threshold is five billion. I’m in it for two. That leaves one each for you three. So, which is the more powerful driver? Boning me, or your own self-interest?

Source: Billions Frenemies and incentives Without the help of the other hedge funds, Axelrod doesn’t have the capital and activation energy to catalyze the devaluation of the Naira. Further on in this episode, Axelrod finds out that Birch, one of the hedge funds who originally agreed to participate in the short sale backed out and is backstabbing Axelrod. To make up for this roadblock and officially cause the markets to change, Axelrod get his esteemed friend Lawrence Boyd to appear on public TV saying that he believes the currency should be devalued. It’s like having Janet Yellen come out on CNBC and say Bitcoin is the future.

Another interesting mental model at play here is incentives. People are ultimately driven by incentives and rewards and Axelrod poses the ultimate question to his compadres: do you care about profits above all else? The true nature of the hedge fund managers comes out as they put aside their competition with Axelrod for this opportunity. Axelrod knew the incentives were right to get his competitors on his side for this play.

Outside of finance and politics, I’d love to know other real-life examples of how to “keep your enemies closer.” My hunch is that most people don’t have frenemies, or maybe I just don’t have enough at stake where enemies will line themselves up against me. In this case where millions are at stake, Axelrod’s enemies stand to lose more if they don’t collaborate with Axelrod.

Key takeaways Figure out what catalyst is needed to move something past its breaking or tipping point. If it’s something you’re trying to change in your life like going to the gym more often in the morning, having your gym bag packed and gym shoes out might be the “activation energy” you need to get out the door. In the case of price-fixing a currency, the first step is don’t do that. The second step is finding others to give you leverage so that the scale tips in your favor.

Conclusion Some of these mental models are a bit of a stretch in terms of the scenes from the show. The plots and story lines in the show are obviously written to create drama and would not happen in real life. A hedge fund would not agree to short the Naira just to make a profit. I’d hope there’s much more calculus that goes into a decision like this in reality.

Nonetheless, the decisions people make on the show reflect our existing cognitive biases and ways of thinking. The one thing Billions has taught me is to constantly learn about new ideas, markets, and people. Just from this post alone, I learned about a real life chicken price index and forex trading.

Other Podcasts & Blog Posts In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• How I Built This: Live from the HIBT Summit: Adam Grant
• All The Smoke ep. #100: Carmelo Anthony

The post Dear Analyst #78: 3 mental models from the show Billions appeared first on .

View Details

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:

The 80/20 rule for meaningful accomplishment:

Spend 20 percent of your time exploring broadly and 80 percent of your time relentlessly digging in.

— Shane Parrish (@ShaneAParrish) October 4, 2020

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 show up at the top of your spreadsheet.

2) Recency bias Perhaps not a mental model from a traditional definition, but something to have thinkboi thoughts on nonetheless. Via wikipedia:

Recency bias is a cognitive bias that favors recent events over historic ones.

I’m singling out this cognitive bias because of its impact you see in business, politics, and hey, your personal thoughts. For all the basketball heads out there, who do you think are the top 5 NBA players of all time? That list probably includes the likes of Jordan, Kobe, and Lebron.

Source: The Ringer Asking this question to a 20-something will probably yield different results than a 50-something. Why? Because you’re not seeing some of the greats from the 70s and 80s play regularly and in social media (unless you’re watching ESPN Classic). This means your benchmark for a “great player” is coming from the most recent 5 NBA seasons, maybe 10?

If you had to pick the top center of all time, would it be Shaq or Kareem Abdul-Jabbar? You think about Shaq’s run with the Lakers, his thunderous dunks, and 300lb+ body and may feel inclined to pick him just with those data points. What do you remember about Kareem’s career? Skyhook probably comes to mind. But dominating like Shaq? Maybe not. But if you compare the two big men’s stats from all their career games:

Source: Land of Basketball Kareem led in every major stat category.

Recency bias and investing Outside of sports, you hear (and probably experience) recency bias in the investing world. Investors tend to give more credence to short-term results and pick funds and stocks based on recent information they have about the asset.

I think Bitcoin is a great example of this to a certain degree. If Bitcoin goes below $40K these days, hodlers may think Bitcoin is cheap and it’s time to load up the bags. One year ago (September 2020) Bitcoin was $11,000. Is <$40K still considered cheap or undervalued? Bitcoin’s presence and portrayal in the media only adds fuel to the fire:

Crypto may be a bad example, but apply recency bias to stocks and you’ll hear the rallying cry from Fidelity, Vanguard, and robo-advisors like Wealthfront. Diversify your portfolio, index funds, asset allocation, and their ilk. If you pick stocks individually, you’ll always underperform the S&P in the long-term, they tell us. I’m not saying the long-term strategy we’re being fed by brokerages and 401k plans is wrong, but it’s not 100% right either for every investor. Will save my rant on this for another time.

Recency bias and data This may creep into recency bias’ sister: confirmation bias (finding facts to support your opinion). If you’re doing an analysis and remember that one of the key takeaways from last quarter’s BvA report was that supplier X drove the higher than expected expense, you may look to supplier X again to find the explanation for this quarter’s overspend.

It’s easy to turn to the most recent explanation to account for why something happened, but the correct method is to go farther back and take history into account in your analysis. Maybe supplier Y had an unexpected expense that is 300% greater than their previous expense 6 quarters ago. You wouldn’t know that if you didn’t look at the trends from more than a year ago.

Speaking broadly, the amount of data we have available today versus 5 or 10 years ago can lead to recency bias as well. If you have more granular and frequent data points getting streamed into your database, tempting to use these robust datasets to run your analysis. Just because your data from 10 years ago doesn’t have as many dimensions or granularity doesn’t mean it shouldn’t be included in your analysis for an important decision. Data from many quarters or years ago can still be accurate without being stale.

3) Hanlon’s Razor I’ve written about this aphorism before (see episodes #47 and #38) in relation to Excel errors. Via Wikipedia:

Never attribute to malice that which is adequately explained by stupidity. It is a philosophical razor that suggests a way of eliminating unlikely explanations for human behavior.

It’s giving someone the benefit of the doubt that they were not intentionally trying to hurt you. They just didn’t know any better.

Christian Pham was a 40-year old professional poker player ready to enter the World Series of Poker in 2015. He had been playing no-limit Texas Hold ’em in St. Paul, Minnesota, and this tournament was a chance for him to make a name for himself. He signs up for the tournament, and eagerly awaits his seat at the table.

It’s game day, and he sits down at the table. He’s told the buy-in is $1,500. This is very strange, he thinks, since the buy-in for the Main Event (no-limit Texas Hold ’em) is supposed to be $10,000. Worry not, this must just be some snafu with how they collect the buy-in.

The dealer starts doling out cards and this is where things get really weird. He gets dealt 5 cards. His nerves really get strained now because you’re only supposed to be dealt 2 cards in Texas Hold ’em.

Christian signed up for the wrong event.

He accidentally signed up for a game at the World Series of Poker called no-limit deuce-to-seven draw lowball. He’s never played this type of poker in St. Paul, and he’s staring in awe at his 5 cards on the table.

Source: PokerNews Christian’s opponents at his table think Christian is bluffing. They think he’s pulling a fast one on them and pretending to not know how to play Kansas City lowball. If I saw a player at the table start to win hands at a game he claims to not know, I’d be a bit skeptical as well.

Christian wasn’t bluffing, and he really had no idea what he was doing. He didn’t know what strategy to use and was trying to piece together the game by talking to the dealer and some of the other players. Inexperienced poker players can sometimes be the most difficult to play against because they are effectively gambling while the pro players are employing every technique they have been taught to win hands.

The players at Christian’s table soon realized Christian really had never played the game. Being the rational (and human) players as they are, the other players gave Christian some tips on how to play the game to maybe remove some of the unpredictability from Christian’s style of play. Regardless, Christian ended up beating out 219 players and walked away with $80,000 from playing a game he didn’t sign up for.

Hanlon’s Razor working at work I think that this mental model is a great way for improving your overall attitude at work and in your personal life, but isn’t that just having a positive attitude? Saying you ascribe to Hanlon’s Razor is a nice party trick and might make you feel like you have control over the negative thoughts that dance their way into your psyche. At the end of the day, you choose who and where to direct your effort and thoughts, as wasteful as it may be.

Let’s say you’re working on a group project and your group mate or colleague agrees to do the final formatting on the presentation you’ve been working for weeks on. Just a simple verbal agreement and trust between two teammates is all you need to know that the formatting will be done and you’ll look good in front of your boss come presentation day.

Your teammate sends you the presentation back and it’s not formatted at all and you don’t have time to fix it before the presentation. Is your teammate trying to sabotage you? Did they juts lie straight to your face? Your teammate is human, after all, and no amount of stewing or vitriol thrown at your teammate will make the situation better or complete the formatting task.

Why waste time thinking people are actively plotting against your downfall? Most people are too enthralled with their own lives to have the capacity to plan for these supposed roadblocks in your life. This is starting to get meta, but the question to ask yourself is whether the feelings and emotions are truly justified, or is there another rational explanation for what transpired?

Other Podcasts & Blog Posts In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• The Comfort Crisis: Embrace Discomfort To Reclaim Your Wild, Happy, Healthy Self by Michael Easter

The post Dear Analyst #77: The top 3 mental models I never think about and live by (I think) appeared first on .

View Details

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:

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:

Thinking about productivity and its tools
Your email is what others think you should work on.
Your Todo list is what you think you should work on.
Your calendar is (usually) what you actually* work on.

How much do they overlap in your world? pic.twitter.com/sLyKGagsKP

— Des Traynor (@destraynor) June 26, 2019

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 schedule a reminder event to call customer service. Eventually, the proverbial frog couldn’t stand the heat anymore, so I jumped out as things boiled over.

Very proud of my photo editing skills on this one 🙂 Save the clicks (or taps) The real problem (as with many consumer apps) is the number of clicks and taps you’re required to make on your phone when scheduling an event on Google Calendar. Let’s assume you are in the Gmail app on Android and need to create a Google Calendar event after you’ve just seen an email requiring you to schedule something. Some other requirements:

• You need to invite someone to the event
• The event needs to be at a specific time
• The event needs a description

Let’s compare the clicks/taps required to make this event using the Google Calendar app. Typing characters for the event are not counted.

Using the Google Calendar app Just to get to this screen requires 2 clicks (one to get out of Gmail and another to get to the Google Calendar app):

Once you’re in the app, you need to click on the big plus sign in the bottom-right to get to the create event screen (1 click):

By default, the cursor shows up in the “Add title” field so you don’t actually need to click into the field to start typing the event title. Saving a tap here. But if I want to change the date of the event, I need to tap on the start date (1 click):

Now depending on how far away in the future your event is, you may need to click a few more times to access future months. Let’s just say the event is later in September so you tap on that date to set it and then tap “OK” (2 clicks). Next is the time. You tap on the event time to bring up this time picker (1 click):

I’ve gotten use to setting times on Android but it’s not the most intuitive experience. To set the time to 9:30AM, it would require hitting the “9,” then hitting “30,” and then the “AM” modifier at the top, then “OK” (4 clicks). To invite others and add an event description:

• Add attendee (3 clicks): Click on the “Add people” field, type in the person’s email address and click on the autofilled name, then click “Done”
• Add event description (1 click): Click on the event description field

Of course, to save the event after you’ve filled out all the fields, you click on the big “Save” button in the top-right (1 click).

In total, it requires a minimum of 15 clicks to create a Google Calendar event on the Android app (not to mention the context switching).

Using Gmail to create a Google Calendar event Just to be clear, this solution applies only to sending emails from Gmail on a mobile device. On desktop, there’s already a solution for creating events from Gmail. I’ve never used this feature on desktop since Google automatically puts the subject of the email as the event title. Sometimes the subject line is super long and it wouldn’t make sense for it to be the event title anyway (I like to keep event titles short and succinct).

Using this script, you create an email that looks like this to create a Google Calendar event:

The number of clicks to get a Google Calendar event scheduled using this method (again, ignoring typing characters):

• Create Gmail draft: 1 click
• Set the recipient of the email: 1 click
• Write the event title, date, and time in the subject line: 1 click
• Write attendees and event description: 1 click
• Save the event (send email): 1 click

This method requires 5 clicks.

One could argue that typing out the date and time is more cumbersome than tapping the date/time using the Android time/date picker interface. You also have to know the full email address of your attendees since the body of the email is freeform text (thus no autofilling of email addresses).

Nevertheless, I feel more comfortable typing out the date/time than using the awkward date/time picker. The best part of this solution is that I stay within the context of the Gmail app. Switching from Gmail to Google Calendar isn’t context switching from what you’ve seen in the media and on blogs.

This is just saving the time and mental overhead needed to load a completely different app with a different interface from what you were using before.

Creating the solution in Google Apps Script Creating a solution to save time by not having to open the Google Calendar app on your phone is pretty inconsequential in the grand scheme of things. We’re talking about 5 taps versus 15 taps to create a Google Calendar event. Doesn’t feel like that much time saved, but it’s so nice to stay within Gmail where the triggers for creating calendar events exist in the first place.

Since I use Gmail and Google Calendar for both work and personal life, I figured Google Apps Script would be the glue to hold everything together. I have posted about Google Apps Script I created in the past to help automate various workflows. The main reason I continue to use it is because it’s free. Unless you are running a script many times a day on a large amount of data, you’ll never hit the quotas before you need to pay a penny to Google (the cost of having all your data with Google is a story for another time).

At a high-level, these are the features I wanted in this Google Apps Script:

• Send myself emails with the details of a Google Calendar event
• Event title, date, and time would be in the subject line
• Other event data (attendees, event description, location, etc.) would be handled in the body of the email

The script requires you to do some setup in Gmail. Namely, creating a filter where emails to yourself automatically get labeled with something like “EventsFromEmail.” The filter looks for a specific email to yourself that includes the “+” character. For instance, if your email is “john@gmail.com”, the recipient email address might be “john+calendar@gmail.com.”

Once this filter is setup, the script does the following:

• Go through all the emails in the “EventsFromEmail” label
• Find the ones that are unread
• Create a Google Calendar event from the details in the unread emails
• Mark these unread emails as read after the event is created

Automating the script with triggers Going through all the emails in a specific label and creating events from the unread emails feels inefficient. The real solution I was hoping for was having the script run right after you send yourself an email. After doing a little research, turns out there are no Google Apps Script triggers that would kick kick off a script when an email lands in your inbox. There is a solution involving webhooks and Google Cloud’s Pub/Sub feature, but this would require you to dip your toes into setting up a Google Cloud account, billing, etc. which is annoying. Google Apps Script runs on Google Cloud anyway, but the nice thing about Apps Script is you don’t have to deal with all the overhead of setting up Google Cloud.

Since we can’t “watch” for emails in Gmail, we turn to the time-driven triggers. This means the script runs every 5 minutes or whatever interval you think is appropriate for your use case. Again, I don’t think this is the most elegant solution because:

• Requires the script to constantly check for new emails in your “EventsFromEmail” label
• Wastes compute power

I believe a time-driven trigger is how most standalone Google Apps Scripts are run. The no-code interface for setting up the cron job is really easy to understand:

Other Podcasts & Blog Posts No other podcasts this episode!

The post Dear Analyst #76: Productivity hack for creating a Google Calendar event by sending yourself an email appeared first on .

View Details

This post originally appeared on the KeyCuts blog.

When you need to capture some data in a structured way, you’ll open up an Excel file or Google Sheet and just start throwing data into the spreadsheet. Not much thinking; just copy and paste. As that dataset grows, the original structure you had set up for that spreadsheet may not be ideal. Specifically, the dataset is not ideal for putting into a PivotTable. Long-term, I’d argue that all your spreadsheets should be structured in a way that’s suitable for a PivotTable (which makes it ready for storing in a traditional database). This post explores how you can structure a dataset that looks like 99% of data out there into a structure you can analyze in a PivotTable. Link to the Google Sheet is here.

Video walkthrough of Google Sheet here.

Why this is important

Telling someone that their data should be structured is a platitude like “such is life” and “forgive and forget.” Let’s be more specific in how this statement can impact your work.

To be specific: 9 times out of 10, structure your data so that it can always be analyzed in a PivotTable.

Consider this scenario:

Your accounting team needs your group to start forecasting expense for next month’s budget

You start gathering the data and throw it into a spreadsheet

Every month new data gets added to the spreadsheet, and perhaps the CFO wants to get more granular analyses on the forecast

You start adding additional columns to the spreadsheet and perhaps summary tables in other sheets in the file

Other teams now need to see your data to understand how your team’s decisions will impact their decisions

This spreadsheet ends up being too hard to maintain, so there’s an internal project to put this data into a real database (some ERP solution)

One quarter of planning goes by, and another quarter for implementation

6 months later, the business has changed, the structure of the database needs to be adjusted, and the data engineer role still needs to be filled

This concocted scenario is quite extreme, but the key lesson is this:

Focusing on the schema and structure of your spreadsheet today takes time and requires you to think about how your data will be used and maintained in the future.

U.S. public food assistance dataset

I’ve started browsing Kaggle’s to find interesting datasets recently, and this one caught my attention since it looks at spending and household participation related to a public food assistance program called SNAP. As the creator of the dataset discusses, there are many issues with collecting government datasets. Data is spread out across different agencies, there are multiple formats, and data is sometimes aggregated. This makes consolidating the data a pain. These problems may sound familiar if you’re working at a large organization.

The “Raw” sheet in the Google Sheet simply shows the cost, households participating, and total people associated with the SNAP program for the 2019 fiscal year across four states (CA, IL, LA, NY):

In your organization, this could be sales data, headcount data, COGS, whatever. The key thing about this dataset is that you have all the numbers organized by month across the top. This table would be great for a simple time series analysis where you may want to see the cost per household for California over time. But what if you need to build out a more dynamic dashboard looking at various metrics for just a few months or a subset of states?

Pivoting this data

If you create a PivotTable with this data, you’ll run into this issue of having to select individual month names to put into the Values section of the PivotTable builder. We only have twelve months of data for FY19, imagine if this we had to this for ten years worth of data going back to FY09.

Some people asked me about what a “denormalized dataset” means in the context of Excel/Google Sheets I mentioned this term in the previous episode. We need to “denormalize” this data so that it’s easier to pivot off of. This means putting in data that may repeat itself in a certain column, but this helps with structuring the data properly for a PivotTable.

In Excel, there is hacky way of denormalizing your data, and it involves going through the antiquated PivotTable wizard (which I believe you can only access via old Excel keyboard shortcuts). I don’t think the PivotTable wizard is available in the ribbon in recent versions of Excel.

This video below shows you how to do it. It involves checking a radio button for “Multiple consolidation ranges” and then double-clicking in the grand total of the sum of Values in the PivotTable. It’s not pretty, but it works:

Unfortunately for Google Sheets users, that PivotTable wizard isn’t available. If you find a similar workaround let me know.

Moving time periods to rows in Google Sheets

Whenever you see time periods (in this case, months in 2018 and 2019) organized across the columns, think about how you can put those time periods into one column. This starts the process of denormalization. You want something that looks like this:

When you pivot off of the Period column in the PivotTable, you can then filter for and group your values by specific dates:

Moving metrics from rows to columns

In the original data set, there’s a Metric column which contains metrics we care about for each state (Cost, Households, and Persons). This structure will make a PivotTable very hard to organize and analyze because you will have to filter for a specific metric in order to get any meaningful statistics from your dataset. Additionally, this structure is mixing data types (e.g. Cost is in dollars and Households is a number).

Whenever you see metrics organized in this manner, think about moving each individual metric to its own column:

Now, each of these columns is a value you can drag and drop into the “Values” section of the PivotTable. This means you can get summary results or drill down into a specific state’s numbers:

Transposing the data

Setting up the data structure to look like the structure in the “Solution” sheet of the Google Sheet does take a little spreadsheet gymnastics. The easiest method I’ve found is to apply the TRANSPOSE function to the original dataset and then do some copying/pasting. Here’s what a TRANSPOSE looks like:

The nice thing about this function is that it puts all your time periods (months in this case) into its own column. Each metric also is organized in a top-to-down fashion. The problem is that each state’s data is still organized across the top. At this point, you’re doing a copy and paste to consolidate the 13 columns that result from the TRANSPOSE function into the 5 columns we ultimately care about: State, Period, Cost, Households, and Persons.

Setting things up for a database

You may be wondering what other benefits there are for having this data structure besides the ease of creating a PivotTable. If your data ever ends up in a regular database (e.g. SQL), this is the ideal data structure for that tool.

I’ve seen scenarios at different organizations where an Excel file or Google Sheet has hundreds of thousands of rows that represent critical business data cobbled together over time. There comes a point in time from an organizational perspective where that data needs to be put into a database for ease of querying. A data engineer will have to do some data manipulation or run an ETL process to convert the data into a suitable format for a database. Guess what? You can help your data engineer out by getting this structure correct from day one.

Data down good 😀, data right bad 😔

To summarize how your data should “grow” over time (big data ain’t going nowhere), your data should NOT grow right:

Instead, it should grow down:

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

Acquired Podcast Season 7 Episode #3: Epic Games

People I (Mostly) Admire Ep #2: Mayim Bialik

Subscribe at alchen.substack.com

View Details

This post originally appeared on the KeyCuts blog.

This episode is the audio from a presentation I gave a few weeks ago to members of Betaworks based in NYC. Betaworks is a startup accelerator, co-working space, and community of founders. No-code is a pretty hot topic right now, and in this presentation I talk about how spreadsheets is one of the first no-code “platforms” and how your spreadsheet skills can be extended to build real tools. The presentation is adapted from a talk I gave last year at Webflow’s No-Code Conference. I embedded the “slides” at the bottom of the post, and here is a link to the slides if you want to look on your own.

Summary of presentation

The skills you’ve learned in Excel/Google Sheets — include data structuring — translate to building workflows for any part of your business

Thinking beyond spreadsheets as a way to do data analysis or “number crunching”

Any tool that helps automate or solve some workflow at your company can be built with spreadsheets

Why learning spreadsheets can set you up well for learning “no-code” tools

Spreadsheet examples from presentation

During the presentation, I showed actual spreadsheets (Excel and Google Sheets) I’ve built in the past for freelance clients and friends. The main concept I’m trying to convey is that each of these spreadsheets look and feel more like an application rather than a model that forecasts out certain values. Each of these examples consists three core elements:

Database – A place to store information

User Input – Fields and forms for someone to fill out

Calculations/Display – Formulas (e.g. “business logic”) to make the spreadsheet output something for you (the administrator) or the user

My 2 cents: When you’re building an application in a spreadsheet, you’re extending the original purpose and audience Excel and Google Sheets was meant to serve: financial models for accountants. But this is what makes the spreadsheet so versatile. The fact that an analyst can string together formulas to make a spreadsheet look and feel like an application is what gives the spreadsheet power. This innovation also pushes Microsoft, Google, and other platforms to release new features that give analysts the ability to build tools, not just models.

I’ve written extensively about this subject in the past, so will leave my soliloquy at that. On to the examples

Bachelorette planning Google Sheet

The first example I discuss is this bachelorette party planning Google Sheet I built for a friend. This spreadsheet has been duplicated quite a few times by friends of friends, and all it does is help a to-be bride plan figure out which weekend works best to have a bachelorette party.

The key insight is that the database is everything from column B onwards and row 3 and below. All the availability for each person is captured in each of these cells and there’s some conditional formatting to give the bride a visual indicator to see when a weekend is available.

The user input is the ability for each friend who is shared the Google Sheet to edit the cells. “Yes,” “No,” and “Maybe” are the only inputs that matter for this Google Sheet. Finally, the calculations are in rows 31-33 which tallies up the user inputs for each weekend so the bride can see which weekend is the “most free” for her friends.

There are countless iPhone and Android apps you can download to do this exact same thing, but this spreadsheet just does one thing and one thing well: help brides figure out which weekend to plan a bachelorette party.

Splitting costs with friends

This splitting costs with friends blog post is by far the most popular post on my blog since I published it in 2014 (thanks Google search!). Every day I still get requests to give people edit access to the Google Sheet (please just make a copy of it instead of requesting edit access). Here’s the Google Sheet if you want to make a copy for yourself.

Similar to the previous example, the database is all the items, costs, and who participated in the cost from rows 2 and down. The user input are the cells themselves, but the most important part of the Google Sheet are the 1s and 0s from column C onward. Those 1s and 0s represent whether a friend or family member “participated” in the cost. This allows the spreadsheet to do some basic calculations to figure out who owes what.

Rows 26-28 are the calculations that the trip organizer can see at a glance to see who is owed or who owes money. Again, numerous apps and custom tools you can pay for or download to split costs with friends, and this Google Sheet mimics the features of those apps in a more bare bones way.

Patient intake system

This example shows when the spreadsheet is really extended beyond what it was intended to do. This was for one of my consulting clients who needed a new CRM system for managing new patients at their clinic.

The Excel file basically lets the operations manager at the clinic quickly “move” new patients from one spreadsheet to another using a VBA macro. To mimic the look and feel of an application, I drew these blue and green buttons using the shape feature in Excel and tied a macro to each button. The database consists of patient details, the user input is simply each row of data, and the calculations involve these macros that move data from one spreadsheet to another.

This gets into an important concept that an Excel file or Google Sheet are not that great for: workflows. Since everything is usually calculated in real-time in a spreadsheet, it can be difficult to do a if-this-then-that type of workflow without using a macro or script (see my last post on automating a tedious filling values down task).

“Slides” from Betaworks presentation

The rest of the presentation includes tool and tips for building applications with other no-code tools. Link to slides.

Original talk from Webflow’s No-Code Conference in 2019.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

No other podcasts for this episode given how long this episode is!

Subscribe: Apple Podcasts | Android | Google Podcasts | Stitcher | TuneIn | Spotify | RSS

Subscribe at alchen.substack.com

View Details

This post originally appeared on the KeyCuts blog.

SPACs (Special Purpose Acquisition Companies) or “blank check” companies have been in the news recently, so I used some real SPAC data for this episode. Your spreadsheet has empty cells in column A, and these empty cells should be filled with values. Your task is to fill values down up until you find another cell with a value, at which point you need to fill that value down. This episode walks through how to do this programmatically with a script in Google Apps Script (for Google Sheets) and VBA (for Excel). This is the Google Sheet associated with the episode. The Google App Script is here and VBA script is here. See a quick example of what the issue is in the gif below and how the script “fills in” the values for you.

See the video below if you want to jump straight to the tutorial:

Why is this data structure a problem?

You’ve inherited a spreadsheet and the data structure looks like this:

It’s a list of data but there are empty cells in column A. This is usually a category or dimension in your data set that needs to be “filled down” so that the data set is complete. In the Google Sheet, each row represents one person that is associated with a given SPAC, but the SPAC Ticker column is incomplete. You’ll usually get this type of data structure through the following:

Data was manually created by someone who didn’t fill down the values in column A since they thought it was a “category”

You are working a data set that originally came from a PivotTable but you only have the “values” from the PivotTable, not the PivotTable itself

This data structure is a problem because if you want to do any type of analysis on this data, it will be extremely difficult since you have missing values in column A. Sorting, filtering, and PivotTables are all out of the question if your data set looks like that screenshot.

Solving this with keyboard shortcuts

Totally doable for this Google Sheet. This is what you could do:

All I’m doing above is the following (on PC):

SHIFT+CONTROL+DOWN ARROW – Select all the empty cells from the current cell with a value up until the next cell with a value

SHIFT+UP ARROW – Reduce the selection by one row

CONTROL+D– Fill the value from the first cell in the selection down

CONTROL+DOWN ARROW – Skip to the next value that needs to be filled down

The obvious tradeoff here is time vs. human error. Every time I have to do this task on a spreadsheet, I think about whether it was worth filling the values down “manually” using keyboard shortcuts or using a VBA script (in Excel) to do this programatically. It really depends on the number of rows. For the example SPAC Google Sheet, doing this with keyboard shortcuts takes 10 seconds tops. If this spreadsheet was 1,000,000 rows, then we have a problem.

Don’t worry, I got you. Here’s the script you can use to do this programmatically.

Using Google Apps Script in Google Sheets

First off, here’s the script you can use for Google Sheets (gist here). Just 14 lines of code and you’re good to go:

function fillValuesDown() { var spreadsheet = SpreadsheetApp.getActive() var currentRange = spreadsheet.getRange("A2:A" + spreadsheet.getLastRow()) var newRange = [] var newFillValue currentRange.getValues().map(function(value) { if (value[0] !== '') { newFillValue = value[0] newRange.push([newFillValue]) } else { newRange.push([newFillValue]) } }) currentRange.setValues(newRange)}

Never used macros or Google Apps Script before? It’s super simply. First go to Tools then Script Editor:

You may be asked to authenticate your Google account so just hit Yes to all those screens. Copy/paste the script into the editor:

Go to File and Save in order to save the script into the Google Apps Script project. Go back to Google Sheets and go to Tools, Macros, and click Import to import the fillValuesDown function into Google Sheets. Now you can use this function as a macro in your Google Sheet:

You can close out the Google Apps Script editor and now click on Tools, Macros, and click on fillValuesDown to run the script on your dataset:

How does the script work?

The script utilizes the Spreadsheet service for Google Apps Script to access the data object for your Google Sheet (more on that below). The script is really only 12 lines long, and does the following in sequential order:

Sets the spreadsheet variable so that we can use the active worksheet you’re on

Sets the currentRange variable to start from A2 to the last row in the table

Two more variables are set: newRange to store the new range of values we want to put into column A, and newFillValue which is kind of like an intermediate variable used in the loop

The script goes through all values in currentRange (including the blank ones) and adds all the correct values to the newRange array

The currentRange is then set equal to newRange to get all the “correct” values into column A

On the backend, the currentRange array looks like this:

[['HZAC'], [], ['FST'], [], [] , []...]

The purpose of newRange is to create a new array that is a complete list of values:

[['HZAC'], ['HZAC'], ['FST'], ['FST'], ['FST'] , ['FST']...]

Recording macros vs. programming Google Sheets

When I first started learning macros, the first thing I did was record my keystrokes and break down what the backend “code” looked like. Here’s what recording a macro looks like:

When you open up the script editor, you’ll see this:

There’s a lot of activate() and getCurrentCell() functions being called. You can then deconstruct all these keystrokes to build a script that accomplishes the task. But here’s the key difference between recording keystrokes versus working with the data object:

You are programming keystrokes instead of the Google Sheets application.

Other advantages of programming the application instead of the keystrokes:

Utilizes less compute resources and runs faster

Easier to debug

Easier to adapt to more scenarios and use cases

In the keystroke world, you are literally telling Google Sheets to select cells, select ranges, and moving the cursor around which doesn’t seem like a big deal. When you are working with hundreds of thousands of rows, this could cause serious performance issues. Since Google Apps Script runs in the cloud, you may not see these performance deficiencies, but you’ll definitely see this in your Excel workbooks.

Speaking of Excel workbooks…

Using the VBA script for Excel

Sub fillValuesDown() Dim lastRow As Double lastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Dim currentRange As Variant: Set currentRange = ActiveSheet.Range("A2:A" & lastRow) ReDim newRange(1 To lastRow) Dim newFillValue As String Dim i As Long i = 1 For Each cell In currentRange If IsEmpty(cell.Value) = False Then newFillValue = cell.Value newRange(i) = newFillValue i = i + 1 Else newRange(i) = newFillValue i = i + 1 End If Next cell currentRange.Value = Application.Transpose(newRange)End Sub

The structure of the VBA script is pretty similar to the Google Apps Script, but it’s just a little different syntax. I’m not going to walk through the tutorial of how to set this up since it’s pretty similar to Google Sheets. In the VBA script, you do end up doing some “cell selection” like in line 8. Most of the script, however, is working with the Excel data object model so the script should run pretty quickly regardless of the size of your Excel file.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

Developer Love #3: Developer Experience Teams with Peggy Rayzis of Apollo

Subscribe: Apple Podcasts | Android | Google Podcasts | Stitcher | TuneIn | Spotify | RSS

Subscribe at alchen.substack.com

View Details

This post originally appeared on the KeyCuts blog.

Since TikTok is in the news right now about who is going to buy them, I thought using some fake-ish TikTok acquisition data would be relevant for this episode. A classic Excel/Google Sheets challenge: how to do a VLOOKUP to the “left” e.g. your lookup column is not the first column in your lookup table. There are all sorts of strategies to overcome this issue with how your data is structured. Notably, the INDEX/MATCH strategy is the most commonly-cited strategy when good ‘ol VLOOKUP is not at your disposal. In this episode I walk through a strategy that allows you to use VLOOKUP: array formulas. Skip to strategy #3 below if you want to see the answer. Associated Google Sheet for this episode if you want to follow along.

Was trying to find some gif associated with “looking up”

See the video below if you want to jump straight to the tutorial:

Why the VLOOKUP won’t work

If you are new to why VLOOKUP won’t work in this scenario (see Google Sheet), take a look at the data data structure below:

We have ID in column A and we want to find Company Name and Market Cap in columns C and D, respectively, for these IDs. The ID in column A is the unique identifier for the row, and we need to do a lookup to Company ID in column I.

While you can eyeball the result for the first row (“Triller” is the company for ID 3), we want to find a scalable solution using formulas.

As you start writing the VLOOKUP formula in column C, you’ll start to notice the problem: the Company ID column is not the first column in your table to lookup the ID value in column A:

Here are a few strategies for solving this problem (#3 is probably the one you haven’t seen before).

Strategy #1: Move the lookup column to the first column position

This is not the most ideal solution, but you could just simply cut and paste the Company ID column and move it to the left-most “first” column of your lookup table. In Excel you would have to do a cut and paste, but in Google Sheets you can just drag and drop the column into the proper position:

Now the VLOOKUP for Company Name will work correctly since Company ID is the first column in your lookup table:

I don’t like this strategy because it involves some manual cutting and pasting of columns. If your lookup table isn’t static (e.g. might be sales data that gets added daily), then you might be ruining the “structure” of your data on subsequent updates. Let’s see what else we can do.

Strategy #2: Make copies of the columns to the right of the lookup column

Also not an ideal solution, but it works in one-off cases where your data is static and you don’t care about showing your back-end work to a colleague. It looks like data is duplicated, but you’re basically referencing existing columns in your table so that those columns appear to the “right” of your lookup column:

Now you can do a VLOOKUP for columns I to K to get the Company Name and Market Cap values to show up in columns C and D:

Strategy #3 (preferred): Use array formulas

A relatively unknown feature in Google Sheets is you can create your own “tables” using array formulas. An array is simply a range of cells, and you can separate different range of cells using a semicolon. To create an array, you put curly brackets around your ranges. Here’s how an array of columns F and G would look like:

What’s the result? You simply get a reference to the two ranges after you enter the formula:

The key here is that you can create any order of range references in the array formula. We could’ve put G2:G6 first and F2:F6 second, and you would’ve seen the values in Website first followed by Company Name after entering the formula.

Knowing this, we can create our own lookup “table” using the array formula syntax like so:

Notice how the second argument in the VLOOKUP formula is no longer a table, but rather an array of column I followed by columns F to H. In this array, the second “column” is Company Name since we are saying column F is the second range of cells after column I. Market Cap is now the fourth column in this array:

In order to fill this formula down, we need to turn the range references in the array formula into absolute references as shown above.

Strategy #4 (most common): INDEX/MATCH

As mentioned at the beginning of this post, this is the most common method for looking up values to the left. I won’t give a detailed explanation of how INDEX/MATCH works, but here’s how you would get the Company Name given the data structure:

Which strategy should you use?

I’m a little torn between strategies #3 and #4 since INDEX/MATCH is the go-to method for looking up data to the left, and is also more performant than VLOOKUP on large data sets. The fact that the array formula in strategy #3 doesn’t involve a nested formula makes it potentially easier to debug in complicated spreadsheets. I haven’t used an array formula in many VLOOKUP situations since I learned INDEX/MATCH such a long time ago, but I may try this strategy in the future.

Of course, this all becomes irrelevant if you have the XLOOKUP function at your disposal which became available to certain Office 365 subscribers about a year ago (September 2019). This video is a fun poke at XLOOKUP, but also holds some truth for the VLOOKUP purists out there (start watching at 1:19):

A little Kant and poker

I talk about this in the 2nd half of the episode, but thought it would be worth sharing a passage from The Critique of Pure Reason as it relates to betting on your convictions. Listen to the Knowledge Projectepisode for the full background:

The usual touchstone, whether that which someone asserts is merely his persThe usual touchstone, whether that which someone asserts is merely his persuasion — or at least his subjective conviction, that is, his firm belief — is betting. It often happens that someone propounds his views with such positive and uncompromising assurance that he seems to have entirely set aside all thought of possible error. A bet disconcerts him. Sometimes it turns out that he has a conviction which can be estimated at a value of one ducat, but not of ten. For he is very willing to venture one ducat, but when it is a question of ten he becomes aware, as he had not previously been, that it may very well be that he is in error. If, in a given case, we represent ourselves as staking the happiness of our whole life, the triumphant tone of our judgment is greatly abated; we become extremely diffident, and discover for the first time that our belief does not reach so far. Thus pragmatic belief always exists in some specific degree, which, according to differences in the interests at stake, may be large or may be small.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

The ShopTalk Show #424: Web Components, Frameworks vs Vanilla, Accessible Numbers, and SVG Memory Usage

The Knowledge Project #89: Maria Konnikova: Less Certainty, More Inquiry

Subscribe: Apple Podcasts | Android | Google Podcasts | Stitcher | TuneIn | Spotify | RSS

Subscribe at alchen.substack.com

View Details

This post originally appeared on the KeyCuts blog.

It's 2010, and the world is coming out of recession. Two Harvard professors--one of whom is a former economist for the IMF and chess Grandmaster--publish a paper suggesting that a country with a high public debt-to-GDP ratio of over 90% is associated with low economic growth. Turns out the Excel model the professors use is riddled with some basic statistical and formula errors. The results potentially lead to incorrect economic policies, austerity measures, and high unemployment around the world. This is a Google Sheet which shows one of the spreadsheet errors, and I show how you can prevent such an error in this post.

See the video below if you want to jump straight to the tutorial:

Background

Economists Carmen Reinhart and Kenneth Rogoff published a paper in 2010 called Growth in a Time of Debt (originally published in the American Economic Review) where they argued:

[...] median growth rates for countries with public debt over 90 percent of GDP are roughly one percent lower than otherwise; average (mean) growth rates are several percent lower.

In 2013, PhD students Thomas Herndon, Michael Ash, and Robert Pollin of the University of Massachusetts, Amherst had re-created the study from Reinhart and Rogoff's paper as part of their PhD program. The students had to analyze the original Excel files that Reinhart and Rogoff used, and they weren't able to replicate the original results. They cited in their own paper entitled Does High Public Debt Consistently Stifle Economic Growth? A Critique of Reinhart and Rogoff :

[...] coding errors, selective exclusion of available data, and unconventional weighting of summary statistics lead to serious errors that inaccurately represent the relationship between public debt and GDP growth among 20 advanced economies in the post-war period.

Reinhart and Rogoff suggested that the debt/GDP ratio and economic growth is simply a correlation, and that correlation still holds after correcting for the spreadsheet mistakes. However, that correlation is not as strong as their original paper posited.

Why this was a big deal

The implications of their findings resulted in news outlets, politicians, and policymakers using the 90% benchmark as a signal that a country is heading for low economic growth. Some notable examples:

2012 Republican nominee for the US vice presidency Paul Ryan included the paper in hi proposed 2013 budget

The Washington Post editorial board takes it as an economic consensus view, stating that "debt-to-GDP could keep rising — and stick dangerously near the 90 percent mark that economists regard as a threat to sustainable economic growth."

Austerity measures are put into place around the world despite the advice from economic advisers, pushing unemployment rate above 10% in the eurozone

3 main Excel spreadsheet problems with the model

The three main errors that Herndon, Ash, and Polling discovered are the following:

Years of high debt and average growth where selectively excluded from the data set

Countries' GDP growth rates were not properly weighted

Summary table excludes high-debt and average-growth countries

This video illustrates the three individual problems with the spreadsheet really clearly:

If you fix these errors, the average real GDP growth rate for countries carrying a public debt-to-GDP ratio of over 90% is actually 2.2%, not -0.1%. In the Google Sheet I shared, you wont' see the correct 2.2% average growth rate since I'm not doing the full analysis and focusing on the third Excel error stated above.

Fixing incorrect cell references for average GDP growth rates

The third error of incorrectly excluding high-growth countries from the average GDP growth rate is a particularly egregious mistake, and Reinhart and Rogoff admit that they made this simple cell referencing mistake. As you can see in the screenshot below, they simply omit rows 45 to 49 in their AVERAGE formula:

Source: https://statmodeling.stat.columbia.edu/

Here are three methods Reinhart and Rogoff could have used to ensure that they referenced the correct cells to avoid this mistake:

Method 1: Check the summary dropdown in the bottom-right

After you select all the cells that contain GDP growth rates in column G, you can look at the dropdown in the bottom right of Excel or Google Sheets to see the average. No formulas required:

You can also get other summary stats like the SUM, MIN, and MAX of your selected range of cells. Probably the easiest method to get a quick sanity check of your averages that you've calculated in lines 26-27 of the Google Sheet.

Method 2: Adding a checksum/checkaverage formula to compare results

This one is my preferred method, and is quite common in financial models. Usually you'll see this type of "error checking" when you want to make sure you've captured the correct cell references for a SUM formula, but with some extra work you can check for averages too.

You start by writing a formula below your actual summary stats (in this case starting on line 28 of the Google Sheet) and create a SUM formula of the data:

The big question is this: how do you know if you've referenced the correct cells in your "checksum" formula? The hope here is that by writing the SUM formula for the second time, in theory, you won't make the same mistake twice. Obviously this is a big assumption in this method, but let's assume you've properly made the reference for this internal error-checking formula.

The next formula below the "checksum" is a "count" formula:

Notice how it's not a COUNT formula. This is because the table contains the "n.a." text so a COUNTA formula would be incorrect since it would count all values in the column. We only want the numeric values, hence the reason for using COUNT.

Finally, the "checkaverage" formula compares your actual average in line 26 with the result of checksum / count. If the values aren't equal, then you'll get the text "Error" as the result of the IF formula:

Since line 26 references the "incorrect" averages used in Reinhart and Rogoff's paper, we get errors across the board. This "checksum" or "checkaverage" methodology gives you a visual indicator on whether your calculated results are properly referencing all the cells in the range instead of a subset. Instead of writing a "checksum" and "count" formula, you could simplify the "checkaverage" formula to this:

We simply put the SUM and COUNT formulas inside the first argument of the IF statement.

Method 3: Create a PivotTable and compare results

This method also relies on you selecting the proper cells to build your PivotTable. Again, assuming you don't make the same mistake twice, selecting the cells in the range should be a pretty simple task. After you select the cells (B4:G24 in this case), you build a PivotTable with Country in the Rows and the four debt/GDP buckets in the values. You then summarize each metric with the AVERAGE selection:

The "Grand Total" on the last line of the PivotTable contains the average across all growth rates. You can then compare these numbers to your computed numbers on the first sheet that contains your table.

Lessons to be learned for your own models

People don't check their analyses with the the above 3 methods because it takes extra work and...well...people are lazy. In addition to putting in error checks to ensure you are not making simple spreadsheet errors like this, there are other strategies you can use to ensure others can replicate your work to detect potential errors.

For Reinhart and Rogoff, they didn't make their full underlying data public. They only shared their spreadsheet after Herndon, Ash and Pollin reached out to them as the trio was trying to replicate their results. Some other strategies:

Upload your results to a public repository like GitHub early on in your analysis and "open source" your data

Write detailed steps on experimental design, procedures, equipment, data processing, and statistical methods used so others can replicate your experiment

I really liked this quote from a commenter about the Excel error on this Stat Modeling blog

I’d like to see how many researchers expose themselves to such criticism. Uploading a raw dataset is one thing but allowing people to see all your intermediate calculations in messy detail is rare.

Too often we're caught up in doing all the number crunching ourselves and then sharing the output once we think we've crossed finished the analysis. As this example suggests, sharing your data set and model as you are doing the analysis can prevent a blunder like this from happening.

Auto date formatting and human gene naming problems

In the second half of this episode, I discuss an article in The Verge about how the HUGO Gene Nomenclature Committee had to rename gene names because of Excel's simple feature of auto-formatting dates. Gene names like "MARCH1" and "SEPT1" get re-formatted to the dates "1-Mar" and "1-Sep" when these values are entered into Excel. I thought this was interesting to see the scientific community bending to this standard feature in Excel given the widespread use of Excel in the scientific community.

Source: The Verge

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

The Verge: Scientists rename human genes to stop Microsoft Excel from misreading them as dates

This Week In Startups #948: HackerOne CEO Mårten Mickos shares insights on how he grew his bug bounty army to 400,000 strong by providing a path to hack for good, most common security vulnerabilities, worst security breaches, hacking the Pentagon, protecting the open source that unites us & scaling a company culture that defaults to disclosure

Subscribe: Apple Podcasts | Android | Google Podcasts | Stitcher | TuneIn | Spotify | RSS

Subscribe at alchen.substack.com

View Details

This post originally appeared on the KeyCuts blog.

Let’s say you have a set list of names (in this case TV characters from popular 90s TV shows). You want Google Sheets/Excel to generate a random list of names from your list as if you were picking names out of a hat. How would you do this? It most likely would involve the RAND function, but let’s take it a step further and say you want to give the end user the ability to dictate the number of random names to return from your list (e.g. out of my list of 100, give me 5 random names). This is the Google Sheet with all the completed formulas. In addition to the audio format of this episode, I’m also going to start releasing the video tutorial:

Create your list in column B

Start with your list of names in column B. This can be any list you want to randomize. My list is just a bunch of TV characters from shows I watched when I was a kid.

Source: Fandom

In column A, you put the RAND function and copy it all the way down to the bottom of our list. You’ll get a decimal with random numbers. Doesn’t look that useful now, but this random number column will drive the rest of the tool to generate your list of random names:

Sort this random list of numbers

It sounds kind of weird, why would you sort a random list of numbers? What does that even mean? As you have probably seen, every time you refresh your Google Sheet or commit an Excel formula by hitting ENTER, all those random numbers in column A will change. This means if you sort this list of random numbers, the sorted list will change too. I put a space in column C so in cell D2, you enter this formula:

The SORT function takes in a range of cells as the first parameter, the sort index as the 2nd (which is just the number column we ant to sort on, column #1), and then true or false for sorting in ascending or descending order. You can also put 0 to indicate false which is what I did in this example to sort in descending order.

The nice thing about the SORT function is that it automatically fills the formula down to the bottom of your data set. This is a relatively new function in Excel since it kind of acts like dynamic array formulas or array-entered formulas. The formula kind of “spills” down for you as your list grows so you don’t have to worry about dragging the formula down until the last row in your data set.

A good ‘ol VLOOKUP

What does this column of sorted random numbers do for us? Well, we know that each random number in this sorted column corresponds to one of the numbers in column A where we generated the random number. So in column E, we just do a VLOOKUP using column D as our lookup value and columns A:B as our lookup table to get the name associated with the random number in column D:

This is not the usual way you might use VLOOKUP because you’re usually using VLOOKUP with some unique identifier as the lookup value. Column A isn’t really a unique “TV character ID” since that “ID” changes all the time with the RAND function. We don’t really care about that, because now when you refresh the Sheet, column E will always have a random list of names:

In the above gif I’m just pressing COMMAND + R a few times to refresh the Sheet so that the RAND function in column A constantly changes.

We could stop here since you now have a random list of names in column E. Let’s take this a step further and give the end user the ability to choose the number of random names from the list.

User input with OFFSET

We’re already doing some hacking with VLOOKUP and using it in a way that it probably wasn’t made to use, so let’s do something similar with the OFFSETfunction. Cell H5 is just my “user input” cell where I’m getting the number of results from the user. This is a hard-coded number the user has to input. Then in cell H2, I have this OFFSET formula:

Let’s break this down by each parameter:

E2 – This is the “starting point” for my OFFSET function

0 – I don’t want to move any rows up/down

0 – I don’t want to move any columns up/down

H1 – References my user input cell indicating how many rows of data I want to return from my OFFSET (e.g. “height” of the range)

1 – How many columns to return (e.g. “width” of the range)

Now as you put a number in cell H1, the list of random names will grow and shrink. If you put a number that is more than the list of names you have, then it will just return the max number of names from your list (in random order, of course):

Picking the right tool for the job

A caveat I point out at the end of this episode is that while you can do this random list of names generator in Excel or Google Sheets, a spreadsheet may not be the best tool for the job. There are hundreds of random list generator apps that may be built specifically for your industry be it education or hospitality. Sometime it’s just easier to do it in a spreadsheet because all our data is there, but constantly question if the tool you are using is the right one for the job.

There’s a similar template in the Coda gallery which generates a random list of teams of players based on the number of teams and players you have. Just another nifty way at approaching the same problem in a different tool. Disclosure: I work at Coda.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

Google Cloud Platform Podcast #226: Documentation in Developer Practices with Riona Macnamara

Subscribe: Apple Podcasts | Android | Google Podcasts | Stitcher | TuneIn | Spotify | RSS

Subscribe at alchen.substack.com

View Details

This post originally appeared on the KeyCuts blog.

You blink a few times at the screen and realize what you're seeing is not a type. $6.2B has left your bank due to some rogue trader making untimely bets on the market. That's B as in billion. You call up the modeler who was supposed to make sure this never happens to your bank. The modeler takes a closer look at his model, and realizes that he made a fundamental error in how he calculates one value that caused the dominoes to fall. This is the story of the "London Whale" at JPMorgan Chase in 2012 who cost the bank $6.2B and a breakdown of the Excel error that may have caused the whole thing. This is the Google Sheet if you want to follow along with the Excel error.

Derivative of a derivative

I'm not going to pretend like a know the intricacies of all the financial products involved here, so you can read the Wikipedia article if you want the full details. In 2012, there was a CDS (credit default swap) product called CDX IG 9 that the trader at JPMorgan may have made large bets on, and ended up on the wrong side of the bet. The London trader's name is Bruno Iksil, and it was a classic scenario of a gambler trying to get out of his losses by doubling down on black at the roulette table.

Source: The Fiscal Times

Multiple investigations were taken by the authorities in the U.S. and U.K., the the investigations show that a variety of institutional failures may have facilitated the large bets made by the London Whale. This HBR article by Ben Heineman, Jr. provides a nice summary of all the key players:

London traders - The traders simply didn't understand the complexity of the derivative products they were buying and selling

Chief Investment Office(CIO) - The head of the CIO didn't monitor the trading strategies and put in the proper controls for the portfolio of products the office was buying. The Value at Risk (VaR) model was flawed (see more below).

Firm-wide Leaders - Not enough oversight by the CFO and CEO (Jamie Dimon)

Board and Risk Policy Committee - The committee was told that everything was fine with the CIO, and didn't get accurate pictures of what risk officers really felt about the risky trades being made.

Appendix of the Task Force Report by JPMorgan

There is a 130-page report created by JPMorgan Chase in 2012 which details what happened internally that led to this debacle. In my opinion, the juicy stuff starts in the appendix starting on page 121 of the report. I read off some parts of this appendix in this episode, but the appendix basically details issues with the VaR models created by one of the quantitative modelers at JPMorgan to more accurately value the complex traders that were happening. Or at least they thought the model was more accurate.

At the very end of the appendix, there's a section called "Discovery of Problems with the New VaR Model and Discontinuance" where the report details the Excel error that contributed to the large inaccuracies in how the model valued risk.

The $6.2B Excel error

This is how the error is described in the report (emphasis mine):

Following that decision, further errors were discovered in the Basel II.5 model, including, most significantly, an operational error in the calculation of the relative changes in hazard rates and correlation estimates. Specifically, after subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended.

Note: I don't have domain expertise in VaR models, synthetic credit derivatives, or trading in general. The following example is my over-simplification of the error based on what's written in the report.

The report talks about hazard rates (for what I assume relate to the default of corporate loans in this case) and how the changes in the hazard rates were improperly calculated. Here's a simple table from the Google Sheet showing fictitious dates, hazard rates, and the change in rates:

Now here's what happens when you apply a SUM vs. an AVERAGE to the "Change in %" column:

This is hitting the border of my knowledge of growth rates and time periods, but the sum of changes will always be 5X the average of changes given there are 5 values we are summing/averaging.

The difficulty with detecting this type of formula error

The magnitude of the difference between the SUM and the AVERAGE is not what I think is interesting, but rather the absolute difference between the SUM and AVERAGE. Here is a chart plotting the same data:

Based on this chart, can you estimate what the average of the Change in % is? Looks like something around 0%, but 3% doesn't feel that far off. The point I'm trying to make is that unless you are monitoring the SUM and AVERAGE consistently over time to detect any outliers, it will be difficult to know whether you made the formula mistake in the first place. With the presence of outliers, it makes it more clear that you might have an error in your model. Here's the other table from the Google Sheet with intentionally skewed hazard rates:

Here we see the magnitude of the difference is still 5X, but the absolute difference is much wider. This would cause an analyst to look deeper into the model and try to figure out why there is such a large discrepancy. But this is only because there are fictitious hazard rates. In the case of JPMorgan Chase, my hunch is that the gap between the lower and upper bound of daily hazard rates was really narrow, so detecting a change like this would've been very difficult without the proper controls in place.

This reminds me of the tale of the boiling frog:

Urban myth has it that if you put a frog in a pot of boiling water it will instantly leap out. But if you put it in a pot filled with pleasantly tepid water and gradually heat it, the frog will remain in the water until it boils to death. (Source)

Without a really hot pot of boiling water, it was too late for JPMorgan to detect there was something wrong with the CDS trades, and the proverbial frog boils to death.

Hanlon's Razor

One frame for this egregious Excel error is Hanlon's Razor:

"Never attribute to malice that which is adequately explained by stupidity", known in several other forms. It is a philosophical razor which suggests a way of eliminating unlikely explanations for human behavior. (Source)

Perhaps the modeler cannot be blamed for his Excel error because it was an error that he had know way of knowing or predicting. I'm not trying to remove blame from the modeler, but it's an interesting frame to analyze the problem because this is an spreadsheet error that is difficult to prevent unless you have other models and risk controls that are able to predict this type of error in advance. There are many other cases of Excel errors that led to false calculations that cost firms millions of dollars, and it's hard to say if one can blame the modeler for "malice" or plain stupidity.

New intermediate Excel class on Skillshare

Quick plug for a new Excel class I just launched today on Skillshare. It’s an intermediate Excel class for cleaning and analyzing data.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

a16z Podcast: The Future of Decision-Making--3 Startup Opportunities

Listen to this episode here

Subscribe: Apple Podcasts | Android | Google Podcasts | Stitcher | TuneIn | Spotify | RSS

Subscribe at alchen.substack.com

View Details

This post originally appeared on the KeyCuts blog.

In Excel or Google Sheets, text manipulation is usually associated with data cleaning, data cleansing, and data transformation. Sometimes your data is “dirty” and needs to be categorized in a different way or you need to “extract” a piece of text from a another piece of text. In this example, we use a combination of the FIND, RIGHT, and LEN functions to extract the domain name from an email address (e.g. the “tesla.com” from “john.smith@tesla.com”). Here’s the Google Sheet if you want to make a copy for yourself to follow along.

Start with finding the @

The first step is to use the FIND function to find the location of the “@” symbol in the email address. The FIND function takes two required arguments and one optional argument. You’re basically find the index location of where that characters or string exists within the cell:

In the case of “john.smith@amazon.com,” the FIND function would return 11 since the “@” symbol starts at the 11th position within the email address. Pretty simple right?

Nesting LEN inside the RIGHT function

The next part is a little trickier. Now that we know the position of the “@” symbol, we want all the characters after the “@” symbol to get the domain of the email address. There are multiple ways of doing this, but I chose to use the RIGHT and LEN functions. To make this more clear, I could have put the LEN function in its own column, but decided to next it within the RIGHT function:

The RIGHT function takes two arguments and simply returns the number of characters from the “right” of the text you give it (in this case the email address). Since we don’t know how many characters to pull from each e-mail address, we use the result of the LEN(A2) - B2 formula which tells us how many characters to pull from the right of the email address.

LEN(A2) gives us the length of the entire text (for “john.smith@amazon.com” it’s 21). If we subtract the index position of the “@” symbol from that length, we’ll get the exact number of characters to pull for each unique email address. Pretty nifty.

Note: The “Position of @” column also could’ve been nested in the 3rd column (and replaced the current cell reference of B2).

I typically use a combination of FIND, LEN, and MID to extract the text I need from a longer piece of text. Once you master these few functions, you’ll be able to to pull anything you want out of a long piece of text to get “clean” data.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

The Tim Ferriss Show #444: Hugh Jackman on Best Decisions, Daily Routines, The 85% Rule, Favorite Exercises, Mind Training, and Much More

EconTalk: Robert Lerman on Apprenticeships

Subscribe at alchen.substack.com

View Details

Subscribe at alchen.substack.com

View Details

Subscribe at alchen.substack.com

View Details

Subscribe at alchen.substack.com

View Details

The QUERY()function in Google Sheets gives you the ability to quickly filter and sort your data similar to how you might get data from a database. If you write SQL queries, the QUERY() function feels easy and natural to use. There are a few caveats as I discuss in this episode. If you want to follow along with the exercises I discuss in this episode, make a copy of this Google Sheet which contains the QUERY() functions I mention in the episode.

Basic query to find confirmed cases greater than 50,000

Our data set is from the COVID-19 Data Repository by the Center for Systems Science and Engineering (CSSE) at Johns Hopkins University. The data shows confirmed cases, deaths, and recovered cases by country (188 countries) on May 1st:

The first query simply pulls back the list of countries and confirmed cases where the number of confirmed cases is greater than 50,000. Notice how you reference the column letter name versus the actual name of the column in the header row:

The first parameter is covid_data which is a named range in Google Sheets. In this case, it references cells A1:E188 in our data set.

More SQL-like commands

You can do many database-like commands with the QUERY() function. The next example shows how you can use the ORDER BY command to find countries with deaths between 0 and 5 and the resulting list is sorted in descending order:

Check out Ben Collins’ blog post about the QUERY() function to see some of the other SQL commands you can use.

Adding in new calculated columns

In the third query, we get a little more advanced and use the LABEL command to create a new “column” called Case Fatality Rate. This calculation is simply Confirmed / Deaths. Unlike SQL, you put the LABEL at the end of the command instead of in the beginning of the SELECT statement:

Coming from SQL, you’ll need to account for the difference in the order of commands in the query in order for it to work correctly.

Inability to select column names

You’ll notice that you don’t put the actual names of the columns in your header row in the query. This can be a pro or con of the QUERY() function depending on how your underlying data set is structured.

Columns are changing a lot

If you underlying data is constantly “shuffling” where columns are moving around and the structure of the data is not set in stone, the QUERY() function will most likely break because you’re referencing the column letter instead of the column name like in a traditional SQL query.

Columns are fixed

If your columns are not shuffling around a lot, this syntax of selecting the column letter may actually be easier for you. This is because you don’t have to type out the long column name in the QUERY() function. If data is simply getting appended to the bottom of your data set, then the QUERY() function should work fine for you because the letters of the columns will always reference the correct columns of data.

PivotTables vs. the QUERY() function

One of the reasons I don’t use the QUERY() function too often is because I find PivotTables to be easy enough to use to filter, sort, and aggregate my data to do my analysis. Additionally, your columns can move around in the underlying data set and the PivotTable will still work since it’s not referencing columns by letter but rather by the name in your header row.

Plotting trend lines for COVID-19

One of the articles I discuss in this episode is this Vox article about how the Council of Economic Advisers may have applied a stock trendline in Excel to “forecast” deaths as a result of COVID-19. The article discusses the concept of “smoothing out” volatile data versus prescribing a forecast, and that line between these two concepts is a bit blurry. This is the cubic chart in Excel which you can easily build from the trendline features in Excel:

Source: Vox

And then this is the chart from a CEA Tweet that appears to show the cubic trendline as a potential forecast:

SUM by David Eagleman

A book I discuss at the end of this episode is SUM: Tales from the Afterlives by David Eagleman. I read a chapter from the book called Incentive and how it relates to some recent shows I’ve been watching like Westworld and Devs. Highly recommend checking out the book.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

The Trump administration’s “cubic model” of coronavirus deaths, explained by Matthew Yglesias

Jocko Podcast #222: Life is a Challenge. Life is Suffering. So Live With Fortitude. With Dan Crenshaw

SUM: Forty tales from the afterlives by David Eagleman

Subscribe: Apple Podcasts | Android | Google Podcasts | Stitcher | TuneIn | Spotify | RSS

Subscribe at alchen.substack.com

View Details

This post originally appeared on the KeyCuts blog.

I worked on a "small" side project recently to sync data between Google Sheets and tables in Coda. The full blog post tutorial is here, and the GitHub repository is here. I started using Google Apps Script last year and it's a super powerful way to connect different apps you use in the G Suite ecosystem. The impetus for creating these two scripts was seeing a few people in the Coda community talk about syncing data between their Google Sheets and Coda. The big caveat is that these are only one-way syncs, but there are several use cases where doing this could be useful in business workflows and making your team more productive.

Writing a script in Google Apps Script

Some Google Apps scripts can be super simple to set up. See this pretty simple workflow below of sending email automatically when there is data in your Google Sheet:

Most of the "work" with writing these scripts was transforming data so that the model in Google Sheets matches the model in Coda as per Coda's API. Once that data munging is done, the rest of the script was relatively easy in terms of giving users the ability to add, delete, and modify data. I would highly recommend taking a look at Google Apps Script especiallyif you use a lot of Google Sheets. You'll be able to connect your Google Sheet with other applications in G Suite and other 3rd-party apps you use for work.

Use cases for syncing data between Coda and Google Sheets

This comes straight from the blog post, but thought it was worth repeating again:

Data synced from your Google Sheet

HR & recruiting - All your candidates are stored in a Google Sheet but you want to be able to move candidates through different stages in the interviewing pipeline and Google Sheets isn't sufficient for your needs. Having all your candidates in a table in Coda means you can use templates like this one to manage candidates more effectively.

E-commerce and ERP - Orders, customers, and POs may all be different tabs in a Google Sheet that gets updated through Shopify or some other e-commerce platform. In order to manage your e-commerce business, you may want to see charts, calendar of shipments, and reports that Google Sheets cannot provide easily. Syncing the data from Google Sheets to Coda means you can do ERP properly (see this template as an example).

Customer Feedback - You may have a ticketing system like Zendesk or Intercom and all feedback lands in a Google Sheet somewhere. You can do some basic analytics in the Google Sheet but to reply to the feedback means you have to go into Gmail and start replying to customers. If your customer feedback is all in a Coda doc, you can run analytics and send emails using the Gmail Pack (see this template).

Data synced to your Google Sheet

3rd-party vendor reporting - Your vendors may not be using Coda yet, but you have all your vendor data in Coda and need to send them the data in a format they prefer. While you could publish your Coda doc, the vendor still wants the data in a Google Sheet you have edit access to.

Data "backup" - Your team may create thousands of rows of data every quarter in a Coda doc and want to start each quarter "fresh." Coda docs grow with your teams and they may get slow as you add in more functionality, so having a backup of your data in Google Sheets is another reason to sync data from your Coda doc to Google Sheets.

Finance & Accounting - Most internal finance and accounting functions still use Excel and spreadsheets for month-end reporting, taxes, and other business-critical activities. As your data grows in Coda, you can keep your finance counterparts in the loop by having your data synced to a Google Sheet which your finance team can use for their reporting and forecasting purposes.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

Visual Developer's Podcast #25: Sheets vs. Airtable vs. Coda

Jocko Podcast #226: The Code. The Evaluation. The Protocols. The PATH. With David Berke

Subscribe at alchen.substack.com

View Details

This post originally appeared on the KeyCuts blog.

Now that you're staying home and picking up new hobbies and taking classes online, here are a few tips on how to learn Excel and spreadsheets from an online class. I have seen viewership on my own Excel classes spike since COVID-19 hit which has led me to think about the best way to learn online.

First of all, why are so many people trying to learn Excel? Maybe since all schools and universities have pushed to online learning, students may be questioning the value of their college degrees. Maybe I should start learning skills that will actually help me land a job...enter stage left: Excel and spreadsheets.

Spreadsheets most sought after skill

In episode 22, I brought up an episode of Freakonomics where they discussed different stats around subjects Freakonomics listeners wished they had learned in high school to better prepare them for their current jobs. The high-level numbers:

Skills currently used on their jobs

Less than 5% - Percent of survey responders who said they still use calculus, trigonometry, or geometry in their current jobs

70% - Those who use Excel or Google Sheets on a daily basis

75% - Those who visualize data or present data to make an argument on a daily, weekly, or monthly basis

Skills people wished they had learned in high school

0% - Those who wished they had learned other traditional math subjects in high school beyond what they had already learned

65% - Those who wished they had learned skills around analyzing and interpreting data to uncover insights

60% - Those who wished they had learned how to visualize and present data

It's pretty clear that data-related skills are what's actually being used on the job, and during a pandemic where you may have been furloughed, laid off, graduating from university, or really any scenario where your future is unclear and you want to secure a job, learning Excel and data skills may bubble to the top on your to-do list while you're in quarantine at home. Hopefully these tips will help you gain the skills you need to learn Excel and spreadsheets to help land your next job.

1) Block out time on your calendar to take your class

If you're a fan of David Allen's Getting Things Done philosophy, you've probably head the phrase that if it doesn't gets scheduled, it doesn't get done. Blocking off time on your Google or Outlook calendar to actually take your Excel class versus taking the class when you feel like it will ensure you get through the material and get into a state of flow with the material.

2) Minimize distractions

While it's easy to stay connected with family and friends while at home, you really need to put away your phone and apps for doing all your meetings and virtual hangouts. Turning off notifications for Facetime, Facebook, Houseparty, Slack, etc. will ensure you can get some uninterrupted time to learn Excel. There are small nuances to writing Excel formulas that can be easy to overlook when you are distracted by your friends or social media.

3) Connect with the instructor and community

Many online Excel classes encourage you to ask the instructor questions and many platforms such as Skillshare encourage students to participate in the community of other students who are taking the class with you. For my Excel classes, there are several discussions where students ask me questions and either I or another student taking the class will jump in an answer. Active participation ensures you are engaged with the class and the instructor and students can help keep you accountable.

4) Have Excel open alongside the video

It's easy to simply watch a screenshare of an instructor doing something in Excel and say: "I get that, that looks easy to do." It's one thing to see the instructor write a VLOOKUP() formula but a completely different experience when you write the formula yourself. Have Excel or Google Sheets open next to the window where you are taking the class is important for you to get hands-on experience with using Excel. Pause the video and try doing what the instructor is doing in Excel.

5) Practice with real use cases from your daily life

Probably the most important tip. In order to take what you learn from the online Excel class marketable to the real world, you need to use spreadsheets for real-life scenarios. The main way I learned Excel was from looking at other people's spreadsheets in a work environment. If you know someone who can share an Excel file they use at work (removing sensitive info, of course), this would give you a way to see how people use Excel in the real world. Then you can talk more intelligently about how you might design a spreadsheet during an interview.

Don't have access to Excel files from people who use Excel every day? Try Googling "financial model Excel example" or "track customers Excel example" and you'll get all sorts of nice templates. Better yet, take Google Sheets or Excel and start tracking something in your daily life. The number of home workouts you do every week. What you are spending on online deliveries. Track COVID-19 stats for your county or state. By building these simple reporting tools, you'll get a feel for how to use spreadsheets for a real world use case.

Some of my favorite Excel teachers

Been following some of these instructors for a while now, and can definitely say their classes are worth checking out if you are new to Excel:

Oz du Soleil's Lynda classes

Mynda Treacy's myOnlineTraininghub classes

Bill Jelen's Mr. Excel YouTube channel

MAKRO is back!

One of my favorite Excel streamers is back with this livestream. He makes some good points about how Microsoft is dumbing down Excel for beginners and alienating advanced Excel users. Bless you MAKRO.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

a16z Episode #523: Innovation Through Software Development and IT

Knuckleheads Season 3 Episode #6: Isiah Thomas AKA Zeke

Subscribe at alchen.substack.com

View Details

Have you ever wondered what an “array-entered formula” is? It’s an intermediate/advanced concept in Excel but in late 2018, Microsoft released dynamic array functions and formulas that “spill” into the cells below your current cell with a function. This makes writing formulas easier and less prone to human error, but there are some tradeoffs to […]

The post Dear Analyst #29: Working with dynamic array functions and formulas that spill appeared first on .

Subscribe at alchen.substack.com

View Details

This spreadsheet tip is based on a question I get asked all the time when I teach (well taught) Excel at in-person classes: How do I fill a formula down to the last row of my data set without over-shooting the last row with keyboard shortcuts? This problem occurs with larger data sets where you […]

The post Dear Analyst #28: Filling a formula down to the last row of your data set appeared first on .

Subscribe at alchen.substack.com

View Details

This is an Excel trick that’s not super complicated but super useful for labelling a simple table in Excel. Let’s say you have one set of labels along the rows (e.g. “Region”) and then another set of labels along the columns (e.g. “Month”). Cell A1 is now empty because you don’t know which label to […]

The post Dear Analyst #27: Splitting a cell diagonally to label y and x-axis and COVID-19 dashboard appeared first on .

Subscribe at alchen.substack.com

View Details

Given the media attention placed on the coronavirus (COVID-19) in the media the last few weeks, I thought it was important to take a step back and look at the math behind infectious diseases and how diseases spread. I spend the entire episode taking a look at Going Critical, a blog post by Kevin Simler […]

The post Dear Analyst Episode 26: Data visualizations for infectious diseases/ideas during coronavirus (COVID-19) appeared first on .

Subscribe at alchen.substack.com

View Details

This episode is based on a video and Tweet posted by Mr. Excel (Bill Jelen). Bill discusses an Excel challenge someone emailed him about regarding how to “transform” a badly structured table of data into a structure that makes it easy to do PivotTables and other downstream analysis. Interestingly, I received a ticket from a […]

The post Dear Analyst Episode 25: Structuring data challenge (denormalize data) with Get and Transform appeared first on .

Subscribe at alchen.substack.com

View Details

I discuss how to calculate percentiles in Excel or Google Sheets using the PERCENTILE function. With the PERCENTILE function, you can calculate the value that would represent nth percentile in your list of values. This is not exactly the calculation I was looking for. Instead, what if you wanted to know what the rank percentile […]

The post Dear Analyst Episode 24: Finding and ranking percentiles appeared first on .

Subscribe at alchen.substack.com

View Details

In this episode, I discuss how to calculate trends over time in Excel for the purposes for forecasting future values. I reference an old post about calculating trends where someone recently left a comment about the counter-intuitiveness on calculating averages of changes in your values. In order to follow along with this episode, I would […]

The post Revisiting Calculating Average Trends Across Time Periods in Spreadsheets appeared first on .

Subscribe at alchen.substack.com

View Details

If you are by your computer, you may want to open this Google Sheet to understand the example discussed in this episode. I walk through a rather long formula involving the FREQUENCY(), COLUMN(), MAX(), and the ARRAYFORMULA() functions in Google Sheets. Here’s the full formula below to calculate win streaks in the Google Sheet: [crayon-5e9a5d15b7f0f403479039/] […]

The post Dear Analyst Episode 22: Calculate win streaks for a pool of players in Google Sheets appeared first on .

Subscribe at alchen.substack.com

View Details

This is my talk from Webflow’s No-Code Conference that took place on November 13th, 2019 in San Francisco. The title of my talk was called Building No-Code Tools and Applications from Spreadsheets. The slides from my presentation are on SlideShare here. Themes from the talk This was my first time talking about my experience with […]

The post Dear Analyst Episode 21: Building No-Code Tools and Applications from Spreadsheets appeared first on .

Subscribe at alchen.substack.com

View Details

A few weeks ago I had the opportunity to teach a week-long data analytics course through General Assembly. The course was taught entirely online using Zoom. I discuss some of the topics the students learned in the class, and what the experience was like teaching an online class in real time. The topics we covered […]

The post What it’s like teaching a week-long online data analytics course appeared first on .

Subscribe at alchen.substack.com