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:
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:
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:
|Do not wipe out value in Database|
|Wipe out value in Database|
|Do not wipe out value in Database|
|Wipe out value in Database|
|*Do not wipe out value in Database. Requires EXPO users run an 84 job to convert to low values before loading data|
|Do not wipe out value in Database|
|Wipe out value in Database|
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
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:
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.
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.
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:
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:
LMI: Explain how the following cases of employer reports are identified by the extract program:
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.
20. Foreign addresses (QUEST non-quarterly format only)
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.
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.
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