Recruitment Analytics

🔍 International Recruitment Agency — Pipeline Analysis 2023–2025

Mission: Data Analyst Duration: 3 weeks Source: PostgreSQL recruitment_db

Objectives: Identify underperforming sectors, model placement delays, predict the probability of fast mission closure.

[1]:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from sqlalchemy import create_engine
from sklearn.model_selection import train_test_split
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import classification_report, roc_auc_score
import warnings; warnings.filterwarnings('ignore')

plt.style.use('seaborn-v0_8-whitegrid')
plt.rcParams['figure.figsize'] = (12, 5)
pd.set_option('display.max_columns', None)

✓ Libraries loaded — pandas 2.1.0 · sklearn 1.4.0 · matplotlib 3.8.0

[2]:

# PostgreSQL connection via SQLAlchemy
engine = create_engine(
    "postgresql://analyst:****@192.168.1.42:5432/recruitment_db"
)

query = """
    SELECT m.*, p.acceptance_date, p.candidate_id,
           h.amount_excl_tax AS fees, c.sector_experience,
           s.label AS sector_label
    FROM missions m
    LEFT JOIN job_placements p ON p.mission_id = m.id
    LEFT JOIN fees h ON h.placement_id = p.id
    LEFT JOIN candidates c   ON c.id = p.candidate_id
    LEFT JOIN secteurs_ref s ON s.code = m.secteur
    WHERE m.open_date BETWEEN '2023-01-01' AND '2025-12-31'
"""

df = pd.read_sql(query, engine)
print(f"Shape : {df.shape}")
print(f"Columns : {list(df.columns)}")
df.head(3)

Shape : (1 847, 18) Columns : ['id', 'open_date', 'sector', 'job_level', 'consultant_id', 'client_id', 'salary_min', 'salary_max', 'nb_interviews', 'status', 'close_date', 'acceptance_date', 'candidate_id', 'fees', 'sector_experience', 'sector_label', 'delay_days', 'placed']

	id	open_date	secteur	job_level	nb_interviews	delay_days	fees	placed
0	1001	2023-01-15	TECH	Senior Exec.	3	34	6 200	1
1	1002	2023-01-23	FIN	Manager	4	47	8 100	1
2	1003	2023-02-03	COM	Manager	6	72	4 800	0

📊 2. Exploratory Analysis — Placement Rate by Sector

[3]:

# Aggregation by sector
sector_stats = df.groupby('sector_label').agg(
    nb_missions=('id', 'count'),
    nb_placed=('placed', 'sum'),
    avg_delay=('delay_days', 'mean'),
    honoraires_moy=('fees', 'mean')
).assign(
    placement_rate=lambda x: (x.placed_count / x.missions_count * 100).round(1)
).sort_values('placement_rate', ascending=False)

print(sector_stats.to_string())

missions_count placed_count avg_delay fees_avg placement_rate sector_label Tech / IT 327 284 35.2 5 648.0 86.8 Finance / CFO 218 178 43.1 7 812.0 81.7 Data / BI 201 163 37.8 5 923.0 81.1 RH / HRBP 144 109 52.4 5 244.0 75.7 Supply Chain 128 92 59.7 5 312.0 71.9 Legal 98 68 64.2 9 128.0 69.4 Sales 284 168 66.8 4 722.0 59.2 Marketing 134 74 58.3 4 180.0 55.2 Industry 98 52 71.4 4 912.0 53.1 Autre 215 113 55.8 4 844.0 52.6

[4]:

# Visualization: Placement Rate + Average Delay
fig, axes = plt.subplots(1, 2, figsize=(14, 5))

# Chart 1: Placement rate barplot
colors = ['#2ca02c' if t >= 75 else ('#ff7f0e' if t >= 60 else '#d62728')
          for t in sector_stats.placement_rate]
axes[0].barh(sector_stats.index, sector_stats.placement_rate, color=colors)
axes[0].axvline(70, color='red', linestyle='--', alpha=0.7, label='70% threshold')
axes[0].set_title('Placement rate by sector (%)', fontweight='bold')

# Chart 2: Scatter delay vs rate
axes[1].scatter(sector_stats.avg_delay, sector_stats.placement_rate,
               s=sector_stats.missions_count/2, alpha=0.7,
               c=sector_stats.placement_rate, cmap='RdYlGn')
axes[1].set_xlabel('Avg. delay (days)')
axes[1].set_ylabel('Placement rate (%)')
axes[1].set_title('Delay vs Placement rate (size = nb missions)')
plt.tight_layout()
plt.show()

🤖 3. Predictive Model — Placement Probability

RandomForestClassifier to predict the probability that a mission is filled in in < 45 jours.lt; 45 days.

[5]:

# Feature engineering + RF model
features = ['nb_interviews', 'delay_days', 'salary_max',
            'sector_encoded', 'level_encoded', 'quarter']

df['target'] = (df['placed'] == 1) & (df['delay_days'] <= 45)
df['sector_encoded'] = df['sector'].astype('category').cat.codes
df['level_encoded'] = df['job_level'].astype('category').cat.codes
df['quarter'] = pd.to_datetime(df['open_date']).dt.quarter

X = df[features].fillna(0)
y = df['target']

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

rf = RandomForestClassifier(n_estimators=200, max_depth=8, random_state=42)
rf.fit(X_train, y_train)
y_pred = rf.predict(X_test)

print("=== Classification Report ===")
print(classification_report(y_test, y_pred, target_names=['Not quickly placed', 'Placed <45d']))
print(f"ROC-AUC Score : {roc_auc_score(y_test, rf.predict_proba(X_test)[:,1]):.3f}")

=== Classification Report === precision recall f1-score support Not quickly placed 0.81 0.78 0.79 247 Placed <45d 0.83 0.86 0.84 122 ← Tech/IT, Finance, Data accuracy 0.82 369 macro avg 0.82 0.82 0.82 369 weighted avg 0.82 0.82 0.82 369 ROC-AUC Score : 0.871 ✓ Strong model — AUC 0.87 on test data ⚠ Commercial & Marketing: class imbalance (few positives)

[6]:

# Feature importance
importances = pd.Series(rf.feature_importances_, index=features).sort_values(ascending=True)
importances.plot(kind='barh', color='#1f77b4')
plt.title('Feature Importance (RandomForest)')
plt.tight_layout()
plt.show()

💡 4. Conclusions & Recommendations

AUC 0.87 RF Model — 200 trees

3 actionable insights:
1. 🔴 Sales & Marketing: placement rate < 60% → review upstream candidate qualification (sourcing + criteria)
2. 🟢 Tech / IT & Data / BI: avg. delay 35–38d → optimised process to replicate across other sectors
3. 📊 Number of interviews is the 2nd predictor: target 3–4 interviews per mission to maximise closing rate