Dear Analyst

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

Data is stuck in silos

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

Data is trapped in silos.

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

Starting the process to export data

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

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

Creating accurate construction cost estimates

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

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

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

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

1) Artificial intelligence and natural language processing doesn't eat your lunch

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

https://twitter.com/jbrowder1/status/1602353465753309195

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

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

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

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

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

Other Podcasts & Blog Posts

No other podcasts mentioned in this episode!

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

A "master" record in master data management

The definition of master data management according to Wikipedia is pretty generic:

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

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

Source: Info-Tech Research Group

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

Data cleaning CRM data at AutoTrader

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

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

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

* Excel and SQL are still the main tools used for data cleansing

* The same type of data problems exist at startups and large corporations alike

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

Have you ever faced a spreadsheet where one column contains values for each "section" of the spreadsheet but you want to fill those values down through the rest of the column? This is a common problem when you get a data dump from a database or perhaps a copy/paste from a PivotTable. You have values interspersed in the column and want to fill the empty cells below those values with the value above it, but not all the way down. See this screenshot below to see what I mean:

This episode is a re-broadcast of a presentation I gave at the Vancouver Power BI & Modern Excel User Group in April 2021 hosted by Ken Puls. The full video of the presentation is below where I walk through two scripts: a VBA script for Excel an a Google Apps Script for Google Sheets. You can see the original episode/post I did about filling values down programmatically in episode #42. Filling values down is apparently a really common operation and problem faced by analysts because this post about filling values down to the last row of data has become the most popular post on my blog.

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

Johnson & Johnson is one of the largest corporations in the world and they produce everything from medical devices to baby powder. They were also on the front lines of developing a vaccine during the pandemic. Internally, J&J is also at the forefront of digital transformation. Sarfraz Nawaz studied computer science and built data analytics and decision intelligence platforms inside companies across different industries. Sarfraz currently does product and digital management for J&J's supply chain team. In this episode, we discuss data analytics platforms, supply chain platforms, and optimization problems in the context of vaccine distribution.

Building a supply chain platform at Johnson & Johnson

Johnson & Johnson's supply chain supports 1.2 billion consumers every day. It's a staggering number. Building and optimizing a supply chain platform that supports so many people must be a huge problem, but could also be a fun one if you love to see things scale.

Sarfraz discusses the one thing underpinning a successful supply chain: a data strategy. You normally may not think of a supply chain when it comes to data analytics. According to Sarfraz, J&J's data strategy lays the foundation for how other technologies at J&J are enabled. He's referencing things like cloud, governance, and machine learning.

Increasing visibility into the supply chain is something Sarfraz works on a lot. For instance, one concept we talked about in this episode is Availability to Promise, or ATP. This concept basically ensures there is enough inventory available when a customer places an order, and that there isn't too much inventory sitting around either. There's a lot of esoteric software I've never heard of that helps corporations like J&J with ATP like Logiwa and Cogoport. Even SAP has an ATP platform showing how important this concept is for companies with big supply chains. Behind these ATP platforms are, of course, a ton of data. And more of that data is coming from customers.

Competing with Amazon

Demand forecasting and planning is a constant challenge for J&J. However, with J&J's digital transformation initiatives and data strategy in place, the corporation is getting better at forecasting every day. An important signal for demand forecasting is customer engagement.

Sarfraz discusses the various inputs that go into this demand forecasting model. Imagine the model is an Excel file, and there are various inputs that go into the model (over-simplified analogy). There are data points coming from the manufacturing division, inventory levels, transportation, and many more inputs that go into the model. This is similar to the model Amazon Prime has built for its customers. You can go one level deeper and get data points from building control towers, temperature inside the vehicles that carry products, and the constant feedback look that arises from these data "producers."

Source: Business Insider

Optimizing COVID-19 vaccine distribution

Sarfraz spoke a bit about the research and planning that went into developing and distributing the J&J vaccine. The key takeaway from this experience (like many problems related to big data) is scale. How does J&J ensure they have the right processes in place to produce over 500 million vaccine do...

