Dashboards Tableau

Where Did PPP Loans Go? Taking a Deeper Dive.

Recently, I published a dashboard that took a broad look at PPP loan data. Over the past few weeks, I looked deeper into that data set in order to pull more interesting findings from it.

Before you take a look, here are the most significant highlights in my opinion:

  • Spelling mistakes were prominent. Out of some of the 9 largest cities in the USA, there were more than 100 spelling variations.
  • About 4000 investment-related firms took out loans totaling between 1 and 3 billion dollars. This is interesting because investment fees and activities were at record highs during lockdowns.
  • The top 10 industries by loan count were heavily in-person industries, indicating that loans were distributed largely to businesses who needed them most.

While the dashboard has been developed for mobile as well, the viewing experience will probably be better on desktop.

Like this post? Enter your email below to subscribe for more visualizations and Tableau related posts.

Dashboards Tableau

Looking at PPP Loan Data – A Visualization

$667 billion dollars were set aside in 2020 for businesses across the US. This program was called the Paycheck Protection Program (PPP for short). It was meant to be a stimulus package to hold over businesses who were impacted by COVID-19 lockdowns. Without revenue streams, many businesses had to lay-off workers en masse, providing a potentially catastrophic destabilization of American society. According to the Bureau of Labor Statistics, there were close to 18 million unemployed people in June of 2018. The PPP data indicates that over 31 million jobs were retained due to the program.

Without getting into the inaccuracies of the standard unemployment numbers provided by the Bureau, we can still see that laying off potentially 10% of a country’s population wouldn’t be healthy for society. In the past, the US government has provided stimulus and bailouts primarily via public spending increases and buying toxic assets from big corporations. But this stimulus was meant to directly support businesses of all sizes who were directly impacted by the lockdown. Did it server its purpose? You can be the judge of that! I’ve created a data visualization meant to help show where the money went across the nation. This visualization includes the ability to search and filter on columns of the data. In the near future, I’ll be releasing a dashboard that digs into the… more suspicious loans taken out.

Here’s the dashboard, it’s interactive and has some additional info when you hover over data. It is configured for desktop and phones, but is better to view on desktop.:

This data was recently released regarding PPP loans. It was split into different data sets. For loans greater than $150k, data for the whole nation is provided in one data set. For loans less than $150k, data is split by state or territory into its own data set. I’ll be providing a combined data set of all loans here in a little bit, but for now this visualization is for loans greater than $150k.

Some interesting takeaways from this visualization:

  • Allegedly nearly 10% of the US population’s jobs were retained due to the program
  • There were TONS of misspellings and bad data entry
  • The majority of loans were in the $150k-$300k range

P.S. Check out the next visualization here, where I take a deeper look at the PPP data.

Like this post? Subscribe to be notified of future dashboards and Tableau tips.


Data Visualization is Not Even 25% of the Work

What Did You Just Say?

I’m a Data Visualization specialist at this point in my career, and I’ll tell you an unpopular opinion… The visualization part of data projects really isn’t the hardest part of the project, it’s not the most important, and it’s the least time consuming. Even with all of these factors considered, it’s often the most visible and emphasized part of the project (no pun intended) in many businesses. This is wrong.

I’ve estimated that not even 25% of the work on data visualization projects has to do with the visualization. Are there any studies or hard numbers to back this up? Nope. Just an estimation which I’ll go through now.

Estimating a Data Project Timeline

When it comes to a full-scale data project that ends in a visualization, the hard work and complexity happens behind the scenes. Gathering business objectives, setting scope, listing deliverables, data collection, data exploration and availability, data cleaning, data structuring, exploratory analysis, and maybe some additional modeling and data science before we even begin crafting an end visualization. That right there is up to 10 different steps and could be broken down further. If we (wrongly) assumed that each step took the same amount of time, then data visualization only takes 9.1% of the time (1 out of 11 steps).

