HR Survey Analysis Project

---

Instructor

Chris Dutton

---

Industry

Human Resources, Government

---

Project Brief

This project involves the analysis of survey response data from approximately 1,500 city employees in Washington state. The goal is to profile and QA the data, prepare it for visualization, and derive insights to share with the HR leadership team.

The Data Set

*** Data Type:** Excel *** Data Structure: Single table *** Number of Records: 14,725 *** Number of Fields:** 10 *** Description:** The dataset consists of employee survey responses, capturing feedback on various aspects of their job and work environment.

---

Objective 1: Profile & QA the Data

The first objective was to explore and QA the data by calculating basic profiling metrics, removing blank and duplicate records, and standardizing inconsistent text fields.

Tasks Completed

*Calculate Profiling Metrics: Calculated the minimum, maximum, count, and number of blanks for each numerical field.

Data Cleaning:

• Removed any records with blank responses to ensure completeness.
• Removed any records containing duplicate values across all fields to maintain data integrity.

Standardize Text Fields:

• Calculated the count or frequency of each value in the Department and Question fields.
• Standardized any inconsistencies found in these fields to ensure uniformity.

---

Objective 2: Prepare the Data for Visualization

The second objective was to produce clean source data for visualization by calculating response frequencies and proportions for each of the 10 survey questions.

Tasks Completed

Create Chart Source Tab:

• Generated a unique list of survey questions on a new tab named 'Chart Source'.

Calculate Response Frequencies:

• For each question, calculated the count of records associated with each response type (1-4) and the average response, excluding zeros.

Calculate Percentages:

• Added new columns to convert the counts into percentages, based on the total responses for 1, 2, 3, or 4.

Sort Data:

• Copied and pasted the data as values, then sorted the questions descending by average response.

---

Objective 3: Visualize the Data and Summarize Findings

The final objective was to visualize the data using stacked bar charts and Likert scales, and derive insights and recommendations to share with the HR leadership team based on the findings.

Tasks Completed

Create Stacked Bar Chart:

• Visualized the percentages as a 100% stacked bar chart, showing the question with the highest average response at the top.

Update Chart Colors:

• Updated colors to shades of orange or red for negative responses (1, 2) and shades of blue for positive responses (3, 4).

Enhance Readability:

• Added data labels and removed the x-axis, title, and vertical gridlines.
• Formatted individual chart elements to improve readability.

---

Recommendations Based on Insights

Insight 1: Lack of Workplace Friendships

Observation: More than 48% of responses to the question "I have a best friend at work" were in disagreement. This indicates that many employees feel isolated and lack close friendships at work.

Recommendation: To address this issue, HR and stakeholders can implement the following strategies to foster a sense of camaraderie and friendship among employees:

Weekly Colleague Meetings: Organize regular informal meetings where colleagues can interact and get to know each other better.

Team-Building Activities: Arrange team-building exercises and games that encourage collaboration and bonding.

Company Outings: Plan occasional company outings or team tours to provide employees with opportunities to connect outside the work environment.

Daily Check-Ins: Encourage HR and senior managers to have regular one-on-one conversations with employees, showing genuine interest in their well-being and fostering a friendly atmosphere.

Insight 2: Lack of Recognition and Praise

Observation: Approximately 45% of responses disagreed with the statement "In the last seven days, I have received recognition or praise for doing good work." This suggests that many employees feel undervalued and unappreciated.

Recommendation: To improve employee recognition practices, HR and managers can implement the following measures:

Regular Feedback: Establish a routine of providing regular and timely feedback to employees, acknowledging their efforts and contributions.

Recognition Programs: Create formal recognition programs where employees can be publicly acknowledged for their achievements, such as 'Employee of the Month' or 'Spotlight Awards'.

Peer Recognition: Encourage peer-to-peer recognition programs where employees can nominate and appreciate their colleagues for their hard work.

Personalized Praise: Ensure that managers take the time to personally thank employees for their specific contributions, making the recognition more meaningful and impactful.

By implementing these recommendations, HR and the leadership team can create a more positive and supportive work environment, enhancing employee satisfaction and productivity.