/

Preparing and Importing Market Data

Preparing Your Data

The method to import survey data is as follows:

  • Get the survey data into a spreadsheet and make the first row contains column names
  • Save the spreadsheet as a tab-delimited text file
  • Transmit the text file to the account reserved for your organization (FTP account)

Step 1: Choose the Surveys

It may be useful to fill out a form to document your survey sources: This is a suggestion only. This document will not be used in the import process but may be helpful in tracking surveys imported over time.

Survey Source / Survey Label / Effective Date / Pay Represented As / Default Weight / Default Adj / Survey Type / File Name / User Filter Code (opt)
ECS / 2001 Office Personnel – Geographic / 01/01/2001 / AAnnual1
MMonthly12
SSemimonthly24
BBiweekly26
WWeekly52
HHourly 2,080
NNurses 12hr 2,184 / 100 / 0 / PPay1
RRegression / 2001_Office_Geo.txt
ECS / 2001 Office Personnel – Industry / 01/01/2001 / A / 100 / 0 / P / 2001_Office_Ind.txt

Step 2: Get the Market Survey data into a Spreadsheet

  • Survey data is often provided in a spreadsheet format (or a format that can be used in a spreadsheet, such as a “csv” file). Get the survey data into a spreadsheet and make the first row contains column names. The order of columns does not matter.
  • Alternatively, survey data can be manually entered into the spreadsheet

Either way, you will need the following information, one item per column:

Suggested Column Heading / Required / Field Characteristics / Column Description
JobCode / Required / Max20 characters / The job code for the survey
JobTitle / Required / Max 100 characters / The job title
Level / Not Required unless the combination of Code, Title & Scope do not differentiate between records. / Max 10 characters / The survey job level, often an integer or terms such as “Jr”, “Intermediate” or 1, 2,3, etc
Scope
Scope may be defined in multiple columns, in which each column must have its own heading such as “Profitability” or “Location”.
You must have at least one column that defines scope. / Yes. One concatenated column is preferred, such as “Scope” (All orgs / Central / Chicago) but more are allowed. Note Column name restrictions below; cannot use “Scope1” or “Scope2”, etc. / Max 255 characters / The particular cut of data. Scopes involve one or more combinations of a geographic region, an industry, or sales revenue. It may also be defined as “for-profit” or “not-for-profit”.
Oftentimes scope is represented in multiple columns: one column for profitability, another column for geographic region.
Sometimes the nature of the survey itself is a scope, such as The 2001/2002 ABC Company’s Information Technology Positions In The Western States Compensation Report. In this case, there may not be a column representing scope. It is important, however, to include some description in the Scope column of the spreadsheet.
Search_Code1 / Not Required / Max 255 characters / Placeholders to store your own codes. This helps you find specific market data quickly. See ”Defining Special Searches” below for suggestions.
Search_Code2 / Not Required / Max 255 characters
Search_Code3 / Not Required / Max 255 characters
Grade / Not Required / Max 10 characters / ECS Global Grade
Companies / Not Required / Whole numbers only / The number of companies represented
Incumbents / Not Required / Whole numbers only / The number of incumbents represented
Comment / Not Required / Max 500 characters / A comment about the job (up to 500 chars)
JobDesc / Not Required / Max 3000 characters; no embedded carriage returns. / The survey job description
LevelDesc / Not Required / Max 3000 characters; no embedded carriage returns. / The survey job level

The following column heading names are not allowed:

  • Desc
  • isNew
  • globalgrade
  • scope1
  • scope2
  • scope3
  • scope4
  • scope5
  • surveyJobComment
  • Function
/
  • surveyTicketID
  • sJCompanies
  • sJIncumbents
  • sJPageNumber
  • sJScope
  • sJobCode
  • sJobDesc
  • sJobID
  • sJobTitle
  • JobID
/
  • sLevel
  • sLevelDesc
  • sRank
  • surveyID
  • sJobDesc
  • sLevelDesc
  • search1
  • search2
  • search3

Column Names

Rules for column names are:

  • Column names should indicate both the pay type and the comparator. “Base25” is good because it indicates both “base pay” and “25th percentile”
  • Column names cannot contain spaces. Use_an_underbar_instead_of_a_space. OrUseInitialCapLetters.
  • Column names cannot symbols. A column name of “25%tile” is not allowed. Use “Base25” instead.
  • Column names cannot start with numbers. Instead of “25Base” use “Base25”.
  • There cannot be duplicate column headings.
  • Column names cannot be used if they appear on the NOT ALLOWED list above.

