Dear Analyst

Transforming Netlify's data pipeline one SQL statement at a time. Lauren Adabie started her career analyzing data and answering questions about the data at Zynga. As a data analyst at Netlify, she's doing more than just exploratory analysis. She's also helping build out Netlify's revenue data pipeline; something she's never done before. We discuss how her team is transforming data with SQL, how to get her stakeholders to have confidence in the data, and the path that led her to a career in data analytics.

Re-architecting a Revenue Pipeline

Lauren joined the Netlify team near the beginning of this revenue pipeline project project. Currently the pipeline is a combination of a few workflows. There are hourly processes to export the data to CSVs and Databricks jobs to load and aggregate data and then producing topic-specific tables. Lauren is currently helping migrate this workflow to dbt. With the current pipeline, if there's failure downstream, it's hard to find when and where the failure is happening.

Lauren's first task was bringing raw data into the "staging" layer (data lake). She initially tackled it by pulling all data into the staging layer right away. Looking back, she would have done it differently now that she knows more about the tools and processes. The goal is to help her team monitor and catch issues before it reaches the business stakeholders. As we saw with Canva's data pipeline, the benefit for the data team and the people who rely on the data is saving frustration and time.

A good data pipeline is one that doesn't have many issues. More importantly, when issues do come up, it should be very easy for the data team to diagnose the issue. This impact of this revenue pipeline project is reducing time spent triaging issues, increase speed and ease at accessing data, and analyzing data at various levels. Additionally, the team can decrease communication difficulties with a a version-controlled dictionary of their metrics (similar to the data dictionary Education Perfect is creating). 

Learning the tools of the trade

As a data analyst, you may not be diving into GitHub and the various workflows engineers typically use for reviewing and pushing code. Lauren's team is a huge proponent of Github Issues to manage internal processes (she had an outstanding GitHub issue to work on as we were speaking). If engineers add new products to Netfliy's product line, they add a new GitHub issue for Lauren's team to address.

I was curious how Lauren gained the skills for some of the tools she uses every day. When you think of the tools a data analyst uses, you might think of Excel, SQL, R, etc. These are not necessarily tools or platforms you take classes for in college, so what was Lauren's learning path?

Lauren has learned most tools on the job. She learned Python after graduating college.

I learned [python] partially because I was trying to do things in Excel that were frustrating. I was pushing Excel to do too much with VLOOKUPs, references, etc.

Here's a tool you don't hear every day: in college, Lauren learned Fortran 90 because people in her environmental engineering department were still using this programming language. She ended up learning SQL solely because she wanted to go into analytics and learned from a bo...

Numerous studies claim that data scientists spend too much time cleaning and preparing data (although this article claims it is a bullshit measure). I agree with some points in that article in that you should get your hands dirty with cleaning data to understand what eventually goes into the analysis. You may already be cleaning up messy data today with Power Query, an add-in for Excel ten years ago which now is its own standalone application. For those who don't have Office 365, a recent version of Excel, or a Mac, what tool can you use for cleaning up data? The main tool I've been using is OpenRefine. The main reason I reach for this tool: it's free. It's like Power Query for the masses. I've been wanting to do this episode for a while, so get your messy dataset ready. The Google Sheet for the examples in this episode is here.

If you want to watch just the tutorial portion of this episode, see the video below:

https://www.youtube.com/watch?v=0Fyh5x1QjMs

OpenRefine history

You can read more about OpenRefine's history on this blog post. The tool started as an open source project in 2010 before Google bought the company that created the tool (Metaweb). The tool was renamed to Google Refine for two years, but Google eventually stopped supporting it in 2012. The blog post cites a few reasons why Google stopped supporting the tool. I think one of the main reasons is that it's a desktop application and not run in the cloud. This probably conflicted with Google's own cloud ambitions for what is now Google Cloud Platform where they have data cleaning tools all in the cloud.

Since Google dropped support in 2012, it's exciting to see a good number of contributors to the project and an active mailing list. One feature I think that will keep OpenRefine relevant among analysts and data scientists who need to clean their data is the reconciliation service (similar to Excel's rich data types). More on this later.

Clean messy data, not organize clean data

