BambooHR headcount and turnover dashboard for board reviews

Build me a people analytics dashboard that our HR lead and CFO open before monthly leadership and board reviews. BambooHR has no native people analytics view, so our team keeps exporting employee data into spreadsheets to answer the same questions every month: how headcount moved, what turnover was, how long people have been here, and who is coming up on a work anniversary. I want one screen that answers those, with the underlying employee rows always one click away.

Data source. The primary read is BambooHR Request Custom Report, asking for department, division, location, hire date, termination date and employment status, plus employee id, full name and job title so the drill-through rows are readable. Use Get Employee Directory to enrich those rows with current identity details such as work email and job title where the report does not carry them. A period selector sits at the top of the app (this month, last month, a quarter, or a custom start and end date) and every view respects it.

Important: the custom report endpoint is paginated with page and page_size, where page_size defaults to 500 and maxes out at 1000. The handler must keep requesting pages until pagination.next_page comes back null, and only then run any math. If it stops after the first page, headcount and turnover will silently undercount and we will present wrong numbers in a board meeting without knowing it. Fetch the full set once per period, cache it for the session, and show the row count that was loaded so the user can sanity check it.

Also important: BambooHR filters fields by permission silently, returning an empty value rather than an error when the connected account cannot see a field. Before rendering, check which of the requested fields actually came back populated, and surface a clear banner naming any field that came back empty instead of charting it as zero. If termination date is unavailable, the turnover view must say it cannot be calculated rather than displaying 0 percent, and if department or location is missing, the corresponding breakdown should say so instead of grouping everyone into Unknown.

View one, headcount trend. Show active headcount over the selected period, broken down by department and by location, with a toggle between the two dimensions. Include starting headcount, ending headcount, net change, plus joiners and leavers counts for the period.

View two, turnover. Calculate turnover as leavers in the period divided by average headcount for that period, where average headcount is the mean of starting and ending headcount. Display the headline percentage along with the three numbers that produced it, so the CFO can see the arithmetic. The list of leavers sits behind the number: clicking the metric expands a table of everyone who left in the period with their department, location, hire date, termination date and tenure at exit. Add a toggle to exclude contractors and interns based on employment status, and make sure it recalculates both the leavers count and the average headcount denominator, not just the numerator.

View three, tenure distribution. Bucket active employees by time since hire date into bands such as under 6 months, 6 to 12 months, 1 to 2 years, 2 to 5 years, and over 5 years. Let the user filter the distribution by department or location, and show median tenure alongside the chart.

View four, upcoming work anniversaries. List employees whose hire date anniversary falls in the next ninety days, sorted by date, showing the number of years they will reach, their department and their manager where available. Highlight milestone years such as 1, 5 and 10.

Drill-through everywhere. Every tile, bar and metric must click through to the employee rows behind it, showing name, department, division, location, hire date, tenure, employment status, and termination date where relevant. This is the whole point of the app: it should end the habit of exporting to a spreadsheet just to see who is actually in a number.

Alternative data source. Use List Custom Reports to show the saved reports in the account, and let the user load one with Get Report by ID instead of the default ad-hoc field set. Make it obvious which source is currently active, and if a loaded saved report is missing fields the dashboard needs, say which views cannot be rendered from it rather than showing empty charts.

Action one, export the snapshot to Google Sheets. A button creates a new spreadsheet with Create Spreadsheet, named with the company and the selected period, then uses Append Values to write one tab per view: headcount by department and location, turnover with the full leavers list, tenure distribution, and upcoming anniversaries. Include a header row with the period, the turnover definition and the contractor toggle state, so the tab is self-explanatory when it is pasted into the board deck. Show the resulting spreadsheet link in the app when it finishes.

Action two, post a summary to Slack. A second button writes a short plain-language summary of the selected period covering starting and ending headcount, net change, turnover percentage, the departments with the most movement, and the count of upcoming anniversaries, then posts it to a chosen channel with Send a Message. Use List Channels to populate a channel picker, and show the user a preview of the message with a chance to edit it before it sends.

Persistence and per-user behavior. Remember each user's last selected period, their contractor and intern toggle state, their chosen Slack channel, and their preferred data source, so the HR lead and the CFO each land on the view they were last looking at. Any load failure against BambooHR should be shown as an explicit error with the reason, never as an empty chart.