Superstore Analytics
2023 — A dashboard of what a US superstore sells and earns, when, to whom, and where
01 Problem
A superstore sells furniture, office supplies, and technology to consumers, companies, and home offices across the United States. Its order records can tell how much it sells and earns, in which months, to whom, where, and on which products, but not as rows in a spreadsheet.
The goal is a dashboard that answers those questions at a glance, one year at a time, for all customers or for one segment.
02 Data
The dataset holds 9,994 rows from January 3, 2014 to December 30, 2017. Each row is one product in an order, with the order and ship dates, the ship mode, the customer and their segment, the city, state, and region it ships to, the product and its category, and the sales, quantity, discount, and profit.
The file is saved in Windows-1252, not UTF-8. Read as UTF-8, its curly quotes and accents are invalid, and the original strips them out of product names before charting, so Southworth 100% Résumé Paper becomes Southworth 100% Rsum Paper. Reading the file in its own encoding keeps every name whole.
ecom <- read.csv("superstore.csv", stringsAsFactors = T, fileEncoding = "windows-1252")03 Types
Dates are written as month, day, and year, like 11/8/2016, so they are parsed into real dates, and the year and month of each order are pulled out for the filters and the monthly lines.
Text columns are read as factors, and a loop turns every one with more than 20 distinct values, such as IDs, names, cities, and states, back into plain text. Ship mode, segment, country, region, category, and sub-category stay factors. Finally the rows are sorted by order date.
ecom <- mutate(
ecom,
Order.Date = mdy(Order.Date),
Ship.Date = mdy(Ship.Date),
Order.Year = year(Order.Date),
Order.Month = month(Order.Date)
)
for (col in colnames(ecom)) {
if (is.factor(ecom[[col]])) {
if (nlevels(ecom[[col]]) > 20) {
ecom <- mutate(ecom, !!col := as.character(!!sym(col)))
}
}
}
ecom <- arrange(ecom, Order.Date)04 Orders
The original counts rows as orders, but a row is one product in an order. The 9,994 rows are 5,009 orders, so for 2017 it showed 3,312 orders where there were 1,687, and every orders chart was inflated the same way.
Counting distinct order IDs fixes it. 51% of orders hold a single product, and the largest holds 14.
filtered_data <- filter(ecom, Order.Year == selected_year)
total_order <- n_distinct(filtered_data$Order.ID)05 Months
Sales grow from $484,247 in 2014 to $733,215 in 2017, up 51% after a dip in 2015, and profit grows faster, from $49,544 to $93,439, up 89%.
Every year, September, November, and December are the three best months, bringing in 40% to 47% of the year's sales, and January or February is the weakest. Profit swings harder, and 2 of the 48 months lose money, which are July 2014 and January 2015.
plot_data <- ecom %>%
group_by(Order.Year, Order.Month) %>%
summarise(total_sales = sum(Sales)) %>%
ungroup()06 Categories
Furniture brings in 32% of sales but only 6% of profit. It keeps 2 cents of every dollar it sells, against 17 for office supplies and 17 for technology.
Tables alone lose $17,725 over the four years and bookcases lose $3,473. Furniture is also discounted the most, 17% off on average against 13% for technology.
plot_data <- ecom %>%
group_by(Category, Sub.Category) %>%
summarise(total_sales = sum(Sales), total_profit = sum(Profit)) %>%
ungroup() %>%
arrange(desc(total_profit))07 Customers
Consumers bring in 51% of sales, companies 31%, and home offices 19%. The West and East together earn 70% of the profit.
Central sells more than the South, $501,240 against $391,722, but keeps less, $39,706 against $46,749. Its orders are discounted the most, 24% off on average, against 11% in the West.
plot_data <- ecom %>%
group_by(Region, Order.Year) %>%
summarise(total_profit = sum(Profit)) %>%
ungroup()08 Products
The Canon imageCLASS 2200 Advanced Copier leads both rankings in 2016 and 2017. In 2017 it earns $15,680, 17% of the year's profit and 4.3 times the next product.
The original groups products by ID and labels each bar with that ID, leaving the name to the tooltip, which takes the first name it finds. But 32 IDs are shared by two different products. In 2017 it ranks the Logitech G19 Programmable Gaming Keyboard number 8 by profit with $1,493, but only $379 is its own, and the other $1,114 belongs to the Plantronics Savi W720 Multi-Device Wireless Headset System under the same ID. Grouping by ID and name together changes 10 of the 32 top 10 rankings the filters can show.
plot_data <- ecom %>%
filter(Order.Year == selected_year) %>%
group_by(Product.ID, Product.Name) %>%
summarise(total_profit = sum(Profit)) %>%
ungroup() %>%
arrange(desc(total_profit)) %>%
head(10)09 Original app
The Shiny dashboard has five tabs, each with its own year picker. Overview shows four totals, monthly orders, sales, and profit against their median month, and pie charts of orders by segment, category, ship mode, and region. Sales and Profit repeat the totals and their monthly line, then add monthly lines per category, segment, and region, and pie charts of the year by the same three. Product ranks the top 10 products by sales and by profit for a chosen year and segment. Data was meant to list every row.
This rebuild keeps the year and segment filters, and folds Overview, Sales, and Profit into one switch, since they are the same charts for a different measure. One set of totals replaces sixteen info boxes, bars replace the pies and can show a loss, and the other three years replace the median as the yardstick for each month. The monthly lines per category and region are dropped, since three or four lines in one ink tangle into each other and their shared median pooled every group into one number. The bars show the year by group, and the segment filter shows any one segment month by month. The Data tab is dropped too, since its rows add nothing the charts do not already show.
Three smaller bugs go with them. The Profit tab's region chart follows the year picked on the Sales tab. The Data tab stays empty, because the server fills a table under a different name than the page asks for. And profit tooltips cut losses short, so January 2015's loss of -$3,281 reads -$281.01.
10 Limits
Superstore is a sample dataset made for practice, not a real store’s books.
Under Orders, the category chart counts every order holding at least one product of that category. 1,717 orders mix categories, so its bars add up to more than the total.
Profit comes precomputed on each row, with no cost column, so the dashboard can show where money is lost but only hint at why through the discount.
Superstore Analytics
Furniture brings in 32% of Superstore's sales but 6% of its profit