top of page

Data Analysis Case Study:
"The Mystery of the Vanishing Sales"

Product

A POS product for an industry that requires licensed businesses to follow a strict set of compliance regulations.

My Role

  • Led the investigation efforts.

​

  • Conducted the data analysis and data explorations.

​

  • Developed the investigation documentation, including the problem statement, initial findings assessment, action plan, and investigation summary.

​

​

  • Developed the issue resolution business requirements & JIRA tickets.

​

​

  • Acted as the product liaison between support and engineering.

The Problem

Customers from a specific region were increasingly frustrated because some product sales, which were required by law to be reported daily, were not reported to the state’s compliance tool. 

​

This issue, which had been ongoing for weeks, was causing internal strife between engineering and customer support. Engineering had confirmed that sales were going through and could not identify a technical reason for the sales reporting failures. The engineers believed the issue resulted from user error as a new compliance workflow had recently rolled out for that region. Support was adamant that it was far too many customers to have it be just a “user error,” and if that was the cause, they needed concrete evidence to take back to the customers.

Part 1: Developing the Problem Statement

To rectify the issue, we needed to find the root cause, and to find the root cause, we needed additional data insights. To obtain the data insights, I needed to develop a list of questions to help us better understand how inventory was brought into our POS system, what data was associated with the inventory items, and what information was reported for each sale.   

 

First, I outlined the criteria required for an item to be successfully reported to the state compliance tool. Item sales are recorded if the item:

​

  1. Belongs to an inventory group that falls under the state’s compliance regulations.

  2. Has an active compliance ID assigned to it.

  3. Was accepted in the state regulatory tool before being added to their POS inventory

  4. Has an active qty in the customer's inventory list.

​

Using the criteria above, I developed specific scenarios that could lead to sales reporting failures:

​

  • Scenario 1: Items that did not have a compliance ID were reported as part of a “mixed” sale (i.e., sales of items that require compliance IDs and those that do not), and the state compliance tool API rejected all or a portion of the sale.  ​

​

  • Scenario 2: The regulatory ID, a 28 alpha-numeric string, was incorrectly entered or entered into the wrong ID field when the customer added the product to their POS inventory.

​

  • Scenario 3: The product is not in the customer’s “active” inventory in the compliance tool. 

​

​

​

​

​

 

From here, I developed a problem statement document that outlined the problem we needed to solve, the hypothetical scenarios, and the open questions we needed answered. The problem statement was sent out to support, product, engineering, and executive leadership to a) help cross-functional resources understand the potential causes of the issue and b) help key stakeholders prep for the customer interviews.

​

INSERT DOCUMENT PAGES HERE

 

Note: Products in our POS inventory have 2 ID Fields, one for the compliance ID and one for scanning products at the register. Customers have the choice to apply the compliance ID across both ID fields or manually add their register SKU.

Part 2: Initial Findings

While the customer support and product owner worked on obtaining answers to the open questions from the customers, I selected a customer (Customer A)  to analyze. I prepped and cleaned their currently available data, which included:

​

  • An export of Customer A’s sale data from the compliance regulatory tool

  • Exports of Customer A’s sale data, active inventory, and zeroed inventory for the last month

  • An inventory export from Customer A’s POS onboarding

  • An export of the Customer A’s  inbound product history

​

I conducted a few different exercises to analyze the data, including comparing the list of completed sales from our POS to those successfully reported to the compliance tool and cross-referenced products between the sales lists. 

​

Now that I had a baseline understanding of the issue, I could eliminate the first scenario from the list as it was clear that “mixed” sales were not the cause of the reporting failures. I also confirmed that several of Customer A’s products had the compliance ID in the wrong field and that this was the likely cause of the reporting failures. However, I did not have enough evidence to rule that as the only cause, nor had I been able to rule out scenario 3. 

 

Next, I reviewed the insights from the completed customer interviews, which yielded a surprise. The customers affected by the sales reporting issues had not manually entered their product information; it was pulled directly from the regulatory system. This new insight confirmed that Customer Support was likely correct; the root cause of the issue was likely not user error. It also posed a new set of questions: ​​

1. If the products were automated into our system via the compliance tool, why are the regulatory IDs in the wrong field?​

​

2. Similarly, why is the regulatory ID in the wrong field for some products but not all?

Part 3: An Unlikely pattern

