Navigating Analytics Job Market During COVID-19 Using Web-Scraping and Text Analysis
This analysis is a part of our project in the Summer Data Competition 2020 hosted by Fuqua School of Business. I want to send my special thank to my teammates: Yaqiong (Juno) Cao and Xinying (Silvia) Sun, for their great contribution.

Photo by Annie Spratt on Unsplash

Problem Definition

Are you an analytics master student who is seeking a job and have no idea what the job market looks like, especially during COVID-19? Our project aims to analyze the current online job posting situation that can help students better understand the current job market, find the most suitable job positions, and make the best preparation for these jobs. After integrating the latest data from authorized websites like LinkedIn, Yahoo Finance, and company official websites, we generated some interesting insights from our multi-dimensional analysis. We encourage master students to develop their communication and problem-solving skills besides technical skills. Students should pay more attention to job postings like Data Engineer, Financial Analyst, and Data Scientist. They should also consider narrowing their search to big cities with high ratings and focusing on financial and tech companies like Amazon, which has an incredible hiring growth rate in 2020.

Student Journey

Image by Author

So, which data do we need to answer our case question? Our workflow was formed based on the student job search journey. The first step is to define the company and the industry. In that case, we need to collect relative industry and company information. Then the second step is to find the position, whether you want to be a financial analyst or data scientist. After you know which industry and position you want to work for, you might consider the skills you need to qualify. That is when we need to collect skills information​. We also consider a different scenario, in which students may want to start their job search on position first. In that case, we would like to collect data and create different tables instead of putting every info into one table.

Data Integration

Using different data collection methods, including web scraping in Python and data merging in SQL and R, we consolidated data from a variety of sources, as shown in our relational schema.

Image by Author

Job Data Table & Company Workforce Table: Using keywords via scraping from LinkedIn, we first collected analytics and data-oriented job posts from the top 10 companies (in revenue) of each of the top five largest industries: Information Technology, Financial Services & Investment Banking, Retail, Fast-Moving Consumer Goods (FMCG), and Media & Entertainment. At first, we limited our data to two months: August and September 2020, a period right after graduation, which we believe will effectively reflect the job market on the LinkedIn platform. But now, this project was updated with October and November data. We then extracted primary information such as companies and associated industries, job post’s date, job titles, and job descriptions that include skills and requirements for each position. We also obtained data relating to each selected company’s current hiring situation for the past 12 months to provide evidence support for further deployment. To clean the job data, we focused on three tasks. First, we removed duplicate observations resulted from multiple scraping runs. Second, we researched each column/variable, doing corrections for city names of job locations and industry classification. Last, we spent most of the time cleaning job titles and allocated them into four title levels: Associate, Analyst/Engineer, Manager, Scientist/Researcher. This step would provide “good” data for a separate analysis of job titles.

Job Skills Table: For the next part, we created a subset from the LinkedIn original data set by extracting information from the job description column. This column contains the skills and requirements for each analytics job. Based on our original data set, we selected 15 positions that best fit the capability and desire of analytics master graduates. Also, conducting text analysis in R for job descriptions, we identified the top 28 most popular skills, which means they appear in the basic/preferred qualifications and requirements of job postings most of the time. This process resulted in a subset of data, including company names, job titles, and a list of skills required for each job title.

Company Info Table & Company Rating Table: Based on the raw company data that showed active hiring activities on LinkedIn, we dove deep into the financial information of these 25 companies. We collected the data about the size and market capitalization from Yahoo Finance and analyzed the second and third quarter fillings of 2020 and 2019 from the investor section of each company’s official website. The rating for each company was, meanwhile, obtained from different sources.

Job Locations Table: We built a location table with a location column from the job data (a city where each job is located). Then we added such information as state, longitude, and latitude based on Google Maps. Using R, we merged our raw data and the cities information table to create a new location table with unique location values that could be used in mapping visualization in the following analysis.

Deployment

Overall job postings in the US

We first analyzed the US overall job postings using R mapping, which shows that companies located on the east and west coasts are hiring more. Our data proves that big cities also provide more job opportunities.

Image by Author

When we analyzed the data in-depth on the regional level, we found that the job posting of GA and NY on the east coasts, WA, and CA on the west coasts, MN, and IL in the Midwest is relatively high in other places in the US. It is reasonable since the fast-growing cities provide more employment opportunities, and higher-salary jobs appeal to many employees. When looking at specific cities with the largest number of job postings like Seattle, Atlanta, and New York, we found that almost 90% of job postings in Seattle are from Amazon, 70% of the job postings in Atlanta are from The Home Depot. Only New York has a different pattern when the local job postings are from a diversity of companies. This finding verifies our assumption that looking for an analytics job is easier in big cities.

Image by Author

We also observed another insight into the hiring situation based on the number of jobs posted through the week. In general, we saw the number of jobs posted reached a peak on Wednesday and Thursday, while recruiters were less likely to post new jobs during the weekend from Friday to Monday.

Image by Author

Hiring situation at the industry and company levels

