Marketing, sales and HR analytics in practice

Customer segmentation

Good segments are measurable, sizeable, reachable, distinct and actionable.

Sales performance and productivity

Rep productivity = output ÷ input (revenue per rep, deals per 100 calls, revenue per hour). Compare like with like — territory potential, tenure and lead quality differ. Attainment vs plan corrects partly for territory size, if plans are fair.

SELECT e.job_level,
       COUNT(DISTINCT sp.employee_id) AS reps,
       ROUND(AVG(sp.sales_actual / sp.sales_plan) * 100, 1) AS avg_attainment_pct,
       ROUND(1.0 * SUM(sp.deals_closed) / SUM(sp.calls_made) * 100, 1) AS deals_per_100_calls
FROM sales_performance sp JOIN employees e ON e.employee_id = sp.employee_id
WHERE sp.year_month LIKE '2025%'
GROUP BY e.job_level ORDER BY avg_attainment_pct DESC;

HR analytics: workforce and attrition

WITH hc AS (
  SELECT
    SUM(hire_date <= '2025-01-01' AND (exit_date IS NULL OR exit_date > '2025-01-01')) AS start_hc,
    SUM(hire_date <= '2025-12-31' AND (exit_date IS NULL OR exit_date > '2025-12-31')) AS end_hc,
    SUM(exit_date BETWEEN '2025-01-01' AND '2025-12-31') AS leavers_2025
  FROM employees)
SELECT start_hc, end_hc, leavers_2025,
       ROUND(100.0 * leavers_2025 / ((start_hc + end_hc) / 2.0), 1) AS attrition_pct
FROM hc;

Drivers by department:

SELECT d.department_name,
       SUM(e.exit_date BETWEEN '2024-01-01' AND '2025-12-31') AS leavers_24_25,
       COUNT(*) AS ever_employed,
       ROUND(AVG(CASE WHEN e.exit_date IS NOT NULL THEN e.last_rating END), 2) AS avg_rating_leavers,
       ROUND(AVG(CASE WHEN e.exit_date IS NULL THEN e.last_rating END), 2) AS avg_rating_stayers
FROM employees e JOIN departments d ON d.department_id = e.department_id
GROUP BY d.department_name ORDER BY leavers_24_25 DESC;

Interpretation. High-volume departments (Sales, Customer Service) lose the most people; leavers have lower average ratings — consistent with both voluntary exits of disengaged staff and dismissals. Separate voluntary from involuntary before concluding.

Employee performance analytics

Ratings are subjective and manager-dependent; compare distributions across managers (calibration), combine with objective outcomes where they exist (sales attainment), and beware of using one metric to rank people with different roles.

Recruitment metrics

SELECT source, COUNT(*) AS candidates,
       SUM(furthest_stage = 'hired') AS hired,
       ROUND(100.0 * AVG(furthest_stage = 'hired'), 1) AS hire_rate_pct
FROM recruitment GROUP BY source ORDER BY hire_rate_pct DESC;

Ethics and privacy in people analytics

• Use aggregates; suppress groups smaller than ~5 people so individuals cannot be identified.
• Restrict access to HR data; log access.
• Don't build models that decide about individuals (e.g. "flight risk" scores) without legal review, transparency and human oversight; check for bias across protected characteristics.
• Use data only for the purpose it was collected for.

Practice

Summary

• Segments must be measurable, distinct and actionable.
• Productivity comparisons need like-for-like context.
• Attrition = leavers ÷ average headcount; separate voluntary/involuntary; analyse drivers carefully.
• People analytics demands aggregation, access control and fairness checks.