As I've been using OpenRefine over the years, I've found that I reach for OpenRefine for specific use cases. It doesn't aim to be an all-in-one tool. When you first launch OpenRefine, you'll see the main tagline for the tool in the top left:

A power tool for working with messy data.

It just says it like it is. It doesn't do PivotTables, charts, or other things you might find in a spreadsheet tool. It does one thing and one thing well: clean messy data. I also think it does a good job of exploring outliers of your data, but it's all in service of ridding your dataset of inconsistencies.

This post from Alex Petralia is a good read with regards to how you should think about OpenRefine:

In fact, what differentiates clean data from messy data is not organizational structure but data consistency. While clean datasets may not be organized as you’d like (eg. datetimes are stored as strings), they are at least consistent. Messy datasets, on the other hand, are defined by inconsistency: typos abound and there is no standardization on how the data should be input.

This episode is all about data storytelling at a "traditional" enterprise company like LinkedIn and also at a major news publication. Janie Ho is a former global account analyst at LinkedIn in NYC where she facilitated data-driven presentations to close revenue deals for LinkedIn's top global strategic accounts. Currently, she is a senior editor in growth and audience at the New York Daily News under Tribune Publishing. This episode goes into best practices for creating data-driven presentations, learning new skills in non-traditional methods, and tools journalists use to find new stories to pursue.

Upleveling skills: from SEO to data

As a former journalist at various publications like ABC News and Businessweek, Janie forged a non-traditional path to a career in data.

In New York there was a popular platform called Mediabistro where they held these one-night courses. Many of them were free, and Janie took as many free courses as she could. She took many courses on SEO, and her SEO skills ended up being her gateway into data analytics.

I always find it interesting how people from all different backgrounds end up getting into data whether it's learning Excel, SQL, or some other data tool. It further shows that no matter what your role is, you will come across a spreadsheet at one point or another. In the world of SEO, you have tons of data around keyword performance, traffic estimates, rank, and more to play with.

LinkedIn: an enterprise behemoth

Janie eventually found herself at LinkedIn in 2011 as the first analyst in her group focused on global revenue accounts. When she left LinkedIn three years later, there were 50 analysts. Most of the analysts were recruited from management consulting so these analysts most likely had some data experience. Luckily, LinkedIn emphasized professional development so Janie was able to not only learn data skills, but also how to build data-driven presentations.

Most people don't realize that LinkedIn is expensive enterprise software that powers a lot of hiring functions around the world. Seats for the software cost $10K/year and above. When Janie joined LinkedIn, the company was in high-growth mode since there was so much demand for the product on the enterprise side.

LinkedIn was basically hiring salespeople as fast as they could, and the salespeople were expected to start selling the next day. There wasn't an extended onboarding period; they just needed people to sell. With all these salespeople doing QBRs and creating new pitch decks for the C-suite, LinkedIn needed many analysts like Janie to help produce these presentations at a fast rate.

Concise business review data presentations

In order to create these presentations, Janie and her fellow analysts were basically downloading LinkedIn usage data and slicing and dicing the data in Excel. She had to show LinkedIn's top strategic clients how things are going during these QBRs, but also what the opportunities are to spend more on LinkedIn.

Internally, LinkedIn had a program called Data-Driven University which was created by former Bain consultants. Janie would learn the key data storytelling skills from this "university" and turn around and train salespeople. Some examples of slides that Janie would create are below. These are the "after" slides that show how the data could tell a better story where there's only one key takeaway per slide:

Oz is one of the best creators of Excel content I know with his Excel on Fire YouTube channel. Unlike traditional "how-to" videos, his videos blend education with entertainment making the learning process feel like binging your favorite Netflix show. Oz and I met on Google+ way back in the day and in person at the 2014 Modeloff competition. While Oz is an Excel MVP and Excel trainer on LinkedIn, our conversation goes deeper into data literacy and understanding where your data is coming from before it gets into the spreadsheet.

Know just enough Excel to get your job done

When I first met Oz at the Modeloff in 2014, he told me a story about how he discovered the power of Excel for changing people's lives. This story really shows the human side of a spreadsheet program that is typically associated with business and enterprise use.

