QCEW UI Modernization Guide

Numerous states are undergoing modernization efforts of their Unemployment Insurance Tax systems. The UI Tax extract is the primary source of Quarterly Census of Employment and Wages (QCEW) data. While the QCEW program generally applauds efforts to make UI systems more efficient and faster, UI system changes may result in changes that create data issues when building the extract. LMI directors and QCEW supervisors must ensure that extracts from the new system are built correctly as to ensure data integrity and continuity. Mistakes in the UI extract can result in content problems such as incorrect employment and wages, missing accounts, lost administrative data, the loss of dates that would inactivate or activate accounts, or unidentified predecessor/successor transactions.

This document provides insights and lessons learned from previous UI modernizations. This document also attempts to answer the question "My state is going through a UI modernization. What do I need to know?" Since this is a compilation of experience, this document will be subject to periodic revision. If you are looking for technical documentation the QCEW UI Extract Guide and policy letters from the Employment and Training Administration can be found on the BLS UI Modernization website.

LMI and QCEW involvement in UI modernization activities is vital. LMI and QCEW staff should be included in all discussions regarding all changes made to the UI system including but not limited to UI Modernization efforts. This will allow QCEW staff to know the breadth of the changes, how they will be implemented, and will allow QCEW staff to plan for those changes. This will also allow staff to know if new definitions will be used, reporting changes will be implemented, or new UI laws have been passed.

LMI and QCEW staff should work with UI to establish a communication process to relay when and how changes to the UI system will be made. It is important to note that although changes made by UI may not affect the QCEW program, they may affect other LMI efforts, so all programs should be consulted when it is learned that changes will be made. States need to communicate what they learn with their regional office. Regional offices must be kept up to date on the progress of the UI modernization.

One of the most fundamental and essential responsibilities of the LMI director and QCEW supervisor is to ensure that UI extracts are built correctly. LMI staff should encourage ongoing communication and cooperation between themselves, UI, and UI developers. It is recommended that at least 2 LMI staff attend all meetings related to the UI Tax system development, UI Tax extract development, or UI system Change Control Board to ensure continuity and safeguard against loss of essential data. When extract changes are planned, LMI staff should explain extract requirements, identify pertinent data tables, and ascertain sources for the correct information on the UI system. LMI staff should discourage any attempts to reassign UI numbers and should enforce proper formatting of all data elements within the QCEW extract. LMI staff must also ensure that any UI-generated employment and wage estimates are not extracted into the QCEW system. UI estimation procedures are not approved by BLS/QCEW standards and must not be used.

LMI directors and QCEW supervisors must establish who will write the new extract. It is also important to know who will provide the funding for the extract. All states are encouraged to rewrite their extract using the new "QUEST" format. The QUEST format allows states to take advantage of new field lengths, added data fields, and more optional state use fields which may benefit the state during processing with information they can utilize.

The QUEST extract is divided into 5 separate formats to minimize the number of times some fields need to be extracted and loaded. The 5 formats are:

• Non-quarterly, QCEW UI Extract Guide, p. 15
• Quarterly, QCEW UI Extract Guide, p. 49
• Predecessor/Successor, QCEW UI Extract Guide, p. 77
• Electronic Contact Information, QCEW UI Extract Guide, p. 86
• Supplemental State Use, QCEW UI Extract Guide, p. 90 *this file format is not available for use in the WIN or EXPO systems.

When using QUEST extracts, states must develop the first 3 (non-quarterly, quarterly, and predecessor/successor) extracts. Almost all fields in the non-quarterly format change infrequently. Only updated non-quarterly data for continuous records and non-quarterly data for new accounts are included in the non-quarterly file. The quarterly file is comprised of data that change every quarter and has a shorter format. The predecessor/successor extract pulls data related to predecessor/successor transactions including those with multiple predecessors or successors. The predecessor/successor file also contains several state use fields that allow States to load related information that may be in the UI Tax files including partial indicators and the percentage of employment transferred. Although not as critical, states can also create the e-contacts extract which collects the email address and website of the business. States can also create the programming for the Supplemental State use format, but it cannot be loaded into current systems since these fields do not exist in EXPO or WIN. Until QUEST is available, EXPO has developed programs to convert four of the five formats into the IMT format so they can be easily loaded into EXPO. WIN has created load programs that load four QUEST formats directly into WIN. Fields not currently in EXPO or WIN will not load until the state conversion to QUEST.

