Final Project: R Markdown

Complete Journey Analysis

1.SYNOPSIS

Report Summary and Problem Statement:

This report investigates sales trends over the past year from 2,469 households, as provided by 84.51° and stored in the completejourney package. Our analysis focuses on understanding purchasing behavior across different demographics, such as income range, age group, marital status, and household size.

The goal is to uncover how these demographic factors influence product choices, identify the departments where most purchases are made, and evaluate overall sales performance throughout the year. Insights from this analysis aim to assist the finance team in identifying areas for investment or divestment within the product mix, while also guiding the marketing team to effectively target key customer segments driving sales in specific departments.

Please use the tabs to navigate our analysis, which includes details on the setup and seven visuals illustrating trends across demographics. The summary tab outlines the key takeaways and implications of our findings.

© 2024 Group 11. All rights reserved.

2.PACKAGES REQUIRED

The following packages were utilized in our analysis, along with brief descriptions:

• completejourney: Contains retail shopping transaction data for 2,469 households over a one-year period.
• tidyverse: A collection of packages designed for data science tasks.
• ggplot2: A system for creating graphics in a declarative manner.
• dplyr: A package used for efficient data manipulation.
• lubridate: Provides tools for parsing and manipulating dates more easily.
• DT: Allows for exporting data into HTML format for easy output.
• stringr: A set of functions for working with strings.
• wordcloud: A package used for creating word cloud visualizations.

library(tidyverse)
library(ggplot2)
library(dplyr)
library(lubridate)
library(completejourney)
library(DT)
library(stringr)
library(wordcloud)

© 2024 Group 11. All rights reserved.

3.DATA PREPARATION

#Importing data sets needed for analysis
transactions <- get_transactions()
products <- products
demographics <- demographics

#Joining, mutating and cleaning data sets
tran_prod_dem <- transactions %>% 
  inner_join(products) %>% 
  inner_join(demographics) %>% 
  mutate(Transaction_date = date(transaction_timestamp))
glimpse(tran_prod_dem)

## Rows: 825,622
## Columns: 25
## $ household_id          <chr> "900", "900", "1228", "906", "906", "906", "906"…
## $ store_id              <chr> "330", "330", "406", "319", "319", "319", "319",…
## $ basket_id             <chr> "31198570044", "31198570047", "31198655051", "31…
## $ product_id            <chr> "1095275", "9878513", "1041453", "1020156", "105…
## $ quantity              <dbl> 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
## $ sales_value           <dbl> 0.50, 0.99, 1.43, 1.50, 2.78, 5.49, 1.50, 1.00, …
## $ retail_disc           <dbl> 0.00, 0.10, 0.15, 0.29, 0.80, 0.50, 0.29, 0.29, …
## $ coupon_disc           <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
## $ coupon_match_disc     <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
## $ week                  <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
## $ transaction_timestamp <dttm> 2017-01-01 06:53:26, 2017-01-01 07:10:28, 2017-…
## $ manufacturer_id       <chr> "2", "69", "69", "2142", "2326", "608", "2326", …
## $ department            <chr> "PASTRY", "GROCERY", "GROCERY", "GROCERY", "GROC…
## $ brand                 <fct> National, Private, Private, National, National, …
## $ product_category      <chr> "ROLLS", "FACIAL TISS/DNR NAPKIN", "BAG SNACKS",…
## $ product_type          <chr> "ROLLS: BAGELS", "FACIAL TISSUE & PAPER HANDKE",…
## $ package_size          <chr> "4 OZ", "85 CT", "11.5 OZ", "17.1 OZ", "5.0 OZ",…
## $ age                   <ord> 35-44, 35-44, 45-54, 55-64, 55-64, 55-64, 55-64,…
## $ income                <ord> 35-49K, 35-49K, 100-124K, Under 15K, Under 15K, …
## $ home_ownership        <ord> Homeowner, Homeowner, NA, Homeowner, Homeowner, …
## $ marital_status        <ord> Married, Married, Unmarried, Married, Married, M…
## $ household_size        <ord> 2, 2, 1, 2, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
## $ household_comp        <ord> 2 Adults No Kids, 2 Adults No Kids, 1 Adult No K…
## $ kids_count            <ord> 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
## $ Transaction_date      <date> 2017-01-01, 2017-01-01, 2017-01-01, 2017-01-01,…