Armed with the new information, I developed a new hypothesis that our POS system was, for unknown reasons, saving the compliance ID in the wrong field for certain products. To provide or disprove my theory, I selected a second customer (Customer B)  to analyze.

 

Not long into Customer B’s data exploration, I made an observation that seemed to confirm my hypothesis. Not only did Customer B have products with the compliance ID in the wrong field, but the incorrect IDs started with the same L6 and L7 alphanumeric sequence as Customer A. Given that these two shops were not part of the same enterprise and were located on opposite sides of the region, what are the odds they decided to create their own barcode SKU using the same alphanumeric characters? 

 

After doing a quick data pull on the other affected customers, I quickly identified that they, too, had products with IDs in the compliance ID field that started with the same L6 or L7 alphanumeric sequence as Customer A and B. It was now clear that these barcodes were the cause of the sales reporting failures, but their existence only added more questions - ​​

  1. Where were these incorrect IDs originating from? Our system or the compliance tool?

  2. If it’s our POS, what triggered our system to create these “false” compliance IDs? 

  3. At what step are these “false” IDs being applied to the product?

  4. Were all of the affected products brought in on the same inventory manifests? Or are they scattered across multiple inventory manifests?

Part 4: Identifying the root cause

I could answer some of the questions posed above on my own, but others would require help from engineering. I relayed my findings, along with the Excel sheets I had compiled, for Customer A and Customer B to our lead engineer, and together, we collaborated to find the root cause.


Upon reviewing a data export of Customer A and B’s inbound inventory transfers, I confirmed that the affected products were spread over several inventory manifests. Some products on those manifests had come into the POS with no issues, while others had the “false” L6 and L7 IDs applied. 

 

I then checked the product data itself to try and find any similarities, and this is where I had my “Aha!” moment. Our POS categorized products by unique inventory groups and then by inventory types, and the affected products all had “new” inventory types that had been created but not mapped in our system. Unfortunately, this issue was not immediately apparent because the new inventory types were close in name to existing inventory types; for example, we had a type called “Fruit Chews,” and a new inventory type had been added called “Fruit Chew.”

​

Using this information, Engineering confirmed that these “new” inventory types, introduced accidentally by a well-meaning internal resource, were the source of the issue. Since these products had an inventory type not “known” by the POS, the system assumed these were unregulated products and categorized them as such. Thus, the system did what it was supposed to; it generated a unique product SKU for an unregulated product and applied this ID to the compliance ID field. We finally had our root cause.

 

Part 5: Issue Resolution.

Now that we had our cause, we needed to implement fixes, both on the technical and customer support sides. Working with the team Product Owner, we came up with a resolution plan:

​

​

1. Fix the Inventory Types

  1. Remove the “bad” inventory types

  2. Associate the affected products with the correct inventory types

​

 

2. Fix the “bad” compliance ID’s

  1. Across all customers, identify which products were affected

  2. Provide engineering with a .csv of the correct compliance IDs for each customer

  3. Run the script to replace the compliance IDs

  4. Check to make sure that all IDs have been updated properly

​

​

3. Explain the situation to customers

  1. Contact customers to inform them the issue has been resolved

  2. Provide customers with information on what the issue was and the steps we’ve taken to resolve

​

​

4. Resubmit missing sales to the state compliance tool

  1. Work with customers to identify all of the sales that were not reported to the state compliance tool

  2. Resubmit failed sales

  3. Work with customers to confirm all of the sales are now showing in the state compliance tool

​

 

Using this as a guide, I created a data analysis summary outlining a recommended action plan, my findings, and an overview of the exploration methods used to conduct the analysis. The final document was shared company-wide to help everyone understand what caused the issue and how we intended to resolve it.

 

Outcome

In the end,  the team was able to fix the inventory types and bad compliance IDs and release a fix in less than a  week. A team retrospective was held shortly after, and that meeting helped shape processes and policy for forthcoming work, including:

​

  1. Issue Prevention:

    1. New functionality to prevent unauthorized users from being able to alter product data, such as inventory groups and types.

    2. A sign-off from the compliance team is required before new inventory types are added to the system. 

​

2. Identification of potential issues:

  1. New tools and reports for Customer Support to help them identify the number of daily sales reported to the compliance tool for each customer. 

  2. Customer Support and Product to meet weekly to review unresolved customer support tickets to help identify patterns that may indicate a more significant issue.

bottom of page