Preparing your data

Before using PayAnalytics to analyze and close you pay gaps, you need to prepare your data. This article offers a detailed explanation of mandatory fields needed for pay equity analysis, followed by a section of check points to set up your data for a smooth import experience.

The data preparation phase can be broken down in five easy steps:

1. Get familiar the structure of the data in PayAnalytics using our sample dataset.

2. Be aware of mandatory data fields.

3. Understand data formats and checks.

4. Troubleshoot common issues.

5. Discover the additional optional steps.

Sample data set

To operate properly and yield the expected results, the PayAnalytics software need to be fed a certain amount of mandatory, and optionally non mandatory, data.

The following figure illustrates a sample data sheet containing employee information intended for import into PayAnalytics.

Data set example

This is a tip message.

You can download a demo data set to practice data uploading and running analyses in PayAnalytics at the end of this article.

Mandatory data fields

Only a few factors are required to perform an initial pay equity analysis. In fact, you can start with only four data points:

• Employee unique identifier: a piece of information that uniquely singles out every employee. It often comes from an existing employee database or from your HR information system.

• Main grouping: criterion that groups different employees in a way that makes sense for your organization and gives you relatively substantial groups of employees. Job role, Job title, Global grade, Job family etc. are examples of groupings. You may also combine the job titles "Customer Service Associate I", "Customer Service Associate II" and "Customer Service Team Lead" into the job role 'Customer Service', for instance.

• Compensation information: pay information that the system will use to calculate pay gaps. To start, you only need one compensation type, such as a pay rate, a full-time equivalent salary, or a total discretionary compensation.

• Demographic variable: a characteristic that the system uses to calculate pay gaps between different individuals. You need at least one demographic variable; since most organizations are currently focusing on the gender pay gap, gender is the variable that is primarily included.

This is an note/information message.

For an overview of other optional factors that you can include in your data and your pay equity analysis, refer to Commonly used factors in Pay Equity Analysis.

Data formats &checks

You can load your data into the system either by uploading an Excel file (.xlsx) or by submitting data directly from payroll and human resources systems via API. To avoid loading errors, make sure your data file and your data satisfy the following requirements:

Data file basic checkpoints:

• The data file is not secured or password-protected.

• The Excel document is in a recent format (ending with .xlsx) and not an older format (ending with .xls).

• The data set is located in the first sheet within the file.

• The column names are in the top row, while all subsequent rows contain employee data.

Data field checkpoints:

• All the dates are converted into numerical variables, such as months or years. This can easily be done with the following Excel formula: =(TODAY()-” The cell with the date”)/365.25. The cells should be formatted as a number.

• The employee IDs are unique. It is advisable to use consistent unique IDs for employees across all data sets to facilitate the comparison between them.

• There are no missing values in columns intended to be numerical variables. If a cell doesn't contain any value, the system will automatically interpret the entire column as a text column.

• The spelling of strings representing categories is consistent, such as the name of the job group or educational requirements. For instance, the system reads 'Master's', 'MS' and 'MSc' as three different categories.

• The data set includes components for automatic calculations with the data later on. For more information, see Complex compensation data & modifying data.

• The currency converter is equipped with a currency code (EUR, USD, etc.) for the pay component.

• There are more then one pay component, if you want the system to aggregate several pay components.

• The part-time hours are converted to a percentage of full-time hours equivalent as the compensation component scaling requires an employment percentage to scale to FTE.

• The salaries of employees on leave reflect their full wages, and contain variable payments, like bonus payments and variable overtime payments, that are assessed over the appropriate period.

This is a tip message.

Remember that you can always start small and add more variables or columns as you go, and once you are ready.

Optional additional data setup

The following list gives you insights into some additional steps you might want to perform before you start using the PayAnalytics system, depending on your specific requirements:

• If you are using the data set to comply with local reporting requirements, additional column might need to be included to your data set. For an overview of the various local requirements supported by PayAnalytics, refer to Local requirements.

• For factors you might want to add to your data, see Commonly used factors in Pay Equity Analysis