📊 Recruiting Funnel Analysis

End-to-end recruiting analytics project — synthetic dataset of 10,500 candidate records, Python (Spyder) analysis pipeline, and a full Power BI dashboard guide covering stage conversion, time-to-hire, source effectiveness, and diversity metrics.

---

🗂️ Project Structure

Recruiting_Funnel_Analysis/
├── data/
│   └── recruiting_funnel_data.csv      ← 10,500 synthetic records (35 columns)
├── scripts/
│   ├── 01_generate_data.py             ← Dataset generation with realistic funnel logic
│   └── 02_funnel_analysis.py           ← Full analysis + chart exports (Spyder-ready)
├── powerbi/
│   └── PowerBI_Setup_Guide.md          ← Step-by-step Power BI guide + all DAX measures
├── outputs/
│   ├── 01_funnel_chart.png             ← Stage volume & conversion
│   ├── 02_time_to_hire.png             ← TTH by BU and job level
│   ├── 03_source_effectiveness.png     ← Source conversion, volume, TTH
│   ├── 04_diversity_funnel.png         ← Gender & ethnicity through stages
│   ├── 05_bu_bubble.png               ← BU performance bubble chart
│   ├── 06_quarterly_trend.png          ← Hiring trend over time
│   ├── summary_funnel.csv
│   ├── summary_source.csv
│   ├── summary_business_unit.csv
│   ├── summary_recruiter.csv
│   └── summary_quarterly_trend.csv
└── README.md

---

🔑 Key Metrics Analyzed

Category	Metrics
Stage Conversion	Applied → Screen → Technical → Onsite → Offer → Hire rates
Time-to-Hire	Avg/median TTH overall, by BU, job level, source, and quarter
Source Effectiveness	Conversion rate, application volume, offer acceptance, avg TTH by source
Diversity	Gender & ethnicity representation at every funnel stage
Business Unit	Conversion rate, TTH, offer acceptance by BU and EMT
Recruiter Performance	Hires, conversion rate, avg TTH per recruiter
Quarterly Trend	YoY hiring volume and conversion rate trends (2022–2024)

---

📋 Dataset Schema

Column	Type	Description
candidate_id	string	Unique candidate identifier
application_date	date	Date of application
application_year	int	Year of application
application_quarter	string	e.g., Q1 2023
business_unit	string	Engineering, Sales, Product, etc.
emt	string	Executive owner (CTO, CRO, CPO, etc.)
level3_manager	string	Level 3 hiring manager name
job_title	string	Job title applied for
job_level	string	IC1–IC6 / M1–M4
tbp_range	string	Total Base Pay range band
tbp_midpoint_usd	float	Midpoint of TBP band in USD
recruiter	string	Assigned recruiter
source	string	LinkedIn, Referral, Indeed, etc.
location	string	Office location or Remote
region	string	West, East, Central, Remote, International
gender	string	Male, Female, Non-binary, Prefer not to say
ethnicity	string	Self-identified ethnicity
age_group	string	22–29, 30–39, 40–49, 50+
stage_phone_screen_date	date	Date of phone screen (if reached)
stage_tech_assessment_date	date	Date of technical assessment (if reached)
stage_onsite_date	date	Date of onsite/final interview (if reached)
stage_offer_date	date	Date offer was extended (if reached)
start_date	date	First day of employment (hired only)
stage_reached	string	Furthest stage reached
final_disposition	string	Hired / Rejected / Withdrew / Offer Declined
offer_accepted	bool	True/False (for candidates who received offers)
rejection_reason	string	Reason for non-hire
offer_amount_usd	float	Offer salary (if extended)
days_applied_to_screen	int	Days from application to phone screen
days_screen_to_tech	int	Days from screen to technical assessment
days_tech_to_onsite	int	Days from technical to onsite interview
days_onsite_to_offer	int	Days from onsite to offer
total_days_to_offer	int	Total days from application to offer
total_days_to_hire	int	Total days from application to start date

---

📈 Key Findings (Synthetic Data)

• Overall hire rate: 8.1% — consistent with typical tech/corporate recruiting benchmarks
• Referral is the #1 source by conversion rate (11.0%), beating LinkedIn (7.0%) by 57%
• Avg time-to-hire: 63 days — driven mainly by the Technical Assessment stage (~13 days avg)
• Offer acceptance rate: 74% — Glassdoor sourced candidates had the lowest acceptance (62%)
• Diversity drop-off: Female representation declines from 46% (applied) to 44% (hired); Asian representation declines from 22% to 19% through the funnel
• Sales closes fastest (60.7 days avg) while Legal takes longest (65.5 days)
• M1-level roles have the shortest time-to-hire (60.1 days), likely due to lower complexity

---

⚙️ How to Run

Requirements

pip install pandas numpy matplotlib seaborn

Step 1: Generate the Dataset

python scripts/01_generate_data.py

Output: data/recruiting_funnel_data.csv (~10,500 records)

Step 2: Run Analysis & Export Charts

python scripts/02_funnel_analysis.py

Outputs: 6 charts + 5 summary CSVs saved to outputs/

In Spyder IDE

1. Open scripts/02_funnel_analysis.py in Spyder
2. The script uses # %% cell markers — run cells individually with Ctrl+Enter
3. Change matplotlib.use('Agg') to matplotlib.use('Qt5Agg') to display plots inline

Power BI Dashboard

See powerbi/PowerBI_Setup_Guide.md for:

• Data import steps
• Date table creation
• 20+ DAX measures (volume, conversion, TTH, diversity, YoY)
• 6 dashboard page layouts with recommended visuals

---

🛠️ Tech Stack

Tool	Usage
Python 3.10+	Data generation & analysis
pandas	Data wrangling
NumPy	Statistical simulation
matplotlib / seaborn	Visualization
Spyder IDE	Interactive analysis
Power BI Desktop	Dashboard & reporting

---

💡 Potential Extensions

• Connect to an ATS (Greenhouse, Lever, Workday) via API for live data
• Add ML model to predict offer acceptance based on source, role, and TBP
• Build automated monthly report using Python + Jinja2 templating
• Add cost-per-hire analysis if budget/agency fee data is available
• Implement Recruiter SLA tracking (# of open reqs, time-in-stage alerts)

---

👤 Author

Anushree Iyer 📧 anushreeayyar@gmail.com

---

This project uses fully synthetic data generated for portfolio and demonstration purposes only.