The actual QUEST extract formats can be found in Appendix D of the QCEW UI Extract Guide. While the QUEST development team has built an assortment of legacy features from old extract programming using the IMT format to accommodate errors or problems in old extracts, it will not be doing so for new extracts. The new extracts must be created correctly; special features will not be available in QUEST to accommodate extracts that do not meet the specifications.

UI extract developers should be encouraged to read the QCEW UI Extract Guide. While the guide is 120 pages long, it can be reduced using the button "Tech Guide" on the first page. Using the button will reduce the QCEW UI Extract Guide size to 94 pages (this 'reduceable' version of the guide can only be found on StateWeb). The QCEW supervisors working with the developers should be familiar with the longer version since it provides details pertinent to the QCEW program. The QCEW staff and regional office must work with the extract developers to ensure that they understand the QCEW extract requirements and are complying with them.

Alternative approaches for extracting data from UI Tax include:

• Perform a large data-dump from tax with the appropriate extract load job parameters to protect already reported or clean data. This would require uploading a large quantity of data to the state processing systems.
• Extract all appropriate fields for each format based on transactions that have been made to the UI Tax system. This can be more efficient if one can guarantee that all the appropriate transactions are identified and the correct/appropriate data fields are written to the extract files.
• Many states compare the data currently in the state QCEW system against the tax system to identify what has changed or is newly reported.

It is important to understand that if EXPO or WIN continues to use an output format, such as the 76 job output, to create the extract by comparing the EXPO/WIN or QUEST database against UI Tax, these outputs are only inputs into the extract build process not the output format from the extract build. Nothing in EXPO, WIN, or QUEST will use the 76 job formats as an extract load file.

LMI staff should consider the timing of going live with new extracts. It has proven to be beneficial to pull the first extract from the old tax system or using the old extract and then using the new extract for the balance of data. This gives the state the opportunity to focus on a smaller volume of records when reviewing the new extract. *Do not go live with the new extract if it is not producing correct data*. Also consider the implications of going live with first quarter. First quarter may not be ideal due to the special processing and refiling code changes that are added to the system. Third quarter is typically considered to be the best quarter for implementing a new extract or extract changes. For more information review the Timing and Frequency Issues section of the QCEW UI Extract Guide.

It is important to parallel test and compare new extracts to the old extracts. It is recommended to parallel test and compare the extracts for at least two quarters, roughly six months, prior to using the new extract for production purposes. An extract comparison will identify any changes between extracts, such as a significant increase or decrease in the number of UI accounts, top-side employment or wage levels are out of character, or a change in individual data element counts. Please contact your regional office for help in setting up a testing environment and developing testing protocol.

If the state was using any of the EXPO 76 job output or the WIN 7 Quarter History File - EXPO format (similar to 76P) to do this comparison, they can continue to use this approach. The formats for these are in Appendix F of the QCEW UI Extract Guide, starting on page F-3. States may also choose to compare the last UI extract to the information currently on the UI Tax system.

Testing should not be done on the State production EXPO or WIN systems. The BLS can help set up a test environment for your new extract. For help in setting up a testing environment, regional offices should contact SCR2@bls.gov. A test copy of a State's EXPO or WIN system can be created at the central facility by EXPO developers or on a state server by WIN developers which can be used to load extracts and run different jobs to test for completeness and usability of the extract. As needed, the test data can be "refreshed" and returned to a version prior to the test extract load so that subsequent tests can be run as often as needed. Barring any unexpected circumstances, this test site would be available for as long as needed to resolve extract problems. Extracts should not be approved that have problems. Problems are usually far more difficult to resolve after signoff/approval. Sometimes, a change is not bad if it resolves a serious past data or extract problem BUT it must be properly documented and notification sent to BLS.

