An Analysis of Layoffs in the USA

Introduction

For many small businesses the Covid Epidemic forced them into closures across the globe even Medium and Large Scale companies felt the impact of the epidemic. For most small and medium businesses this will affect them beyond the epidemic. This analysis will cover unemployment in the USA and not the entire world.

Dataset

The data was pulled from kaggle.com (https://www.kaggle.com/datasets/swaptr/layoffs-2022?resource=download) . It is a very interesting data set that will result in more insights if analyzed further. I will try as much as possible to answer the following questions below in my analysis;

1. How many people were laid off during this time and how much was raised ?
2. How much was raised by Companies and is there a relationship between funds raised and employment?
3. How much was raised by different industries and what Stage raised the most?
4. Which months and years were the highest number of people laid off ?
5. Does Unemployment varies by industries type and if so which are the most affected by unemployment?
6. How much was the percentage /numbers by year?

Data Cleaning and Preparation

The dataset came as an Excel Csv file and I did a little cleaning first. I removed duplicates and filtered for USA Country. I then copied the USA data into another Excel file and saved it as a Csv file too and renamed it as USALayoffs. I converted the percentage_laid_off from a fraction to a percentage .I then uploaded it into Power BI and opened the Power Query took a look to make sure everything looked fine . After this was done I ‘Close and Apply’ the changes in Power BI.

This is how the final dataset looked like after Data Cleaning and Preparation in the Excel Spread sheet.

Analysis Tools

SQL, Power BI and Excel will the be the tools that will be utilized to do analysis and data cleaning. Before uploading the dataset into https://sqliteonline.com/ I uploaded it into https://sqlizer.io/ to easily convert it into an SQL Database .I then uploaded the database into https://sqliteonline.com/ to run SQL queries on the dataset and analyze it. Since there were a large number of companies in the dataset I decided to limit my visuals in Power BI using the Top N filter to only the top 10 in different categories.

Analysis and Findings : Power BI

I created a dashboard on Power BI Canvas saved it and uploaded to my workspace if you would like to take a look at the dashboard please click this link please

How many people were laid off during this time and how much was raised ?

The first numbers that I pulled out gave an overview of the data set this included finding out how much funds were raised , how many people were unemployed and average number of people laid off in the three year span from 2020 to 2023.

The funds raised totaled $1 Million while 297 thousand people were laid off and an average 25.13 % were laid off.

How much was raised by Companies and is there a relationship between funds raised and employment?

In Chart 1 below we see that Netflix raised the highest amount of money ( $ 487.6K) and at a distance second is Meta (formerly called Facebook), also making the list of top 10 are companies such as Uber ,Twitter, Telsa and Lyft.

Chart 1: Funds Raised By Company

In Chart 2 below I wanted to find out whether the top 10 companies that raised the highest amount also had a high number of layoffs. At the top of the list is Amazon with over 27 thousand employees laid off. Again Meta (facebook) was second in the list of top 10 with 21 thousand laid off. Other notable companies include Google, Uber ,Microsoft and Salesforce.

Chart 2 :Total Number of People Laid Off

Looking at the two Charts above there is a relationship between funds raised and employment. Most of the top 10 companies that raised a large sum of money are not in the top 10 list of companies in layoffs. An exception is Meta (Facebook ) and Uber who are in both top 10 lists.

How much was raised by different industries and what Stage raised the most ?

In Chart 3 below I created a pie chart to see which industry raised the most funds and the Media Industry raised the most at $497 thousands while the Transportation and Consumer Industry raised $172 thousand and $133 thousand respectively.

Chart 3 : Funds Raised By Industry Type

In respect to what Stage raised the most companies that were Post- IPO raised the most funds in fact almost over 72% of the funds was raised in this manner. The remining 28 % was split between the various Stages from 4.8 % to 0.9%.

Chart 4 : Funds Raised By Stage

Which months and years were the highest number of people laid off ?

Chart 5: Total Number of layoffs By Month

How much was the unemployment percentage /numbers by year?

Chart 6: Total Number of Layoffs From 2020 to 2023

Does Unemployment varies by industries type/location and if so which are the most affected by unemployment?

Chart 5 & 6 I pulled the number of months and years that had the highest number of layoffs. The months January and November had the highest layoffs while 2020 had a larger number of layoff 2022 and 2023 saw an uptake in the number of layoffs. Using a Pie Chart we can see that 2023 accounted for 42.72% of the layoffs while 2021 accounted for only 3.19% of the layoffs. Its just ironic that after the Covid Epidemic layoffs have continued to rise.

The final Chart 7 that I created on Power BI was one that I wanted to find how many were laid off by Industry type and the Consumer industry was at the top pf the list with 49K followed by the Retail and Hardware industry.

Chart 7 : Total Laid Off By Industry

Total Number of locations/Cities

The total number of cities that were covered in this data totaled around 97 and some of them had higher percentage of layoffs than other. The San Francisco Bay area had a noticeably higher number of layoffs in the USA than any other regions as shown in the Chart 8 below;

Analysis and Finding : SQL

The first thing that I wanted to find out was the total amount of money that was raised and total number of people in the USA who were unemployed during the period 2020 to 2023. Also I wanted to find out the Percentage of employees that were laid off in this time frame.

The first Sql query that I ran was to take a look at the table. The query shows that we have the following columns; Company_name, Location, Industry, Total_laid_off, Percentage_laid_off, Funds_raised, Date ,Stage and Country

Select * From layoffsUSA Limit 10

The next Sql queries were to find key employment indicators like Total laid off , Percentage Laid off and Total funds raised

SELECT SUM (total_laid_off) FROM layoffsUSA

296,570

SELECT SUM (funds_raised) FROM layoffsUSA

$ 1,231.131.50

SELECT AVG (percentage_laid_off ) FROM layoffsUSA

25.1%

With this query I get the number of total laid of as 296,570 ( approximately 297K) total funds raised and AVG percentage laid off are $ 1,231.131.50 and 25.1% respectively.

SELECT COUNT(DISTINCT company_name) FROM layoffsUSA

1288

SELECT AVG ( total_laid_off) FROM layoffsUSA

275

The Count Command gave us a total number of 1288 companies while the average number of layoffs were 275 per company. Using the subqueries below I was able to find out how many companies had percentage laid off more than 25.1% and those with less than 25.1% employees laid off.

SELECT COUNT (company_name) FROM layoffsUSA
WHERE percentage_laid_off > (SELECT avg(percentage_laid_off) 
FROM layoffsUSA)

291

There were 291 companies that had percentage laid off more than the average 25.1% while 784 companies had percentage laid off less than 25.1%.

SELECT COUNT (company_name) FROM layoffsUSA
WHERE percentage_laid_off < (SELECT AVG (percentage_laid_off) 
FROM layoffsUSA)

784

Using a Common Table Expression (CTE) below I was able to find out that there were 912 companies that had more than the average number of employees laid off.

WITH Common_table As 
(Select company_name,total_laid_off FROM layoffsUSA) 
SELECT COUNT (company_name)FROM Common_table WHERE total_laid_off < 275

912

In the same manner using another CTE I was able to find out that the number of companies that had more than 275 laid off were 164

WITH Common_table As 
(Select company_name,total_laid_off FROM layoffsUSA) 
SELECT COUNT (company_name)FROM Common_table
WHERE total_laid_off > 275

164

I also ran an Sql query to list the top 10 industries that had the highest number of layoffs and how many employees were laid off in each industry.

SELECT DISTINCT (industry ),SUM (total_laid_off) from layoffsUSA 
GROUP BY industry 
ORDER by SUM (total_laid_off) DESC LIMIT 10

Using the Sql query below I was able to find out that the Stage that had the highest funds raised was the Post IPO

SELECT stage, MAX (funds_raised) FROM layoffsUSA
GROUP by stage 
ORDER BY MAX(funds_raised) desc

Post IPO

Another important query was to find out which industry raised the highest amount of funds and the top of list were Media, Consumer and the Transportation industry in that order. The query below was the one I used.

SELECT DISTINCT (industry) ,MAX (funds_raised) FROM layoffsUSA
GROUP BY funds_raised
ORDER BY funds_raised DESC

I was also interested in finding out how many were laid off especially from the Retail Sector since it was the most affected segment of the market during the Covid Pandemic.

SELECT industry ,SUM (total_laid_off) FROM layoffsUSA
WHERE industry ='Retail'

42,981

Being from the Data field I wanted to find out whether those in the Data Science Field were affected by layoffs. There were some that were affected as shown below. Also the Consumer industry and Health Care Sector were not spared with the Consumer Industry having the highest number of layoffs.

SELECT industry, SUM (total_laid_off) FROM layoffsUSA
WHERE industry ='Data'

5,329

SELECT industry, SUM (total_laid_off) FROM layoffsUSA
WHERE industry ='Consumer'

48,860

SELECT industry, SUM (total_laid_off) FROM layoffsUSA
WHERE industry = 'Healthcare'

14,603

I also wanted to fond out how locations are there in the USA and which location had the highest layoffs

SELECT COUNT(DISTINCT location) FROM layoffsUSA

97

There were 97 locations in the dataset and from the following Sql query I was able to list the top 10 locations with the highest number of layoffs with San Francisco Bay topping the list with 142 thousand layoffs.

SELECT DISTINCT (location), SUM (total_laid_off) from layoffsUSA 
GROUP BY location
ORDER by SUM (total_laid_off) DESC LIMIT 10

The toughest day for layoffs was the 20/1/2023 with over 12000 laid off in the SF Bay Area.

SELECT industry ,location ,date,MAX (total_laid_off) FROM layoffsUSA

I also wanted to find out what company/ies in the SF Bay Area laid off the 12,000 employees on the 20/01/2023.

SELECT date,company_name,location , MAX (total_laid_off ) FROM layoffsUSA
WHERE location > (SELECT MAX (total_laid_off) FROM layoffsUSA)

I was surprised to find out that Google laid off the highest number of employees on the 20/01/2023.These two Sql queries give the same output the first is a Subquery and the second is a straight Select Sql Query.

SELECT company_name,industry ,location ,date, MAX (total_laid_off) 
FROM layoffsUSA

Conclusion

In conclusion the above analysis gave us a snap short of what transpired during the Covid pandemic and post pandemic period till 2023. Thanks for reading my blog if you have any questions or suggestions please feel free to get in touch.