Oz was teaching Excel at a medical school and helping the students in his class automate their reports. He met one student who was simply copying and pasting cells up and down the spreadsheet, and was spending an hour doing these manual operations. He realized the student just needed one formula to automate the task she was doing, she just didn't know what that formula was.

I started learning about people who needed to know how to use certain features in Excel, but didn't need to know how to learn how to use everything in Excel.

Once the student saw how the formula could eliminate all the tedious work she was doing, it changed how she worked and gave her so much more time to focus on more important aspects of her job.

I think a lot of people approach their tools and software with a similar mindset. You know there is probably a better or faster way of doing something, but you go with what you know. There's a bit of the JTBD (jobs-to-be-done) framework here. Knowledge workers need to know just enough to solve the problems they face on the job, and can leave the rest of the software's feature set for the power users.

You'll work with data no matter what role you have

Prior to our conversation, Oz mentioned to me he wanted to talk about more than just Excel tips and tricks. These topics are covered at nauseum by other content creators; and for good measure as people need and want this training (yours truly has benefited from creating this type of content). What really tickles my fancy are the topics surrounding Excel, and there is no one better to go in-depth with me on these topics than Oz.

Analyst might not be in your title.

Nonetheless, you are or will be sorting, filtering, and summarizing data no matter what department or level your work in. Excel is merely a tool to get you from the raw data to the story you tell to your internal stakeholders to launch X feature or to external clients to purchase your product.

Oz talks about how people taking an Excel class will get them feeling comfortable about using the tool, but it only goes so far. As you get real world experience, you'll start to ask questions about data quality and the data source(s). These are topics that go beyond Excel and into the realm of databases, data transformation, and data pipelines; topics I'm trying to cover more of on this podcast.

Oz opined about the dilemma one faces with duplicate data. Do you de-duplicate at the source (perhaps in a view in a database) or do you do it in the spreads...

It's a story we've all heard before. You're working a full-time job, and you have more fun doing your side hustle than your 9 to 5. This is what happened to John Michaloudis. He was a financial controller at General Electric but found his passion sharing Excel tips and tricks on an internal GE newsletter which his colleagues ate up. John decided to become an entrepreneur and built an Excel training company from the ground up. We chatted about how he got started, his favorite marketing tactics, and of course, why he loves Excel.

10,000 followers on an internal company blog

At General Electric, there was an internal blog called Colab where employees could could write and publish articles only for GE employees to see. As a financial controller, John became well-versed in Excel and decided to contribute to the internal blog. He started posting Excel tips, and eventually he had a weekly column just devoted to being better at Excel.

GE's Colab

John quickly amassed more than 10,000 subscribers to his column as he saw how hungry people were for Excel knowledge. But it was only his side gig at GE.

I liked doing the blog more than my actual job. I felt the subscribers valued me more than my boss valued me.

After getting this positive feedback from his colleagues around the world, he wanted to find a way to take his Excel column to the next level. For the next 12 months, he went off and created a course all about PivotTables. He asked his boss at the time if he could sell his course to the 10K subscribers to his column, but of course compliance told him no. He decided to leave GE, and as a last salvo sent out a message to his followers about a webinar he was going to host about his PivotTables course.

Creating a library of Excel content

Based on the feedback he got from his subscribers to his weekly Excel column, John was able to find a few topics to build additional Excel classes about. A perennial favorite of mine, keyboard shortcuts were high on the list. Creating charts was also a big topics since most of his now students work at companies, and presenting data in a compelling way is important.

John is constantly learning new Excel features but ultimately the content he produces is determined by what his students, the customers, want to learn. He periodically sends his students a survey and asks them what they want to learn about. These topics are what you'll see on MyExcelOnline, John's Excel training company.

Taking the leap to become an entrepreneur

While the idea of going off on your own and being your boss is a romantic one, for many the decision is a matter of dollars and cents. John was (and currently still is) working in Spain, and started earning a few grand from offering his courses on Udemy. He realized this was enough for him and his family to live on, and went full-time on his training company in January 2015.

His advice for aspiring entrepreneurs is don't just leave with nothing. Create a product and test it out. Use cheap methods like Adwords to validate your idea 4-Hour Workweek style.

