LINKEDIN | 👨‍💻 Data Cleaning & Automation

Business Problem

In this project, we, as data analysts, are focused on analyzing the job market for roles related to data analytics in different countries. Our data source comes from web scraping that we have obtained from LinkedIn, which contains information about job listings in this field. Our primary objective is to validate the quality of the data and subsequently extract key insights about the job market in the field of data analytics.

This project will attempt to answer the following questions:

1. Which companies have the highest number of job listings?

2. How many job listings do we have in the table by location

3. How many job listings do we have per day of publication?

4. What are the top 10 job title roles used for posting job listings?

5. What are the top 5 locations with the highest number of job listings?

6. How many job listings combine keywords with the job title? Can you provide the count by both fields?

7. How many job positions are labeled as "junior"? Can you provide the count by job title?

8. Can you now provide the count of job listings with the title "junior," but broken down by country?

9. Can we determine the number of job listings published per month and keyword? Which months are the most prominent, and with which keywords?

Data-Set Diagram

The data is originally in CSV format, and the data source comes from a web scraping download obtained from LinkedIn, which contains information about job listings in data analysis.

Analysis

Part I - Create "linkedin_data" Table + Stored Procedure (SP)

1. Load the "linkedin_data" database.

2. Create the "linkedin_ofertas" table with the following fields:

fecha_actualizacion (datetime) nombre_empresa (varchar(200)) fecha_busqueda_oferta_linkedin (datetime) fecha_publicacion_oferta (date) pais_oferta (varchar(200)) search_id_oferta (int) titulo_oferta (varchar(200)) Modify the date restrictions by executing the following statement: SET @@SESSION.sql_mode='ALLOW_INVALID_DATES';

3. Define the query that will clean and format the data as desired.

4. Create a stored procedure with the cleaning query to insert data into the table.

5. Create an event that executes the stored procedure daily.

Part II - Create "linkedin_busquedas" Table + Stored Procedure (SP)

1. Create the "linkedin_busquedas" table with the following fields:

id_busqueda (INT PRIMARY KEY) fecha_busqueda (datetime) fecha_actualizacion (datetime) keyword_busqueda (varchar(200)) pais_busqueda (varchar(200)) n_resultados_busqueda (int) Modify the date restrictions by executing the following statement: SET @@SESSION.sql_mode='ALLOW_INVALID_DATES';

2. Define the query that will leave the data as desired (clean it).

3. Create a stored procedure with the cleaning query to insert data into the table.

4. Create an event that executes the stored procedure daily.

Exploratory Analysis - Data Validation

In the initial phase, we conduct an exploratory analysis of our dataset to better understand it. Some queries that we execute include:

## 1- Validacion de datos: Para validar los datos podemos ir a la fuente original, es decir, Linkedin y verificamos si la cantidad de ofertas por dia tiene sentido con nuestro data_set: SELECT fecha_publicacion_oferta, COUNT(*) FROM linkedin_data.linkedin_ofertas GROUP BY fecha_publicacion_oferta ORDER BY fecha_publicacion_oferta DESC ## 2- Chequeamos el rango de tiempo de nuestros datos: SELECT MIN(_fivetran_synced), MAX(_fivetran_synced), MIN(date), MAX(date), MIN(date_published), MAX(date_published) FROM linkedin_data.raw_linkedin_results; -- Tenemos empleos publicados desde el 04 Enero 2022 hasta 03 Diciembre 2022 (casi un año de ofertas laborales) ## 3- Mediante una verificacion observamos que el campo "searches" en la tabla de linkedin_results representa el campo "id" de la tabla raw_linkedin_searches SELECT * FROM linkedin_data.raw_linkedin_results WHERE searches = 2417; SELECT * FROM raw_linkedin_searches WHERE id = 2417; ##4- Chequeamos nulos: SELECT COUNT(*) FROM linkedin_data.raw_linkedin_results WHERE title IS NULL; -- Hay 1973 registros nulos, debidos a algun error en el codigo de descarga (deberia tratarse con el ingeniero de datos)

Data Cleansing & Automation