© 2024 Group 11. All rights reserved.

4. EXPLORATORY DATA ANALYSIS

A. Word Cloud of Departments

# Frequency of transactions by department
cloud_topn <- tran_prod_dem %>% 
  group_by(department) %>%
  summarise(no_rows = NROW(department)) %>% 
  mutate(freq = (no_rows/sum(no_rows))*100) %>% 
  arrange(desc(no_rows)) 

cloud_topn

# Building word cloud
# Color palette: vibrant colors from RColorBrewer's "Set3" palette
wordcloud(words = cloud_topn$department, freq = cloud_topn$no_rows, min.freq = 2,
          max.words=200, random.order=FALSE, rot.per=0.35,scale=c(4,.5), 
          colors=brewer.pal(8, "Dark2"))  

#making a data frame of words and its frequency
cloud_grocery <- tran_prod_dem %>% 
  filter(department == "GROCERY") %>% 
  group_by(product_category) %>%
  summarise(no_rows = length(department)) %>% 
  mutate(freq = (no_rows/sum(no_rows))*100) %>% 
  arrange(desc(no_rows)) %>% 
  filter(freq >= 1)

cloud_grocery

#building the word cloud
wordcloud(words = cloud_grocery$product_category, freq = cloud_grocery$no_rows, min.freq = 1,
          max.words=200, random.order=FALSE, rot.per=0.35,scale=c(1,.5), 
          colors=brewer.pal(8, "Dark2"))

Summary/Explanation:

When evaluating where investments should be made in marketing and promotions, we analyzed the distribution of transaction frequencies across different departments. The Grocery department stood out, accounting for 6% of all transactions, while all other departments each had a relative frequency of 1.07% or less.

Building on this analysis, we examined which products were most frequently purchased within the Grocery department. Notably, soft drinks led with 6.67% of transactions, followed by fluid milk products at 5.24%, and baked bread, buns, and rolls at 5.06%. These findings provide insights into which products drive the most activity in the Grocery department, guiding potential promotional efforts.

© 2024 Group 11. All rights reserved.

B. Total Sales by Product type and Household Size

tran_prod_dem %>%
  group_by(household_size, product_type) %>%
  summarize(total = sum(sales_value, na.rm = TRUE)) %>% 
  arrange(desc(total)) %>%
  slice(2:6) %>%
  ggplot() +
  geom_bar(aes(x = product_type, y = total, fill = household_size), stat = 'identity') +
  scale_fill_brewer(palette = "Spectral") +  
  theme_minimal() +
  theme(axis.text.x = element_text(size = 10, angle = 45, vjust = 1, hjust = 1),
        plot.title = element_text(size = 16, face = "bold", color = "#2e86c1")) +
  labs(title = "Sales of Top Product Types by Household Size",
       subtitle = "Analysis of sales data from 2,469 households",
       caption = "Data source: 84.51° completejourney package",
       x = "Product Type",
       y = "Total Sales ($)",
       fill = "Household Size") +
  scale_y_continuous(labels = scales::dollar)

Summary/Explanation:

This graph illustrates total sales categorized by product type and household size. It shows that fluid white milk is the top-selling product across all household sizes, while shredded cheese ranks as the product with the lowest sales.

Additionally, households with five or more members contribute the least to total sales overall. In contrast, households with one or two members have a greater impact on the sales value of these product types.

© 2024 Group 11. All rights reserved.

C. Total Sales Throughout 2017 by Age Group

