Visualizations

Below are a few visualizations to provide insight into the type of analysis that we can provide and the deliverables we can offer.

Example 1:  Transaction Analysis for a Retail or Service Business

The following visualization options provide a retail or service business with the tools needed to track key metrics over time. With this series, business users can easily identify trends in key areas and look at results compared to previous periods or budgets. The ability to compare actual results, prior period results, and budgets as well as the ability to drill down to a more detailed level of data to investigate variances is instrumental in answering questions and identifying topics for further research that previously weren’t on the radar.  These charts represent a small sample of the potential analytical approaches using Tableau. The data was obtained from summary level tables exported into Excel from a point-of-sale (POS) system and then imported into Tableau for purposes of this presentation.

  • Viz 1: Multi-Stat Multi-Store Time-Series (User input determines main statistic (blue), comparison statistic (gold), time period and stores)
  • Viz 2: Multi-Stat per Store Time-Series (Same user inputs provided by broken out by Store)
  • Viz 3: Transaction Comparison Time-Series (compares Transactions to prior year and budgeted values)
  • Viz 4: Labor Hours: Actual vs Scheduled
  • Viz 5: Sales Mix Percentages (By Store)
  • Viz 6: Sales Mix Count
  • Viz 7: Animation of Gross Margin per Transaction (colored circles show ending value for each store but animation of grey lines can show the movement over time; up and to the right is better than flat)
  • Viz 8: Daily Transaction Bins (Store C most commonly experienced daily transaction counts of between 200 and 249; daily transaction counts of over 600 were reached in 2014, but had not been reached in previous years)

 

Example 2:  What Are My Customers Doing?

The data set in this example contains 1.15 million rows of transaction data from more than 300,000 customers over a 3 ½ year period covering multiple locations.  The data only contains a partial year for 2011 and ends in March 2015.  This study provides insight as to when customers first became customers, how many times they have visited the business, what they have purchased, and how coupons/discounts have affected their behavior.  These visualizations have drill down capabilities that allow subsets of the data to be exported for further examination.  The visualizations are just a small sample of the types of analysis that can be performed.

  • This dashboard segments customer activity for each calendar year by the year of customer acquisition (colors). This provides a high level view of new customers, retention and spend.
  • The yearly customer transactions are now filtered to months that include 2015 data (Jan, Feb, March). This shows that customers acquired in 2012 have had more transactions in the first quarter of each of the subsequent years than customers acquired in 2013 - 2015.
  • Customer transactions are now grouped by the calendar year of the customer acquisition. Based on Q1 results, each year of customer acquisition after 2012 has been lower than the previous year’s results. An inquiry can be made as to what has changed for the business over the past 4 years that may have led to these results.
  • This dashboard presents data on 3 different areas of customer activity (whether the transaction is from a new customer or an existing customer, whether customers had a single order or multiple orders over the lifetime, and a breakdown of customer counts by the number of their total orders).
  • Drilling into the workbook view, we see that approximately 9 months after the business started, the number of new customers per month has been on a slight downward trend.
  • Drilling into another workbook, we see the number of customers with a lifetime total order of 1 (single) is fairly steady even in months with very different transaction totals.
  • Another workbook shows that approximately 50% of customers have had only 1 order, and 90% of customers have had 8 or fewer orders over 3 ½ years.
  • The top visualization shows that a large number of customers have either never used a coupon or have used very few when they order. The bottom one shows that customers that have used at least one coupon have a higher overall number of orders and have placed an order more recently.
  • Note that for customers who used discounts, there are many more data points (customers) above the 50 order line and they are clustered closer to the vertical axis indicating fewer days since the customer’s last order.
  • This demonstrates how customers can be segmented by single or multiple orders and the number of different items the customer has ordered during their customer lifetime (bars). Even customers with multiple orders tend to purchase a small variety of items over their transaction history.

 

Example 3:  Monthly Statistics in Power Pivot