On Udemy, John's PivotTable course originally would earn him about $2K/month but this went up to $7K/month. The problem was he was also selling his course on his website for $290. If his customers who bought the course fro his website found out they could get it cheaper on Udemy,

The NY Enterprise Tech meetup was one of my favorite events to attend in-person prior to the pandemic. While the meetup is now all virtual, the speakers they bring in continue to be top-notch--particularly in the data space. The host of the meetup is Work-Bench, a VC focused on enterprise software companies. Priyanka Somrah is a VC analyst at Work-Bench who speaks with the most innovative startups building data tools on a weekly basis. In this episode, Priyanka talks about how data tools can effectively sell into the enterprise, how the data engineering profession has grown in the last few years, and the most effective Go-to-Market (GTM) strategies for data companies.

Note: There were some recording issues with this episode so apologies in advance for some of the gaps in the conversation as well as the background noise.

Flipping VC on its head

Work-bench is early-stage enterprise VC firm that flips the VC model upside down. Unlike most VCs that go off and try to source the most innovative companies, Work-Bench hosts quarterly corporate roundtables with Fortune 500 companies. From speaking with the end customer of these data infrastructure and engineering tools, Work-Bench finds the pain points these large enterprises face. From there, they go and find the companies that offer a solution to these problems.

This aligns with what I hear a lot in the B2B SaaS space in terms of creating an effective marketing strategy. Paint point selling or solution selling are some buzzwords you might hear in this regard. Instead of selling the features of your product, you focus on the problems your product solves for your target customer. Seems like a reasonable strategy marketing and sales people should adopt, but all the cold spam InMails and connections on LinkedIn might convince you otherwise.

How to become enterprise-ready

One of the main challenges for data infrastructure tools is selling into the enterprise. A company's data is one of the most important assets the company has, and Priyanka provides some best practices on how data tools can better prepare themselves to be a legitimate enterprise solution:

* Security - Being able to safeguard the customer's data is priority number one. This means getting SOC 2 certification or offering a single sign-on feature.* Scale - Can the tool reduce the administrative overhead and grow and scale as the customer's requirements grow and scale* Flexibility - This is becoming a table stakes feature. Is the tool modular enough to integrate with a customer's existing tool stack?

Source: Work-Bench SOC 2 Playbook

Assuming the data tool meets these enterprise standards, Work-Bench then acts as the matchmaker between the various Fortune 500 companies they are connected with and the startup that provides a relevant solution.

Big data and the rise of data engineering

Big data has really changed the face of the world. As a result, the ETL process and data modeling have changed considerably as well. With tools and processes changing, the skills and expertise required by data professionals needs to adapt. We saw with the last episode about

Data warehouses have come a long way since the days of Oracle and Microstrategy. A data warehouse should be able to grow and expand with the business it supports. At Canva, the data amount of data coming into the data warehouse has exploded in the last few years given the platform's surging usage. In this episode, Krisha Naidu, a data engineer at Canva, talks about how his team is making it easier for analysts to get the data they need and the tooling to analyze data. The goal of the data warehouse team at Canva is to maintain reliability, improve access and tooling, and oversee compliance with regulations. At the end of the episode, we also discuss our mutual love for keyboard shortcuts.

A design tool for the rest of us

I never considered myself a "good" designer or artist. I still feel lost sometimes in Photoshop and Figma, but Canva makes the design process super seamless for a newbie like myself. I use the tool at least once a week for a variety of use cases. All the thumbnails on my YouTube channel were created on Canva because I can create a decent design in five minutes or less.

Krishna didn't use Canva before he joined the company. His first foray into Canva was creating a birthday invitation for his daughter. But he quickly saw the power and potential of Canva after seeing his family members use the tool and a family friend who uses Canva for creating marketing brochures. Once Krishna joined Canva, the scope of the mission became clear to him. It's not just about making design easy, but also giving people the ability to get their designs seen by the right people. Like many other SaaS tools, Canva has also added more collaboration features as more teams become distributed.

Canva invitations

Structure of Canva's data team

Given Canva's size (1,500+ employees according to LinkedIn), the data team is quite mature relative to other SaaS companies. They have data analysts, scientists, and engineers.