- ----------------------------------------------------------------------------------------------------------------------- ##2-LIMPIEZA DE DATOS -- ----------------------------------------------------------------------------------------------------------------------- -- PARTE 1 - Creamos la tabla de linkedin_results con los campos que nos interesan: CREATE TABLE linkedin_ofertas ( id_oferta INT, fecha_actualizacion DATETIME, nombre_empresa VARCHAR (200), fecha_busqueda_oferta DATETIME, fecha_publicacion_oferta DATE, Ubicacion_oferta VARCHAR(200), search_id_oferta INT, titulo_oferta VARCHAR(500), fecha_actualizacion_sp DATETIME, PRIMARY KEY (id_oferta) ); -- Limpiamos los datos y los insertamos en la tabla: SET @@SESSION.sql_mode='ALLOW_INVALID_DATES'; INSERT INTO linkedin_data.linkedin_ofertas SELECT id as id_oferta, TIMESTAMP(_fivetran_synced) as fecha_actualizacion, company_name as nombre_empresa, STR_TO_DATE(date,"%Y-%m-%d %H:%i:%s") as fecha_busqueda_oferta, date_published as fecha_publicacion_oferta, location as Ubicacion_oferta, searches as search_id_oferta, title as titulo_oferta, NOW() as fecha_actualizacion_sp -- Cada vez que este codigo se ejecute me devolvera la fecha y hora de la actualizacion FROM linkedin_data.raw_linkedin_results; -- Creamos un Stored Procedure (SP), que lo que hara es insertar los NUEVOS datos en la tabla cada vez que esta se actualice CREATE DEFINER=`root`@`localhost` PROCEDURE `update_table_linkedin_ofertas`() BEGIN SET @@SESSION.sql_mode='ALLOW_INVALID_DATES'; INSERT INTO linkedin_data.linkedin_ofertas SELECT id as id_oferta, TIMESTAMP(_fivetran_synced) as fecha_actualizacion, company_name as nombre_empresa, STR_TO_DATE(date,"%Y-%m-%d %H:%i:%s") as fecha_busqueda_oferta, date_published as fecha_publicacion_oferta, location as Ubicacion_oferta, searches as search_id_oferta, title as titulo_oferta, NOW() as fecha_actualizacion_sp -- Cada vez que este codigo se ejecute me devolvera la fecha y hora de la actualizacion FROM linkedin_data.raw_linkedin_results WHERE id NOT IN (SELECT id_oferta FROM linkedin_data.linkedin_ofertas); END -- Creamos un EVENTO que ejecutara nuestro SP cada diariamente: CREATE EVENT `Update_taable_lindekin_ofertas` ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(NOW() + INTERVAL 1 MINUTE) DO CALL update_table_lindekin_ofertas; SHOW EVENTS; -- Para comprobar que el evento se ha creado -- PARTE 2 - Creamos la tabla de linkedin_busquedas con los campos que nos interesan: CREATE TABLE linkedin_busquedas ( id_busqueda INT, fecha_actualizacion_busqueda DATETIME, fecha_busqueda DATETIME, keyword_busqueda VARCHAR(200), pais_busqueda VARCHAR(200), n_resultados_busqueda INT, fecha_actualizacion_sp DATETIME, PRIMARY KEY (id_busqueda) ); -- Limpiamos los datos y los insertamos en la tabla: INSERT INTO linkedin_busquedas SELECT id as id_busqueda, TIMESTAMP(_fivetran_synced) as fecha_actualizacion_busqueda, TIMESTAMP(date) as fecha_busqueda, keyword as keyword_busqueda, location as pais_busqueda, CAST(REPLACE(REPLACE(n_results,",",""),"+","") AS UNSIGNED) as n_resultados_busqueda, NOW() as fecha_actualizacion_sp FROM raw_linkedin_searches; -- Creamos un Stored Procedure (SP), que lo que hara es insertar los NUEVOS datos en la tabla cada vez que esta se actualice CREATE DEFINER=`root`@`localhost` PROCEDURE `update_table_linkedin_busquedas`() BEGIN SET @@SESSION.sql_mode = 'ALLOW_INVALID_DATES'; INSERT INTO linkedin_busquedas SELECT id as id_busqueda, TIMESTAMP(_fivetran_synced) as fecha_actualizacion_busqueda, TIMESTAMP(date) as fecha_busqueda, keyword as keyword_busqueda, location as pais_busqueda, CAST(REPLACE(REPLACE(n_results,",",""),"+","") AS UNSIGNED) as n_resultados_busqueda, NOW() as fecha_actualizacion_sp FROM raw_linkedin_searches WHERE id NOT IN (SELECT id_busqueda FROM linkedin_busquedas); END -- CREAMOS UN EJECUTADOR DEL SP CON EVENTOS (ejecutara el SP diariamente): CREATE EVENT `update_table_linkedin_busquedas` ON SCHEDULE EVERY 1 DAY -- frecuencia STARTS(TIMESTAMP(NOW() + INTERVAL 1 MINUTE)) -- comienzo DO CALL update_table_linkedin_busquedas(); -- accion SHOW EVENTS;

Reporting

1. Which companies have the highest number of job listings?

SELECT nombre_empresa, COUNT(*) AS n_ofertas FROM linkedin_data.linkedin_ofertas GROUP BY nombre_empresa ORDER BY n_ofertas DESC LIMIT 10;

2. How many job listings do we have in the table by location?

SELECT ubicacion_oferta, COUNT(*) AS n_ofertas FROM linkedin_data.linkedin_ofertas GROUP BY ubicacion_oferta ORDER BY n_ofertas DESC;

3. How many job listings do we have per day of publication?