These tables provide a glimpse into two powerful tools from Microsoft’s Self Service BI suite, PowerPivot (an add-in that has the ability to analyze and visualize millions of rows of data within Excel) and DAX (Data Analysis Expressions Language).  Data was extracted from the POS system, then queried within Access to transform the data into a suitable format to optimize the capabilities of PowerPivot, and then over 3 millions rows were imported into the PowerPivot data model where dozens of measures were created using DAX.  Along with monthly statistics covering transactions and revenue, the customer stratification capabilities in some of these tables is particularly powerful and enlightening as a way to enhance the marketing process that can contribute toward increasing transactions and maximizing revenue.  Most often these types of statistics are not available from your POS or ERP (enterprise resource planning) system. PowerPivot is an excellent tool to combine data from various databases without having to learn SQL or any other database language to get the job done. The data in the tables can also be viewed in various chart formats using another Microsoft BI tool, PowerView. These tools are a very cost effective option to the average small business.

  • Monthly Statistics - Customers, Transactions and Revenue Metrics (customer retention and new customer information)
  • Monthly Statistics (Includes Accounts Receivable measures and stratifies by Customer Segment)
  • Customer Count Stratification by Transactions (Stratifies customer activity by 3 different measures – Total Transactions, Average Time Period Between Transactions, and Days Since Last Transaction)
  • Stratification of Customer Count by Number of Days Since Date of Last Visit (bars represent Unique Customers)
  • Labor Hours - Scheduled vs Actual (Combines data from two sources - scheduling and timekeeping software)

 

Example 4:  Transactional Analysis Uncovering Irregularities

This series tackles a more serious topic, employee theft, and is an example of how transactional irregularities are not always evident, particularly if the right reports are not available from your POS software.  Discounted transactions, including voids and unclosed transactions, are the focus for this craft brewery.  The first few visualizations show nothing out of the ordinary in either the relationship between gross and net sales or in the Discounted $ Amount per Staff.  (Note: Jack is the owner and Morgan is responsible for happy hour promotions.)

However, once we start to apply a custom algorithm to assign a risk score to each transaction, we start to see variances that should be reviewed in further detail.  In the third visualization, a trend comes to light that indicates Bernie as having some high risk transactions (remember, Jack is the owner and understandably discounts a high number of transactions).  In the scatter plot, Bernie clearly emerges as an outlier, having the higher average discount across all transactions.  Once we’ve identified Bernie as the main contributor to discounted sales, we can then investigate his actions more carefully.  Through the last three visualizations, we learn that he discounts sales most often on Tuesdays and Saturdays at 3 pm and 9 pm and most commonly discounts 32oz and 64 oz growlers of Beer 1 and Beer 2.  This visualization was created from over 750,000 rows of data that were imported into Tableau from an iPad-based cash register system.

  • Time series of Gross and Net Sales
  • Discount $ per Staff
  • Average Risk per Transaction by Staff
  • Average Discount per All Transactions
  • Discount % by Weekday by Staff
  • Discount by Hour and Item
  • Discount % by Item by Staff

 

Example 5:  Great American Beer Festival Award Winning Craft Breweries

We did this one just for fun since we appreciate good craft beer.  We downloaded award data from www.greatamericanbeerfestival.com for the last 10 years and looked at the geographic distribution of GABF awards across the US. The examples that follow include both single visualizations and dashboards encompassing multiple visualizations linked together by user action.  The user actions and filters unfortunately aren’t active in this demo but it provides an idea of the options.  Some of these visualizations would lend themselves for inclusion in marketing materials or annual reports.

  • Viz 1: GABF Award-Winning Breweries Dashboard (winner density plotted geographically with corresponding bar chart information below)
  • Viz 2: GABF Awards by State Dashboard (with Top N Filter)
  • GABF Awards by Brewery (Gold, Silver, and Bronze awards by Year)
  • Top 20 GABF Award-Winning Breweries for the past 5 Years

 

Example 6:  VA ABC Licenses in the Richmond, VA Vicinity

We created this dashboard in order to learn more about our local craft breweries and restaurants which have gained national attention.  We downloaded the ABC license data for the state of Virginia from www.abc.virginia.gov and selected Hardywood Park Craft Brewery as a point of interest from which to view surrounding licensed businesses.  It allows us to see the geographic distribution of all the types of businesses that have applied for ABC licenses.  Tableau has the ability to blend in various levels of demographic and GIS data (County, Zip Code, Census Tract) and present it in a map format along with your underlying data plus show it in motion over a selected time period.  We can search within a mileage radius from a particular point, by zip code or see where new licenses have been awarded within the selected time period.  This type of visualization lends itself to many different industries and is helpful for site selection, potential sales opportunities, and related demographic research for a potential business evaluating its competitors and customer base.

  • VA ABC Licenses within the vicinity of Hardywood Park Craft Brewery in Richmond
  • Select ABC LIcenses issued in central Richmond over the past 12 months (radius input)
  • Select Active ABC LIcenses in Virginia