Iowa Sales Dashboard

Author

Richard Diaz

Published

Invalid Date

Introduction

Sales are the lifeblood of any business. To stay competitive, companies must outperform their rivals at both local and state levels. This project aims to highlight alternative resources for refining metrics critical to strategic planning and competitive analysis. We designed this experience to be accessible for beginners, hoping to illuminate new perspectives on data utilization.

Business Problem

By creating an interactive dashboard visualizing various aspects of liquor sales in Iowa, we aim to provide actionable insights into sales trends, county comparisons, and local-level sales analysis. This tool is designed to empower businesses with data-driven strategies to enhance their market position. Time is essential for a business to stay-aflot or ahead and so it necessary for companies to have a dashboard for reports or before important meetings.

Data Source

This dataset provides a comprehensive overview of liquor sales to retailers by the State of Iowa since January 1, 2012. As the state oversees the wholesale distribution, this dataset offers a detailed view of retail sales. It includes transactions from various outlets, such as grocery and liquor stores. For this report, we analyzed a sample of 113,131 rows from the original 28,389,280.

SQL Code

For more details, please visit the following: Link.

Import Data - Retried from SQL Query

We retrieved the data through a SQL query from a live database hosted on Google BigQuery. For those interested in accessing the dataset, a free account can be created on Google BigQuery, where the Iowa Sales Database is available in the marketplace. SQL queries are stored on online databases so users can pull data from. For more information on click on: process.

#importing dataset 
library(readxl)
library(dplyr)
library(lubridate)
library(forecast)
iowa_sales <- read_excel("~/Desktop/iowa_sales 2 1.xlsx", 
    sheet = "Data")
tail(iowa_sales)
# A tibble: 6 × 8
  date                county   city      category_name bottles_sold sale_dollars
  <dttm>              <chr>    <chr>     <chr>                <dbl>        <dbl>
1 2012-01-25 00:00:00 HARRISON WOODBINE  VODKA FLAVOR…           12       191.  
2 2012-01-25 00:00:00 WAPELLO  OTTUWMA   COFFEE LIQUE…            1         8.58
3 2012-01-25 00:00:00 WEBSTER  FORT DOD… CANADIAN WHI…           12       314.  
4 2012-01-25 00:00:00 LEE      FORT MAD… IMPORTED VOD…            1         8.8 
5 2012-01-25 00:00:00 POLK     WEST DES… STRAIGHT BOU…           12       115.  
6 2012-01-25 00:00:00 POLK     WEST DES… IMPORTED VOD…            2        33.5 
# ℹ 2 more variables: volume_sold_liters <dbl>, bottles_sold_1 <dbl>
# Select only the 'date' and 'sale_dollars' columns
iowa_sales_filtered <- iowa_sales %>%
  select(date, sale_dollars)

# Check the first few rows: we need to check for data type stored to transition to ts frame
head(iowa_sales_filtered)
# A tibble: 6 × 2
  date                sale_dollars
  <dttm>                     <dbl>
1 2012-01-03 00:00:00       468.  
2 2012-01-03 00:00:00         7.84
3 2012-01-03 00:00:00       419.  
4 2012-01-03 00:00:00        91.4 
5 2012-01-03 00:00:00        32.7 
6 2012-01-03 00:00:00        34.4

Preliminaries - Report and Dashboard

Prior to diving into the core analysis, we conducted preliminary investigations using Excel, Looker Studio, and PowerBI. These tools enabled us to identify Des Moines as Iowa’s leading city in terms of liquor sales, with total revenue reaching an impressive $4.10 billion and volume sales of approximately 259.7 million. Among the counties, Woodbury, Story, and Scott emerged as the top performers in revenue generation. This foundational analysis not only sets the stage for deeper inquiry but also underscores the significant economic impact of liquor sales across different regions within the state.

PowerBI

Looker Report

Excel Report

As we transition into a more detailed examination, our next focus will be on the Sales Time Series.

summary(cars)
     speed           dist       
 Min.   : 4.0   Min.   :  2.00  
 1st Qu.:12.0   1st Qu.: 26.00  
 Median :15.0   Median : 36.00  
 Mean   :15.4   Mean   : 42.98  
 3rd Qu.:19.0   3rd Qu.: 56.00  
 Max.   :25.0   Max.   :120.00

Plot Output

You can also embed plots, for example:

Code 
library(ggplot2)
dat <- data.frame(cond = rep(c("A", "B"), each=10),
                  xvar = 1:20 + rnorm(20,sd=3),
                  yvar = 1:20 + rnorm(20,sd=3))

ggplot(dat, aes(x=xvar, y=yvar)) +
    geom_point(shape=1) + 
    geom_smooth()
Figure 1: Pressure

Note that the code-fold: true parameter was added to the code chunk to hide the code by default (click “Code” above the plot to see the code).

The use of the label and fig-cap options make this a cross-referenceable figure (see Figure 1).

Interactivity

You can also add interactive plots. For example:

library(dygraphs)
dygraph(nhtemp) %>% 
  dyRangeSelector(dateWindow = c("1920-01-01", "1960-01-01"))
Figure 2: New Haven Temperatures

Tables

Use the knitr::kable() function to print tables as HTML:

knitr::kable(head(ggplot2::diamonds))
carat cut color clarity depth table price x y z
0.23 Ideal E SI2 61.5 55 326 3.95 3.98 2.43
0.21 Premium E SI1 59.8 61 326 3.89 3.84 2.31
0.23 Good E VS1 56.9 65 327 4.05 4.07 2.31
0.29 Premium I VS2 62.4 58 334 4.20 4.23 2.63
0.31 Good J SI2 63.3 58 335 4.34 4.35 2.75
0.24 Very Good J VVS2 62.8 57 336 3.94 3.96 2.48

LaTeX Math

You can also include LaTeX math:

P\left(A=2\middle|\frac{A^2}{B}>4\right)