After having a general idea of the US job market in the last two months, we wanted to learn more about the hiring situation at the industry and company levels. According to our data, Information Technology & Services companies offer more job opportunities, mostly by Amazon and IBM. There are about 50% of the job opportunities provided by the tech industry.

Image by Author

Moreover, the job opportunities offered by Amazon account for 25% of the total. Meanwhile, companies in FMCG and Media & Entertainment industries like Johnson & Johnson and AT&T are way behind in hiring. About 1% of job opportunities are provided by the Media & Entertainment industry, and about 2% provided by FMCG. Conducting external research, we found that these industries and companies have performed in reverse directions under the impact of COVID-19, which could result in their hiring decisions.

Image by Author

Rating and Revenue analysis overall and within the industry

After learning about the hiring conditions, we are curious about how companies are running their business under COVID-19. Also, we analyzed the company ratings using mapping. It is reasonable that retail pharmacy companies have higher revenue recognized because of the volume of goods sold, like the case of CVS Health. Under the effect of COVID, media companies like The Walt Disney and Omnicom Media Group decreased their revenue significantly. It surprised us that Johnson& Johnson’s revenue decreased in Q2 2020 even though it recovered in Q3 2020. By doing more research on their current 10Q, we found that a poor sales performance of the medical device, which dropped 33.9% compared to 2019Q2, is the main reason to reduce the total revenue even though the pharmaceutical sale segment increased under the effect of COVID as expected. We also witnessed the recovery of Nike and PepsiCo in Q3 2020. Meanwhile, Amazon saw a massive expansion in revenue, matching its hiring growth, since their online and cloud-based business model benefits from the current pandemic. Interestingly, Truist increased its revenue by 80% as a result of the merger of BB&T and SunTrust Bank.

Image by Author

Image by Author

In terms of rating, as most tech companies are located in California or Seattle, the West coach companies have a relatively higher rating than the other US. Employees more favor salary, benefits, company culture, and working conditions of tech companies. Therefore, we encourage analytics master students to focus on job search in the technology industry, financial services, and investment banking industry based on revenue and rating analysis.

Image by Author

Job postings at the job title level

In terms of job postings at the title levels, we selected Analyst/Engineer and Scientist/Researcher levels to do more analysis. Regarding the Analyst/Engineer level, we saw the dominance of Data Engineer, Financial Analyst, and Business Analyst. Meanwhile, Data Scientist has still been the most popular job demanded in the Scientist/Researcher market.

Image by Author

To leverage our insights, we picked the top 4 companies from different industries to learn more about which job titles they are looking for most. Another interesting inference is that each company has a distinct demand for its top analytics jobs. Respectively, Financial Analyst, Data Engineer, Business Analyst are the most popular by Amazon, CVS Health, JPMorgan Chase. These companies are currently different in terms of revenue performance and business situation, as mentioned in the previous analysis.

Image by Author

Job skills for each title

We found that Communication, Problem-solving, Data Visualization, Data Modeling, and Critical Thinking are the top five skills for every position and every company. An analytics master curriculum with a good balance between general/soft skills and technical skills will definitely make a difference in a job search.

Image by Author

Diving deep into each job title, we found that the Financial Analyst and Business Analyst requirements are similar since they include Data Visualization, Microsoft office, Data Modeling, and SQL.

Image by Author

For the Data Engineer and Data Analyst position, we saw a different pattern. While the Data Engineer jobs require such database skills as Excel and SQL, the Data Analyst jobs prefer more advanced programming languages like Python and Data Modeling/Machine Learning techniques. Interestingly, Data Visualization is very demanded for the Data Analyst position.

Image by Author

Regarding Data Scientist and Data Researcher, they both ask for a high level of mastering the most popular languages like Python, SQL, and R. Besides, we found that Data Scientist, Business Analyst, and Data Engineer have more requirements about hard skills such as SQL, Python, R.

Image by Author

Also, Analytics Manager usually requires 2+ years of work experience. To sum up, every position requires soft skills, but Business Analyst, Financial Analyst, Data Engineer, Data Scientist, and Data Researcher will demand more technical language skills in the increasing order.

Implementation

Our project aims to help analytics master students, have a better understanding of the current context of recruitment, and make sufficient preparation while seeking employment. Although we believe that our analysis is useful and efficient, it does have some limitations. First, because the job postings decreased over time by recruiters, the availably collected job data may not perfectly reflect the job market. Second, we could not have insights into the job market in some states in the US since no job posting was recorded. To verify our assumption that these areas are negatively affected by the COVID-19, we can collect the data from other hiring websites. Third, we found that Audit and Consulting firms like BIG4, McKinsey, BCG, and Bain don’t have public financial information, which limited our quantitative analysis for the company revenue part. Last, using keywords in scraping job titles, we might ignore some positions that don’t include “analytics” in their titles. These jobs are, however, data-oriented and should be classified as analytics jobs. For example, companies that don’t have a specific audit analytics position but require auditors to learn Python and Tableau. In that case, we should try other data collection methods to obtain a wider scale of relevant data and improve analysis accuracy.

For interactive visualization of this analysis, please visit my Public Tableau.
['Dexter Nguyen']
['Python', 'Web Scraping', 'R', 'Covid 19', 'Data Science']