A Structured Data Lake Strategy Turned Walgreens' Fragmented Data Into The Data Foundation for AI/Microsoft Copilot

The Solution

Phase One: Cleaning the Data Before Building AI

With the requirements established, Quandary and BVNA began addressing the existing data. Walgreens had accumulated information across years of construction, facilities, development, and capital improvement activity; and, like most enterprise datasets, historical information was not necessarily created with generative AI in mind.

Quandary worked to identify and address issues such as:

• Duplicate records
• Inconsistent naming conventions
• Incomplete records
• Legacy and obsolete fields
• Conflicting data formats
• Redundant information
• Inconsistent site and project identifiers
• Different terminology between departments
• Historical fields that no longer aligned with current processes
• Relationships between stores, projects, programs, vendors, milestones, and other records

This work was a critical part of establishing AI readiness.

AI systems can retrieve, analyze, and surface information at remarkable speed. However, that speed only creates value when the underlying data is accurate, consistent, properly structured, and connected to the right business context.

If information is duplicated, inconsistently defined, incorrectly related, or poorly organized, AI does not solve the problem. It can simply surface the wrong information faster and with greater confidence.

That is why Quandary focused first on strengthening the data foundation. By improving data quality, structure, relationships, and governance before introducing AI, the team could create an environment where future tools like Microsoft Copilot and AI agents would have reliable information to work with.

The objective was not simply to make the data cleaner. It was to create a trusted operational foundation capable of supporting the next phase of AI development.

Creating a Common Language for Walgreens Data

Data standardization became another critical component of Phase One because AI systems need more than access to information. They need a consistent way to understand what that information represents and how it relates to the business. During stakeholder discovery sessions with Walgreens, JLL, and the teams supporting store development and refresh operations, Quandary identified areas where departments used different terminology, definitions, and data structures to describe similar operational concepts.

A "project" could mean something different depending on the department using the term. A site, store, program, scope, milestone, or completion status could be categorized differently across systems and workflows. Those inconsistencies might be manageable when experienced employees know how to interpret them, but they become significant obstacles when introducing AI.

Quandary worked to establish more consistent definitions, structures, and relationships around core operational entities, including: Store | Site | Project | Program | Scope | Vendor | Contractor | Region | Department | Milestone | Budget | Status | Project Type | Completion

The goal was to create a shared data language that could be understood consistently across departments, applications, reporting environments, and eventually AI systems and this was especially important because ProTrack was already built around interconnected operational information.

A single site record could serve as the connection point for approvals, real estate information, construction milestones, maintenance activity, project limits, assets, documents, communications, vendors, and other information associated with that Walgreens location. Phase One expanded that same principle beyond ProTrack, Quandary began establishing a more consistent enterprise data model where relationships between stores, projects, vendors, budgets, milestones, documents, and other entities could be clearly understood.

That structure would become increasingly important as Walgreens prepared for Microsoft Copilot and future AI development.

For example, if an employee eventually asked an AI assistant to "show me all active refresh projects in this region that are behind schedule and over budget," the AI would need to understand exactly what Walgreens meant by a project, region, active status, milestone, and budget—and how those records were connected.

Without standardized definitions and relationships, the answer could be incomplete or incorrect.

With a common data foundation, however, future AI systems could have a much clearer framework for retrieving information, interpreting operational context, identifying relationships, and supporting employees with reliable answers.

The objective of Phase One was therefore bigger than cleaning up individual records or improving ProTrack. Walgreens needed to establish a common operational language for its data so that people, systems, Microsoft Copilot, and future AI agents could ultimately work from the same understanding of the business.

Building Data Lakes Around the Business, Not the Technology

Once the information had been evaluated, cleaned, standardized, and properly structured, Quandary and BVNA helped move it into data lakes designed around the specific operational requirements uncovered during the six-week stakeholder discovery process.

This was not a traditional data consolidation exercise where years of historical information were simply moved into one massive repository, the architecture was intentionally designed around how Walgreens actually operated.

The discovery process had revealed that different departments relied on different datasets, relationships, reporting requirements, and operational contexts. Construction teams needed one view of the business. Real estate teams needed another. Finance, facilities, vendors, project managers, and leadership each interacted with information differently.

Rather than forcing every department's data into a single generalized structure, Quandary and BVNA helped organize the environment so that individual datasets could support specific business requirements while remaining connected to a broader enterprise data foundation, for example:

• Store and site data could provide a consistent foundation for identifying individual Walgreens locations and connecting related operational activity.
• Project and program data could organize new-store development, refresh initiatives, capital improvements, and other project activity.
• Construction data could connect scopes of work, contractors, milestones, schedules, documents, approvals, and completion information.
• Vendor and contractor data could establish consistent relationships between external partners, projects, locations, contracts, and performance history.
• Financial data could connect budgets, forecasts, project costs, commitments, and other financial information to the appropriate projects and programs.
• Real estate data could support property, lease, location, development, and site-related requirements.
• Document and communication data could preserve the context surrounding projects, approvals, decisions, and operational activity.

