Module 4 Lab

library(tidyverse)
library(lubridate)
library(completejourney)
transactions <- get_transactions()
dim(transactions)

[1] 1469307      11

promotions <- get_promotions()
dim(promotions)

[1] 20940529        5

# how many transaction do we have demographics on?
transactions %>%
semi_join(demographics, by = "household_id") %>%
tally()

# how many transaction do we NOT have demographics on?
transactions %>%
anti_join(demographics, by = "household_id") %>%
tally()

transactions %>%
inner_join(demographics, by = "household_id") %>%
group_by(age) %>%
summarise(total_sales = sum(sales_value)) %>%
arrange(desc(total_sales))

# Identify households with $1000 or more in total sales
hshld_1000 <- transactions %>%
group_by(household_id) %>%
summarise(total_sales = sum(sales_value, na.rm = TRUE)) %>%
filter(total_sales >= 1000)

# How many of these households do we have demographic data on?
hshld_1000 %>%
semi_join(demographics, by = "household_id") %>%
tally()

# How many do we not have demographic on?
hshld_1000 %>%
anti_join(demographics, by = "household_id") %>%
tally()

# Which income range produces the most households that spend \$1000 or more?
hshld_1000 %>%
inner_join(demographics, by = "household_id") %>%
count(income, sort = TRUE)

# join transactions and filtered promotions data
front_display_trans <- promotions %>%
filter(display_location == 1) %>%
inner_join(transactions, by = c('product_id', 'store_id', 'week'))

# total sales for all products displayed in the front of the store
front_display_trans %>%
summarize(total_sales = sum(sales_value, na.rm = TRUE))

# Identify the product displayed in the front of the store that had the largest total sales
front_display_trans %>%
group_by(product_id) %>%
summarize(total_front_display_sales = sum(sales_value, na.rm = TRUE)) %>%
arrange(desc(total_front_display_sales))

coupons %>%
filter(campaign_id == 18, coupon_upc == 10000089238) %>%
inner_join(products, by = "product_id")

#Identify all products that contain pizza in product_type description
pizza <- products %>%
  filter(str_detect(product_type, regex("pizza", ignore_case = TRUE))) %>%
  distinct(product_id, product_type)

#Which of these products produces the greatest amount of total sales
pizza_sales <- transactions %>%
  inner_join(pizza, by = "product_id") %>%
  group_by(product_id, product_type) %>%
  summarise(total_sales = sum(sales_value, na.rm = TRUE), .groups = "drop") %>%
  arrange(desc(total_sales))

pizza_sales

relevant_products <- products %>%
filter(
str_detect(product_category, regex("pizza", ignore_case = TRUE)),
str_detect(product_type, regex("snack|appetizer", ignore_case = TRUE))
)

relevant_products %>%
inner_join(transactions, by = 'product_id') %>%
group_by(product_id) %>%
summarise(total_qty = sum(quantity)) %>%
arrange(desc(total_qty))

pb <- products %>%
filter(str_detect(product_type, regex("peanut butter", ignore_case = TRUE)))
tally(pb)

pb %>%
inner_join(transactions, by = "product_id") %>%
group_by(month = month(transaction_timestamp, label = TRUE)) %>%
summarize(total_sales = sum(sales_value, na.rm = TRUE)) %>%
arrange(desc(total_sales))

coupon_redemptions %>%
  filter(campaign_id == 18, coupon_upc == 10000085475) %>%
  distinct(household_id) %>%
  tally(name = "n_households")

coupon_redemptions %>%
  filter(campaign_id == 18, coupon_upc == 10000085475) %>%
  inner_join(transactions, by = "household_id") %>%
  filter(yday(transaction_timestamp) == yday(redemption_date)) %>%
  summarise(total_sales = sum(sales_value, na.rm = TRUE))

coupon_redemptions %>%
  filter(campaign_id == 18, coupon_upc == 10000085475) %>%
  distinct(household_id, campaign_id, coupon_upc, redemption_date, .keep_all = TRUE) %>%
  inner_join(
    coupons %>% distinct(campaign_id, coupon_upc, product_id, .keep_all = TRUE),
    by = c("campaign_id", "coupon_upc"),
    relationship = "many-to-many"   # silence expected many-to-many warning (dplyr ≥ 1.1)
  ) %>%
  inner_join(products, by = "product_id") %>%
  filter(str_detect(product_category, regex("vegetables", ignore_case = TRUE))) %>%
  inner_join(transactions, by = c("household_id", "product_id")) %>%
  filter(yday(transaction_timestamp) == yday(redemption_date)) %>%  # use yday as requested
  group_by(product_type) %>%
  summarise(total_sales = sum(sales_value, na.rm = TRUE), .groups = "drop") %>%
  arrange(desc(total_sales))