A USEFUL TIP: USE STANDARD COLUMN NAMES

You are strongly encouraged to establish a common row of column names that is always used when importing survey data into REWARD. For each survey vendor, REWARD remembers the associations, or mappings, between your column names and the REWARD data fields from the previous import. If you use consistent column names, you are less likely to have to make changes. This makes the import process faster and more convenient.

How To Format Pay Data For Pay-Based Surveys (as opposed to regression-based)

For each survey job, you will want at least one column that contains pay data. Pay data is represented as the combination of a pay type (base, bonus, total cash) and a comparator (50th percentile, weighted average, median). REWARD can import as many pay columns as necessary. Although the spreadsheet may contain many columns of pay data, you have the option of only importing the columns that you need.

Special Rule: The last column of data must have every cell filled in with values. This is to overcome a glitch in Excel when performing step 3: Saving the file as a tab delimited text file.

This is an example of a survey spreadsheet:

JobCode / JobTitle / Scope / Search_Code1 / Search_Code2 / Search_Code3 / Profitability / Level / Companies / Incumbents / Grade
3090 / Photographer / Group: Electrical Equipment And Electronics / Industry / Electric / For Profit / 1 / 10 / 13 / 10
3090 / Photographer / Group: Electrical Equipment And Electronics / Industry / Electric / For Profit / 2 / 29 / 48 / 10
3090 / Photographer / Group: Electrical Equipment And Electronics / Industry / Electric / For Profit / 3 / 27 / 61 / 10
3090 / Photographer / Group: Electrical Equipment And Electronics / Industry / Electric / For Profit / 9 / 66 / 122 / 10

(Columns continued...)

JobDesc / LevelDesc / Comment / BaseAvg / Base25 / BaseWtd / Total50 / Total75 / BonusPct50
Schedules, coordinates and provides photographic services, including stills, motion pictures and videotapes to support sales, promotions and other organization-wide needs. Provides related processing, studio and darkroom services. Collaborates with outside vendors as needed. Maintains files of projects. Arranges for the repair/maintenance of supplies & equipment. / this is a comment
about this job / 31300 / 28500 / 30900 / 32400 / 34300 / 0.04
Schedules, coordinates and provides photographic services, including stills, motion pictures and videotapes to support sales, promotions and other organization-wide needs. Provides related processing, studio and darkroom services. Collaborates with outside vendors as needed. Maintains files of projects. Arranges for the repair/maintenance of supplies & equipment. / 39700 / 34200 / 39000 / 41700 / 44900 / 0.04
Schedules, coordinates and provides photographic services, including stills, motion pictures and videotapes to support sales, promotions and other organization-wide needs. Provides related processing, studio and darkroom services. Collaborates with outside vendors as needed. Maintains files of projects. Arranges for the repair/maintenance of supplies & equipment. / 47400 / 41600 / 45200 / 47600 / 55300 / 0.04
Schedules, coordinates and provides photographic services, including stills, motion pictures and videotapes to support sales, promotions and other organization-wide needs. Provides related processing, studio and darkroom services. Collaborates with outside vendors as needed. Maintains files of projects. Arranges for the repair/maintenance of supplies & equipment. / 41600 / 34200 / 40900 / 43700 / 46000 / 0.04

Defining Scope

In the example above, scope is defined in two columns, Scope and Profitability. Every spreadsheet must have at least one column that defines scope. Alternately, one column, Scope, could be used to house “Group: Electrical Equipment And Electronics / For Profits”. The end result in REWARD will be the same.

Deleting Rows Based On Scope And Survey Jobs

While REWARD allows you to handpick which scopes you wish to import into your database, we strongly encourage you to audit the survey data - in its spreadsheet form – and remove scopes that will never be used. For example, if your organization will never match to industry scopes in the Food Service sector, delete them from your spreadsheet.

This also goes for survey jobs as well. If you know in advance that your organization will never match to a “Cook” job, then we strongly encourage you to remove these rows from your spreadsheet prior to importing the data into REWARD.

Job Descriptions