tran_prod_dem %>%
  inner_join(products, by = "product_id") %>%
  mutate(month = month(transaction_timestamp, label = TRUE)) %>%
  group_by(Transaction_date, age) %>%
  summarize(total_sales = sum(sales_value, na.rm = FALSE)) %>%
  ggplot(aes(x = Transaction_date, y = total_sales, color = age)) +
  geom_line(size = 1.2) +  
  scale_color_brewer(palette = "Dark2") + 
  theme_minimal() +
  labs(title = "Total Sales by Age Group",
       subtitle = "Comparing total sales over the span of 2017",
       x = "Month",
       y = "Total Sales ($)",
       color = "Age Group") +
  scale_y_continuous(labels = scales::dollar)

Summary/Explanation:

This graph offers insights into the contributions of different age groups to total sales throughout 2017, highlighting their purchasing trends. Notably, there is a significant spike in sales among the 45-54 age group around the holiday season, indicating that this demographic is a key driver of total sales for the year.

In contrast, the age groups of 19-24, 55-64, and 65+ contribute the least to total sales, exhibiting a relatively steady mean and variance throughout the year. This suggests that these age groups may have less purchasing activity compared to others, presenting an opportunity for targeted marketing strategies to engage them more effectively.

© 2024 Group 11. All rights reserved.

D. Total Sales by Income Range and Kid Count

demographics %>%
  group_by(kids_count, income) %>%
  inner_join(transactions, by = "household_id") %>%
  summarize(total_sales = sum(sales_value, na.rm = FALSE)) %>%
  ggplot(aes(x = kids_count, y = total_sales)) +
  geom_point(aes(color = income)) +
  scale_y_continuous(name = "Total Sales ($)", labels = scales::dollar) +
  labs(title = "Total Sales of All Products per Income range per Kids count",
       subtitle = "Comparing the total sales of all products amongst income ranges and kids count",
       x = "Kids Count",
       y = "Total Sales",
       color = "Income")

Summary/Explanation:

The point graph above illustrates how income range and the number of children can influence total sales. Contrary to expectations, families in the highest income bracket of $250k contribute less to total sales compared to several other income ranges. As the graph moves to the right, indicating higher income levels, there is a noticeable decline in maximum total sales. This trend supports the notion that raising children incurs significant expenses, leading families with children to allocate their resources more diversely than those with no kids.

© 2024 Group 11. All rights reserved.

E. Top three Products

tran_prod_dem %>%
  filter(str_detect(product_type, regex("FLUID MILK WHITE ONLY|SOFT DRINKS 12/18&15PK CAN CAR|GASOLINE-REG UNLEADED", ignore_case = TRUE))) %>%
  filter(kids_count >= 1) %>%
  mutate(month = month(transaction_timestamp, label = TRUE)) %>%
  group_by(product_type, month) %>%
  ggplot(aes(x = product_type, y = sales_value, fill = month)) +
  geom_col(position = 'dodge') +
  coord_cartesian(ylim = c(0, 250)) +
  scale_y_continuous(name = "Sales Value ($)", labels = scales::dollar) +
  labs(title = "Comparing the Sales Value of the Top 3 Products of Families with kids", 
       subtitle = "Using the 2017 data provided this graph was created",
       x = "Top 3 Products",
       y = "Sales Value",
       fill = "Months") +
  theme(axis.text.x = element_text(angle = 30, hjust = 1, size = 10, margin = margin(t = 10)))  

Summary/Explanation:

The column graph indicates that demand for products fluctuates throughout the year, with no consistent trend across all items. A notable outlier is the spike in gasoline sales during the month of July. In contrast, milk demonstrates a steady demand year-round, characterized by minor peaks and valleys, reflecting its continuous appeal among consumers.

© 2024 Group 11. All rights reserved.

F. Sales value per Department

