This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Announcing Alteryx + Snowflake | Alteryx and Snowflake make analytics and data science fundamentally easier. With the new integrated starter kit, you can push down data prep transformations and more into Snowflake for faster data quality and analytics output. Learn More
An analyst at Walmart Global Investigations used Alteryx Designer and Gallery to build a robust, repeatable workflow to identify high-risk transactions related to a nationwide, social engineering fraud scheme involving high-dollar gift card loads. The results of the workflow were then passed to Tableau Server for visualization of the risk in a near real-time fashion so that relevant stakeholders could take immediate corrective action to mitigate the risk. The workflow, visualizations, and mitigation processes have proven to be highly successful with millions of dollars of loss being averted and returned to the affected stores.
Describe the business challenge or problem you needed to solve
With a diverse and highly talented cadre of analysts and investigators, Walmart Global Investigations strives to identify and mitigate risk stemming from large-scale organized retail crime, high-level corporate fraud, and other significant risks to brand and company reputation. Through investigative efforts and analytical review, Global Investigations became aware of a significant nationwide social engineering fraud scheme occurring at Walmart and Sam's Club stores across the United States. Though the approach varied somewhat from one instance to the next, the result remained consistent across each event as the “bad actors” were able to successfully load high-value gift cards and other third-party gift cards without actually tendering any payment during transactions. This activity resulted in significant shortages and caused financial losses to affected stores. While simultaneously working with operational business partners to formulate policy changes and process improvements to mitigate the risk, Global Investigations also began working on a way to identify the high-risk transactions in real time so that immediate corrective action could be taken to not only stem the losses and identify the perpetrators but also to recover the fraudulently obtained funds before the bad actors had a chance to profit from them.
Describe your working solution
To combat the aforementioned fraud scheme, I used Alteryx Designer and Server to build, deploy, and schedule a robust, repeatable workflow. Relevant transactional records for the gift card loads are contained within multiple internal databases and include such data formats as Teradata, DB2, SQL Server, etc. I used Alteryx to run an initial SQL query to extract card loads over a certain value from multiple databases, cleanse and parse the data into a user-friendly format before unioning the disparate data streams back together into a comprehensive transactional data set with card loads from each of the affected business segments. Once the data sets were combined, I then sorted and grouped the data so that transactions occurred in chronological order by register and operator. I then used the multi-row formula tool to identify back-to-back transactions conducted by the same operator at the same register because those are the highest risk transactions and likely related to fraud. Once the high-risk transactions were flagged, I then enriched the transactional records with an ESRI .shp file containing United States Census data related to Metropolitan Statistical Areas (MSA) for each store. The MSA data was added so that the affected stores could be aggregated to a metro area rather than many individual locations. Following the geo-aggregation, the data was then segmented into groups based on the type of card being loaded. (i.e. third-party card or store gift card). If the card was a store gift card then it was passed through a dynamic input tool that would then run that card number through another internal database via a SQL statement to determine if any balance remained on the card. The data sets were then unioned back together and passed to Tableau Server.
The cleansed, blended, and enriched data that was passed to Tableau Server was then visualized in a dynamic dashboard that provided many valuable insights. For example, the aforementioned MSAs were plotted on a map to show the most affected areas across the country. In addition to the spatial overview of the MSAs, the top affected stores were also listed. Along with the geographic overview, the top operators at each facility were also depicted including information showing the percentage of the operator's transactions that were back-to-back loads. Finally, the key line item transactional details were shown along with an embedded hyperlink that would instantly take an interested user to a virtual receipt copy of the transaction in question. A mobile version of the Tableau dashboard was also deployed so that users could access the site in a mobile friendly format if they were away from their desk and needed to act on a series of transactions.
The Alteryx workflow was published to the Alteryx Gallery and set to run on 15-minute intervals with the refreshed data being pushed to the Tableau dashboard so that near real-time transactional analysis of the fraud scheme was available. Once displayed in the Tableau Dashboard, analysts, investigators, and other business partner stakeholders could then view the transactions as they occurred, conduct additional review, and determine the proper corrective response to each event. For example, a fraud confirmation protocol was developed so that each suspicious block of transactions that arose on the dashboard could be validated to determine if fraud had occurred or if the transaction was legitimate business. Once fraud was confirmed, the analyst or investigator that was reviewing the transaction could then initiate a recovery process to retrieve any funds that remained on the fraudulently loaded cards.
Describe the benefits you have achieved
The Alteryx workflow and Tableau dashboard constructed for this use case have been wildly successful and had a significant return on investment in the following key areas:
• Data Access - By providing the ability to link disparate data stored within many different databases, we are now able to see a much more comprehensive view of the issue rather than being limited by inefficient, segmented data silos.
• Time Savings - Because the workflow is repeatable and automated via the Alteryx Gallery scheduler, an analyst is now free to use their valuable, limited time on other business critical projects rather than constantly running a query and manually building a visualization for decision-makers. Furthermore, due to automation, near real-time visualizations of the fraudulent transactions are now possible.
• Flexibility - The underlying framework of the Alteryx workflow has proven to be highly flexible and easily adjusted to address other situations as they arise. For example, with a couple of small adjustments to the underlying SQL code that pulled the card loads, a completely separate workflow was built to apply the same methodology to another nationwide fraud scheme. This flexibility resulted in huge time savings¬ because a proven, successful response could be quickly deployed to address another situation without the need for a substantial amount of time being devoted to development of a new solution.
• Recovery - The ability to see near real-time transactions and confirm fraud has been a game changer. The new insights into transactional data have provided Global Investigations with a way to quickly see the fraud as it is occurring, as well as the ability to take immediate action to reduce the losses stemming from the fraud. By being able to link different internal databases and see if balances are still available on fraudulently loaded cards, Global Investigations is now able to initiate a recovery process against those cards and bring back funds that would have otherwise been lost to fraud and stop the fraudulent individuals from profiting. The recovery process has proven highly successful with millions of dollars being returned to the stores rather than going to the individuals’ pockets.