Dear Analyst

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 her writing gigs to pay for her engineering education, and eventually became a geodata engineer working in the field of Geographic Information Systems (GIS). In this episode, she discusses falling in love with Python, how her engineering background helps with a career in data, working from home before everyone did it, winning the a NASA challenge, and more.

How web development can help with your data analyst skills

Helen started her career in geomatics. Prior to this conversation I didn't know much about this industry:

Geomatics is the integrated approach of measurement, analysis, and management of the descriptions and locations of geospatial data.Source: University of Florida

Over time, Helen realized there wasn't a lot of creativity in the geomatics space. Most of her time was spent doing research and publishing for scientific journals. It was time for a change.

Due to personal reasons, she decided to look for jobs and projects she could do entirely at home before the working from home trend really started. Given the engineering background, she started picking up projects in web development marketing automation, data analysis. She realized she could be a lot more creative with these web development and data jobs versus her old career in geomatics.

Falling into data engineering

As Helen continued taking on projects while working from home, data became more prominent in her projects. She worked with an ad tech company where she helped with getting data from their website into their CRM. She was doing a lot of data cleaning and was actually doing ETL (Extract Transform Load) for her clients. Before she knew it, she was doing data engineering work.

Helen decided to find some data science workshops in the Philippines and started working on a Master's degree in data science. While her geodata engineering experience helped with some of the coding skills required in her data science projects, she felt that learning algorithms was still difficult.

Like many people I've spoken with on this podcast, people fall into data analysis and data engineering. You work on data projects without realizing you are actually doing things that a data scientist is doing. And if you want to formalize the skills you are acquiring, you can go back to school like Helen did. In Helen's words:

I learned the practical application of data skills before learning the theory.

Building a "portfolio career"

As Helen discussed her various roles and projects, she brought up a phrase I haven't heard before when you are progressing through your career: the "portfolio career."

In HR speak, I think this is analogous to people who call themselves "generalists." Helen has gotten exposure to a variety of industries and people. She brought up an interesting point about how most data analysts progress through their careers. They are laser focused on their field or industry, and maybe don't have the time, need,

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 about interviewing guests on a show :). Kelly Schuster-Paredes starting teaching Python in middle school about four years ago. Sean Tibor also taught Python in middle school but transitioned to a cloud engineering role earlier this year. We chat about teaching data literacy in middle schools, developing empathy, the AP Computer Science exam, and the Teaching Python podcast.

Data literacy and Python for middle school students

Hearing the words "Python," "data literacy," and "middle school" in the same sentence is foreign to me. When I was in middle school in the late 90s, the only exposure we had to computers was the one computer in every classroom we sometimes got to play computer games on. In high school, there was only one computer science class and the only language you could learn was C++.

This might just be my "get off my front lawn" moment. The classroom has obviously changed a lot since the late 90s, and Kelly and Sean are at the forefront of this change.

They talked about the rigid rules you typically come across when it comes to learning math and science. You might use a graphing calculator in a math class, but then use a different set of math "tools" in science class. At the end of the day, it's all just data and how you store and manipulate it to get the results you need. Instead of using a calculator in physics, perhaps you could write a simple program to solve the problem.

Kelly and Sean eventually developed a curriculum centered around Python. They don't teach Python to their students the way you might normally learn computer science at university. At university and in bootcamps, you're usually given the practical knowledge and skills to be proficient in solving problems. In middle school, students learn with their "entire being" while adults learn the concrete things to get the job done. It's all about making a connection with the students, according to Kelly and Sean

How does data fit into math and science classes?

Both science and math classes involve collecting and analyzing a lot of data. But how is that data stored and interpreted? Kelly and Sean talk about how the only class that involves storing data in tables is in science class. In math and science classes, you might draw graphs on paper or on a graphing calculator. But how do you go from that paradigm of teaching to millions of rows of data stored in a database? In math class, it's a bit tougher to integrate data subjects because the goal of the class is to eventually be good at calculus.

Teaching skills that are used in the workplace

One of my favorite Freakonomics episodes is #391 where Stephen Dubner talks to various experts and academics about the math curriculum taught at middle and high schools. The theme of the episode is that teachers are still teaching math like we are preparing students for going to the moon. In reality, students just need to learn how to use Excel, PowerPoint, and Google Sheets since these are the tools they would use every day in the workplace.

I used to lean more heavily on the side of teaching the practical skills in middle and high schools. From talking with Kelly and Sean, I've started shifting my position to somewhere in the mi...

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 specialist for Microsoft helping organizations with digital transformation. We discuss a specific data project involving vehicle telemetry data, when data and the real world collide, and selling data solutions into the enterprise.

Pulling performance data about bus fleets and trucks

Who knew that cars generated so much data? With all the sensors and chips installed in cars these days, data is constantly being generated in real-time? According to statista, 25GB of data is generated every hour by modern connected cars:

One of Victor's first roles in the data field was as a data engineer setting up data pipelines for collecting data from vehicles. The startup he worked at helped its customers collect telemetry data about their buses, trucks, and other vehicles. They would deliver insights from this data back to the customer. The North Star goal is to reduce the costs per kilometer by any means possible.

Vehicles have various apps and IoT devices collecting and producing data. The data collected would include how long is a car stopping in traffic? How often is the engine running? Victor's job involved building ETL/ELT pipelines to collect this raw data and transform it into a data model that could be used for analytics and reporting.

Don't sleep on data strategy and architecture

Before Victor could get into the fun parts of analyzing the data, he had to build out a data strategy and architecture. This is the part where you have to decide which tools is best for a specific part of the data pipeline.

Do you go with Google Cloud or Microsoft Azure? What is the best architecture? What's the most cost-effective solution? I remember when I was studying for the AWS Solutions Architect exam, I came across AWS' Well-Architected Framework. These are playbooks for picking the right tools (within the AWS ecosystem) for various use cases and scenarios:

In setting the data strategy and architecture, the main variable that affected Victor's decision was cost. His team first started with Google Cloud and piping data into BigQuery, Google Cloud's main data warehouse solution. All of the big data warehouse tools allow you to trial the platform before throwing all your data in. He found that BigQuery was the most cost effective solution, but collecting data in Google Cloud wasn't as great as other cloud providers.

The ultimate architecture looked something like this:

* Ingest billions of rows of data in Microsoft Azure* Pipe the data into Google Cloud Bigquery for data modeling and analytics* Use Tableau and PowerBI for data visualization

Finding insights from the data and delivering impact

Victor had all this data streaming into his multi-cloud architecture, so what happens next? He helped figure out what KPIs to tr...

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 with an NDA, Elena couldn't come on the podcast to talk about the data projects she was working last year. A few months ago, Elena became a principal data scientist at Superhuman, and is now able to share a little more about her experience at Peloton. As an avid user of Peloton's bike and app, I was extremely excited to dig into the types of projects Elena was working on that shaped Peloton's product roadmap. We get dive into the world of product analytics at Elena's former employer and at her current gig at Superhuman.

Joining Peloton as the first product analyst in 2018

Hard to believe, but Peloton was just a bike company when Elena first joined the company. Peloton was working on the Tread product at the time but it hadn't been released. The product team wanted to make more data-driven decisions to help inform what product features to build next. There was an existing business intelligence team who was building reports around sales and marketing campaigns, but no product analysts were there to help guide the product roadmap. Elena was hired in 2018 to help build a practice around product analytics.

Source: Buzzfeed

Surprisingly, Elena didn't really have direct product analytics experience. In her previous roles, she was more of a product owner and did things more akin to a product manager. It was an opportunity for Elena to define the purpose of the product analytics team and how they interacted with the rest of the business.

Answering data questions from the business

As one might imagine, the product team at Peloton is asking what features might increase repeat engagement on the app. It was Elena's job to answer these type of questions. Like many organizations, the analytics function will get inundated with questions and Elena found herself with a backlog of important questions to answer.

There are so many great quotes and gifs from Peloton to include in this post.

As Peloton grew, so did the number of questions. Elena started hiring a team and was leading the process for how questions get asked, who gets to ask these questions, and how the product analytics team engages with internal stakeholders. Elena eventually grew the product analytics team to 14. As the analytics team grew, Elena had to maintain a ratio of product managers and analysts as the product analytics manager.

Elena mentioned a few resources that helped her with learning about product analytics, engagement, and user activation:

* Sequoia Capital's "Building Data-Informed Products" Medium posts - Learn about growth accounting, stickiness metrics, and how to build a data team* Amplitude's Blog - Amplitude also happens to be the main product analytics vendor at Peloton* Reforge's Product Management courses - Paid course used by a lot of Silicon Valley product managers

Establishing cross-functional KPIs and metrics definitions

In addition to getting asked data questions, Elena's team was in charge of building dashboards showing stats about product usage and metrics. This series of events might also sound familiar to many of you who spend your days...

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 at Cal State Fullerton and didn't realize he had a passion for Biostatistics until his fellowship at Harvard. He is currently getting his PhD in Biostatistics at UCSD and is the youngest person to ever pursue a PhD in Biostats at UCSD. In this episode we talk about doing network analysis for the public health sector, facial/voice recognition, and Tyler's #1 strategy he thinks everyone should use to land their next job or internship in data.

Predicting HIV rates when you are missing data

As a neophyte to the data science and machine learning space, Tyler definitely veered into concepts that were quite foreign to me as he discusses his current PhD thesis. His thesis involves analyzing social networks knowing that there's a lot of missing data within the context of public health. We talk about why finding the HIV rate in a sample is different from other metrics you could get from a sample.

For instance, if you want to get the average height of people in the U.S., you pick a random sample of people, find the average height, and extrapolate this to the rest of the population (roughly). This is a straightforward analysis since each person's height is independent of each other.

In the case of public health, people are connected via social networks. With HIV, predicting whether someone tests positive or negative is dependent on the people you are connected with and whether those people have tested positive or negative. In this type of analysis there's a lot of bias and "non-parametric estimation of network properties," according to Tyler. I'm not even going to pretend I know what these terms mean. There's actually very little published work on this subject so Tyler's thesis would be adding a lot to the current research on this subject.

Source: Alteryx community

Training a voice and face machine learning model

Tyler has a history of working on one-of-a-kind projects. During his undergrad years, he worked on a project that combined face and voice recognition. Kind of like having a double authenticator system if you wanted to unlock an iPhone, for instance. Since you're combining both image and voice features to train a model, it creates a "highly dimensional problem."

Tyler helped with coding the project all in MATLAB. Given the tools and frameworks available, Tyler was pleasantly surprised to see the speed in which they were able to go from hypothesis to working app on this project.

Predicting "fragile" countries

During Tyler's research at Harvard, he worked on a project to help predict which countries will become "fragile." This is the definition of a "fragile state" according to the United States Institute of Peace:

Each fragile state is fragile in its own way, but they all face significant governance and economic challenges. In fragile states, governments lack legitimacy in the eyes of citizens, and institutions struggle or fail to provide basic public goods—security, justice, and rudimentary services—and to manage political conflicts peacefully. 

The project's aim was basically trying to predict which countries might become fragile in the future so that the governments could better plan for these...

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 make better decision. Not to say David doesn't get into the weeds when he has to (his GitHub profile tells that story), but it was interesting hearing how to build high performing data teams. We get into topics relating to data strategy, bundling and unbundling of data tools, and ways to be an effective manager.

Moving from data to product

For most of David's career, he's led business intelligence and analytics teams in various industries including retail, credit, and e-commerce. That's why I thought it was interesting when he moved into a product role at Avora.

As a leader of data teams, David was using various data tools to analyze, store, and transform data. Over time, you might start to see some of the deficiencies in these tools, and even have ideas for new product features. As an end user of some of these tools in my organization, I'm quite familiar with the little quirks of these tools that just make you shrug a little bit when you have to open the tool up in your browser.

David had been advising Avora on building various product features before moving full-time into the Chief Product Officer role at the company. In terms of transitioning to a product role, David had various PMs he could observe to learn the rituals of leading a product team like leading a standup and creating a product roadmap.

Source: Product Coalition

The great bundling debate

A few months ago, David wrote this blog post about the bundling and unbundling of tools for the data engineering profession. For those new to this world in data engineering (like me), this analogy sums it up nicely from the Analytics Engineering Roundup:

Unbundling means taking a platform that aggregates many different verticals, such as Craigslist, and splitting off one or more services into stand-alone businesses that are more focused on the experience of that niche customer base. Sometimes, as was the case with Airbnb, that experience is so much better it eclipses the market cap of the original business.Anna Filippova, Analytics Engineering Roundup

Taking this concept to the modern data stack, David's blog post discusses the unbundling of Airflow into many off-the-shelf and open-source tools. I've never worked in a data engineering role and was worried that this part of the discussion would go way over my head. I hear about ETL, streaming, and automation from being in the space but have only lightly touched some of these tools in this data stack:

Source: Analytics Engineering Roundup

David's blog post also discusses the role the data engineer plays in a world of unbundled software:

For most companies, the cost of hiring and keeping data engineers working on pipelines that could be built with specialist “unbundled” tooling is unwarranted. The real value is in the next steps of data-enabled decision making, whether human or automated: Is this sneaker actually the same as this other sneaker? Should we lend to this customer?

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 predict what the future might look like. Santiago Viquez is currently a data analytics mentor at Springboard, an education platform helping students prepare for new careers. On the side, Santiago has built a ton of cool projects related to data science, natural language processing, and more. In this conversation we dig into how Santiago learned data science from scratch during the pandemic, and how he thinks analysts should learn data science.

Santiago Viquez

Started at the bottom now we're at a multinational corporation

Santiago studied physics in Costa Rica, but realized he didn't want to pursue a career in physics. After doing some research, he realized a career in data analytics and data science would be more suitable. Having known a little bit of Python, he started applying to a few positions and eventually got his data analytics career started as an intern at a small startup. His internship turned into a full-time role as a data analyst which he kept for two years.

Santiago left the startup and went in the complete opposite direction in terms of company size. He had roles in data analysis and data science at large corporations like Walmart and UPS working remotely the entire time. During his time at Walmart, he started working part-time at Springboard helping students land careers in data analytics.

The experience working at a startup versus a large company is night and day. We've seen stories of people like Preksha in episode 85 and Lauren in episode 64 make completely new transitions to a career in data. But we don't hear about the data analytics professional who moves from startup to large company too often.

One example Santiago bought up is how corporations frame problems. You typically have clear success metrics, KPIs, stakeholders, and data sources to work with. At a startup, you are defining the problem by yourself. It's just you. You're in charge of collecting the data sources, providing analyses to key stakeholders, and owning the entire model or analysis end-to-end.

Reducing food waste for restaurants in Costa Rica with data science

When Santiago was a consultant, he was helping a big restaurant group in Costa Rica figure out ways to reduce food waste. The restaurant group consisted of 30-40 restaurants (which is big for Costa Rica). Each restaurant had its own manager and each manager would request food from various suppliers. The problem was that some managers were good at forecasting how much food they would need for the next 10-15 days, others were not so good.

Santiago's goal was to create a tool that would help each manager predict how much food to order from the suppliers. The first phase of the project was gathering data. In this case, Santiago had to get the recipes from each restaurant manager. These recipes were then joined with each restaurant's sales data to see the volume of ingredients required.

The interesting thing is that each recipe had to be...

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 University of Technology in the Netherlands, scoured all the emails that had Excel spreadsheets attached to them and analyzed 15,000 internal Enron spreadsheets to see what patterns existed in the models, formulas, and yes, the errors. After reading her paper, my opinion is that Enron's spreadsheet errors were not unique to Enron, but could happen at any large company.

24% of Enron's spreadsheets contain errors

One of the key takeaways from Hermans' paper that has been cited elsewhere is that 24% of the spreadsheets she analyzed contains an error. These are spreadsheets where there was a "runtime" error, so the errors you typically see when you divide by 0 or make an error in your formula. Here's a table from the report showing how many of these errors appears in these spreadsheets:

The sheer number of errors is startling:

In total, we have found 2,205 spreadsheets that contained at least one Excel error, which amounts to 24% of all spreadsheets with formulas (14% of all spreadsheets). They together contain 1,662,340 erroneous formulas (49,796 unique ones), which is 585.5 (17.5 unique ones) on average. There were 755 files with over a hundred errors, with the maximum number of errors in one file being 83,273.

755 files with over one hundred errors. It's easy to say the people creating these spreadsheets weren't skilled Excel users, or they were deliberately trying to make these errors. Putting the ethical argument aside, I'd say these Excel errors are only one side of the story.

A lot of times you might not have a finished model or haven't collected all the data, so there may be formula errors until the final model or report is finished. I don't believe Hermans scanned for the version of these files, so it's likely that many of these files were simply incomplete. I'd argue that this is the predicament at many companies. You have files at various stages of completeness or readiness, so looking at the files with only these "runtime" errors can only tell you so much about the intention of the person who worked on the spreadsheet.

Auditing Enron's files for accuracy beyond formulas with runtime errors

One type of formula error that wouldn't show up in Hermans research is the formula that references incorrect cells. This can be due to human error or lack of understanding of how the model should reflect the business.

In a separate study by Thomas Schmitz and Dietmar Jannach done in 2016, Shmitz and Jannach looked closely at formulas that didn't have normal runtime errors to see if they referenced incorrect cells. This is a much more difficult analysis because you have to know a bit about the business situation the model is based on, but most of the files in their analysis are quite straightforward.

Take, for instance, this Southpoint Gas Model which appears to show how much gas Transwestern (a pipeline subsidiary of Enron) is using on an hourly basis:

The Total Gas Usage column is a straightforward formula ...

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 a good data analyst. The key to good data analysis is a soft skill: asking good questions (see this episode with the co-founder of Mode, Benn Stancil). Having said that, there is one Excel feature that I learned early in my career that really helped me improve my data analysis: PivotTables. Why PivotTables and what features about PivotTables make them so good for data analysis? Read on for more. You can also download the Excel file used in this episode here.

Michael Jordan: an expert user of the pivot

Video tutorial of this episode:

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

1. Getting data to look right for PivotTables

This is a requirement for PivotTables that also teaches you important lessons about how to structure your data. It's tempting to see a long list of data and just say "throw a PivotTable on it!" and not think through if the PivotTable will actually "work." What I mean by this is that the underlying source data has to be laid out correctly in order for you to do any type of exploratory data analysis on your data set. Take this common layout of data you might see in a PowerPoint presentation or some deck (video game sales):

You have years (or some time period) along the columns and then some measure in the rows. Perfectly fine table of data for seeing sales, in this case, for different video game companies. Now there are a ton of other companies and devices to report on, so maybe for the purposes of data analysis, you want to put this into a PivotTable. Once you do that, this is what the PivotTable settings look like:

Not super useful for quickly seeing sales trends by year, seeing which devices had the most sales, etc. You have to "drag in" each year into the Columns field to see the actual sales.

Transforming and massaging the summary table

To make this data look right, it requires a bit of manual massaging of the data. There are ways you could automate this with a macro, but it's important to understand why the data should look this way before you "throw a PivotTable" on the data:

A few things to note about this dataset:

* Years are no longer across the top in the columns. It's just one column with the value being the year itself.* The sales metric is also its own column and not spread out across columns.

Given that more data you're analyzing is coming from databases, data will most likely be structured like this to begin with. But when you're manually aggregating data from reports or other sources in Excel and need to get it into a format ready for PivotTables, this is the transformation you need to do in order for it to look right for data analysis.

2.

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 through printed POS (proof-of-sale) receipts, and bombing meetings with clients when she was growing her startup. In addition to being a startup founder, Preksha was a 2021 LinkedIn Top Voice: Next Gen winner. We discuss how she utilized LinkedIn to grow her community, and how to be vulnerable to build trust with your community. Hopefully Preksha's story inspires you to pursue a career in data no matter where you got your start.

It begins with hospitality

Preksha started her career as a chef at a top hotel in India after studying hospitality management at university. What she liked about being a chef is that you have a set amount of ingredients in recipe and something good comes out at the end of the recipe. After becoming a sous chef, her responsibilities expanded beyond just creating food. She had to manage events, supplies, profitability, and ensure hotel guests were fed and happy.

The hours were brutal. Working from 7AM-12AM was not uncommon. The worst part was at the end of her shift, she had to use a POS (point-of-sale) system to make reports about the day's activity. The POS system was not connected to the Internet so she had to do a print out of every single receipt. She had to manually calculate how much butter chicken was sold that day, compute revenue, and do other tedious calculations by hand. This data would then get manually entered into a computer. This would push her shift to 2AM or 3AM some nights.

At the time, Preksha didn't know she was doing data analytics. The unfortunate part was that while technology was advancing all around her, it wasn't catching up with the restaurant and hospitality industry. She knew that data and processes should be digitized, but wasn't sure how or where to start.

The hospitality industry creates a ton of data

Preksha didn't realize it at the time, but when she was a sous chef she was doing data analyst work. She wanted to get into the data world and decided to join any startup would be willing to give her a job or internship. Any restaurant or hotel that is working with data would want to hire her, she thought.

The toughest part of this process is taking that one small step from your cushy job and willing to be a student or intern again. Preksha was managing a team of 30 and felt like she would be going backwards by starting over. On top of that, you have the peer pressure from friends and family.

She ultimately joined a startup that was working with restaurants and hotels by helping them create menus on iPads. As a business development manager, Preksha ate at 50 restaurants for 3 months but didn't make any sales. She ended up working with one account that wanted a website for their restaurant. The restaurant also wanted to keep track of their orders online. The startup she was working at was losing money and the developers were leaving. Preksha still felt like she wanted to deliver what she promised to this restaurant. During this time, she met her current co-founder and developed a data analytics platform for restaurants.

Turning into the data analytics team for restaurants

20 businesses started treating Preksha's startup as their in-house data analytics team. Preksha wanted to create a self-serve data platf...