tran_prod_dem %>% 
  filter(marital_status != "NA") %>% 
  ggplot(aes(x = sales_value, y = department)) + geom_line(aes(color = marital_status)) +
  labs(title = "Sales Value by Department and Marital Status",
       subtitle = "Brands Labeled",
       x = "Sales Value",
       y = "Department",
       color = "Marital Status") +
    scale_x_continuous(name = "Total Sales ($)", labels = scales::dollar) 

Summary/Explanation:

This graph illustrates the Sales Value per Department segmented by marital status (married vs. unmarried). The X-axis represents sales value, while the Y-axis lists the departments, with purple indicating sales from married couples and yellow representing unmarried couples. While most departments show relatively similar sales values, two stand out significantly: the Drug department and the Miscellaneous department.

In our analysis of sales based on demographics, it becomes evident that the majority of sales are attributed to unmarried couples. This suggests that unmarried individuals tend to spend more than their married counterparts, potentially due to having fewer financial responsibilities. Without the burden of expenses related to children or a spouse, unmarried couples may have greater disposable income to allocate toward their purchases.

© 2024 Group 11. All rights reserved.

5.SUMMARY

Problem:

The goal of analyzing the complete journey data was to evaluate how top sales values and specific demographics could be used to identify purchasing behaviors and trends, helping to determine the best products to invest in and the optimal timing for those investments.

Addressing the Problem:

We tackled this issue by leveraging the transaction, demographic, and product data to create visual representations that provided the necessary insights. We generated graphs comparing total family sales based on income ranges and the number of children, identifying which products had the highest sales values for families with five or more members, and displaying the sales values of the top three products each month. Additionally, we created graphs showing the total monthly sales for 2017, the sales values by department, and whether the brand was national or private. To further visualize the data, we used word clouds to highlight the frequency of top departments and transactions. By analyzing these graphs, we gained valuable insights into the relationships between demographics and purchasing behaviors.

Insights:

We discovered that the highest-income families were not the ones contributing the most to total sales, which was unexpected. Through our analysis of sales value by department, we identified several departments with relatively low sales, and noted that most departments had a significant number of customers purchasing private brands. One standout finding was that “Fluid White Milk” emerged as a top-selling product for families with five or more members, ranking among the highest-selling products overall and maintaining consistent demand throughout the year. Another insight was the steady rate of total sales throughout 2017, including the summer months, followed by a sharp decline to below $4,000 between November and December, before spiking to nearly $16,000 at the year’s end. Our Word Cloud analysis also revealed that the Grocery department had the highest number of transactions, with other departments trailing by about 5%, making it clear that Grocery would be a strong area for future investment.

Implications:

From these insights, we concluded that while higher-income families might be expected to account for a larger portion of total sales, the number of children or overall family size plays a more significant role. Based on our findings, it would be beneficial to invest in departments with higher sales values, as indicated in the “Sales Value per Department,” “Top 3 Products,” and “Sales for Households over 5” analyses. Departments such as Grocery, Drug GM, Miscellaneous, and Fuel had higher sales value and a wider variety of products purchased by a broader range of customers.

Additionally, the “Total Sales of All Products per Month” graph suggests that the optimal time to invest is toward the end of the year when sales spike, likely due to holiday shopping. This makes the holiday season a key period for maximizing returns on investment.

We recommend leveraging the strong sales performance of top products and departments to create targeted marketing strategies aimed at larger households, families with more children, and specific income brackets. Promotions that focus on these demographics could drive further engagement. Furthermore, it may be worthwhile to address departments with lower sales by developing strategies to attract these demographics, thereby increasing overall sales in those areas.

Limitations of our analysis:

For future analysis, it would be valuable to explore which departments have the highest purchase rates across different demographic segments. Additionally, gathering more detailed statistics on age demographics could provide a clearer understanding of the age groups involved, enabling more effective and tailored marketing strategies. Our key area for improvement would be to delve deeper into specific demographic factors. While we did examine a few demographic relationships with sales values, a more comprehensive analysis of other demographic attributes would offer a more nuanced understanding of customer behavior and purchasing trends.

© 2024 Group 11. All rights reserved.