Dear Analyst

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 of how to analyze his photo metrics, he eventually decided to do a career change into analytics and received a Nanodegree in data analytics through Udemy. This career change coincided with the onset of the pandemic as his photography business started slowing down. We talk about Austin's experiences working for a big conglomerate to a startup, working with messy data, and how photography is just like data visualization because you're telling a story.

Marketing analytics at a startup vs. a big conglomerate

As you can imagine, working for a big company comes with its pros and cons. You have a ton of resources to tackle large problems and projects, but the change management can be quite the process. Multiple teams and stakeholders are involved, and changes can take months to years depending on the type of change you are trying to make.

Source: xkcd

Austin worked at Cox Automotive and talked about how the data stack at Cox Automotive was custom built years ago. That means even small changes to the system were very hard to adjust. Then you have the issue of clients wanting custom edits to their reporting. If you don't have a data engineering team that can build an infrastructure that allows analysts to edit reports on the fly, you'll start getting into being a consulting company providing custom data analytics solutions.

Austin moved to a startup called Blues Wireless where they built a robust data stack, but they didn't necessarily have the marketing team in mind when building out the stack. Product usage analytics were top of mind for the small but budding analytics team. Austin was brought in to coordinate web analytics projects so that the marketing funnel--from a website visitor to a conversion--could be better quantified. Getting accurate data, however, is paramount to this project because you can't make decisions on bad data.

Website data platforms fighting each other

Austin is currently enrolled in a data analytics masters program, and he talked about how cleaning "dirty" data is very different in the academic world vs. the real world. This is true for any discipline, I suppose. In the academic world, the problem space is confined and there are "right" answers, so to speak. In the real world, surprises and nuance are littered all over the place. In the marketing analytics world, you are working with a data format that a Google Analytics or some CRM platform forces upon you. In rare scenarios, cleaning data is as simple as using Python to get rid of a bunch of NULL and N/A values.

Austin realized that page data was being counted twice. I'm sure all of you have dealt with double-counting data and coming up with a system to de-duplicate data. The problem came down to Google Tag Manager fighting with Segment to report on page data for the website. Austin uses GA to view early marketing funnel activities and Segment pulls late marketing funnel activity.

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 data, and then enter the data onto the slide. It was super manual work. One benefit was I got really good at using keyboard shortcuts to filter a list of data. I didn't realize that a PivotTable could easily automate my report. I could've built several PivotTables off of my raw data, stuck each PivotTable on an individual worksheet, filter each PivotTable to the data I need, and I'm done.

PivotTables continue to be one of the most important features in Excel, and in this episode I walk through how to use Slicers, one of the best features for filtering your data in PivotTables. You can download the Excel workbook used in this episode here. I also just launched a new Advanced PivotTable Dashboard class on Skillshare which I'll talk about at the end.

Watch this episode to see a video tutorial on how to use Slicers in PivotTables

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

How to create a PivotTable using YouTuber data

The raw data (download file for this episode) we we are using for this episode is a list of the top 200 YouTubers and data associated with their channels. Interestingly, I don't recognize many of the channels on this list (or I just don't watch enough YouTube):

Let's create a PivotTable to better filter through this list of 200 YouTubers. It's not a huge list but maybe we'd like a quick way to see the top channels in a certain country, category, etc. On a Mac Excel, the easiest way is to go to the Insert menu, click on PivotTable, and then hit OK. You can keep all the settings in the menu as is since we want to create a PivotTable on a new worksheet:

If you don't want to start from scratch with building a PivotTable, you can have Excel suggest a PivotTable for you. Instead of clicking on the PivotTable button, click on "Recommended PivotTables" and you'll see a worksheet get created with the PivotTable fields filled out:

Building out PivotTable with filters, rows, and columns

Let's summarize our data in this PivotTable by finding the average number of Likes and Followers by Main Video Category and then Category. To set this up, your PivotTable fields should look like this:

The resulting PivotTable isn't well formatted as you can see below:

A few things we can do to fix the formatting so this PivotTable is a bit more clear:

* Currently the Values show "Sum of Likes" and "Sum of followers," and we want to change both of these Values to Average (since we want to find the average Followers and Likes)* Give consistent formatting to the numbers so that there are commas in the thousandths place and no decimals* Remove the "(blank)" option* Change the layout of the PivotTable to the "Classic PivotTable layout"

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 resale platform for some of the world’s top brands. Mel’s career has always been in tech, but she started as an industrial engineer. She discusses her path to leading the analytics team at Trove, how to build a culture of experimentation, and how to navigate a career in analytics.

