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.
Title: Solution Principal, Information Management & Analytics
Company: Tropical Smoothie Cafe
Collaborators: Nelson David, Justin Licker
Overview of Use Case
When Tropical Smoothie Cafe wanted to learn faster from its point-of-sale data and integrate other data sources—such as weather details—for deeper insights, we leveraged the analytics solution of Alteryx to transform approximately 4 billion rows of data into fully enriched business insights that empowered business leaders to make more timely, data-driven decisions that drive profitability and growth. The cloud-based data pipeline solution we ultimately implemented combined the powers of Alteryx, AWS, and Tableau, fitting seamlessly into existing workflows without requiring additional personnel to run it. From there, prepped data was automatically transferred to Tableau, resulting in interactive visualizations that delivered rapid, at-a-glance answers.
Describe the business challenge or problem you needed to solve
With 3+ years’ worth of store-day files that contained check item level data, Tropical Smoothie needed a system that would answer the following questions faster, and would not require the skillset of SQL or other code:
Which markets are growing in comp sales and revenue?
Do people buy more smoothies when it’s sunny?
Are customers more likely to favor mustard on their flatbread in the west or the south?
What kind of special offers will draw customers into stores in stormy weather?
Which new franchises had the highest sales yesterday and why?
To answer these kinds of questions, Tropical Smoothie needed to unite new and historical point-of-sale data from its stores with customer loyalty app data, franchise information, internal metadata about menu items and promotions, and a new “super set” of data including weather and traffic details.
Describe your working solution
What did success look like?
Stand up the AWS cloud, Alteryx Server and Tableau Online
Get the existing data to the cloud
Pipe in the new data as it comes in, in real-time
Process, enrich, connect, transform, and output the data
All the historic 3+ years of data
The daily files coming in
Query the data sets, the largest of which is 2Tb+ and 2B rows
Visualize and create insights based on the requirements
Two Major Processes: The Big Historic Batch
Processing all the data from the previous 3+ years
Must have all of the historic data sources in place: Franchise Info, DMA, GPS Locations, Weather (Daily and Hourly), Promo Item mapping, Menu Item Categorization, Menu Item to Unit mapping, Fiscal Calendar, Daypart, etc.
Takes about 750 compute hours to process
Used Alteryx to separate the files in to 64 groups (by store), then fed each group into a macro
Used r4.16xl EC2 box w/ 64vCPU, 488 Gb RAM, and ran 64 batch macro Alteryx Workflows in parallel, which took about 12-15 hours and produced 7.5M CSV files totaling 3.2 Tb of data
Two Major Processes:The Daily Batch
Processing the ~600 files as they come in on a daily basis
Only runs once a day, but files come in randomly, so how to know when to start?
Alteryx workflow that counts today’s files, looking for the moment there’s 95% of the number of files from yesterday
Also tests to see if it’s after 2pm
If neither, wait 5 min and run again
Alteryx Command Line to the rescue!
Fires emails throughout the process with status updates, data validation, and exception reporting.
We used good old batch scripts to chain everything together
The transformations and outputs of 15 Different Alteryx Workflows all come together in a final Daily Batch workflow, leveraging hundreds of tools, and creating thousands of files every day.
Describe the benefits you have achieved
How was Tropical Smoothie Café impacted?
Able to implement an entire solution with minimal IT infrastructure
Self-service reporting tool for multiple departments
Key company-wide dashboards created
Improved topline performance visibility
Increased speed to insights vs. data manipulation from disparate sources
Improved analytics efficiencies across organization
Established one main source of the truth
Easy visualization to identify market trends
Addition of outstanding data sources to workflow
UAT environment creation
Creation of Metric Moving Scorecard for brand
Advanced analytics on market real estate and promotional trends