While states are encouraged to write a new extract and improve their processes, some have gone through UI Modernization without writing a new extract and only making a few changes to the existing extract. In these cases parallel testing the extract against the old and new environments is essential to ensure the continuity of the data extracted. Testing the data will confirm that the data elements collected in the prior extract correspond to the new elements. It will also verify that fields which are derived use the same data for their calculations.

1. Blank Spaces

The QUEST extract handles the absence and deletion of data more clearly than the current formats. If a field is not populated in the extract format, the QUEST, EXPO, and WIN systems do the following:

2. UI estimates

State UI agencies may impute unreported employment and wages. UI imputations do not follow BLS standards and should not be extracted and loaded into the QCEW systems. State staff should be aware of the indicators used by UI to identify imputed accounts and then ensure that imputed data are not included in the UI Extract.

3. Carriage returns/end of record markers

Mainframe systems and server-based systems typically do not create end of record markers or carriage returns that look the same. EXPO, WIN, and QUEST all need to be able to determine when one record ends and the next one begins.

4. Unreadable characters

Several UI systems allow unreadable characters. These may appear to work in EXPO but frequently cause problems for downstream data users. Ideally these characters, which may appear as pipes, etc., if included in the extract, are blocked before getting in the system. Numerous cases occur when staff cut and paste information into the systems, thus picking up unseen characters and/or end of record/page markers. Efforts should be to ensure that the unreadable characters are blocked from the extract.

5. Reported zero employment and/or wages vs missing employment and wages

• QCEW state processing systems need to be able to distinguish between reported zeroes and missing data. If the data are reported as zero, they should be included as "0" in the database; spaces should be used if the data are missing.
• UI may treat any QCR submitted as complete whether or not all fields are filled in. In these cases if wages are reported and three months of employment are missing, all six fields are being treated as reported and the employment fields are zero-filled in the extract. State QCEW staff should ensure they are able to identify those fields that were not filled in by the employer.
• For example, a firm with 30 people shuts down in February for retooling or winter vacation but did operate in January and resumed operations in March. A first quarter QCR report of 30 - 0 - 29 for employment would be reasonable and should be extracted with February as 0 reported. But if the record does not normally shut down in February and a hand written QCR report was illegible and February could not be read, then 30 - blank - 29 for employment should be extracted with February as missing.
• Blank fields should never be assumed to be reported zero. These should be treated as missing and left for possible imputation later in QCEW processing. Zeroes on QUEST extracts should be treated as reported. If this is not correct, the extract programs should be modified to ensure they function this way.
• For more information on this topic refer to the Complete Extracts section of the QCEW UI Extract Guide.

6. Rounded vs truncated vs full wage fields (total wages, taxable wages, and contributions)

The QUEST format allows up to 12 digits in the total and taxable wage fields and 10 digits for contributions due. (Currently, EXPO/WIN allow 11 digits in the total/taxable wage fields and 9 for the contributions field.) An amount like the example below for total wages could fit in the whole value including the cents without the decimal point (e.g., 12345678951). Full wage fields which include cents are incorrectly formatted even though they would fit into the field. For example, the value in the UI tax system is 123,456,789.51; values to include in the extract are either rounded = 123,456,790 or truncated = 123,456,789.

7. UI Numbers and Check Digit

The Unemployment Insurance (UI) Account Number in conjunction with the Reporting Unit Number (RUN) is the primary key to pointing to a specific record in QCEW systems. Since EXPO, WIN, and QUEST have multiple files/tables where a specific account or record is included over time, it is important that the key to that record be consistent. When UI account numbers are changed, not for economic events, but for reporting and numbering issues, State LMI staff should include SCR, EXPO or WIN developers, and regional staff in conversations with UI developers to convert all the old data to the new numbering configuration. UI number conversion is done using formulae that reassign numbers, crosswalk tables, or other actions. Converting UI numbers is a massive undertaking that is filled with risks and should be avoided wherever possible. If this does happen, the extract should save the old UI number in the non-quarterly 35-position state use field. The BLS should be given 12-18 months of notice before the "go live" date in order to prepare for the impending change.

