South Dakota State University
Assignment 13
Ecuadorian Sales Analysis - Deep Dive
Judge Eli and Valerie Janis
STAT-442-ST1
FA2024 Semester
December 6, 2024
Abstract
This report explores sales and transaction trends across Ecuadorian supermarkets, emphasizing patterns influenced by events, holidays, and the 2016 earthquake in the Manabí region. Using sales data from 2013–2017, analyses reveal significant trends such as increased sales during shopping holidays, steady workday growth, and heightened transaction volumes following natural disasters. Key statistical tests, including the Shapiro-Wilk test and Wilcoxon rank-sum test, confirm meaningful differences in pre- and post-earthquake transactions, underscoring the critical role of supermarkets during crises. These findings guide strategies for inventory management, event-based promotions, and disaster readiness.
Dataset Overview
This dataset contains information about millions of sales sold at Favorita stores located in Ecuador. The data includes dates, store and product information, whether that item was being promoted, as well as the sales and transaction numbers.
Variables
id
date
store_nbr: Identifies the store at which the products are sold.
product_category: Identifies the type of product sold.
sales: Gives the total sales for a product category at a particular store at a given date. Fractional values are possible since products can be sold in fractional units (1.5 kg of cheese, for instance, as opposed to 1 bag of chips).
items_promoted: Gives the total number of items in a product category that were being promoted at a store on a given date.
transactions: Gives the total number of transactions at a store on a given date.
city: Store metadata for the city.
state: Store metadata for the state.
store_type: Store metadata for the type.
store_cluster: Is a grouping of similar stores.
oil_price: Daily oil price of “dcoilwtico” reflecting the cost per barrel in U.S. dollars. Ecuador is an oil-dependent country and it’s economical health is highly vulnerable to shocks in oil prices.
event_type: Categorical variable indicating the category of the event (e.g., Disaster, Holiday, Shopping Event, or Sport Event).
event_description: Detailed description of the event corresponding to its type (e.g., Navidad, Mundial de fútbol, or Carnaval).
Additional Notes
Wages in the public sector are paid every two weeks on the 15th and on the last day of the month. Supermarket sales could be affected by this.
A magnitude 7.8 earthquake struck Ecuador on April 16, 2016. People rallied in relief efforts donating water and other first need products which greatly affected supermarket sales for several weeks after the earthquake.
Dataset Structure
Data Frame Summary
dataset
Dimensions: 3000888 x 31Duplicates: 0
| Variable | Stats / Values | Freqs (% of Valid) | Graph | Missing | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| id [character] |
|
|
 |
