Overview:

The Utilization Analysis aims to provide insights into the efficiency of the professional services team by examining two key metrics: Billable Utilization % and Effective Utilization %. These metrics offer valuable insights into how effectively team members are utilizing their time, particularly in terms of billable hours and overall productivity.

Data Sources:

The analysis utilizes data from multiple sources, including user data, project data, timecard data, and sales data. These datasets contain information about user demographics, project details, timecard entries, and sales records, which are essential for calculating utilization metrics.

Project Types and Time Utilization

Billable Utilization % is defined as the percentage of billable time a person spends out of the time they have available, where the available time excludes PTO. The project types included in billable utilization are:

Effective Utilization % is defined as the percentage of total productive (non-admin) time a person spends out of the time they have available, where the available time excludes PTO. The project types included in effective utilization are:

Non-Productive Time includes project types that are not considered productive or effective:

Methodology:

  1. Data Cleaning:

    • Process date columns in user_data and timecard data.
    • Replace ‘N/A’ values with NA in character columns of project data.

  2. Data Validation:

    • Ensure no users have start dates after end dates.
    • Ensures time logged is between 0 and 24 hours.
    • Filters timecard_data to retain only entries within the user’s employment period.

  3. Calculating Available Hours: Each user’s available hours are calculated based on their start and end dates, assuming a standard 40-hour work week from Monday to Friday.

  4. Determining Billable and Effective Hours: Monthly hours logged for billable projects and effective (productive) hours are aggregated from the timecard data, categorized by project type.

  5. Calculating Utilization Percentages: Utilization percentages are computed for both billable and effective hours, factoring in any Paid Time Off (PTO) taken by users.

  6. Summarizing by Team Role Code, Region and User Team: Utilization metrics are aggregated by team features and month to identify trends and patterns.

Billable Utilization By Team Role Code

Effective Utilization By Team Role Code

Table: Total Billable and Effective Time Utilization By Role Code

Team_Role_Code total_billable_logged_hours total_effective_logged_hours total_available_hours billable_hours_pct effective_hours_pct
PM 28370.317 29209.333 64574.22 43.93 45.23
RX-RA 15599.767 16253.667 24104.50 64.72 67.43
RX-RM 4424.350 6258.683 10096.00 43.82 61.99
RX-RS 9498.917 10183.217 14953.00 63.53 68.10

Billable Utilization By Team Region

Effective Utilization By Team Region

Table: Total Billable and Effective Time Utilization By Region

Team_Region total_billable_logged_hours total_effective_logged_hours total_available_hours billable_hours_pct effective_hours_pct
APAC 8967.267 9597.267 14595.55 61.44 65.75
EMEA 8535.717 10311.233 16826.50 50.73 61.28
NA 40390.367 41996.400 82305.67 49.07 51.02

Billable Utilization By User Team

Effective Utilization By User Team

Table: Total Billable and Effective Time Utilization By User Team

User_Team total_billable_logged_hours total_effective_logged_hours total_available_hours billable_hours_pct effective_hours_pct
PM-APAC 5481.383 5719.883 9149.55 59.91 62.52
PM-EMEA 4321.967 4698.633 9154.50 47.21 51.33
PM-NA 18566.967 18790.817 46270.17 40.13 40.61
RX-APAC-RA 2589.333 2685.583 3684.50 70.28 72.89
RX-APAC-RM 896.550 1191.800 1761.50 50.90 67.66
RX-EMEA-RA 1313.333 1528.833 1900.00 69.12 80.46
RX-EMEA-RM 1092.250 2094.250 2900.00 37.66 72.22
RX-EMEA-RS 1808.167 1989.517 2872.00 62.96 69.27
RX-NA-RA 11697.100 12039.250 18520.00 63.16 65.01
RX-NA-RM 2435.550 2972.633 5434.50 44.82 54.70
RX-NA-RS 7690.750 8193.700 12081.00 63.66 67.82

Time Utilization Trends by Project Type - Effective Time

Time Utilization Trends by Project Type - Billable Time

Insights

Billable Utilization Analysis

  • Highest billable utilization percentage: RX-APAC-RA (70.28%).
  • Lowest billable utilization percentage: RX-EMEA-RM (37.66%).
  • PM-NA has the highest total billable logged hours (18566.967).

Effective Utilization Analysis

  • Highest effective utilization percentage: RX-EMEA-RA (80.46%).
  • PM-APAC has a relatively high effective utilization percentage (62.52%).
  • PM-NA has the highest total effective logged hours (18790.817).

Regional Analysis

  • APAC has the highest billable (61.44%) and effective (65.75%) utilization percentages.
  • NA has the lowest effective utilization percentage (51.02%).

User Team Analysis

  • RX-APAC-RA has the highest billable and effective utilization percentages among user teams.
  • PM-NA has the highest total available hours (46270.17).

Recommendations

  1. Performance Analysis: IInvestigate factors contributing to the high billable and effective utilization percentages in high-performing teams (e.g., RX-APAC-RA, RX-EMEA-RA) to identify best practices and replicate them in other teams
  2. Training and Development: Provide training and support to teams with lower utilization percentages (e.g., PM-NA, RX-EMEA-RM) to improve efficiency and productivity.
  3. Resource Allocation: Balance utilization across teams and regions.
  4. Continuous Monitoring: Implement regular monitoring to track utilization trends.
  5. Seasonality Analysis: Analyze time utilization trends by month for seasonal patterns.
  6. Employee Engagement: Engage with team members to improve work-life balance and productivity.

By leveraging these insights and recommendations, Qualtrics can optimize workforce utilization and drive better business outcomes.