Intuitive POS Data Mart Drives Smarter Analyst Decisions for a Major QSR

Too much data was too much of a good thing

This major QSR’s global digital analyst team needed to look at patterns in its POS system and supporting data to determine where greater adoption of digital technology could lead to gains in productivity, items sold, and check size.

The current data source, a data warehouse that stored all domestic POS transactions and order items, was too large and the data too raw to return fast results that business analysts and data scientists could understand and trust.

Additionally, they needed to combine this data with supporting data from other systems to answer questions such as: Did loyalty programs lead to a higher guest count, or did the use of the kiosk affect total order time? They wanted to predict the impacts of changes to the menu or loyalty programs on sales.

Use cases were the solution’s cornerstone

From a technical perspective, the POS data warehouse is impressive: it consolidates three billion transactions annually across thousands of retail locations into a single database schema and refreshes data from all locations on a nightly basis. However, the data set is so large that this single database simply could not meet all possible query requests promptly, if at all. Wavicle’s mission was to provide a viable source of answers to pressing business questions. This meant taking the time to intimately understand the business use cases and make them the cornerstone of our technology solution.

We worked closely with the user community, including business analysts and data scientists, and discovered a variety of needs, such as how they wanted to make sales comparisons across different times of the day and individual locations and regions, and how they would like to understand how different POS locations in the restaurant (e.g. kiosk, drive-thru) impacted measures of service efficiency.

Data mart solution built on AWS platform

Understanding that some users were looking to run specific queries against certain data sets, while others wanted to explore the data set as a whole, we were able to look at the solution architecture differently.

The solution consisted of a data mart built on the QSR’s existing database platform, Amazon Redshift. It integrates the various silos of POS, SoS, offer, and loyalty data and presents actionable information in a high-performance and intuitive format.

We leveraged Amazon Redshift design features, such as stored procedures, sort keys, and distribution keys, to build tables that met the needs of business analysts and data scientists. While summary tables were optimized for specific queries, a table built specifically for exploration allows data scientists to search for patterns using a more granular level of data.

New data mart provided instant COVID-19 insights

Once complete, the data mart solution allowed this QSR to quickly dive into its data for numerous planned analyst uses, but it was also invaluable for unforeseen and emerging needs. In fact, during the COVID-19 restaurant shutdown, they needed to quickly measure the impact on restaurant sales and operations. Specifically, they needed to understand the impact of sales shifting away from dine-in options to take-out and delivery. The new summary data mart made it easy for analysts to pull data summarized by POS area (e.g. drive-thru, delivery) at the restaurant and regional levels, informing the leadership team as they navigated an unprecedented crisis.