PayScale Inc.
Column Mapping UI
B2B Product Design, UX, UI | Spring/Summer 2019
PayScale is a B2B HR tech company, providing compensation management software to businesses across North America.
The users my product served were typically human resources generalists, with the occasional small business owner or HR manager/director. Most were between the ages of 30 and 60, worked out of an office with a laptop computer, and were proficient in Excel. They are incredibly busy and have very little time to dedicate to compensation management.
I worked as the sole designer on PayScale’s data upload product, alongside a Product Manager and a dedicated Engineering team.
The Problem
To keep their data current, users occasionally need to export their employees’ data from their HR system and upload that spreadsheet to PayScale. However, that spreadsheet can’t be uploaded to PayScale as-is. As there is no standard for HR field names (ie “Gender” vs “Sex”, “Hire Date” vs “Date of Hire”, etc.), the column headers on the raw exported spreadsheet don’t always match PayScale’s field names. Anything other than an exact match can cause upload errors.
The “solution” has always been to ask users to reformat their exported data into a PayScale-provided Excel template before giving their file to PayScale’s internal data upload team to process further. Filling in the template can take upwards of 8 hours, and in some cases it will still be sent back and forth multiple times for formatting revisions before it can be uploaded. The back-and-forth to fix these errors can sometimes take weeks. This inconvenience stops many users from refreshing their data at all.
The Project
Eliminate PayScale’s upload templates and time wasted on back-and-forth revisions by allowing users to submit raw HR data exports directly to the internal data upload team for further processing.
How?
Create an interface that lets users tell PayScale which field each of their spreadsheet’s columns should be imported into.
Research
Existing Flow
Research began by understanding all steps of the existing data upload flow:
Users export an employee data spreadsheet from their HR system
Users reformat this spreadsheet into PayScale’s provided template
Users upload the spreadsheet via PayScale’s secure file transfer
PayScale’s internal data uploads team receives the spreadsheet and reviews it for common formatting errors. If there are errors, the spreadsheet is returned to the user with instructions on what to adjust. The user fixes the errors and resubmits. If errors persist, the user may need to repeat this process multiple times.
The approved spreadsheet is processed by PayScale’s team, using a complex, custom, internal data loading tool
The user is notified via email that their data has been refreshed. Any employee that was unable to be uploaded because of errors is listed in an attached report.
Interviews
This flow gave me two groups of people to interview for differing perspectives:
PayScale Users
I spoke to users about their experience uploading data, their pain points, how long the process takes, which HR system they use, and what their most common formatting errors were.
PayScale’s Internal Data Upload Team
PayScale has a team of data uploaders who process client spreadsheets full-time, with the help of a complex internal data processing tool and robust knowledge of PayScale’s specific data formatting requirements. I spoke with them about how long their side of the process takes, which formatting errors occur most often, which fields cause the most trouble, and what has to be done to get the sheet formatted correctly the first time.
Example Spreadsheets
I asked users to show me what their HR system’s raw data exports look like, and compared exports from 3 popular HR systems to PayScale’s template.
Data Cleaning UI Review
I reviewed a selection of data cleaning tools on the market to look for existing patterns users might recognize.
Trifacta Wrangler is a heavy-duty data transformation platform with robust capabilities and many unique, helpful features. Its language and UI are both incredibly technical and seem to be aimed towards data engineers.
Flatfile is a user-facing data cleaning product. Its UI is clean and easy for a user unfamiliar with data to navigate. Its column mapping functionality is simple—a bit too simple for my product’s needs.
Excel is the tool our user base is most familiar with. They know how to complete Excel transformations with relative ease, but find it time consuming, especially when they’re unfamiliar with PayScale’s incredibly specific formatting requirements.
Conclusion & Required Functionality
After better understanding users’ needs and the existing landscape, and discussions with the Product and Engineering teams, I finalized a list of required functionality:
Spreadsheet Info
Users must be able to see the name of their spreadsheet, the name of the file it was extracted from, and the category of data that the spreadsheet includes
Required Fields
An upload can’t be processed without certain fields present. The user should be told which fields are necessary and should be able to see if they’ve completed them all.
Upload Type
There are different types of uploads with different results: adding new data, replacing existing data, and deleting data. The user needs to select the appropriate type.
Column Mapping
Users can view the names of the columns on their spreadsheet, and assign them to a corresponding PayScale field.
Suggested Fields
Engineering had built machine learning functionality that suggests the 3 most likely field matches for each column, with 80-90% accuracy. These 3 suggested fields need to be prominently displayed.
Field Search
If none of the suggested fields are a match, the user needs to be able to search for the correct field.
Ignore a Column
Users need to be able to opt out of uploading certain columns altogether.
Progress Indicator
The Product Manager envisioned this UI as the first step in an eventual larger, user-driven data upload process where users upload more than one spreadsheet at a time and perform more intricate data validation. In that scenario, users would need a progress indicator to orient themselves in this multi-step process.
Navigation
In a potential multi-step process, users need to navigate back and forth.
Save/Exit
In case users don’t have time to complete the rather long process in one sitting, they can save their progress and finish it later.
Wireframe Iterations
Mid-fidelity concepts were explored in the wireframe stage, using PayScale’s existing design system. They were refined with critique from the Product and Engineering teams, as well as UX peers.
Layout A: Progress bar on left, required fields on bottom
Layout B: Progress bar on right, required fields beneath title
Layout C: No progress bar, with most common values graph
Layout D: Progress bar on bottom, with most common values graph
Proposed Solution
The user begins by selecting an Upload Type on the left, with a tooltip giving more information on each of the five upload types. Changing the Upload Type will also change the Required Fields.
User switching between two upload modes
On the body of the page is a series of boxes, one for each column. The column name is displayed at the top, and beneath are four selector pills. The first three pills are populated with suggested fields, generated by machine learning. The fourth pill always reads “other”.
Beneath the pills is a preview of the three most reoccurring values in that column. In parentheses next to each value is the number of times the value occurs in the column. This is intended to give the user a preview of the data contained in this column without displaying the full table, so they don’t have to switch between windows to ensure they are mapping correctly.
The user maps their column by selecting a pill and clicking Confirm Mapping. The box then collapses, and the confirmed mapping is displayed. If the column was mapped to a required field, it will be reflected in the Required Fields bar at the top.
User mapping the Employee Number column to the Employee ID field, then confirming
To map to a column that hasn’t been auto-suggested, the user selects “Other”. This brings up an autocomplete search box. Once a selection has been made, the user confirms their mapping and the box collapses.
User searching for a column name with the “Other” pill
Users can click Ignore Column to opt it out of the import.
Mapped and ignored columns can be edited at any time by clicking Edit.
User editing an ignored column and assigning a mapping
Users will see an error message if they attempt to map two columns to the same field.
Error message on a selected pill
Error message on search box
All of a user’s mappings are remembered and reapplied during subsequent uploads. The user will only need to re-map a column if its header name changes, or if they include new unrecognized columns.
User Testing
While InVision prototypes were used during initial design reviews, we worked with a philosophy that anything shown or tested outside of the Design, Product, and Engineering teams should be done so in code. A prototype of V1 was built by engineering and used for moderated usability sessions.
V1 testing took place with PayScale’s data upload team. Some participants had previously been interviewed for the project; others were entering the test sessions blind.
Methodology
A client had provided us with a spreadsheet containing raw, unformatted employee data that had been exported from their HR system. This spreadsheet was fed into the product, populating the column mapping UI with realistic data.
I met with testers one-on-one, in-person. Screen and voice were recorded for future review.
Testers were given a laptop displaying the data-populated UI. They were instructed to guide themselves through the page as if they were updating an existing client’s account, and to speak their train of thought out loud as they were doing so. I observed and listened to their narrations.
When confused testers asked for guidance, I encouraged them to explore the page a bit more and speak about what they found confusing. If testers hit a roadblock they couldn’t solve, I gently guided them in the right direction and asked for their comments.
I concluded each session with a wrap-up discussion, asking testers to summarize their thoughts and experiences.
Findings
Testing uncovered both UX issues technical bugs. Issues pertinent to design included:
Problem
Many testers missed the Upload Type selection on the left side of the page
Move Upload Type selection to an earlier, separate part of the flow
Solution
Problem
A majority of users misinterpreted the “other” pill. They thought the three suggested fields were their only options, and that selecting “other” would put that column into a field named “other”. They didn’t know it would bring up a search bar to select from.
Rename the “other” pill to something more actionable, like “search all fields…”
Solution
Problem
“Ignore column” wording confused some users
Rename “ignore” to something more explicit, like “do not import”
Solution
Problem
Most users did not see the Required Fields section at the top of the page
Move the Required Fields somewhere more prominent
Solution
Problem
The test spreadsheet had many named columns that contained no data. Empty columns like this are common in HR data exports, and they are not meant to be uploaded to PayScale. Columns in the UI are displayed in the same order as the uploaded spreadsheet, so testers had to manually check which columns were empty and manually map all of them to “ignore”. This was incredibly time consuming.
Auto-map all empty columns to “do not import”, and hide them inside of a collapsed accordion beneath the populated columns
Solution
Problem
No one understood what Most Common Values meant, or how it was supposed to be helpful. Users said they wanted to see the first few values from the original column to help them be sure of what information the column contains.
Replace “most common values” with a row preview
Solution
Problem
Testers also wanted a way to reference the in-account data already stored in each PayScale field, to help double check that the column is being mapped to the right location
Add a row preview for the destination field as well
Solution
Revised Solution
User feedback was incorporated and a V2 design was presented for development.
Upload Mode was removed from the page.
Required Fields were moved to the left side.
The “Other” pill was renamed “Search all fields…”
“Ignore” was renamed “Do Not Import This Column”.
The cards were redesigned to incorporate a dynamic value preview. Values from the original column are shown on the left. When the user hovers over a field pill, the column on the right is populated with values from that field in the user’s account. These values stay when a field is selected.
User mapping a column in the new layout, with value previews
Card ordering was adjusted for ease of use and to reduce scrolling. Cards are now presented in the following order:
Unmapped columns that require user action are displayed first, with the card open
Confirmed mappings remembered prior uploads are next, with the card closed. Users can review and edit individually as necessary.
Collapsed inside an accordion are all empty columns, set to Do Not Import, with the card closed
User expanding and collapsing the according hiding empty columns