The three following UI numbering scenarios can be accommodated by the QCEW:

• A state may opt to switch to a new UI numbering system for new accounts as long as the old UI numbers for existing accounts are unchanged and there is no overlap with the UI number for new accounts.
• Several UI Systems that are doing this have switched to a number that has a 7 position sequential number followed by 3 positions that are used to identify the accounts as special accounts. In some cases the new UI numbers start with 1 in the first position. While it can go beyond 1 in the first position, numbers 0 thru 0999999 are not used. This limits the number of future unique numbers as the state churns through account numbers. Some states use the same number in the first 7 positions for records but have unique last 3 positions, but these seem to be fairly rare.
• Some states have a limit on the number of digits for the UI Number (e.g., 5 digits, 6 digits, etc.) and have run out of numbers that can be used for the first time. Some states are reusing UI numbers where the accounts they were assigned to have long since been inactivated. (This numbering scenario is highly discouraged by the BLS).
• If a state is reusing UI account numbers, QCEW supervisors should take care to review reactivated accounts to avoid erroneous linkages.
• Some states use check digits to ensure that data entered for the account are entered to the correct account or financial payments are associated with the correct account. While there is a separate field for the check digit on the extracts and in the State processing systems, if the check digit is already being treated as part of the UI number do not move it to the check digit field. If it is not part of the UI now and is in a separate field, leave it as is. Try to keep the key fields consistent over time.

8. UI Collected Multiple Worksite Data

While not a large group, a growing number of states are beginning to collect QCR data at the worksite level. Having UI collect multi worksite data is generally discouraged by the BLS, but if your state does decide to collect the data below are some considerations for the state.

• Instead of having UI establish the multi breakouts, they can use the multi data currently held in the QCEW system. This will avoid having to match existing QCEW breakouts with UI established ones.
• UI and LMI will need to develop processes on how to deal with employers who will not report multis to UI. Should they be directed to MWRweb for reporting? A process for sending data back needs to be established if UI wants the breakout information collected by QCEW.
• A research process will need to be established to determine when/if an employer changes from reporting from one entity to another.
• Data will need to be reconciled from the old reporting method to the new method to ensure that data increases or decreases are not caused by reporting issues instead of economic events.

9. Dates

Most state tax files contain many dates. QCEW must ensure that the correct dates are included in the extract file. States need to ensure that they compare the new dates to the old dates in the extract. Dates must be in the YYYYMMDD format. Below is a list of dates that will need to be found in the UI systems. For more information on dates please see the Dates section of the QCEW UI Extract Guide.

• Initial Liability Date (ILD) - The date a new business is subject to UI reporting requirements.
• End of Liability Date (EOL) - The date that the business ceases operations or no longer has employees or pays wages.
• Reactivation Date (REACT) - The date that an inactive unit or out-of-business unit (on the state UI system) is reactivated. If the reactivated unit is still present on the state’s QCEW system, the Reactivation date should be assigned while all other dates are maintained without change.
• Transfer Date - The date an establishment goes through a predecessor/successor transaction.
• Setup Date - The date that the information for the UI account is put into the state UI system.

10. Codes (NAICS, OWN, Area (County, Town, Zone, Other))

Most new tax systems include self-registration and self-coding tools. These can be labor-saving if they work well but a painful burden if not. Ensure that:

• The most current set of BLS NAICS codes is used. A spreadsheet with the current NAICS codes can be found on the BLS.gov NAICS page. Do not use NAICS codes from Census.
• NAICS codes are valid 6-digit codes. Partial codes are not sufficient.
• QCEW should still have access to the status determination form information and any information entered into self-coding tools. This information is vital for QCEW staff to validate the codes.
• Remember that BLS uses some NAICS codes that are different from the Census version. If NAICS codes differ between BLS and the UI Tax system, the extract program cannot adjust the codes and QCEW staff will have to manually fix them to comply with the BLS coding requirements.
• Some state UI Tax systems assign area codes based on an address. Often the area codes are assigned using an address that is not the physical location. Area codes assigned by UI on the basis of a non-physical location address should be discussed with regional office staff to determine treatment in the QCEW extract.
• UI Tax systems must be built to take replacements to the NAICS code set. The NAICS codes are updates and replaced on a five-year cycle.
• 
  

  11. Openings, Closings, and Continuations

  UI and LMI staff should walk through the following cases to ensure the correct collection of openings, closings, and continuations.

      UI: Walk through processing for the following cases of employer reports:

  • A business lets UI know that they will begin operations in the state at some point in the future.
  • A business begins operations in the state and starts reporting to UI.
  • A business ends operations in the state and informs UI.
  • A business ends operations in the states and does not inform UI.
  • A business continues operations in the states and continues reporting to UI.
  • A business continues operations in the state and does not report to UI.
  • A business changes ownership and informs UI.
  
  

      LMI: Explain how the following cases of employer reports are identified by the extract program:

  • A business lets UI know that they will begin operations in the state at some point in the future.
  • A business begins operations in the state and starts reporting to UI.
  • A business ends operations in the state and informs UI.
  • A business ends operations and does not inform UI.
  • A business continues operation in the states and continues reporting to UI.
  • A business continues operations in the state and does not report to UI.
  • A business changes ownership and informs UI.
  
  

  12. Tax Rates

  EXPO, WIN, and QUEST have specific tax rate formats. For all systems, there should be no decimals loaded. For the QUEST quarterly load, the rate is 5 positions long without decimal places or percent sign. A tax rate of 0.0325, or 3.25%, would be expressed as a value of 03250 and 0.035 or 3.5% would be expressed as a value of 03500. For more information see the tax rates section of the QCEW UI Extract Guide.

  
  

  13. Annual Reporters

  Annual reporters are typically either domestic or seasonal employees. QCEW staff need to be aware of reporting requirements, including how often employers are required to report employment and wages and how much information they are required to report (for example, one quarter or a full year). Staff should also know how annual reporters are identified on the UI tax file.

  
  

  14. Seasonal Accounts

  QCEW staff should learn how the determination of seasonal business is made by UI Tax. The determination can be made using a number of criteria including NAICS, the name of the business, and self-reporting by the business. QCEW staff should also find out how seasonal businesses are identified on the UI tax file.

  
  

  15. Special Accounts/Records

  Special accounts are federal or state, reimbursable, and employee paid accounts. QCEW staff need to learn how these are indicated in the UI Tax system and treated by UI. QCEW staff need to learn the reporting requirements of special accounts.

  
  

  16. Predecessor/Successor Data

  It is important to understand how UI will treat predecessor/successor transactions as their treatment can vary widely from state to state with potentially harmful results. QCEW staff need to learn how UI Tax will process predecessor/successor transactions. Will UI handle one-to-one transactions differently than those with multiple predecessors or successors? For more information on predecessor/successor situations, please visit the section of the same name in the QCEW UI Extract Guide.

  
  

  17. Legal vs Trade Name and Addresses

  Name and address fields are fixed lengths and self-contained. Some UI systems have longer or variable length fields. Do not wrap residual parts of fields into other fields.

  
  

  18. Physical Location Address (PLA)

  If multiple addresses are available in the UI Tax system, QCEW staff should review the addresses on the file to see if any meet the QCEW definition of physical location address. Physical location addresses should always be in-state and should be geocodeable. For example 123 Main Street, Suite 2, Anytown, UT 12345 is geocodable. Rural route 27, Box 3, Outer Limits, UT 67890 - is not a PLA.

  *PLA's must have an actual physical address and should not include P.O. Box, "PO_", "P.O.", "POB", "P. O.", "P O Box", "P O Box", "Post Office Box", HC, HCR, CCR, PMB, RR in any part of the field.

  
  

  19. Business Addresses

  Several UI systems store a business address rather than a physical location address. If QCEW extracts the business address for use as a PLA, it must follow the QCEW criteria of a PLA.

  • Some UI systems also use this address to automatically assign the county or sub-county (e.g., town) codes. If a state UI office does assign a county code, QCEW staff should review the codes to determine if they fit QCEW requirements for a county code.
  • If, for instance, UI allows any address in the business address field, it could include out-of-state headquarters or accounting/payroll offices or an address that is not the physical location of the record, which could result in incorrectly assigning county or other area codes.
  
  

  20. Foreign addresses (QUEST non-quarterly format only)

  • Foreign country addresses from the UI Tax file can be included in the UI and the mailing/other address extract fields but cannot be used in the physical location address field.
  • If the address is in the United States, leave the foreign country blank. Even though the guide does allow for USA in the foreign country field, leaving it blank is preferred. If it is a foreign country address, use the correct country code even if a crosswalk is needed to populate this correctly. (The codes can be found in Appendix B of the QCEW UI Extract Guide.)
  • If using the QUEST extract while still operating with EXPO or WIN, the programs written by the EXPO and WIN developers will convert the format for use in their systems.
  
  

  21. Wage records

  Using the Wage File Creator, the QCEW program uses LEHD (Longitudinal Employer Household Data) formatted wage record data to build several summary wage record count and wage record wage data. The Wage File Creator uses two quarters of wage records to compile information on new entrants, hires, exits, separations, and other compiled information and identify possible predecessor/successor relationships. The Wage File Creator is a program that is outside of the QCEW system and its output can only be loaded into the EXPO or QUEST systems.

  The WIN system has its own separate external system, the WIN Wage Record Tool, which takes state generated wage records files to be loaded into WIN. The WIN Wage Record Tool uses either the LEHD file or a state created wage records extract. The data produced by the WIN Wage Record Tool will not work in the QUEST system. States are encouraged to obtain access to the LEHD file, if they do not have it, prior to the QUEST conversion.

  • LEHD files can usually be generated at any point in time. QCEW staff should work with UI system developers, who are developing wage record programs and files, and with state staff responsible for creating LEHD files to have these files available for the QCEW program at least once prior to the QCEW quarter's due date and should be available to run a few times each quarter.
  • LEHD files with PII (Personally Identifiable Information) data are loaded into the external Wage File Creator program or WIN Wage Record Tool, separate from the State QCEW processing system. The output files which are summary files with no PII data are loaded in the State's QCEW processing system.
  • During a UI wage record extract rewrite, states should ensure that the wage field is sufficiently long to fit all positions of the wages and that bogus numbers are properly handled.
  
  

  22. Multiple Quarters and other Extracts per Quarter

  In the QUEST Extract, multiple quarters can be on the file at one time. Some records may have different numbers of quarterly extract records than others. For instance, if the future, current, and prior quarters are elected to be extracted, some records will not yet have future data, inactivated records may only have prior quarter data with no current or future data, and other new records may have no prior data. Extract programs should be looking for new or changed data, so most records may have no prior data. Information on this can be found in the Standardized (aka QUEST) UI Extract Quarterly File section of the QCEW UI Extract Guide.

  
  

  23. Complete Extracts

  Each state is required to do at least three extracts for all quarters, two as the current quarter and one as the prior. Extracts should be as complete as possible including all available and relevant data. QCEW staff should work with extract developers to identify all data that are required for the operations of the QCEW program. Please refer to the Timing and Frequency Issues and Complete Extracts sections of the QCEW UI Extract Guide for further information.

  
  

  UI Modernization Checklist

  
  

  The UI modernization checklist can be found at https://www.bls.gov/cew/additional-resources/unemployment-insurance-modernization/ui-modernization-checklist.htm. For more information, please contact QCEW National Office staff involved with this process.

   

  Last Modified Date: September 16, 2019