Tips to Prepare Your AAP Data

Our goal is to deliver quality and comprehensive AAPs as soon as possible in your new plan year so you can communicate and implement your results. Here are some helpful tips to begin planning for comprehensive and timely data collection for your AAP project. 

• Establish a deadline to pull data for the AAP as soon as possible after your plan date.  
• Share the data requirements template with your HRIS and Talent Acquisition teams now to begin preparing for a data pull on or soon after your plan date. See the chart below for an example of the appropriate date ranges. 

Helpful Tip: Did your company implement a new HRIS or Applicant Tracking System during the last year? Build in time to have new Excel queries developed to pull the data needed for your AAP. Test and validate the new queries to ensure you have the data required and that it is an accurate representation of all the activity that you are required to analyze as part of your AAP. 

• Establish and communicate a deadline to have all personnel actions for the prior 12-month period processed in your HRIS so you can meet the deadline you’ve established for the data pull. A deadline is especially important if you know there will be personnel actions processed retroactively. 
• Remind recruiters and/or hiring manager to review the requisitions that were filled with an external hire or competitive promotion in the previous 12 months. The requisitions need to be closed, show there was an applicant pool, and the disposition reasons should be accurate.

Helpful Tip: If you are able to pull applicant data by “Close Date”, rather than the dates the applicants applied, you’ll be more likely to have the entire applicant pool needed. 

• If you have employees who did not self ID for race or gender, try to provide a visual identification. From OFCCP FAQ’s: 
  ”Visual observation is an acceptable method for identifying demographic data, although it may not be reliable in every instance. If self‐identification is not feasible, post-employment records or visual observation may be used to obtain this information.” 

• Include Work Location for every employee in the Roster. Run a pivot table on the work location column in the roster to get a count of the number of employees in each location. Check if any establishments went above or below 50 employees from last year. Communicate changes to your Berkshire Consultant! 

• Review the data queries for completeness and accuracy. Use this checklist. 

Helpful Excel Tips: 

Race: filter your race column in each table to show records with blank, “unknown”, “not specified”, or “I choose not to identify”. This is also a good way to make sure all races are captured. Don’t see anyone with Hispanic in this column? Maybe there’s a separate field that captures Ethnicity that needs to be pulled as well! 

Roster: make sure the Roster snapshot includes the right employees. Here’s an example using a January 1, 2025 plan date: 

The Roster needs to show the point-in-time snapshot of your workforce before the workday starts on January 1.  Remember, your personnel activity only covers actions that occurred from January 1, 2024 – December 31, 2024.  Therefore, anyone whose last day of work is December 31, 2024 should not be included on the Roster. Anyone who started on January 1, 2025 should not be included. If someone received a promotion on January 1, 2025, the job listed in the Roster should be the job they had on December 31, 2024 and not the new job they promoted to. 

Personnel Actions: sort the date column by most recent to be sure all of the correct transactions are included.  

Applicant Flow: make sure requisition and/or Candidate ID are included in the New Hire AND Applicant Data tabs. 

• Send your data file to your Berkshire Consultant as soon as possible after your plan date. Ideally, data by the 15th of the month. Remember to use your Consultant’s secure file transfer link for all data submissions. Due to strict data security protocols, our Consultants are not permitted to accept files sent via email.