peopleanalyst

library / lib02ef40f16da17540

Predictive HR Analytics, Text Mining & Organizational Network Analysis_ with Excel

In a sentence

A practical, do-it-yourself guide showing HR professionals how to run predictive analytics, text mining, sentiment analysis, and organizational network analysis entirely in Microsoft Excel to drive better business decisions.

This is the only book that teaches Predictive HR Analytics, Text Mining, and Organizational Network Analysis using tools you already own and know—Microsoft Excel and free add-ins—without months of learning R or buying expensive SPSS software. Through step-by-step print-screen instructions, it walks you from defining a business problem through the ARHAT framework, gathering and analyzing data with decision trees, correlation, multiple and logistic regression, mining unstructured text into word clouds and sentiment scores, and mapping employees' social networks into measurable centrality metrics. Packed with real-world case studies (Best Buy, Nielsen, Xerox, HP, Hilton, JetBlue) and dozens of HR metrics, it shows you how to predict attrition, performance, engagement's impact on sales, diversity's impact on EBIT, and workplace accidents—and crucially, how to translate those findings into an engaging data story that drives change.

The story it tells the reader

The reader An HR or people analytics professional who wants to deliver data-driven recommendations that improve business performance and establish credibility with executives.

External problem

They need to run predictive analytics, mine text, and analyze networks but lack expensive software, programming skills, and a structured method.

Internal problem

They feel intimidated by statistics and machine learning and fear their analytics won't be trusted or won't drive change.

Philosophical problem

HR shouldn't be relegated to a cost center reporting what happened; it deserves to influence strategy with fact-based prediction—just like Finance and Sales.

The plan

  1. Learn the basics of machine learning, statistics, and the analytics maturity model
  2. Apply the five-step ARHAT framework to a real, sponsor-backed business problem
  3. Install free Excel add-ins (Analysis ToolPak, Solver, NodeXL, Azure ML) following step-by-step instructions
  4. Run decision trees, correlation, regression, logistic regression, text mining, sentiment analysis, and ONA
  5. Translate findings into a data story with narrative and visuals to drive stakeholder action

Success

  • You predict attrition, performance, and engagement impact with confidence
  • You uncover actionable insights from text and social networks
  • You tell compelling data stories that win executive approval and drive change
  • You become a trusted, strategic, analytically driven HR leader

At stake

  • Your analytics projects stall for lack of a strong sponsor or data access
  • Your insights get ignored because you couldn't tell an engaging story
  • HR remains a cost center, losing influence and missing opportunities to retain talent and improve results

Model of the world · 14 constructs · 21 relations

A causal model in which HR design levers and contextual conditions (compensation, training, diversity practices, network position, demographics, sentiment) influence psychological and behavioral states (engagement, job satisfaction, flight risk) that in turn drive organizational outcome metrics (attrition, performance, sales, EBIT, safety incidents). The analytics process itself (ARHAT framework + storytelling) is the meta-lever that surfaces these relationships.

Design levers

  • Compensation Competitiveness
  • Training & Development Investment
  • Diversity & Inclusion
  • Analytics & Data Storytelling Process

Intermediate states & behaviors

  • Employee Engagement
  • Employee Flight Risk
  • Organizational Network Position
  • Employee Sentiment

Outcomes

  • Employee Attrition & Absenteeism
  • Employee & Sales Performance
  • Business Financial Outcomes
  • Workplace Safety Incidents

Moderators / context: Employee Demographics & Context · Personality Traits

Consolidated shape of the book’s model — full constructs and relationships below.

Employee Engagementpsychological state

The degree of emotional commitment, enthusiasm, and discretionary effort employees invest in their work and organization, measured through engagement surveys and net promoter scores.

Compensation Competitivenessdesign lever

How an employee's pay compares to the external market and internal salary ranges, expressed through market-ratio, compa-ratio, and pay-spread metrics; a key design lever affecting retention and performance.

Training & Development Investmentdesign lever

The provision, quality, and perceived effectiveness of learning and development opportunities offered to employees, including training satisfaction, knowledge gained, and on-the-job application.

Diversity & Inclusiondesign lever