Experimentation to drive decision making

While Mel was at Trove, her team led the charge on implementing an experimentation framework, including incorporating a third party A/B testing tool. People at Trove were hungry for data analysis and wanted to know how to make better product decisions using a new testing framework. The end result will be to give all PMs and analysts an easy way to run thoughtful experiments. In addition to developing this testing framework, Mel was also a big believer in how people internally would learn about testing. At the end of the day, PMs and analysts should be able to make better decisions.

Testing what works on a brand’s website when it comes to reselling is quite different from a traditional e-commerce site. “Recommerce” provides one customer with an experience for trading in their existing goods and another customer with buying a resale item. The product team may not have the luxury of seeing what competitors are doing since there might only be one color or one size of a product.

Pursuing an analytics degree at university and pivoting into data analytics

Mel talked at length about how people like herself have stumbled into data analytics. For Mel, she thought this was the only way to do it since there wasn’t a data analytics “major” when she was in college. I completely agree with her on this point since the only data analytics experience I received was on the job.

Now you can major in specific areas of data analytics like data science and machine learning. If you major in data analytics, your career path is a little more straightforward. One thing Mel pointed out is that datasets are never as clean as what you see in school. When you’re working with real world data, most of the time you’re just cleansing data to get the data in a usable format.

Source: I-O Psych Memes on Twitter

Mel gave some great advice for those who want to learn a specific skill or tool like Python or Excel. Look for a larger company that has an entire analytics department built out where you can specialize. You probably won’t have to do as much data cleaning compared to the data you might come across at a startup. A benefit of being at a startup, according to Mel, is that you can figure out where you would thrive but wearing multiple hats.

Other Podcasts & Blog Posts

No other podcasts mentioned in this episode!

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 early stage data science, applied AI, and machine learning startups. I don't normally hear or read about the investor's perspective in the data space, so this episode was quite the learning opportunity. You'll also hear about some of the data startups David's firm has invested in and what their unique value propositions are.

Mainframes, Tableau dashboards, and the modern data stack

David started his career in actuarial science and finance information systems. He originally worked at Aflac on their mainframes. At the time, the "modern" data stack included tools like Qlik, SAP Crystal Reports, and of course, SQL. David eventually moved to Tableau and was building dashboards for his team. After stints in the banking world at Citi and Deutsche, David moved to NYC and started working a lot with Python and R. He was a lead data science instructor at General Assembly and eventually landed at Galvanize as the data science team lead. I've been an instructor and have gone to events for both GA and Galvanize and encourage you to check out both organizations if you would like to up-level your data skills.

Galvanize co-working space. Source: ERIC LAIGNEL

David currently works full-time at SingleStore as a senior manager of technical enablement. Saying he works "full-time" for SingleStore is not an accurate characterization of what David does day-to-day since he wears many hats. He is also runs a venture capital fund called DataPower Ventures and hosts an artificial intelligence podcast called HumAIn. As a believer in side hustles, I think David shows no side hustle is too small or big to take on!

Evolution of the modern data stack

David shared his perspective on the modern data stack and the key takeaway is (surprise surprise) Excel is not going anywhere. Old and new platforms still have integrations with Excel. David rattled off a few including Refinitiv, Quickbooks, and Bloomberg. As an analyst, you have so many tools in the data stack that allow you to work with data. With ETL or ELT, you can import/export your data tables and schemas into another tool (like Excel) to do the actual analysis. This is where tools like Fivetran and dbt really shine to help you get your data into the right destination. The data can be in a low-code tool where you drag-and-drop tables and schemas or even in a Jupyter notebook. Once the analysis is done, you have visualization tools like Power BI and Looker to help you communicate your findings.

Source: David Jayatillake

The above modern data stack diagram comes from David Jayatillake's substack newsletter. To hear about other tools in the data stack, I'd recommend listening to David's episode or this episode with Priyanka Somrah from Work-Bench.

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 each episode are summarized through this blog and in the LinkedIn newsletter.

Source: scaffmag

I started publishing episodes more regularly during the pandemic because I was always at home and what else was there to do? Perhaps I was also fulfilling my millennial obligation to have my own podcast as well. This episode is a look back at some of my favorite episodes and I'll include snippets of these older episodes as well. To all you data nerds out there, thank you for listening to the podcast and reading the show notes. I could say something trite like "I'm doing this all for you." The reality is I do it for myself to explore topics I'm interested in :).