This episode is a re-broadcast of a podcast I did with Bassem Dghaidi, a senior software engineer at GitHub. Bassem has a podcast and YouTube channel called Glich. On his show, he covers moderate to advanced engineering topics on coding, architecture and management. Data analytics is a little outside of the topics he generally talks about, but there are many crossovers between data analytics and engineering as you have probably heard on Dear Analyst.

We cover a lot of basic concepts like what a data analyst does, tools analysts use, and why Excel is still so dang popular. Then we get into some more "engineering" topics like whether data analysts should learn how to code. We then end on some tips for aspiring data analysts on the skills you need to be come a data analyst and what a typical data analyst career path looks like. You can see the full video interview below:

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

Other Podcasts & Blog Posts

No other podcasts mentioned in this episode!

Matthew Brandt didn't know analytics was a potential career option until he started managing websites with Google Analytics. Matthew is Canadian, spent most of his life in Switzerland, and went to high school in Japan. He attended the EHL hospitality school at Lausanne, and was part of the 70% who left the hospitality field after graduation to start his career in analytics. From data engineering to data architecture to reverse ETL, Matthew has done and seen it all. To merge his love for hospitality and technology, Matthew founded a food tech startup based in Zurich. In this episode, you'll hear about Matthew's wide-ranging experience including livestreaming about analytics on Twitch, working at a fintech SaaS company, and co-founding a food-sharing startup.

Predicting customer churn at a fintech SaaS company

Before Matthew entered the world of livestreaming, he had a "normal" job working for a SaaS company. The company provides account software for small businesses in Switzerland. Swiss tax law is very complicated so this company helps businesses with getting all this back office tax stuff figured out. Matthew was originally hired as a marketing analyst. Out of 40 people in marketing, Matthew was the only one actively working with data. Originally, he was cleaning up data, auditing data, figuring out business workflows, and understanding relationships between entities. A jack of all trades.

The company wanted to reduce churn but didn't have the technical infrastructure to get there. Matthew introduced Salesforce and the ETL model to the company to create a clean set of tables and schemas. He ended up using machine learning to produce different outcomes so that the company knew which parts of their operation to fine tune and optimize. The model would help them identify which customers are most likely to churn. For instance, customer who hadn't logged in in a long time were most likely going to churn.

This was an interesting behavioral exercise since the customer already made the psychological decision to cancel. But we were able to identify these customers before they made that decision.

Keeping a foot in the analytics world with conferences and livestreaming

While Matthew's full-time job is working on a food tech startup (more on this later), he still keeps a foot in the analytics world. He runs AnalyticsCamp, for instance. AnalyticsCamp is an "unconference" focused on data analytics, data visualization, business intelligence, and UX research. If you are based in Switzerland, this might be the perfect conference for you to attend if you're in the data analytics field.

What's more interesting are Matthew's forays into the livestreaming space. I've never met someone who is providing education and creating a community around data analytics through Twitch. During the pandemic, Twitch grew by 82% in terms of hours watched.

Like many people stuck at home, Matthew went onto Twitch and started watching music, science, and technology streams. He discovered that many people were developers and many people were doing live coding streams. Something about teaching analytics on Twitch interested Matthew, so he thought he'd give it a shot. He discovered it had to be edu-tainment. I jumped on one of Matthew's livestreams and it's just as Matthew said: it's a mix of learning and entertainment. I think this might be the first time I've mentioned a Twitch livestream as a way to learn about analytics and tech:

Gift Ojeabulu is a data science professional, community organizer, and data practitioner from Nigeria. He studied biochemistry at university but eventually switched to computer science. He first started in web and Android development before moving to the data science field. By attending developers conferences in Lagos, he started learning more about data science and started building his own data science communities in 2019. He learned a lot of his skills through Udacity and even won a data science contest in Nigeria on malaria detection. In this episode, Gift talks about his background working in data science and community building in Africa.

A paper on FIFA winners leads to a job

Gift was playing a lot of FIFA and was curious about how players in the game became super stars. By browsing through Kaggle, Gift found some open data sets on FIFA players and wrote a paper on why Lionel Messi would win the Ballon d'Or. His paper included data visualizations and told a data story about his findings. Someone from CBB Analytics took notice of Gift's paper and Gift was eventually hired as a contractor at CBB.