The composition of the workforce across ethnicity, gender, and other traits (quantified via Simpson's Diversity Index) combined with inclusive practices that give employees equal access to opportunities.

Organizational Network Positionbehavioral pattern

An employee's structural position in the organizational social network, quantified through centrality metrics (degree, betweenness, closeness, eigenvector) and network reach, immersion, and diversity.

Employee Demographics & Contextcontextual condition

Background and situational characteristics of employees including age, tenure, marital status, gender, commute time, and triggering life events that condition retention and accident likelihood.

Personality Traitscontextual condition

Stable individual dispositions such as conscientiousness, extraversion, agreeableness, creativity, and grit measured through personality assessments and used to predict performance and retention.

Employee Sentimentpsychological state

The polarity (positive, negative, neutral) of opinions employees and candidates express in text such as Glassdoor reviews, survey comments, and social posts, scored through sentiment analysis.

Employee Flight Riskpsychological state

The estimated probability that an employee will voluntarily resign, derived from combinations of low pay relative to market, high performance, demographics, network position, and behavioral signs.

Employee Attrition & Absenteeismoutcome metric

The actual rate at which employees leave the organization (voluntary turnover) and the frequency of unplanned absence, key outcome metrics the book repeatedly predicts.

Employee & Sales Performanceoutcome metric

The output and effectiveness of employees including performance ratings, sales revenue, customer service quality, and productivity, treated as a central outcome predicted from multiple drivers.

Business Financial Outcomesoutcome metric

Organization-level financial and operational results such as EBIT, profit margin, revenue, market share, total shareholder return, and customer satisfaction influenced by HR states and practices.

Workplace Safety Incidentsoutcome metric

The frequency of workplace accidents, injuries, and recordable safety cases, an outcome metric predicted from engagement, demographics, tenure, and earnings pressure.

Analytics & Data Storytelling Processdesign lever

The structured ARHAT framework and data-storytelling practice (data, visuals, narrative) through which HR surfaces relationships and communicates them to stakeholders to drive change.

How they connect

  • employee engagement → predicts employee performance
  • employee engagement − predicts employee attrition
  • employee engagement − predicts safety incidents
  • employee engagement → predicts business financial outcomes
  • compensation competitiveness − predicts flight risk
  • compensation competitiveness → influences employee performance
  • training development → predicts employee performance
  • training development − predicts employee attrition
  • diversity inclusion → predicts business financial outcomes
  • diversity inclusion − predicts employee attrition
  • network position → predicts employee performance
  • network position − predicts flight risk
  • employee demographics → moderates employee attrition
  • employee demographics → predicts safety incidents
  • personality traits → predicts employee performance
  • personality traits − predicts flight risk
  • employee sentiment − predicts employee attrition
  • flight risk → predicts employee attrition
  • employee attrition − predicts business financial outcomes
  • employee performance → predicts business financial outcomes
  • analytics storytelling process → moderates business financial outcomes

Possible measures & feedback loops

A candidate team / org survey built from this book’s model — exploratory operationalizations, not validated instruments. Where a construct maps to a validated measure in Principia, we’ll point to that instead.

Employee Engagement

Engagement index %; eNPS score; Promoter/detractor percentages

self-report suitability: high

Compensation Competitiveness

Market-ratio; Compa-ratio; Pay spread (std dev of merit increase)

self-report suitability: low

Training & Development Investment

Reaction scores; Pre/post test gains; Training hours per FTE; ROI %

self-report suitability: high

Diversity & Inclusion

Simpson's Diversity Index; Female percent; Diversity percentage; Inclusion survey ratings

self-report suitability: medium

Organizational Network Position

Degree centrality; Betweenness centrality; Closeness centrality; Eigenvector centrality

self-report suitability: medium

Employee Demographics & Context

Age; Tenure; Marital status; Commute time (minutes); Triggering event flag

self-report suitability: high

Personality Traits

Conscientiousness score; Extraversion score; Agreeableness score; Grit/creativity score

self-report suitability: high

Employee Sentiment

Sentiment score (0-100%); Polarized word counts; Glassdoor star rating

self-report suitability: medium

Employee Flight Risk

Predicted resignation probability; Flight risk score by group

self-report suitability: low

Employee Attrition & Absenteeism

Voluntary turnover rate; Absenteeism rate; First-year resignation rate

self-report suitability: none

Employee & Sales Performance

Performance rating; Sales revenue; Customer service score; Output per FTE

self-report suitability: low

Business Financial Outcomes

EBIT; Profit margin; Revenue; Market share; TSR; Customer satisfaction index

self-report suitability: none

Workplace Safety Incidents

Accident count; Injury rate; Recordable case frequency

self-report suitability: none

Analytics & Data Storytelling Process

Recommendation adoption rate; Stakeholder buy-in; Project impact measures

self-report suitability: medium

Preview the survey →

Frameworks & instruments in this book

  • Always start with a real, sponsor-supported business problem before doing analytics
  • Don't reinvent the wheel—review existing literature and other companies' hypotheses
  • Formulate clear, simple, testable hypotheses of the form 'if X is done, Y will happen'
  • Use the data you already have before collecting new data, and handle missing data and outliers deliberately
  • Check statistical significance (Significance F < 0.05, P-values < 0.05) before trusting a model
  • Measure analytics success by the impact made, not the number of reports produced

Several of these are operationalized as tools in the People Analytics Toolbox.

Topics

Related in the library