In reality, the longest and most difficult portion of the project happens in the “unsexy” part. That would be data collection through data structuring. These steps are the bulk of the work. Unless the data set you’re working with is simple or there has already been significant effort in building clean and structured data sources, you’ll spend significant amounts of time exploring and verifying data. The reality is usually about 30-70% of the project timeline is spent in these phases. The other reality is that you’ll go through these phases of the projects several times, since the first few presentations of the data visualization will bring up many more questions on data quality and how the project ended up with the values presented in the data visualization.

What is the amount of time then for the data visualization part? If you have your business questions laid out during planning and you are aware of the analyses that need be done, there are only so many paths to take. There are a limited number of visualizations you can choose. The meat of your data story is already outlined with the business requirements. Sure, you can spend more time digging for additional data stories to tell, or on the UX/UI components to make it look better. But additional analyses are cherries on top of the outlined deliverables. And once you meet a certain design threshold, a slightly better look won’t fundamentally change how the visualization is received.

Why Isn’t The Data Visualization the Most Important Part?

Ok, maybe I was a little harsh. The data visualization portion of a data project is important. Striking out on the data visualization can make a project bust. But an excellent data visualization can’t make up for a poorly executed planning and data collection phase. It can’t make up for bad data science or inaccurate data sets. That’s why it isn’t the most important part. What a good data visualization can do is: surface bad data, surface inaccurate data science methodologies, answer business questions that should have been part of the planning phase, and much more. Yes, data visualization is important in data projects. Maybe 2nd most important, but it’s not the be-all and end-all.

But That’s My Job…

Building impactful data visualizations can provide great value to your organization and data projects. So being efficient and good at what you do can certainly provide great value to your company, job security, and a fruitful career. That being said, if you want to make your job more resilient to economic forces, you need to keep some things in mind.

If you’re a Data Viz specialist like me, you need to constantly work at delivering additional value outside of simply your visualization. The one exception to this would be a consultant hired specifically for data visualizations while everything else is perfectly prepared (ha!). Even as a consultant you’re arguably always better off delivering more value than anticipated for your customer.

But I digress. Deliver extra value. Somehow, some way. Whether it’s through your data visualization or through other parts of the project you’re working on. Having a diverse skillset that provides a significant multiplier of your cost to a company will make you a prized member of your company.

For some this is easy. Maybe it’s because you’re actually a BI developer, which encompasses many more responsibilities. Maybe you’re simply the data person in a smaller organization and therefore handle more of the data pipeline. If you’re not explicitly in a situation that pushes you outside of data visualization, just beware the fragility of your position and the need to diversify your value contribution in order to make your position more resilient. Just because you’re job box has a label doesn’t mean you can’t break out of that box or just pop the top open and relabel it yourself.

Give Me A Parting Analogy

Constructing a building is a great parallel to a data project and its steps. The planning, permits, surveying, laying of the foundation, erecting of the frame, installing the mechanicals, and putting up the drywall and insulation take the bulk of the time. Especially before the frame goes up, how many construction projects have you looked at and said ‘they never make any progress on this, they’ll never finish!’ This is only before you return a couple weeks or month later and everything is finished! It’s not that nothing was happening before that last stretch of time, it’s just that the progress wasn’t visible to the untrained eye.

This is equivalent to the business planning through data structuring part of a data project, especially from an end-user’s perspective. Nothing happens… nothing happens… then boom! The data visualization presents itself with all the work wrapped up into the visible end-product. Like the finishing steps of a construction project, the data visualization is what everyone will see and pay attention to. As long as there aren’t horribly obvious mistakes or incredibly artistic/unique details, most people won’t be too moved. The end-product will simply serve its purpose.

On the flip-side, if the foundation and frame of the construction was poorly or improperly done, the looks and/or functionality of the interior and exterior finishes won’t matter. In fact, the construction will eventually become unusable due to its improper foundations. Data visualizations are no different. Without high quality data, structure, and planning, the whole thing falls apart. Then your visualization answers the wrong business questions or answers the right questions incorrectly.

So remember, get the foundation right and spend the most time on it. It’s the most important part. Then focus on the visuals, because that’s what the people will appreciate.

I’m always looking for feedback, tell me what you think of this post! – Dan


Success! You're on the list.