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.

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?"

Key Responsibilities

As a Data Analyst in the Dallas office, your days will be a mix of deep-focus technical work and collaborative problem-solving. Your primary responsibility is to ingest, clean, and analyze complex pharmacy claims datasets provided by various PBMs and insurance carriers. You will run standardized monthly and quarterly reporting packages that track client pharmacy spend, utilization trends, and clinical program performance.

Beyond routine reporting, you will frequently field ad-hoc analytical requests from Lockton’s pharmacy consultants. This might involve modeling the financial impact of changing a client's copay structure or identifying members who are non-compliant with their medication regimens. You will act as the crucial bridge between raw PBM data files and the polished, strategic presentations that consultants deliver to employers.

You will also spend time continuously improving existing processes. This includes automating manual Excel reports using SQL or Python, migrating legacy reports into modern BI dashboards (like Tableau or Power BI), and creating robust documentation for your data pipelines. Collaboration is continuous; you will work closely with other analysts, actuaries, and account executives to ensure that the data you provide is accurate, timely, and perfectly aligned with the client's strategic goals.

Role Requirements & Qualifications

To be highly competitive for the Data Analyst I Pharmacy Analytics Reporting position, you must bring a solid mix of foundational data skills and a strong appetite for learning the healthcare domain.

• Must-have skills – Advanced proficiency in SQL for data extraction and manipulation. Deep expertise in Microsoft Excel (PivotTables, VLOOKUPs/XLOOKUPs, complex nested formulas). A strong analytical mindset with the ability to troubleshoot data discrepancies independently. Excellent written and verbal communication skills.
• Experience level – Typically requires 1 to 3 years of experience in a data analysis, reporting, or financial modeling role. A bachelor’s degree in Mathematics, Statistics, Economics, Information Systems, or a related quantitative field is generally expected.
• Soft skills – High attention to detail is non-negotiable, given the financial implications of the data. You must possess strong time-management skills to juggle multiple client deliverables, alongside a collaborative attitude for working with cross-functional consulting teams.
• Nice-to-have skills – Prior experience with healthcare data, specifically pharmacy claims, PBMs, or medical benefits. Experience building interactive dashboards in Tableau or Power BI. Familiarity with a scripting language like Python or R for advanced data automation.

Common Interview Questions

The questions below represent the types of inquiries you can expect during your interviews. They are drawn from actual candidate experiences and are designed to test your technical depth, business logic, and cultural fit. Do not memorize answers; instead, use these to practice your problem-solving frameworks and storytelling.

SQL and Technical Execution

• Tests your ability to write functional code and manipulate relational databases.
  • Write a SQL query to find the top 5 most expensive drugs by total cost in a given year.
  • How do you handle NULL values when joining two tables?
  • Explain the difference between a LEFT JOIN and an INNER JOIN with a practical example.
  • How would you optimize a query that is taking too long to run on a large claims dataset?
  • What is a CTE (Common Table Expression), and when would you use it over a subquery?

Data Visualization and Excel

• Evaluates your practical reporting skills and tool proficiency.
  • Walk me through the most complex Excel model or formula you have ever built.
  • How do you decide whether to use a line chart versus a bar chart in a client dashboard?
  • If a dashboard is loading very slowly, what steps would you take to troubleshoot and improve its performance?
  • How do you ensure your visualizations are accessible and easily understood by a non-technical audience?

Problem Solving and Domain Logic

• Assesses your analytical thinking and ability to grasp pharmacy/insurance concepts.
  • If a client's pharmacy costs increased by 15% but their employee headcount stayed the same, what data points would you look at to explain the increase?
  • How would you define a "duplicate claim" in a dataset, and how would you prove your logic is correct?
  • What steps do you take to perform Quality Assurance (QA) on a new report before publishing it?
  • Tell me how you would calculate the average cost per prescription from a raw claims file.

Behavioral and Leadership

• Focuses on your communication, adaptability, and stakeholder management.
  • Tell me about a time you had to push back on a stakeholder's request because the data did not support their hypothesis.
  • Describe a situation where you had to learn a new technical tool or business concept very quickly to meet a deadline.
  • Give an example of a time you identified an inefficient process and took the initiative to improve it.
  • How do you handle a situation where you receive conflicting priorities from two different managers or consultants?

Frequently Asked Questions

Q: How technical is the interview process for a Data Analyst I role? You should expect a solid technical evaluation, primarily focused on SQL and Excel. However, because this is an entry-to-mid-level role (Level I), interviewers are generally more interested in your logical problem-solving abilities and your foundation in data manipulation than in highly advanced algorithmic programming.

Q: Do I need prior experience in pharmacy or healthcare analytics to get hired? While prior healthcare or PBM experience is a strong advantage, it is not strictly required for a Level I role. If you lack domain experience, you must demonstrate exceptional technical skills and a clear, proactive eagerness to learn the complexities of pharmacy benefits quickly.

Q: What is the working style like at the Dallas Lockton office? Lockton is known for a highly collaborative, client-first culture. The Dallas office operates in a professional, fast-paced environment. You will be expected to be highly responsive to consulting teams, and there is a strong emphasis on accuracy, accountability, and teamwork.

Q: How should I prepare for the technical assessment or take-home assignment? Focus on accuracy, clear documentation, and business logic. When you present your findings, do not just show the code; explain why you made certain data cleaning decisions and highlight the actionable business insights you derived from the dataset.

Other General Tips

• Master the STAR Method: When answering behavioral questions, structure your responses clearly. Outline the Situation, the Task you were assigned, the specific Action you took, and the quantifiable Result of your work.
• Focus on Accuracy Over Speed: In the insurance brokerage industry, incorrect data can lead to massive financial miscalculations for clients. During technical tests or case studies, verbally emphasize your Quality Assurance (QA) steps and how you validate your numbers.

• Understand the Broker Perspective: Remember that Lockton is a broker, meaning they advocate for the employer/client, not the insurance carrier or the PBM. Framing your analytical answers around "finding savings for the client" or "auditing PBM performance" will show you understand the business model.

• Ask Insightful Questions: At the end of your interviews, ask questions that show you are thinking about the role strategically. Ask about the biggest data quality challenges the team faces, or how the pharmacy analytics team integrates with the broader health and welfare consulting groups.

Summary & Next Steps

Securing a Data Analyst position at Lockton Companies is an incredible opportunity to leverage your technical skills to solve high-stakes, real-world financial and healthcare challenges. By stepping into the Pharmacy Analytics Reporting team, you will be positioning yourself at the intersection of data science and strategic business consulting, doing work that tangibly improves the efficiency of healthcare benefits.

To succeed, focus your preparation on mastering SQL and Excel, refining your data storytelling abilities, and familiarizing yourself with the core concepts of pharmacy benefits and claims data. Remember that Lockton values analysts who are not only technically sound but also deeply curious about the business context behind the numbers. Approach your interviews with confidence, clarity, and a collaborative mindset.

This compensation data provides a baseline expectation for the role in the Dallas market. Keep in mind that total compensation at Lockton often includes performance-based bonuses and exceptional benefits packages, so evaluate offers holistically based on your specific experience level and technical proficiency.

You have the analytical foundation and the drive to excel in this process. Continue to practice your SQL queries, polish your behavioral stories, and explore additional interview insights on Dataford to refine your edge. Stay focused, trust your preparation, and go show the Lockton team the immense value you can bring to their analytics organization.