community
cancel
Showing results for 
Search instead for 
Did you mean: 

alteryx Use Cases

Learn how you can leverage Alteryx in your organization.
Announcement | Get certified today - take the Alteryx Designer Core and Advanced exams on-demand now!

Transforming 4 Billion Rows of Data into Insights with Alteryx, AWS, and Tableau

Atom
Created on
 

Name: Nelson Davis
Title: Solution Principal, Information Management & Analytics
Company: Tropical Smoothie Cafe
Collaborators: Nelson David, Justin Licker
 
TSC Logo.png 
 
 
 
 
 
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?

  1. Stand up the AWS cloud, Alteryx Server and Tableau Online
  2. Get the existing data to the cloud
  3. Pipe in the new data as it comes in, in real-time
  4. Process, enrich, connect, transform, and output the data
    1. All the historic 3+ years of data
    2. The daily files coming in
  5. Query the data sets, the largest of which is 2Tb+ and 2B rows
  6. Visualize and create insights based on the requirements

 

From Data to Success.PNG

 

Alteryx enrichment.PNG

 

 

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 Graph TSC.PNG

 

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.

 

 

Daily Batch Workflow.PNG

 

 
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

What's next?

  • 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
  • Adoption of a data and analytics driven culture
  • 1,000 cafes by 2020

 

Related Resources

Comments
Fireball

I am not worthy. 

 

Would love to see some of the screenshots from Tableau if they don't expose proprietary secrets to smoothie making or whatever.  

 

Thanks for posting this!

brian

Labels