What is a Data Analyst at Lockton Companies?

As a Data Analyst at Lockton Companies, you are stepping into a critical role at the world’s largest privately held insurance brokerage. Specifically within the Pharmacy Analytics Reporting team, you will be at the forefront of helping employers and organizations navigate one of their most complex and expensive challenges: pharmacy benefit costs. Your work directly empowers Lockton’s consultants to negotiate better rates, design optimal plan structures, and ultimately save clients millions of dollars while ensuring their employees have access to necessary medications.

Your impact extends far beyond running simple queries. You will be responsible for transforming massive, complex datasets—such as pharmacy claims, enrollment files, and Pharmacy Benefit Manager (PBM) reports—into clear, actionable insights. By building robust reporting pipelines and intuitive dashboards, you give Lockton's client-facing teams the empirical evidence they need to drive strategic decisions.

Expect a highly collaborative, fast-paced environment where accuracy and business context are paramount. This role requires a unique blend of technical rigor and industry curiosity. You will not just be a number cruncher; you will be a vital strategic partner. The scale of the data you handle and the tangible financial impact of your reporting make this position both deeply challenging and incredibly rewarding.

Common Interview Questions

Getting Ready for Your Interviews

Thorough preparation requires understanding exactly what the hiring team values. At Lockton Companies, interviewers are looking for candidates who can bridge the gap between raw data and business strategy.

Technical Proficiency & Data Wrangling – You must demonstrate a strong command of SQL and Excel to extract, clean, and manipulate large datasets. Interviewers will evaluate your ability to handle messy, real-world data, join complex tables, and optimize queries for reporting purposes.

Analytical Problem-Solving – This evaluates how you approach ambiguous business questions. You will need to show that you can break down a high-level client request into a structured analytical plan, identify the right metrics to track, and draw logical conclusions from your findings.

Domain Curiosity & Business Acumen – While you may not need decades of healthcare experience for a Data Analyst I role, you are expected to understand—or quickly learn—the fundamentals of pharmacy benefits, claims data, and insurance structures. Interviewers look for candidates who proactively ask questions about the business context behind the data.

Communication & Stakeholder Management – You will frequently interact with non-technical stakeholders, including consultants and account executives. You must prove your ability to translate complex data findings into clear, concise, and compelling narratives that drive decision-making.

Interview Process Overview

The interview process for a Data Analyst at Lockton Companies is designed to be practical, thorough, and highly reflective of the actual day-to-day work. You will not face overly academic algorithm puzzles; instead, expect a strong focus on applied data manipulation, reporting logic, and behavioral alignment. The process typically moves at a steady pace, usually wrapping up within three to four weeks from the initial conversation.

You will generally start with a recruiter phone screen to assess your baseline technical skills, compensation expectations, and cultural fit. From there, you will move into a hiring manager interview that dives deeper into your resume, your experience with data visualization, and your approach to problem-solving. A core component of the process is often a technical assessment—either a take-home assignment or a live data exercise—where you will be asked to analyze a mock dataset (frequently resembling claims or financial data) and present your findings. The final stage usually involves a panel interview with cross-functional team members to gauge your communication skills and collaborative working style.

This timeline illustrates the typical progression from the initial recruiter screen through the technical evaluation and final panel rounds. Use this visual to pace your preparation, focusing heavily on applied SQL and reporting skills early on, and shifting toward presentation and behavioral storytelling as you approach the final stages. Keep in mind that specific steps may vary slightly depending on the Dallas office's current hiring bandwidth.

Deep Dive into Evaluation Areas

To succeed, you need to understand the core competencies the Lockton Companies hiring team focuses on. Below are the primary evaluation areas you will encounter.

SQL and Data Manipulation

• This area is the foundation of your technical evaluation. Interviewers need to know you can independently retrieve and format the data necessary for your reports.
• Strong performance means writing clean, efficient SQL queries that handle edge cases (like null values or duplicate records) without needing excessive guidance.

Be ready to go over:

• Joins and Aggregations – Knowing when to use different types of joins and how to aggregate data using GROUP BY.
• Window Functions – Using functions like ROW_NUMBER(), RANK(), and SUM() OVER() to calculate running totals or find the most recent claim per member.
• Data Cleaning – Handling missing data, standardizing text fields, and converting data types.
• Advanced concepts (less common) – Query optimization, indexing basics, and dynamic SQL.

Example questions or scenarios:

• "Write a query to find the total pharmacy spend per member per month, given a claims table and an enrollment table."
• "How would you identify and remove duplicate claim records where the claim ID is the same but the processing dates differ?"
• "Explain the difference between a WHERE clause and a HAVING clause, and provide an example of when you would use each."

Data Visualization and Reporting

• Because your end-users are often non-technical consultants or clients, your ability to visualize data is heavily scrutinized.
• Strong performance involves not just knowing how to use tools like Tableau, Power BI, or Advanced Excel, but knowing which chart or layout best communicates the underlying business message.

Be ready to go over:

• Dashboard Design Principles – Choosing the right visualizations (e.g., avoiding pie charts for complex data, using bar charts for comparisons).
• KPI Development – Defining and calculating key performance indicators relevant to healthcare or financial spend.
• Data Storytelling – Structuring a report so that the most critical insights are immediately visible to the user.
• Advanced concepts (less common) – Parameterized reporting, row-level security in dashboards, and automated report scheduling.

Example questions or scenarios:

• "Walk me through a dashboard you built. Who was the audience, and what key business question did it answer?"
• "If a consultant asks for a report showing year-over-year drug cost trends, how would you design the visual layout?"
• "How do you handle a situation where a stakeholder asks for a metric that you believe is misleading?"

Domain Knowledge and Problem Solving

• For a Pharmacy Analytics Reporting role, understanding the context of the data is crucial. You will be evaluated on your logical approach to industry-specific problems.
• Strong performance looks like asking clarifying questions about the data's origin and showing a logical, step-by-step approach to estimating or calculating business metrics.

Be ready to go over:

• Healthcare/Insurance Basics – Familiarity with concepts like premiums, deductibles, claims, and PBMs.
• Metric Calculation – Formulating logic for metrics such as Per Member Per Month (PMPM) costs or generic dispensing rates.
• Root Cause Analysis – Investigating sudden spikes or drops in data trends.
• Advanced concepts (less common) – Specific knowledge of National Drug Codes (NDCs) or AWP (Average Wholesale Price) pricing models.

Example questions or scenarios:

• "If our reporting shows a sudden 20% spike in pharmacy spend for a client in one month, how would you investigate the cause?"
• "Explain how you would approach calculating the cost savings of switching a population from a brand-name drug to a generic equivalent."
• "What steps do you take to validate your data before sending a final report to a client?"

Behavioral and Stakeholder Management

• Lockton Companies prides itself on client service and teamwork. Interviewers want to see how you handle pressure, ambiguity, and competing priorities.
• Strong performance means using the STAR method (Situation, Task, Action, Result) to provide concrete examples of your past collaboration and adaptability.

Be ready to go over:

• Managing Priorities – Handling multiple urgent reporting requests simultaneously.
• Cross-Functional Collaboration – Working with data engineers or business consultants to define report requirements.
• Handling Mistakes – Owning up to data errors and implementing processes to prevent them in the future.

Example questions or scenarios:

• "Tell me about a time you had to explain a complex technical data issue to a non-technical stakeholder."
• "Describe a situation where you found a significant error in your data right before a deadline. What did you do?"
• "How do you prioritize your tasks when you receive urgent ad-hoc requests from multiple consultants at the same time?"