Cleaning sports data to give to coaches

Sports data is actually very well organized. When Gift was a data scientist at CBB analytics, he helped clean data so that coaches could make data-driven decisions on and off the field. Coaches use the data to make good trades during drafts, for instance. Gift would analyze players' shooting tendencies in the pre-game report. In the post-game report, you could see how the player performed and then do a comparison to the player's previous games. Internally, the team was using dbt to help with organizing the data warehouse.

Gift talked about how domain knowledge is important when analyzing a dataset. In sports, you might see Messi on TV and might notice certain qualities that make him a better or worse player than what his stats show. This domain expertise is a great way to detect whether the data quality is good or bad.

Building a data community in Africa

Gift saw the power of community building when he as in his church's choir. One of his first developer community events he attended was a DevFest meetup hosted by the Google Developers Group (GDG). He met a developer advocate who worked for Google Africa and started learning about community building specifically for the developer and tech communities in Africa.

In Africa, Gift said there is a community of data analysts, data scientists, and data architects who aren't really connected through communities. That's why he started DataFest Africa, a conference bringing together policymakers, startups, and other stakeholders to develop the ecosystem in Africa. Gift also developed an interest in machine learning and became an organizer for the MLOps Community. He's the main organizer for the MLOps Lagos chapter of this community.

Source: DataFest Africa

Other Podcasts & Blog Posts

No other podcasts mentioned in this episode!

Once you learn the VLOOKUP formula, your world opens up in terms of being able to analyze and manipulate data. There are hundreds if not thousands of tutorials on how to use the VLOOKUP formula since it's such a powerful formula for finding the data you need in a long list. Comparable formulas include the combination of INDEX and MATCH and the new XLOOKUP formula which took the Excel world by storm. One common task you might need to do as an analyst is find data based on multiple conditions or criteria. VLOOKUP only allows you to lookup one specific value or criteria in a list of data. In this episode, I'll describe three methods for doing a VLOOKUP when you have multiple conditions or criteria. These methods utilize more advanced formula features, and the third method is my favorite. Copy this Google Sheet to see the different methods in action.

Video tutorial of this episode:

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

Method #1: Creating a new array with ARRAYFORMULA and brackets

The first method is quite advanced and requires knowledge of the following:

* Boolean logic* The ARRAYFORMULA function in Google Sheets* Brackets {} for creating arrays in Google Sheets

In Excel, you can do something similar to the ARRAYFORMULA function in Google Sheets by pressing CTRL+SHIFT+ENTER when entering a formula in a cell. It's not the most intuitive way of entering a formula. If you have Office 365, you actually don't have to know how to use this keyboard shortcut at all.

This is not my preferred method for doing a VLOOKUP with multiple conditions but it is more scalable than my favorite method (method #3). In terms of the dataset, we have a list of cars and we want to find the Fuel_Type for a "ciaz" car made in "2015" and has "15,000" kilometers on the car (see the highlighted yellow row in the screenshot above). The reason we need to do a VLOOKUP with multiple conditions in this case is because there are multiple rows with a car name "ciaz" made in different years.

Explaining the formula for method #1

Let's take a look at the formula and work inside out to see how this formula works:

=vlookup(1,{arrayformula((A2:A302=I5)*(B2:B302=I6)*(D2:D302=I7)),E2:E302},2,0)

The stuff inside the ARRAYFORMULA is a way to compare everything in the list to the Car Type, Year, and Kms_Driven defined in cells I5:I7. The syntax is a bit weird since you're multiplying each condition to get the row that matches all the conditions. In plain English, it reads something like this:

Find rows where the Car_Name is "ciaz" AND the Year is "2015" AND the Kms_Driven is "15,000"

The reason you need to wrap this in an ARRAYFORMULA is because you are telling Google Sheets to look at all cells in a column (an array) instead of one cell at a time. The "result" of the ARRAYFORMULA is a list of 0s and 1s where the 1s represent rows that meet all the conditions: