Dear Analyst

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.

Alex 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…

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

Alex 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).

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

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

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

Alex 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:

Another option is to simply break out each line into its own mini chart:

The 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).

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

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.

When 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.”

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

One 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:

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

Ben 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:

Cognitive 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

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

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

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.

I 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:

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.

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

The 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?

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

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

Matt 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:

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

Following 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).

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

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.

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

Realistically, 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.

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

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 Ariely published 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.

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

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

This 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:

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

No 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 that there are no observations above 50,000.

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

This 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:

To recap what the data hacking looks like with this irregularity:

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.

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.

Ariely 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).

I’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.

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

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

I’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.

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

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

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

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.

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

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

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

Pair’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?

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.

The 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:

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

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

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

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