SELECT DAYNAME(fecha_publicacion_oferta) AS dia_publicacion_oferta, COUNT(*) AS n_ofertas FROM linkedin_ofertas GROUP BY dia_publicacion_oferta ORDER BY n_ofertas DESC;

4. What are the top 10 job title roles used for posting job listings?

SELECT titulo_oferta, COUNT(*) AS n_ofertas FROM linkedin_data.linkedin_ofertas GROUP BY titulo_oferta ORDER BY n_ofertas DESC LIMIT 10;

5. What are the top 5 locations with the highest number of job listings?

SELECT ubicacion_oferta, COUNT(*) AS n_ofertas FROM linkedin_data.linkedin_ofertas GROUP BY ubicacion_oferta ORDER BY n_ofertas DESC LIMIT 5;

6. How many job listings combine keywords with the job title? Can you provide the count by both fields?

SELECT b.keyword_busqueda, o.titulo_oferta, COUNT(*) AS n_ofertas FROM linkedin_ofertas o LEFT JOIN linkedin_busquedas b ON o.search_id_oferta = b.id_busqueda GROUP BY 1,2 ORDER BY n_ofertas DESC;

7. How many job positions are labeled as "junior"? Can you provide the count by job title?

SELECT titulo_oferta, COUNT(*) AS n_ofertas FROM linkedin_ofertas o LEFT JOIN linkedin_busquedas b ON o.search_id_oferta = b.id_busqueda GROUP BY titulo_oferta HAVING titulo_oferta LIKE ('%junior%') OR titulo_oferta LIKE ('%jr%') OR titulo_oferta LIKE ('%intern%') OR titulo_oferta LIKE ('%entry-level%') OR titulo_oferta LIKE ('%entry%') ORDER BY n_ofertas DESC;

8. Can you now provide the count of job listings with the title "junior," but broken down by country?

SELECT pais_busqueda, COUNT(o.id_oferta) AS n_ofertas FROM linkedin_ofertas o LEFT JOIN linkedin_busquedas b ON o.search_id_oferta = b.id_busqueda WHERE titulo_oferta LIKE ('%junior%') OR titulo_oferta LIKE ('%jr%') GROUP BY pais_busqueda ORDER BY n_ofertas DESC;

9. Can we determine the number of job listings published per month and keyword? Which months are the most prominent, and with which keywords?

SELECT MONTH(fecha_publicacion_oferta) as mes_publicacion_oferta, keyword_busqueda, COUNT(*) n_ofertas FROM linkedin_ofertas o LEFT JOIN linkedin_busquedas b ON o.search_id_oferta = b.id_busqueda GROUP BY 1,2 ORDER BY n_ofertas DESC LIMIT 10;

Conclusions

Summary of Results:

Companies with the Highest Number of Job Listings: We identified companies with the highest number of job listings in the field of data analytics in different countries. This provides insights into the leading companies hiring professionals in this field.

Number of Job Listings by Location: We analyzed the geographical distribution of job listings, allowing us to understand where the highest demand is in terms of geographic location.

Number of Job Listings by Publication Day: We evaluated how many job listings are published daily, which can reveal patterns of activity in the data analytics job market.

Top 10 Most Common Role Titles: We identified the top 10 most common role titles used in job listings, helping us understand which types of roles are in high demand.

Locations with the Highest Number of Job Listings: The top 5 locations with the highest number of job listings were highlighted, which can be useful for professionals seeking job opportunities in specific locations.

Number of Job Listings by Keyword and Job Title Combination: We calculated the number of job listings that can be associated with specific keywords and job titles, allowing us to identify areas of specialization in data analytics.

Number of Job Listings Published by Month and Keyword: We analyzed the number of job listings published per month and identified the most relevant keywords for each month.

Achievement of Objectives: We successfully validated the data quality and conducted analyses that provide answers to the research questions, thereby fulfilling the project's objectives.

Impact and Relevance: The results obtained provide valuable information about the job market in the field of data analytics, which can have a significant impact on decision-making for professionals, companies, and academics interested in this area.

Limitations and Areas for Improvement: Limitations of the analysis may include the quality of web scraping data, which may contain errors or be incomplete. Furthermore, the analysis could be improved by using more advanced models and considering additional variables for a comprehensive analysis.

Recommendations:

Based on the findings, we recommend data analytics professionals consider locations with high demand, common role titles, and relevant keywords when seeking employment or specializing in this field.

Companies can use this information to guide their hiring strategies and better understand competition in hiring data analytics professionals.

We suggest improving the quality of web scraping data and exploring the possibility of incorporating additional data to enrich the analysis.

Reflection on the Process:

Challenges related to data quality and result interpretation were encountered. Lessons were learned about the importance of data cleaning and the need to fully understand the data context.

In future projects, best practices in data cleaning can be implemented, and advanced data mining techniques can be considered for deeper analysis.