Survey job descriptions (and optionally, level descriptions) can be also imported. Having these survey descriptions online will help you match your organization’s jobs to the correct survey job(s). The spreadsheet example above shows how job descriptions can be pasted into your spreadsheet (see the column JobDesc). Note that Microsoft Excel 2000 – and future versions - allows more than 255 characters in a single cell. If you plan to include job description text in your spreadsheets and you are using an earlier version of Microsoft Excel, we would encourage you to upgrade to version 2000 or use Microsoft Access to prepare these files.

Defining Pay

In this example there are six columns of pay data. Each column represents an intersection of a pay type and a comparator:

Column Name / Pay Type / Comparator / Special Note
BaseAvg / Base / Average
Base25 / Base / 25th percentile
BaseWtd / Base / Weighted Average
Total50 / Total Cash / 50th percentile
Total75 / Total Cash / 75th percentile
BonusPct50 / Bonus Percent / 50th percentile / Notice that 4% is stored as 0.04

Defining Your Special Searches

Loading all your salary surveys into REWARD enables quick access to thousands of individual survey jobs (each spreadsheet row is considered a single survey job). REWARD offers powerful tools to find the small group of survey jobs that you are attempting to match. The effectiveness of these tools, though, depends on the data native to each survey vendor. Often, this data is inconsistent.

For example, to find survey jobs where the revenue scope is greater than $4 billion dollars, you might be surprised to find that various survey vendors report this as:

  • “Revenue: $3B - $5B”
  • “Rev from $3,500,000 to $5,000,000”
  • Two columns: a FROM column with the number 4,000,000+

REWARD offers a method that seeks to harmonize the data so it can be consistently searched. In that, we offer (up to) three columns called Search_Code1, Search_Code2, and Search_Code3 (see example above). These columns are unformatted and are intended solely for your use.

We suggest you design a standard coding scheme that answers the question “If I’m trying to find survey jobs that match our organization’s profile (e.g. greater than $4B in revenue), what can I search on?” For example, you could code all survey jobs whose revenue size is greater than $4B as follows:

Search_Code1 / Search_Code2 / For Survey Jobs…
REV / >4B / whose revenue is greater than $4B
REV / <4B / whose revenue is less than $4B

When using REWARD to find matches, you would then enter these search criteria:

  • Search_Code1 contains “REV”
  • Search_Code2 contains “>4B”

Other examples for setting-up your special searches:

Search_Code1 / Search_Code2 / For Survey Jobs…
EE / >10K / With employee size is greater than 10,000
GEO / CA / Whose scope includes California
IND / Food / Whose industry group includes food services
Search _Code1 / Search_Code2 / Search_Code3 / For Survey Jobs…
REV / “From $1B” / “To $2B” / whose revenue is between $1B and $2B
REV / “From $4B” / “To $9B” / whose revenue is between $4B and $9B

How To Format Regression-based Surveys

Regression-based surveys follow the same rules as pay-based surveys except that formulas are substituted for pay amounts. At the time of job matching, the user will be asked to substitute a numeric value that represents the variable X in the formula.

Logarithmic Syntax

  • Formulae must be converted to a format that SQL can interpret.
  • Spaces in formulae are optional and are included here for readability only.
  • The formula must contain a single variable and it must be a lower-case “x”.
  • Typically regression formulae are expressed in thousands. It will be necessary then to multiply the resulting expression by 1000.

Base-10 logarithms

Formula (as it might appear in a spreadsheet) /
Translation into SQL for REWARD / POWER( 10.00000 , (0.2376 * LOG10(x)) + 2.0683 ) * 1000

Note: The first parameter of the POWER() function must be “10.00000” (including the 5 significant-digits of zero)

Natural logarithms

Formula (as it might appear in a spreadsheet) /
Translation into SQL for REWARD / EXP( (0.225 * LOG(x)) + 3.507 ) * 1000

Special Rule: The last column of data must always be filled with values.

This is an example of a natural-log regression-based survey spreadsheet:

JobCode / JobTitle / Scope / Profitability / Lev / Cos / Inc / LTI_AVG / LTI_WTD / LTI_25 / LTI_50 / LTI_75
3090 / Executive / Sales Revenue in $M / For Profit / 1 / 10 / 13 / EXP((.117*LOG(x)) + 4.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000
3090 / Executive / Sales Revenue in $M / For Profit / 2 / 29 / 48 / EXP((.117*LOG(x))+6.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000
3090 / Executive / Sales Revenue in $M / For Profit / 3 / 27 / 61 / EXP((.117*LOG(x))+8.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000
3090 / Executive / Sales Revenue in $M / For Profit / 9 / 66 / 122 / EXP((.117*LOG(x))+10.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000 / EXP((.117*LOG(x)) + 4.890)*1000

The Scope column should specify how the variable is to be interpreted and applied. When a job-match is made, the user will manually enter a numeric value that will be applied to variable “x” in the context of the job-match and the incumbent(s) in that job.

Otherwise, the formats are identical. In the example above, there are five columns of pay data represented by regression formulas. Each column represents the intersection of a pay type and a comparator:

  • LTI_AVG = Pay Type (LTI) and comparator (Average)
  • LTI_WTD = Pay Type (LTI) and comparator (Weighted Average)
  • LTI_25= Pay Type (LTI) and comparator (25th %tile)
  • LTI_50= Pay Type (LTI) and comparator (50th %tile)
  • LTI_75= Pay Type (LTI) and comparator (75th %tile)

Step 3: Save the Spreadsheet as a Tab-delimited Text File

  1. Special Rule: The last column of data must always be filled with values. Verify that this is true, otherwise the conversion to a tab-delimited file will not work properly.
  2. Troubleshooting Tip: when columns are deleted, Excel still remembers thephantom columns to the right of the data even though they have blank column names. Prior to saving the file select the right-most column and then move the mouse all the way to the left in order to select all the columns with data in it. Then, with the columns highlighted, select File > Save As. This will stop Excel from automatically including blank columns. Press File, Save As
  3. In the “Save As” dialog box, there is Save As Type drop-down options. Select: Text (tab-delimited) (.txt)

Step 4: FTP the file

FTP file transmission software varies by company. Please consult your IT support group to see if they use a specific software application.

  1. Start FTP software
  2. Enter Address, username and password. For security reasons, this information is given to users by Watson Wyatt during training. Contact your support team if you need the information again.
  3. Select the market text file you created and transfer to the FTP site.
  4. Exit the FTP software.

Importing Your Survey Data

  • Run the Import Wizard
  • Check your results
  • Options for making corrections or importing the survey again

Step 1: Import the survey file into REWARD

Remember, the FTP site is a holding tank for market data files. You must now import the data into the REWARD database.

  1. Login to REWARD
  2. Select Tools > Import/Export Data > Import Salary Survey (Note that general preparation document is hyperlinked here as well).
  3. This begins the 6-step wizard.
  4. Name a new, or select an existing, survey vendor
  5. Name a new, or select an existing, survey name. If new, enter the appropriate effective date and other descriptive factors. If selecting an existing survey, the import looks up this information and it is not necessary to enter it here.
  6. Pick the file. The list you see represents the files on the FTP site!
  7. Map the data appropriately. If you have already loaded data from the survey vendor selected in step a., it will remember the prior mapping. Change as needed. Make sure you map the job code, title, and at least one scope (core identifying fields). Not all fields are typically mapped; only use the fields you need.
  8. Select the scopes
  9. Select the jobs

Step 2: Quality Review

The import will finish and provide summary statistics. You can now validate/perform a quality check on the imported data. Maneuver as if you were going to match a job and select the new survey. You should randomly find some data within the survey and compare it to your original Excel file to validate quality. Check that the fields were mapped correctly, amounts are correct, etc.

Step 3: How to Make Corrections

When survey jobs are imported - whether they are new survey jobs or existing - any existing pay data is for those specific survey records is deleted and the new incoming pay records replace it. This means that you cannot import pay data incrementally. That is, you can't import Base50 today, and then Base75, by itself, tomorrow. What will happen is that tomorrow when Base75 is imported, REWARD will first delete existing pay data for that survey; at the end of the import tomorrow, only Base75 exists. The key is to use your Excel sheet to comprehensively import both Base50 and Base75.

Note that this still allows you to import survey job records incrementally. Today you can import all the CALIFORNIA-scoped jobs and tomorrow you can import the TEXAS-scoped jobs and that will leave you with both CALIF and TEXAS jobs.

With this in mind, if you find survey information was loaded incorrectly you have the following options:

1. Delete the survey in REWARD and import the survey again: This is especially helpful when you have NOT made any matches (links) to the survey yet. This is also important if you have incorrect information in the identifying fields such as survey job code, title or scope. This option allows you to erase the incorrect data comprehensively, make the corrections on your Excel spreadsheet, and re-import your data (Preparing Data: Step 3).