This approach created something far more valuable than centralized storage - It created a business-aligned enterprise data architecture. Which meant that instead of requiring future AI systems to search across disconnected databases, spreadsheets, applications, documents, and departmental repositories, Walgreens could begin establishing governed data environments where information was organized according to clearly defined business relationships and this distinction was essential for the next stage of the AI journey.

A future Microsoft Copilot experience, for example, should not simply know that a document, project record, budget, and store record exist. It should be able to understand how those pieces of information relate to one another and which information is relevant to the employee asking the question.

By organizing data around the operational requirements identified during stakeholder discovery, Quandary and BVNA were helping create the architecture necessary for AI to retrieve information with greater context, accuracy, and relevance. The goal was not to put all of Walgreens' data in one place, it was to put the right data in the right structure so that employees, applications, Microsoft Copilot, and future AI agents could ultimately understand and use it.

Separating New Store Development and Existing Store Refresh Data

One of the most important findings from the six-week stakeholder discovery process was that new-store development and existing-store refresh programs could not simply be treated as identical datasets.

While the two programs shared certain information—such as locations, projects, vendors, budgets, milestones, and construction activity—their operational lifecycles were fundamentally different.

New-store development data tells the story of a location becoming an operating Walgreens store; this lifecycle could include site selection, real estate activity, approvals, design, permitting, construction, vendor coordination, inspections, project closeout, and ultimately the transition of a completed location into ongoing store operations.

Existing-store refresh data tells a different story: how an operating Walgreens location changes over time. A single store could undergo numerous capital improvements across multiple years: New Flooring → New Signage → Painting → Equipment Upgrade → ADA Improvement → Additional Remodel

Each project represents an individual event, but together they create something much more valuable: a historical record of investment and change at the store level and this distinction became particularly important when designing the data foundation for future AI capabilities.

If every refresh were treated as an isolated project, an AI system might be able to answer questions about a specific remodel. But if those projects were properly connected to the store, program, vendors, budgets, scopes, milestones, and previous improvements associated with that location, AI could eventually understand the broader operational history.

An employee could potentially ask:

• "What capital improvements have been completed at this store over the last five years?"
• "When was the flooring last replaced?"
• "Which contractors have worked at this location?"
• "How much has Walgreens invested in this store?"
• "Which locations in this region are likely candidates for another refresh based on their improvement history?"

Those questions require more than access to project records. They require historical context and clearly defined relationships between the data. By separating new-store development from existing-store refresh data while maintaining the appropriate relationships between them, Quandary and BVNA could preserve the unique lifecycle of each program without creating disconnected information silos and the result was a data model capable of representing both how a Walgreens location came into existence and how that location continued to evolve once it became operational.

Establishing those relationships during Phase One was essential. It transformed individual project records into a longitudinal operational history—creating the type of structured, contextual data foundation that Microsoft Copilot and future AI agents could eventually use to identify patterns, retrieve historical context, support capital planning, and help Walgreens make more informed decisions across its store portfolio.

Creating a Store-Centric Data Foundation for AI

At the center of the new data architecture was one of the most important entities in Walgreens' operations: the physical store location; Quandary and BVNA helped establish a model where operational information could ultimately be connected back to the location it affected.

A collection of disconnected project records, documents, budgets, vendor information, and construction activity, future Microsoft Copilot and AI solutions could potentially operate against a more connected representation of each Walgreens location and its history. A store would no longer exist as simply a location ID in one system and a collection of unrelated projects in another. It could become the central point connecting years of operational and capital activity.

For example, a future AI experience could potentially help an authorized employee understand when a store was developed, which capital programs it participated in, what improvements had been completed, how much had been invested, which contractors performed the work, what issues occurred, and when major assets or building components were last upgraded.

That context could also make more sophisticated analysis possible over time.

Instead of asking only, "What happened on Project X?" , Walgreens could eventually ask questions such as:

• "Show me the complete capital improvement history for this store."
• "Which locations have not received a major refresh within the last five years?"
• "Which contractors have completed the most projects in this region?"
• "Which stores have experienced repeated issues following similar improvements?"
• "How much capital has been invested across this group of locations?"
• "Which stores may be approaching the next logical improvement cycle?"

The value comes from the relationships behind those answers and AI can retrieve information quickly, but understanding a store's operational history requires the underlying data to establish how locations, programs, projects, scopes, vendors, costs, milestones, documents, issues, approvals, and completed improvements relate to one another.