The data engineering team (where Krishna works) is broken out into three sub-teams:

* Streaming - Internally this team is know as Canvalytics and they focus on capturing all the clickstream data from the product. This team helps Krishna's team with getting data into the data warehouse.* Platforms - They manage the data lake and tooling for data scientists* Data Warehouse - This is Krishna's team, and they provide tooling for the users of the data warehouse. They also enforce controls and governance of the data warehouse, and their primary business stakeholders are Canva's data anlaysts.

The data coming into the data warehouse is constantly growing which is a good sign because it means the number of Canva users is growing. On top of that, new product features being added to the platform means more clickstream data needs to be captured and transformed in the data warehouse. To better cope with the expanding data footprint, Krishna's team has architected some interesting solutions to cope with the company's growth.

A sandboxed build environment for analysts

When the data team was smaller, it was easier for all analysts to work in the same data warehouse environment. If an analyst made a change to a dataset, then they might work with the data engineering team to roll the change out and that change would be communicated out to the rest of the analysts.

With more analysts, it becomes easier to step on each others' toes since one analyst might make a change on one dataset (where they are building and testing their models),

One of the best feelings is knowing you've streamlined and automated a report such that your colleague doesn't have to spend hours creating the report every week. Whether you automate the report through a bunch of Excel formulas or setting up some sort of data pipeline, analysts are always thinking about driving internal efficiencies. This was one of my first jobs as an analyst, and also what Nadja Jury, a data scientist at Education Perfect, is doing at her company. In this episode, Nadja discusses how she went about automating weekly reports for the head of customer support, communicating data with internal stakeholders, and setting up data definitions so the whole company is on the same page (spoiler: it involves "ski passes").

From customer support to backend junior developer

Education Perfect supports students in an online learning environment by providing assessment and collection of student feedback for teachers. Nadja started in customer support, then moved to the enrollment team, and ultimately became a backend junior developer. You don't come across many people who take that kind of career trajectory, so I asked Nadja how she found herself as a backend developer:

At university, I studied computer science and six months before I graduated, the team pulled me aside to see what I wanted to do. I was interested in an internal data role.

Starting in customer support most likely gave Nadja a lot more context on what types of issues Education Perfect's customers care about as she moved to a more data-focused role. Sometimes it can be easy to "detach" from the front lines of the business when you're deep in spreadsheets and databases all day.

Education Perfect didn't always have a dedicated data person. In September 2019, a product manager laid out plans for a data team which now consists of 4 people who help the company make sense of all the data they have on how students and teachers are using the product.

Weekly reports that don't suck

One of Nadja's projects involves automating the reports for the customer support team. Efficiency is already top of mind for the CS team since they aim for a 10-minuted turnaround time on answering tickets.

The manager of the Inbound team used to take data coming from their email system to build end-of-week reports. These reports include tracking against the CS team's KPIs, data coming from their email system, and more. The end result? Multiple spreadsheets that need to be copied and pasted in multiple places (who hasn't been there before?).

So what is the new workflow that Nadja came up with? The head of the Inbound team just needs to export the data and and drop the files into a singleGoogle Drive. There is a Fivetran integration with Google Drive that automatically connects the data from that folder with the company's main data warehouse. Nadja then set up dashboards in Mode that built off of the data warehouse that generate all the necessary reports for the CS team to show how they are tracking towards their KPIs. We were already using Fivetran so it was easy to start the integration with Google Drive.

Time saved? Typically this would take the head of customer support 2-4 hours every Friday morning to build these reports, and now it takes less then 30 minutes.

What's interesting about Nadja's process with designing the Mode dashboard is that she first saw what the head of customer support had created in Google Sheets. Nadja then drew some new charts in a paper notebook an...

In this episode, I had the pleasure of speaking with John Napoleon-Kuofie, a senior business intelligence analyst at Farfetch. In this conversation, we talked about how John's career led him to Farfetch, a traffic dashboard he's built for his stakeholders at Farfetch, and how Excel was his gateway into SQL and the wonderful world of data. One of the reasons I enjoy conversations like these is because you get to learn from someone who is in the data trenches, as it were.

The path to Farfetch

Farfetch is an e-commerce company focused on boutique fashion companies. Before landing at Farfetch as a customer insights analyst, John was studying mathematics at university and thought he was going to work at a bank after graduation. He ended up working at a media agency where he help built statistical models demonstrating the value of advertising for the agency's clients.

What's so great about this part of John's career (and many entry-level analysts) is that you get to do a little bit of everything. John was working in Excel, R, and other bespoke tools. During this phase of your career, you are constantly learning and experimenting with new tools to figure out what type of career you want to end up in. John wanted to stay focused on analytics for his company's customers.

Storytelling may be more important than the data itself

Data storytelling is both and art and science. It's not just doing the number crunch and creating the analysis, but pulling the salient points out and creating a compelling story with the data. This skill is so important that big news outlets like the New York Times have created data bootcamps to help its journalists become more proficient in data analysis.

John discusses working with a telecoms client at his former media agency, and the client was cycling through different creatives in their online ads. Each ad had a different celebrity, and John noticed that the efficacy of their ads could be improved. Using data and a bit of marketing, his team convinced the client to adopt a more consistent advertising strategy with one celebrity instead of multiple. In marketing speak, this led to stronger brand recall and the numbers backed it up.

I think many online classes teach you how to use all the knobs and switches in Excel, R, SQL, and Python, but the real value analysts can provide is creating these data-driven stories to make decisions. (I'm really passionate about this subject and have an online class about this topic).

Self-service "traffic" dashboards

In order to help its clients generate sales, Farfetch utilizes multiple marketing strategies including pay-per-click advertising, affiliate advertising, and SEO. In order to help internal stakeholders figure out the proper marketing mix to maximize sales, John created traffic dashboards in Looker and Tableau. The key to these dashboards are that they are self-service so that his colleagues can slice and dice the data they way they want.

An example of a metric these dashboards track is website visits. The dashboards allow people to find out which channel is driving the most traffic so you can figure out whether to invest more or less into that channel.

This is a super simple formula in Google Sheets, and I don't want to understate its utility. You can literally translate text from any language into another language. This formula came out on Google Sheets in early 2019 I believe. You basically don't have to copy and paste into Google Translate anymore to get the translation you need. This function could be helpful for those of you who are are working with PDFs that contain tables of text in a different language, and you need to convert the text to your native tongue. Google Sheet for this episode is here.

If you don't get the reference above in relation to language translation, watch this video :)

Le Grand Débat National dataset

One of the reasons I thought about this function is that I am trying to learn a new language myself (French). I came across this dataset on Kaggle and it is a heap of data from Emmanuel Macron's initiative to increase debate all over France in 2019 regarding issues like taxes, democracy and citizenship, and the structure of government. I have no idea how the French government collected and aggregated all this data. I believe if you were not able to attend a debate in person, you can also answer questions online (which probably contributes to the bulk of the responses).

The dataset contains a whopping 170 million words with contributions from 250,000 French citizens. It looks like there are mixed reviews about the effectiveness of Macron's initiative. For analysts and democracy, this was a big step in engaging citizens in public discourse and increased transparency around the data that was collected. We also get a rich dataset to utilize the GOOGLETRANSLATE function.

Applying the function to our dataset

I have a subset of one of the CSVs from this dataset in the Google Sheet. You'll see in Column E we have some messy text with random characters but it's clearly in French:

If I want to translate that title column into English, I start writing the GOOGLETRANSLATE function in column F. You'll notice that when you start typing "GOOGLE" in cell F1, you'll see only two built-in Google functions:

I imagine that Google will come out with many more functions that allow you to query other Google services. I think it's strange there are only two built-in Google functions right now, but it may be Google's way of nudging you to purchase an upgraded account to Google for Work to utilize more features (similar to Office 365). I can see more functions that allow you query your Gmail, Google Calendar, and Tasks. There are some built-in Google Bigquery functions if your organization has Connected Sheets and is using BigQuery as a data warehouse.

Anyway, back to GOOGLETRANSLATE. The function takes 3 arguments:

* Text to translate* Source language (current language of the text)* Target language (language you want to translate to)

The caveat is you need to know the two-letter abbreviation for the last two parameters (full list here). If we want to translate the text in column E from French to English,