How to Create a Headcount Report in Looker

Creating a detailed headcount report in Looker gives you the clarity to strategically manage your company's growth, budget, and talent resources. It helps you move from simply counting employees to understanding the dynamics of your workforce over time. This guide provides a complete walkthrough, starting with preparing your data and then moving to step-by-step instructions for building essential headcount reports in Looker.

First, Why Build a Headcount Report?

A headcount report is much more than a number on a slide, it's a strategic tool. When done right, it answers critical business questions:

Financial Planning: How is our payroll cost trending? Do we have the budget for planned hires in Q4?
Resource Allocation: Is the engineering team growing fast enough to meet the product roadmap? Is the sales team staffed to hit its revenue targets?
Organizational Health: Are we seeing high turnover in a specific department? Which teams are retaining talent the best?
Growth Tracking: How has our company size changed over the last two years? Which locations are expanding the fastest?

By moving this report into a dynamic tool like Looker, you replace static, quickly outdated spreadsheets with a live view of your organization, enabling smarter and faster decisions.

Preparing Your Data for Success

Before you jump into Looker's Explore interface, the quality and structure of your underlying data will determine your success. A headcount report is only as reliable as the HR data it's built on. Your goal is a clean, trustworthy dataset, typically synced from your HR Information System (HRIS) like Workday, BambooHR, or Gusto into a database that Looker can access.

A Looker developer or data engineer will be responsible for modeling this data, but understanding the required components helps you ask for what you need. Here are the essential data fields:

Employee ID: A unique, non-changing ID for each employee.
Start Date: The official hire date for the employee. This is crucial for tracking new hires.
End Date (or Termination Date): The official last day of employment. This field is the most important one for accurate headcount analysis. For active employees, this field should be NULL (empty).
Department: The employee's current department (e.g., "Sales," "Marketing," "Engineering").
Job Title: The specific role of the employee.
Location: The primary office or region where the employee works.
Employment Type: Differentiates between roles like "Full-Time," "Part-Time," and "Contractor."

The Importance of the End Date

The number one mistake people make when building headcount reports is forgetting about the end_date. Simply counting all rows in your employee table will give you the total number of people ever hired, not your current headcount.

The golden rule of headcount is: An employee is considered active if their start_date is in the past and their end_date is either in the future or is NULL.

Ensuring your data prep reflects this logic is fundamental. When you find an active employee who has an end date, it’s a sign that your data might need cleaning.

Step-by-Step: Building Your First Headcount Reports in Looker

Once your HR data is available in Looker, you can start building reports (called "Looks" in Looker). We'll go from a simple snapshot to more complex trend analysis. For this guide, let's assume you have an Explore named "Employees."

1. Creating a "Current Headcount" Snapshot

First, let's get a single, simple number: how many active employees do we have right now? This is perfect for a key KPI on a dashboard.

Navigate to Your Explore: Open your "Employees" Explore in Looker.
Add a Measure: In the left-hand panel, find your employee count measure. This is usually a pre-built measure called "Employee Count," or you can create one by selecting the count of your unique Employee_ID field.
Set the Filter for Active Employees: This is the key step. Go to the "Filters" section and add a filter for the End Date field. Set the condition to is null. This tells Looker to only count employees who do not have a termination date.
Run and Visualize: Click "Run." Looker will return a single number. Go to the "Visualization" tab and select the "Single Value" chart type. Now you have a clear, simple display of your current total headcount.

From here, you can add a dimension like "Department" or "Location" to this Look, change the visualization to a Bar Chart or Pie Chart, and save it as a new Look showing "Current Headcount by Department."

2. Tracking New Hires Over Time

Now, let's track the flow of new employees coming into the company. This helps you monitor hiring velocity and see which teams are growing.

Start a New Look: Open a fresh view of your "Employees" Explore.
Select a Time Dimension: From your dimensions, find the Start Date field. Hover over it and choose the timeframe you want to group by, such as Month or Quarter. Add "Start Month" to your report.
Add Your Headcount Measure: Add the same "Employee Count" measure (a count of Employee_ID).
Filter for the Time Period: To keep the report focused, filter your "Start Month" to be in a relevant timeframe, like in the past 12 months.
Run and Visualize: Click "Run" and switch to a "Line" or "Column" chart visualization. You'll now see a trend of new hires joining the company each month. You can add the "Department" dimension to see which teams are driving the hiring trends.

3. Tracking Employee Departures (Terminations)

Just as important as tracking new hires is tracking departures. This process is nearly identical to the last one, but you'll use the End Date instead.

Select a Time Dimension: This time, find your End Date field and add "End Month" to your data panel.
Add a Measure: Add the "Employee Count" measure.
Filter for Departures: Make sure you are only counting employees who actually have an end date. Add a filter for End Date and set the condition to is not null. Don't forget to filter for a relevant time period, like in the past 12 months.
Run and Visualize: A line or column chart is a great way to see if there are spikes in terminations during specific periods. Adding the "Department" dimension here is critical - it helps you spot potential retention issues on a specific team.

Going Further: Advanced Headcount Metrics

Once you've mastered the basics, you can combine these concepts to create more insightful metrics like employee turnover rate or net headcount change.

Calculating Net Headcount Change

Seeing total headcount grow is good, but understanding the "net change" is better. It tells you if you're growing faster than you're losing employees. Many businesses do this in a spreadsheet after exporting Looks, but you can do it directly in Looker with table calculations.

Create a Look with "Month" as the dimension.
Add two measures: one that counts hires per month (filter by Start Date) and another that counts terminations per month (filter by End Date). You'll do this by filtering each measure — a feature in Looker called "custom fields" or using filtered measures in LookML. If you don't have that, you will have to create the two Looks separately, merge them, and then proceed.
Add a Table Calculation by clicking "Add Calculation." Use a formula like this to create "Net Change":

${hires_count} - ${terminations_count}

This new column will show you, month over month, your net gain or loss in employees. A column chart that shows positive and negative bars is a great way to visualize this.

Calculating Employee Turnover Rate

Turnover rate puts terminations into context relative to your total headcount. The formula is:

Turnover Rate = (Number of Departures in a Period / Average Headcount in that Period) * 100

Calculating the "Average Headcount" can be tricky, as you need a snapshot of the headcount at the start and end of the period. Typically, a Looker developer might create a dedicated measure in LookML called average_headcount to simplify this. However, you can approximate it in Looker using a few steps or merged results:

Get your total terminations for the period (e.g., for last quarter). Let's say it was 15.
Get your headcount at the start of the period. Let's say it was 200.
Get your headcount at the end of the period. Let's say it was 210.
Calculate the average headcount: (200 + 210) / 2 = 205.
Now calculate the turnover rate: (15 / 205) * 100 = 7.3%.

While you can calculate this manually, transforming your historical data to create a "point-in-time" headcount is the most scalable way to track this metric accurately inside Looker over the long term.

Final Thoughts

By following these steps, you can transform Looker from a generic BI tool into a powerful, purpose-built command center for your HR and people analytics. Start with the current headcount, layer in trends for hires and departures, and then graduate to strategic metrics like turnover rate to get a full picture of your organization's health.

Building these reports is a great start, but the real difficulty often comes from the setup process — connecting your HR system, cleaning the data, and getting everything into Looker in the first place can take weeks. Here at Graphed, we automate all of that. We integrate directly with your tools (your HRIS, CRM, finance software) and use AI so that you can simply ask questions in plain English, like, "Show me a chart of our headcount by department over the last year," and instantly get a live, interactive dashboard. We built Graphed to eliminate the manual setup so you can get straight to the insights.