That was a critical objective of Phase One, Quandary and BVNA were not simply preparing individual datasets for AI. They were helping establish a store-centric enterprise data foundation that could give future Microsoft Copilot experiences and AI agents the context required to understand Walgreens' physical locations as connected, evolving operational assets.

Preparing the Division for Microsoft Copilot and Enterprise AI

The data modernization initiative was deliberately positioned as Phase One because data modernization was not the final destination. AI enablement was.

Before Walgreens could introduce Microsoft Copilot and other AI capabilities across these operations, the underlying information needed to be trustworthy, structured, connected, and accessible.

The six-week stakeholder discovery process helped Quandary and BVNA understand how the business actually operated. Data cleanup improved the quality of the information. Standardization created a common language across departments. The new data architecture established meaningful relationships between stores, projects, programs, vendors, budgets, milestones, documents, and historical improvements.

Together, those efforts created the foundation required for the next phase of AI development and once that foundation was established, the opportunity extended far beyond traditional dashboards and reporting. Instead of opening multiple applications, reviewing reports, searching through documents, and manually assembling information, employees could eventually interact with operational data using natural-language AI experiences through Microsoft Copilot.

A project leader could potentially ask:

• "Summarize the current status of this project and identify anything putting the completion date at risk."
• A regional leader could ask:
• "Show me the capital improvement history for this store and summarize the work completed during the last five years."
• A program manager could ask:
• "Which active projects are behind schedule, over budget, or waiting on an approval?"

Leadership could potentially ask: "Which stores in this region have received the highest capital investment, and what were the primary drivers?"

The opportunity could eventually extend beyond answering questions; as Walgreens continued developing its AI capabilities, the same connected data foundation could support more advanced use cases involving project risk identification, portfolio analysis, historical comparisons, vendor performance insights, capital planning, document summarization, exception detection, and AI-assisted decision support.

This represents an important shift in how employees interact with enterprise information. Historically, employees needed to know where information lived, which system contained it, which report to open, and how different records related to one another and the future-state vision reversed that relationship. Which meant that instead of requiring employees to find the information, Microsoft Copilot and future AI experiences could help bring the right information, context, and relationships to the employee and that experience depends entirely on the quality of the foundation beneath it.

Copilot can only provide reliable answers when it has access to reliable information. AI can only understand relationships that have been properly established. And intelligent automation can only make dependable decisions when business definitions, permissions, data structures, and operational context are clear. This is why Phase One mattered - Quandary and BVNA were helping Walgreens prepare the division not simply to adopt AI, but to create an environment where AI could eventually be useful, governed, contextual, and trusted. Each step increased the value of the next, which created a very intentional roadmap: Clean Data → Standardized Data → Structured Data → Connected Data → Accessible Data → Microsoft Copilot & AI

Creating the Foundation for AI Agents

Microsoft Copilot represented one part of the future roadmap; the same data foundation could also prepare the division for more advanced AI agents and intelligent workflow automation.

Future AI capabilities could potentially help teams:

• Summarize project activity
• Identify schedule risks
• Surface incomplete milestones
• Find relevant project documentation
• Analyze store improvement histories
• Compare projects across regions
• Identify exceptions requiring human attention
• Answer questions about projects and sites
• Surface vendor or contractor information
• Assist with executive program reporting
• Coordinate next actions across operational workflows

Those capabilities depend on context and context depends on relationships, and these relationships depend on the data architecture underneath them; which is why the first phase focused so heavily on discovery and data.

From Data Migration to AI Readiness

What could have been approached as a traditional data cleanup and migration project became something much more strategic. Quandary Consulting Group and BVNA were helping Walgreens establish the foundation for an operational environment where AI and Microsoft Copilot could eventually change how employees access, understand, and interact with construction, facilities, real estate, store development, and capital improvement information.

The six-week stakeholder discovery process was critical to that effort. Before redesigning the data architecture, Quandary needed to understand the business behind the data and the team worked across departments to understand:

• Who uses the data.
• What information they need to do their jobs.
• How different teams define and interpret that information.
• How data moves between departments, applications, and workflows.
• Which relationships between stores, projects, programs, vendors, budgets, milestones, and documents matter most.
• Where inconsistencies, duplication, and structural gaps could create problems.
• And ultimately, how the information needed to be organized for future AI systems to use it effectively.

Those findings shaped the work that followed: Data could be evaluated and cleaned based on actual business requirements rather than assumptions. Common definitions could be established around critical operational entities. New-store development and existing-store refresh information could be structured according to their distinct lifecycles. Data lakes could be organized around specific business needs. And information could be connected back to the physical Walgreens location to create a richer historical view of each store.

The result was more than migrated data, it was the beginning of an AI-ready operational data foundation. Instead of future AI systems encountering disconnected records with inconsistent terminology and unclear relationships, Walgreens could move toward an environment where information had greater structure, context, and meaning.