0 (0.0%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| date [Date] |
|
1684 distinct values |  |
0 (0.0%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| store_nbr [numeric] |
|
54 distinct values |  |
0 (0.0%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| product_category [character] |
|
|
 |
0 (0.0%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| sales [numeric] |
|
379609 distinct values |  |
939130 (31.3%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Log[sales] [numeric] |
|
379609 distinct values |  |
939130 (31.3%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Range Scale[Log[sales]] [numeric] |
|
379609 distinct values |  |
939130 (31.3%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| items_promoted [numeric] |
|
361 distinct values |  |
2389559 (79.6%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Log[items_promoted] [numeric] |
|
361 distinct values |  |
2389559 (79.6%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Range Scale[Log[items_promoted]] [numeric] |
|
361 distinct values |  |
2389559 (79.6%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| transactions [numeric] |
|
4993 distinct values |  |
245784 (8.2%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Log[transactions] [numeric] |
|
4993 distinct values |  |
245784 (8.2%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Range Scale[Log[transactions]] [numeric] |
|
4993 distinct values |  |
245784 (8.2%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| city [character] |
|
|
 |
0 (0.0%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| state [character] |
|
|
 |
0 (0.0%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| store_type [character] |
|
|
 |
0 (0.0%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| store_cluster [character] |
|
|
 |
0 (0.0%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| oil_price [numeric] |
|
1352 distinct values |  |
0 (0.0%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| event_type [character] |
|
|
 |
0 (0.0%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| event_description [character] |
|
|
 |
2802030 (93.4%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| city_pop [numeric] |
|
22 distinct values |  |
0 (0.0%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Log[city_pop] [numeric] |
|
22 distinct values |  |
0 (0.0%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Range Scale[Log[city_pop]] [numeric] |
|
22 distinct values |  |
0 (0.0%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| sales_per_100k [numeric] |
|
11923 distinct values |  |
939130 (31.3%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Log[sales_per_100k] [numeric] |
|
524940 distinct values |  |
939130 (31.3%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Range Scale[Log[sales_per_100k]] [numeric] |
|
524919 distinct values |  |
939130 (31.3%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| trans_per_100k [numeric] |
|
4695 distinct values |  |
245784 (8.2%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Log[trans_per_100k] [numeric] |
|
22679 distinct values |  |
245784 (8.2%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Range Scale[Log[trans_per_100k]] [numeric] |
|
22679 distinct values |  |
245784 (8.2%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| lat [numeric] |
|
22 distinct values |  |
0 (0.0%) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||
| lng [numeric] |
|
22 distinct values |  |
0 (0.0%) |
Generated by summarytools 1.0.1 (R version 4.4.0)
2024-11-27
Exploratory Data Analysis
1) Mean Sales by Day and Month
Identifying patterns in daily and monthly sales to optimize store operations.
Interpretation
This heatmap reveals the interplay between sales, days of the week, and months of the year. Key insights include:
Thursdays consistently exhibit the lowest average sales, potentially due to mid-week consumer behavior.
December sees a marked increase in sales across all days, reflecting holiday-driven shopping spikes.
Sundays maintain relatively high sales throughout the year, likely due to relaxed schedules or family shopping habits.
These insights can guide staffing, inventory management, and promotional campaigns during peak periods.
2) Monthly Sales by Year
Yearly sales trends to evaluate growth and seasonality.
Interpretation
The bar chart dissects average monthly sales across years, highlighting:
A steady growth trend from 2013 to 2016. Note that the last four months (Sep - Dec) of 2017 are not included.
December emerges as the highest-grossing month annually, driven by holiday spending.
February sales are relatively stagnant after the holiday season, indicating potential for promotional campaigns to boost revenue.
These trends underscore the importance of seasonal marketing strategies and addressing factors contributing to periods of decline.
3) Holiday Sales Analysis
Evaluating the impact of events and types of days on monthly sales.
Interpretation
Workdays: Sales are steady but show a gradual increase over the years, reflecting economic growth or improved store performance.
Sporting Events: Spikes in June and July 2014 align with the FIFA World Cup, showing opportunities for sales during national sports events.
Shopping Events: Recurring peaks in May (Mother’s Day) and November (Black Friday/Cyber Monday) highlight critical periods for targeted promotions.
Holiday Sales: A sharp rise in January sales starts in 2015, suggesting a growing post-holiday shopping trend.
2016 Earthquake: Sales surged after the April 2016 earthquake, possibly driven by relief efforts or changes in consumer behavior.
This analysis emphasizes the need for tailored event-based promotions, particularly during months with significant shopping holidays. Plan promotions around major sports events and shopping holidays. Focus on post-holiday campaigns to capture January’s growing sales trend. Explore strategies to meet increased demand during crises like natural disasters.
4) Holiday Impact Analysis
Comparing workday and holiday sales trends across years.
Interpretation
The interactive box plot compares sales distributions between holidays and workdays, revealing:
Sales are consistently higher on holidays, validating their significance for retail.
A logarithmic transformation indicates smaller stores still experience substantial sales spikes on holidays.
This distinction can inform decisions on extended hours, special deals, or targeted advertising during holidays.
5) Store Type Performance
Analyzing sales variations across different store formats.
Interpretation
This plot highlights the diversity in sales performance by store type:
Type A stores show the highest sales averages, suggesting these locations are either larger or cater to high-demand regions.
Smaller types (e.g., Type C) indicate niche opportunities or inconsistent customer bases.
This insight can guide future investments and focus on scaling high-performing store formats.
Quantitative Analysis
6) Manabi’s Earthquake Impact Analysis
Assessing earthquake-driven changes in daily transactions.
In 2016, a magnitude 7.8 earthquake struck Ecuador on April 16, heavily affecting the Manabí region. To understand the impact on consumer behavior in this region, we compared daily transaction data 30 days before and 30 days after the earthquake (March 18–April 16 vs. April 17–May 17).
Before the earthquake, average daily transactions in Manabí were consistent, around 1,000 transactions on average per day. This reflects stable shopping patterns typical of a pre-disaster period.
Following the earthquake, transactions dipped sharply and then spiked significantly, with averages climbing to approximately 1,500 per day. Some days even saw spikes exceeding 1,500 transactions.
##
## Shapiro-Wilk normality test
##
## data: daily_avg$daily_transactions[daily_avg$period == "Before"]
## W = 0.95153, p-value = 0.1858##
## Shapiro-Wilk normality test
##
## data: daily_avg$daily_transactions[daily_avg$period == "After"]
## W = 0.91344, p-value = 0.01587##
## Wilcoxon rank sum test with continuity correction
##
## data: daily_transactions by period
## W = 74, p-value = 1.766e-08
## alternative hypothesis: true location shift is not equal to 0
Interpretation
The Shapiro-Wilk test checks if the data is normally distributed. For transactions before the earthquake, the test showed they were close to a normal distribution (p = ~ 0.2), meaning the data was fairly regular. After the earthquake, the test indicated a slight departure from normality (p = ~ 0.02), likely because of some extreme values (outliers).
A Paired Samples t-Test was not used becasue it assumes normality.
Instead the Wilcoxon Rank-Sum test, which also compares two sets of
data, showed a clear and significant increase in transactions after the
earthquake (p < 0.001). This means the difference between the two
periods wasn’t random, it was a meaningful change.
After the earthquake, transactions increased as people rushed to
supermarkets for essential supplies like water, food, and first-aid
items. Supermarkets became critical hubs for disaster response, with
spending shifting toward basic needs and emergency preparedness. This
emphasizes the importance of supermarkets in recovery efforts.
For the future, stores should anticipate demand spikes during disasters, ensure sufficient inventory, and develop crisis response plans. Offering discounts or relief packages can also strengthen community support and aid recovery efforts.
Conclusion
This analysis highlights the dynamic nature of sales and transaction patterns in Ecuadorian supermarkets, driven by holidays, sporting events, and external shocks like natural disasters. Key insights include:
Consistent workday sales growth and recurring spikes during major shopping holidays emphasize the importance of targeted marketing and inventory planning.
The 2016 Manabí earthquake triggered a significant post-event surge in transactions, underscoring the critical role of supermarkets as community support hubs during crises.
Statistical analyses validated these findings, with the Wilcoxon rank-sum test confirming a meaningful increase in post-earthquake transactions.
Moving forward, supermarkets should leverage these insights to optimize operations during high-demand periods, develop disaster response plans, and strengthen community ties through targeted relief efforts. This approach not only enhances sales performance but also reinforces the role of supermarkets in economic and social recovery.