1. Episode #1: The inaugural episode about paste special values in Excel

Episode #1

The first episode was published on March 3rd, 2019. I had no idea what I was doing (I still don't). At the time, I thought talking about a relatively technical subject where most people learn via video would be a good idea. Was I ever this young and naive? I had started working at Coda less than a year at this point and was creating content at work comparing Excel and Coda. Needless to say, I was questioning a lot of the common behaviors we do in Excel and paste special values was one of them. I pretty much just read the original blog post I wrote about this subject and framed it as a "why" paste special values is not that great versus "how" to do paste special values.

Source: Super User

2. Episode #28: Filling values down to the last row of data

Explaining formulas and technical concepts over audio is tricky. As a listener, you're trying to visualize the Excel file I'm explaining. This episode is deep in the weeds of how to fill values down to the last row of your data. The reason why this is one of my favorite episodes is because the blog post with the show notes has become one of the most trafficked posts on thekeycuts.com. I guess a lot of people are looking for ways to fill their values down? Prior to this episode/blog post, the post with the most views is this post on how to split costs with friends (published in 2014). From an SEO perspective, I didn't expect this blog post to get so much search traffic in such a short period of time.

3. Episode #50: First guest on Dear Analyst, Shawn Wang

Episode #50 was also a big milestone for the podcast.

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 computer science at Georgetown, and honed his modeling skills at Credit Suisse after graduation focusing on the oil and gas sector. At Uber, Ryan helped scale the Uber Eats product and worked on various moonshot products like Uber Elevate and Uber JUMP. After a stint at an AI gaming company, Ryan eventually joined AI Fund, an AI startup studio founded by Andrew Ng (founder of Google Brain and co-founder of Coursera). This episode follow Ryan's journey writing VBA as a banker, challenging the status quo at Uber Eats, and diving into the world of AI.

VBA is the gateway drug to automation

If you're in Excel long enough, you'll eventually come across VBA macros. Either you use the ones your colleagues build or you dive into writing/recording the macro yourself. The reason I think VBA is the gateway to automation, generally, is because you are able to break Excel down into its building blocks. Furthermore, you can tie other Microsoft apps together using VBA. When I was a financial analyst, I automated the creation of reports in Excel and PowerPoint with the push of a button. That feeling was powerful and extremely gratifying. The next obvious question is: what else can be automated? You don't realize it at the time, but the object-oriented programming skills you learn from VBA can be carried into web development, Python scripting, and more.

Source: The Register

While at Georgetown, Ryan was introduced to VBA and quickly started using VBA in all of his classes since he was in Excel all the time. VBA is not the most popular programming language to learn. According to this source, it's popularity has decreased 60% since 2004. Eventually, Microsoft's Office Scripts (a flavor of TypeScript) should, in theory, replace VBA. But for those old Excel macros from the early 2000s, someone has to maintain or migrate those scripts.

In addition to VBA, Ryan taught himself Python in his free time while at university. Python wasn't in the regular curriculum at Georgetown, so Ryan learned through Codeacademy. He built programs to scrape Twitter to calculate sentiment in Tweets. When Dogecoin came onto the scene in 2013, Ryan built a mini super computer with four Raspberry Pis to mine Dogecoin. He wrote a Python script to tie everything together so that he would get a Tweet when he successfully mined some Dogecoin.

Source: Coincu News

M&A modeling at Credit Suisse

An investment banking job right out of college used be a coveted position since you get to learn a lot of analytical skills on the job. When Ryan first got into banking, he tried to get as much as he could out of the experience. He broke down his experience into three mental models:

* Perception - This is the part of the job where you're doing the actual work. Modeling, analyzing financial statements, spreading comps, and putting together decks. You're mostly being paid to execute,

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 had a knack for building empathy for the end consumer. This empathy would drive how she approached her designs. People assumed Wacarra didn't "know" data given her design background. She quickly saw how her creative ideas could lead to clicks, engagement, and conversions in the classic marketing funnel. This led her to want to build up her data skills, use her design skills in her data visualizations, and eventually creating a fun product for Excel nerds :).

Source: Emarsys

Building customer profiles with Excel, Google Sheets, and more

One of Wacarra's current projects is building customer profiles and journeys. Wacarra works at Showpad, a sales enablement platform. She's essentially looking at tones of Salesforce data to build out these customer journeys. A customer journey tells the story of how a potential customer interacts with your product or service. You analyze how much customers spend, where they work, etc. As Wacarra dug deeper into Salesforce and other data sources, she realized she could combine different data points about companies together.

Outside of Salesforce, Wacarra looked at NPS score and product usage data. The unique identifier was simply the company name. Since there wasn't hundreds of thousands of rows of data, Wacarra united everything in Excel. From there, she built PivotTables with calculated fields to view the company data differently. She also mentioned a Salesforce Google Sheet add-on which allows you to pull Salesforce data directly into Google Sheets.

Once the data analysis was done in Excel, Wacarra's design skills came into play. She created PDF versions of all the customer journeys and profiles to make the data visualization nicer. Being able to communicate the findings from the data is a huge part of the data analysis process. Wacarra loves the data storytelling aspect of her job because it blends her creative and data skills together.

What is the impact of these customer profiles and journeys? One data point she brought up during our conversation is that she was able to show all the different software and platforms customers are using. This helped the product team prioritize certain integrations in the Showpad platform. When new hires join the company, they can quickly understand who Showpad's target customers through these visualizations. Data about the customers is just one side of the story. Customer profiles typically blend quantitative and qualitative data to tell a story about the customer.

Source: UX Planet

Data storytelling through Excel files with 250+ tabs for Abercrombie & Fitch

When Wacarra was in the agency world, one of her clients was Abercrombie & Fitch. As an "experience strategist," her goal was to advise Abercrombie & Fitch on how to improve the customer experience. She was essentially creating a different form of u...

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 with data. Prior to her current role as an independent consultant, she worked on different data challenges faced by operations, marketing, and customer support functions. Eventually, she managed a data engineering team focused on the data platform and infrastructure. From speaking with Sarah, it conjured up memories of working with bad data, manual data tasks, and playing the role of a mediator for your business stakeholders. We also chat about a popular blog post Sarah wrote on SaaS debt.

Building and maintaining a homegrown data pipeline

Sarah's last role before striking it off on her own was at Perpay, a financial services company focused on the buy now/pay later space. The company is a data-driven organization (as are most companies these days). The data that Sarah's team was looking at was all marketing data. Specifically, data that influencers customer conversion rates. The problem that they were trying to solve was how the marketing team could send more personalized emails and messages to potential customers to get them to convert.

The marketing team originally used a tool called Iterable where you send customer data to the platform and the platform would know when to send the right customized email. For instance, abandoned cart e-mails are super effective at increasing conversions and Iterable could help with this task.

The data engineering team's goal was to figure out how to get data about the customer and get it into Iterable. This is a classic data activation scenario. Over time, Sarah's team started building a solution in-house. The biggest challenge was getting the data out of the data warehouse and having it notify Iterable's API. As the the use case for Iterable and the in-house solution grew, the data engineering team had to constantly figure out what was in Iterable and checking diffs (seeing what changed from the previous state to the current state) to debug issues. Eventually the team moved to a paid solution called Census to help with the movement of data from the data warehouse to Iterable. Sarah reflected on the evolution of the solution:

At a startup you have to be ruthless with prioritization. I realized that the data eng team was spending too much time maintaining this in-house solution. This stood out to me as a generic problem where you spend hours per month maintaining the system. When is the cost of the paid solution cheaper than the hours required for maintaining something in-house?

Automating a manual forecasting process with SQL scripts

Sarah was also a quantitative analyst at OneMain, a private lender in the fintech space. The affiliate marketing team was responsible for marketing loans so that they show up on sites like NerdWallet, Credit Karma, and Lending Club. The problem was how to increase conversions by reducing costs--another very common marketing problem that can be solved with bette data. Sarah's team was in charge of forecasting metrics like cost per loan and cost per conversion for these affiliate marketing chann...

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 started his career working at a consumer electronics manufacturer. He started building his data chops as an inventory analyst and eventually got into the world of Oracle database migrations and application development. This episode explores a perspective on data we don't normally see: from the DBA. I think it's important to understand this perspective since data and business analysts ultimately use the data that is transformed and formatted by DBAs.

I remember saying I will never be a DBA

Sean likes to poke fun at the DBA crowd and remembers telling someone at a party he would never become a DBA. Perhaps his tongue-and-cheek attitude towards DBAs is what makes him so successful as a DBA. He currently works at a company called Viscosity where he does database and application development.

In short, I solve puzzles.

Sean explains how his background in data analysis and DevOps has helped him in his career as a DBA. This area of data is beyond my area of expertise, but Sean was able to relate things back to why this area matters for data analysts. Despite being in a "technical" role, Sean discusses the other qualities that make a DBA (or anyone in a technical role) successful:

The best technical people I've met have had great people and business skills.

How data analysts can work with DBAs better

What does DBA stand for? "Don't bother asking." That's Sean's favorite DBA joke. From a data or business analyst perspective, Sean says DBAs are typically seen as people who restrict access to data. DBAs can sometimes be seen as barriers or just standing in the way. Sean's advice to data analysts and the consumers of data in organizations is to help change the perception of what DBAs do. I love this extremely outdated video explaining what DBAs do:

https://www.youtube.com/watch?v=74j_foRlM5U

Sean says many DBAs fail to see the difference between data and databases. Many tend to mix the two together, but Sean believes these two concepts should be thought about and treated differently. Data analysts should seek to work with DBAs to understand where their data comes from. This leads to an important concept I haven't heard of until this conversation with Sean: data as code.

Data as code leading to a diversity of ideas

Sean says that DBAs may think of data as being very fragile and brittle. They have this perception that data needs to be restricted or else it might be deleted when it's in the wrong hands. That's because DBAs aren't thinking of data like other parts of the DevOps process.

Infrastructure as code has become a well-known concept as DevOps engineers manage data centers in the cloud, why can't this same concept be applied to data? We can apply automation and configuration to the management of data. DevOps is typically concerned with storage and networking. The data lifecycle and pipeline can also be added to this list to "harden" data for the enterprise.

Now the actual nuts and bolts of this stuff is way beyond my pay grade. The benefits to data analysts, according to Sean, are plentiful. Analysts typically just analyze the data but don't have much experience managing the data on their own.

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 to why people should and shouldn't use Google Sheets. I'm not going to list all the features and do a pros/cons list. This episode is more about how software (in this case Google Sheets) makes you feel. Yup, that's right. The gooey emotions you feel from watching a rom com can also be applied to the software you use at work. This could be a stretch, but come along for the ride and let's see where the rabbit hole takes us.

Reliability over innovation

The title of this post is a rip off of the old adage "Nobody gets fired for buying an IBM." In the early to mid-20th century, IBM was selling their computers and mainframes to the government and the enterprise. They were seen as innovators and consequently grew to one of the largest corporations during that time. Their brand was associated with providing excellent customer service and of course top of the line machines.

Source: Science Photo Library

Over time, the innovation slowed at IBM. Nevertheless, companies continued buying from IBM. The brand and product was seen as a reliable choice over startups and new entrants. In a nutshell, you could de-risk the decision by going with a vendor that everyone else knew and trusted.

Fast forward to the present, what are the IBMs of our generation? Microsoft Office? The Google Suite? Do we reach for Google Sheets because we know everyone else on our team uses it or because it's actually the best tool to get the job done? I think it's mix of both.

Just good enough to get the job done

Maybe your company doesn't have an Office 365 subscription and you need a little bit more of those collaborative and sharing features. So you reach for Google Sheets. It mimics enough of Excel's features such that you're comfortable with putting important company data in it. Like Excel, it's the most utilitarian tool and all you care about is that the formulas work and that Google Sheets doesn't go down. You don't want to feel anything from using Google Sheets. It's just good enough to be your CRM or event planner.

What it's actually great for is modeling and financial analysis to help you make a decision. So it's a decision-making tool. But since so much of our work is about making non-financial decisions, we use Google Sheets for everything because it's just good enough. We're ok with it not having the bells and whistles of other tools.

That's the rub. Good enough. It doesn't excite us like getting a match on Tinder does. It doesn't tickle our senses like a sizzling steak does when it comes out of the kitchen. Should we feel ok with "settling" with something we have to use every day? More data is being generated from our products and services and is the only way to make sense of it all is to put it into Google Sheets?

For most people, settling with Google Sheets is absolutely fine. You don't want your spreadsheet to incite any emotions because it's there to do one job and one job only. Keep your data organized and help you make decisions. Anything more or less is a waste of your time.

The allure of free

No matter what function you work in, chances are you have to export your data from Salesforce, Jira, or some other internal tool into a Google Sheet. Your data is "trapped" in these SaaS tools and it's difficult to make decisions about what bugs to work on or which email...