Human-Readable ◉‿◉

Farmers Market Business Dashboard in Tableau

Dashboard Access:

GitHub Repository:

Data Science Tools:

The goal of this project was to build an interactive dashboard tool that enables staff to gather insight into business operations through access to and data visualizations of transactional data.

For an outline of the steps involved in this process see the list below:

  1. Ask questions of the data
  2. Run/test queries to verify access to fields of interest necessary to address business needs
  3. Extract queried results to an output file
  4. Import file to Tableau as a sheet
  5. Design charts using data provided from queried output
  6. Drag and drop sheets to a central dashboard
  7. Create a user interface (UI) for clients to interact

In the section that follows I discuss the UI of the dashboard. The format includes a title for each object, a short description of the object, along with a screenshot and code snippet.

Buttons: Dashboard Tips + Reset

Self-feedback to include buttons became apparent after switching into a user testing mindset. The main takeaway was that the UI was unintuitive. The user flow was not apparent moreover for the novice user, it was easy to have poor experience by not knowing how to deselect a selected object.

These objects are not a substitute for poor design principles and yet they can significantly improve the user experience. I highly recommend adding buttons into your dashboard.

Inventory Management Sheet

Keeping track of inventory can be pain. This tables provides insight into which goods were popular for a given market date. Column 2 titled 'Supply KPI' is meant to encourage staff to act on supply shortages, low demand for goods, and to report errors in how the data was collected.

For example, if jalapeno peppers are consistently sold out staff might request that vendors bring more product to the market the following week. Similarly, for goods that aren't as in demand, staff can communicate this information for vendors to reduce the amount of inventory on hand.

SELECT *
FROM
	(SELECT 
		cp.product_id,
		cp.market_date, 
		cp.demand,
		vi.supply,
		supply - demand AS net_abs,
        round(demand/supply,2) AS net_percent
	FROM
		(SELECT 
			product_id, 
			market_date, 
			SUM(quantity) AS demand
		FROM customer_purchases
		GROUP BY product_id, market_date) AS cp
	LEFT JOIN 
		(SELECT 
			product_id, 
			market_date, 
			SUM(quantity) AS supply
		FROM vendor_inventory
		GROUP by product_id, market_date) AS vi
	ON cp.product_id = vi.product_id AND cp.market_date = vi.market_date) AS inventory
LEFT JOIN product p
ON inventory.product_id = p.product_id

Market Revenue

Revenue is important to any business, the farmers market is no exception. To this end a minimum threshold was set to $550.00. Values below the cut-off are labelled green. Hovering over a single bar displays the exact amount of the cumulative spend by customers across all vendors and product types.

The bar chart features a parameter configured to filter for recent market events as well as the option to filter by weekday. As the scope of data is manipulated the pane is filtered to recalculate the reference line (in this case the average is applied).

Selecting a market date of interest filters 3/4 adjacent cells.

SELECT 
	market_date,
	SUM(ROUND(quantity*cost_to_customer_per_qty,2)) AS Total_Daily_Spend
FROM customer_purchases
GROUP BY market_date

Service Time Observations

This bar chart indicates grand sum of transactions. The number of transactions are binned into 30 minute intervals. Access to service time observations can help staff determine best times to open/close the market, scheduling shifts/breaks for staff, and to communicate similar information with vendors.

Darker shading (blue) indicates increased volume of transactions. Users can also filter by year, month, and weekday as seen fit. Tooltips are added to detail the number of transactions per half-hour interval.

SELECT 
	GROUP_CONCAT(DISTINCT market_date) AS market_date,
	CONCAT(market_date,' ', hr,half_hr) AS time_slot,
    COUNT(hr) AS num_transactions
FROM
(SELECT 
	market_date,
	HOUR(transaction_time) AS hr,
    CASE 
		WHEN 
			MINUTE(transaction_time) > 29 THEN ':30:00'
		ELSE ':00:00'
    END AS half_hr
FROM customer_purchases) AS table1
GROUP BY time_slot
ORDER BY time_slot

Revenue Breakdown by Vendor

Not all vendors are successful finding fit. This pie chart displays sales by vendor for the highlighted day. Referencing this worksheet may help staff determine high-low performing vendors.

Assuming vendors are returning for future market events, staff can use this information to structure coaching opportunities as they see fit.

SELECT 
	market_date, 
    cp.vendor_id,
    SUM(ROUND(quantity*cost_to_customer_per_qty,2)) AS cost_goods_sold,
    CONCAT(vendor_owner_first_name,' ',vendor_owner_last_name) AS full_name,
    v.vendor_name, 
    v.vendor_type
FROM customer_purchases cp
LEFT JOIN vendor v 
ON  cp.vendor_id = v.vendor_id
GROUP BY market_date, vendor_id, v.vendor_name, v.vendor_type, vendor_name, vendor_type
ORDER BY market_date

Transaction Summary

This tile consists descriptive statistics for the number of unique and total transactions for a given market date. The market event date is shown to make it apparent which date was highlighted on the 'market revenue worksheet'.

SELECT 
	market_date,
	 COUNT(DISTINCT customer_id) AS num_shoppers,
     COUNT(customer_id) as total_transactions
FROM customer_purchases
GROUP By market_date

This blog post covered the steps required to transform data from a SQL db to a dashboard in Tableau. Each object and worksheet shown on the UI was discussed. Rationale for design considerations were provided along with some thoughts on how end users might interact with the information.