FEM 4.0 is a major update compared to FEM 3.0 that incorporates feedback to the assessment, guidance, scoring, and user interface (see SAC summary). Due to the addition of so many new questions in FEM 2023, it is no longer feasible for the entire assessment to be available in a single csv file (v2) as it would exceed the Excel column limit of 16,384. Therefore, FEM 4.0 required a re-imagining of the previous FEM exports.
What is the new export structure?
FEM 2023 downloads offer the opportunity to both go deeper with FEM 4.0 and maintain existing reporting. In the past FEM results and original answer data were available for brands and facilities to export in two formats:
- V2 data: includes direct outputs from the assessment that includes both the facility-entered and verifier-entered data.
- Performance data: a more curated and simpler data set with edited column names. It only shows one value per content area--if the assessment has been verified, that data is shown, otherwise facility entered data is shown.
Moving forward for FEM 2023 assessments, there will no longer be only a V2 export with all data available in one export. Instead, this data will be broken into smaller exports by the assessment section. While V2 data and performance exports will still exist, there are changes from 3.0.
|Raw Response data by section|
|Meta||2023||Individual + Bulk|
|Site Info & Permit||2023||Individual + Bulk|
|EMS||2023||Individual + Bulk|
|Energy - General||2023||Individual + Bulk|
|Energy - Purchased Energy||2023||Individual + Bulk|
|Energy - Renewable Energy||2023||Individual + Bulk|
|Energy - Non-Renewable Energy||2023||Individual + Bulk|
|Energy - Biomass||2023||Individual + Bulk|
|Water||2023||Individual + Bulk|
|Wastewater||2023||Individual + Bulk|
|Air Emissions||2023||Individual + Bulk|
|Waste||2023||Individual + Bulk|
|Chemicals||2023||Individual + Bulk|
|Scores & Levels||2023||Individual + Bulk|
|Quantitative Impacts||2023||Individual + Bulk|
|Performance||2017-2022 mappings with additional fields from 2023||Individual + Bulk|
|V2||2017-2022||Individual + Bulk|
New FEM 2023 Exports
The fields will be organized by sections such as Waste, Energy (by resource), Scores etc. to offer additional depth and detail from 3.0.
- Sections: FEM2023 data will be filtered into a series of smaller “reports” that can be downloaded as separate csv files such as Water, Wastewater etc.
- Energy will be broken into individual exports of Energy- Purchased, Energy - Renewable, Energy - Non Renewable Energy, and Energy- Biomass
- Scores and levels: This export will ONLY be for FEM2023s. We won’t support a blended (multi-year) bulk export of scores and levels into a single, combined export. If you have multiple FEM 2023s, you can export a scores csv for all of those, combined into one file.
- Quantitative Impacts: Will be treated the same as scores, conceptually. This means we won’t support a blended (multi- year) bulk export into a single combined export.
The performance report mostly includes resource usage and scores that were related to the “performance” of a facility. It creates standard field names and then each year’s data is mapped into this common set of fields. The result is a smaller export that takes key data from each section, as well as calculations, and puts it into a single file.
For 2023, this will maintain the current mapping but we’ll be adding 443 fields to the report to capture new data that is available with FEM2023. Prior years assessments won’t have any values for those fields, since they are new with 2023 and there are no prior equivalents. For example, there are new energy water and waste sources, which will result in new total values, and new scores.
Individual CSV v2
This export has previously contained all the information for an assessment. This is all packed into a single csv file where each field is a “column”. As each year of FEM has introduced and removed questions, the full data for any assessment year varies from year to year. The result is that if you download the individual v2 for any given assessment of the same year, you will have the same number of columns. But there are differences between years (see table below).
Bulk CSV (v2)
In addition, there is also the bulk csv v2 export to give users a single export for all of the assessments shared with them for all years. This takes all of the unique fields from all of the years of FEM and puts them into a single set of columns (~14,588).If a column did not apply to a given year (row), then the cell is blank for that row. This allowed for a single download for all the FEM data, but there are still a lot of fields, and there can be variations in terms of what cells are populated between years.
Over the last few years, the bulk export format has stayed relatively stable for multiple years. And many customers still report on specific fields that are not in the performance summary report. But FEM2023 has so many question additions and removals that if we followed this same approach, the bulk csv would have upwards of 60,000 columns, which is unusable as a single download!
To help with this transition, we are planning to “freeze” the current set of columns that get constructed into the bulk csv, from FEM2017-2022. For the fields that are still applicable in FEM2023, or which are produced as calculated outputs from FEM2023, we will push those into the same format. The result is that a customer will continue to be able to download this bulk csv v2, and have fem2023’s added as new rows. The fields that are the same in fem2023 will be populated into those cells. But, no fields that are new in FEM2023 will be added to that (now legacy) format.
|Year||Field A||Field B||Field C|
|2018||Present (raw)||Present (raw)||Missing|
|2019||Present (raw)||Present (raw)||Missing|
|2020||Present (raw)||Missing||Present (raw)|
|2023||Present (raw)||Missing||Present (calculated)|
- Data dictionary: review new REFIDs and other FEM2023 assessment data. This is a draft and will be updated before the cadence starts.
- REFID Comparison: review REFID changes from 2022 to 2023
Help us Improve: Found an error or think something is missing? Let us know so we can enhance this article. Your feedback is valuable to us!