---
title: "Quantium Virtual Internship - Retail Strategy and Analytics - Task 2"
author: "Michal Kuderski"
date: "2025-05-06"
output: 
  pdf_document:
    df_print: default
    highlight: tango
---

```{r setup, include = FALSE}
knitr::opts_chunk$set(echo = TRUE)
```

# Control Store Analysis for Retail Trial

This analysis aims to identify suitable control stores for trial stores 77, 86, and 88, and to evaluate the effectiveness of the trial that ran from February 2019 to April 2019.

## Load Required Libraries and Data

```{r load_libraries}
library(data.table)
library(ggplot2)
library(tidyr)
library(knitr)

# Set themes for plots
theme_set(theme_bw())
theme_update(plot.title = element_text(hjust = 0.5))

# Note: Replace with the path to your data file
filePath <- "/Users/michalkuderski/Quantium_VI/"
data <- fread(paste0(filePath, "QVI_data.csv"))
```

## Prepare the Data and Calculate Key Metrics

We'll calculate several metrics at a monthly level for each store:
- Total sales revenue
- Number of unique customers
- Transactions per customer
- Chips per transaction
- Average price per unit

```{r calculate_metrics}
# Create a year-month field
data[, YEARMONTH := as.numeric(format(DATE, "%Y%m"))]

# Calculate metrics over time for each store
measureOverTime <- data[, .(
  totSales = sum(TOT_SALES),
  nCustomers = uniqueN(LYLTY_CARD_NBR),
  nTxnPerCust = uniqueN(TXN_ID) / uniqueN(LYLTY_CARD_NBR),
  nChipsPerTxn = sum(PROD_QTY) / uniqueN(TXN_ID),
  avgPricePerUnit = sum(TOT_SALES) / sum(PROD_QTY)
), by = .(STORE_NBR, YEARMONTH)][order(STORE_NBR, YEARMONTH)]

# Filter to stores with full observation periods (all 12 months)
storesWithFullObs <- unique(measureOverTime[, .N, STORE_NBR][N == 12, STORE_NBR])
preTrialMeasures <- measureOverTime[YEARMONTH < 201902 & STORE_NBR %in% storesWithFullObs, ]
```

## Define Functions for Control Store Selection

We'll create two functions:
1. A function to calculate correlation between a trial store and potential control stores
2. A function to calculate magnitude distance between a trial store and potential control stores

```{r define_functions}
# Function to calculate correlation between stores
calculateCorrelation <- function(inputTable, metricCol, storeComparison) {
  calcCorrTable = data.table(Store1 = numeric(), Store2 = numeric(), corr_measure = numeric())
  storeNumbers <- unique(inputTable[STORE_NBR != storeComparison, STORE_NBR])
  
  for (i in storeNumbers) {
    store1Data <- inputTable[STORE_NBR == storeComparison, eval(metricCol)]
    store2Data <- inputTable[STORE_NBR == i, eval(metricCol)]
    
    correlation <- cor(store1Data, store2Data)
    
    calculatedMeasure = data.table(
      "Store1" = storeComparison,
      "Store2" = i,
      "corr_measure" = correlation
    )
    
    calcCorrTable <- rbind(calcCorrTable, calculatedMeasure)
  }
  return(calcCorrTable)
}

# Function to calculate magnitude distance 
calculateMagnitudeDistance <- function(inputTable, metricCol, storeComparison) {
  calcDistTable = data.table(Store1 = numeric(), Store2 = numeric(), YEARMONTH = numeric(), measure = numeric())
  storeNumbers <- unique(inputTable[STORE_NBR != storeComparison, STORE_NBR])
  
  for (i in storeNumbers) {
    calculatedMeasure = data.table(
      "Store1" = storeComparison,
      "Store2" = i,
      "YEARMONTH" = inputTable[STORE_NBR == storeComparison, YEARMONTH],
      "measure" = abs(
        inputTable[STORE_NBR == storeComparison, eval(metricCol)] - 
        inputTable[STORE_NBR == i, eval(metricCol)]
      )
    )
    calcDistTable <- rbind(calcDistTable, calculatedMeasure)
  }
  
  # Standardize the magnitude distance (0 to 1 scale)
  minMaxDist <- calcDistTable[, .(minDist = min(measure), maxDist = max(measure)), by = c("Store1", "YEARMONTH")]
  distTable <- merge(calcDistTable, minMaxDist, by = c("Store1", "YEARMONTH"))
  distTable[, magnitudeMeasure := 1 - (measure - minDist)/(maxDist - minDist)]
  
  # Average across all months
  finalDistTable <- distTable[, .(mag_measure = mean(magnitudeMeasure)), by = .(Store1, Store2)]
  
  return(finalDistTable)
}
```

## Find Control Store for Trial Store 77

We'll use both correlation and magnitude to find the most similar store to trial store 77.

```{r store77_control}
trial_store <- 77

# Calculate correlations for sales and customer metrics
corr_nSales <- calculateCorrelation(preTrialMeasures, quote(totSales), trial_store)
corr_nCustomers <- calculateCorrelation(preTrialMeasures, quote(nCustomers), trial_store)

# Calculate magnitude distances for sales and customer metrics
magnitude_nSales <- calculateMagnitudeDistance(preTrialMeasures, quote(totSales), trial_store)
magnitude_nCustomers <- calculateMagnitudeDistance(preTrialMeasures, quote(nCustomers), trial_store)

# Combine correlation and magnitude for sales (with equal weighting)
corr_weight <- 0.5
score_nSales <- merge(corr_nSales, magnitude_nSales, by = c("Store1", "Store2"))[, 
                     scoreNSales := corr_weight * corr_measure + (1 - corr_weight) * mag_measure]

# Combine correlation and magnitude for customer count
score_nCustomers <- merge(corr_nCustomers, magnitude_nCustomers, by = c("Store1", "Store2"))[, 
                         scoreNCust := corr_weight * corr_measure + (1 - corr_weight) * mag_measure]

# Create a final score (equal weighting between sales and customer metrics)
score_Control <- merge(score_nSales, score_nCustomers, by = c("Store1", "Store2"))
score_Control[, finalControlScore := scoreNSales * 0.5 + scoreNCust * 0.5]

# Find the control store with highest score
control_store <- score_Control[Store1 == trial_store, ][order(-finalControlScore)][2, Store2]
print(paste("Control store for trial store 77:", control_store))
```

## Validate Control Store Selection Visually

Let's check if the sales and customer patterns are similar between trial and control stores before the trial.

```{r validate_visually77}
# Check sales trends
measureOverTimeSales <- measureOverTime
pastSales <- measureOverTimeSales[, Store_type := ifelse(STORE_NBR == trial_store, "Trial",
                                             ifelse(STORE_NBR == control_store, "Control", "Other stores"))
                     ][, totSales := mean(totSales), by = c("YEARMONTH", "Store_type")
                     ][, TransactionMonth := as.Date(paste(YEARMONTH %/% 100, YEARMONTH %% 100, 1, sep = "-"), "%Y-%m-%d")
                     ][YEARMONTH < 201903, ]

# Plot sales trend
ggplot(pastSales, aes(TransactionMonth, totSales, color = Store_type)) +
  geom_line() +
  labs(x = "Month of operation", y = "Total sales", title = "Total sales by month - Trial vs Control Store")

# Check customer trends
measureOverTimeCusts <- measureOverTime
pastCustomers <- measureOverTimeCusts[, Store_type := ifelse(STORE_NBR == trial_store, "Trial",
                                               ifelse(STORE_NBR == control_store, "Control", "Other stores"))
                       ][, nCusts := mean(nCustomers), by = c("YEARMONTH", "Store_type")
                       ][, TransactionMonth := as.Date(paste(YEARMONTH %/% 100, YEARMONTH %% 100, 1, sep = "-"), "%Y-%m-%d")
                       ][YEARMONTH < 201903, ]

# Plot customer trend
ggplot(pastCustomers, aes(TransactionMonth, nCusts, color = Store_type)) +
  geom_line() +
  labs(x = "Month of operation", y = "Number of customers", title = "Number of customers by month - Trial vs Control Store")
```

## Assess Trial Impact for Store 77

Now we'll scale the control store's data to be comparable to the trial store and evaluate the trial's impact.

```{r trial_assessment77}
# Scale control store sales to match pre-trial sales level of trial store
scalingFactorForControlSales <- preTrialMeasures[STORE_NBR == trial_store & YEARMONTH < 201902, sum(totSales)] / 
                               preTrialMeasures[STORE_NBR == control_store & YEARMONTH < 201902, sum(totSales)]

# Apply scaling to control store sales
measureOverTimeSales <- measureOverTime
scaledControlSales <- measureOverTimeSales[STORE_NBR == control_store, ][ , 
                                          controlSales := totSales * scalingFactorForControlSales]

# Calculate percentage difference between trial and scaled control
percentageDiff <- merge(
  scaledControlSales[, c("YEARMONTH", "controlSales")],
  measureOverTime[STORE_NBR == trial_store, c("totSales", "YEARMONTH")],
  by = "YEARMONTH"
)[, percentageDiff := abs(controlSales - totSales) / controlSales]

# Calculate standard deviation in pre-trial period
stdDev <- sd(percentageDiff[YEARMONTH < 201902, percentageDiff])
degreesOfFreedom <- 7  # 8 months in pre-trial period - 1

# Create confidence interval visualization
measureOverTimeSales <- measureOverTime
pastSales <- measureOverTimeSales[, Store_type := ifelse(STORE_NBR == trial_store, "Trial",
                                             ifelse(STORE_NBR == control_store, "Control", "Other stores"))
                     ][, totSales := mean(totSales), by = c("YEARMONTH", "Store_type")
                     ][, TransactionMonth := as.Date(paste(YEARMONTH %/% 100, YEARMONTH %% 100, 1, sep = "-"), "%Y-%m-%d")
                     ][Store_type %in% c("Trial", "Control"), ]

# Calculate 95% confidence intervals
pastSales_Controls95 <- pastSales[Store_type == "Control",
                                 ][, totSales := totSales * (1 + stdDev * 2)
                                 ][, Store_type := "Control 95th % confidence interval"]

pastSales_Controls5 <- pastSales[Store_type == "Control",
                                ][, totSales := totSales * (1 - stdDev * 2)
                                ][, Store_type := "Control 5th % confidence interval"]

# Combine datasets for visualization
trialAssessment <- rbind(pastSales, pastSales_Controls95, pastSales_Controls5)

# Plot with confidence intervals and trial period highlighted
ggplot(trialAssessment, aes(TransactionMonth, totSales, color = Store_type)) +
  geom_rect(data = trialAssessment[YEARMONTH < 201905 & YEARMONTH > 201901,],
            aes(xmin = min(TransactionMonth), xmax = max(TransactionMonth), 
                ymin = 0, ymax = Inf, color = NULL), 
            fill = "light grey", alpha = 0.2, show.legend = FALSE) +
  geom_line() +
  labs(x = "Month of operation", y = "Total sales", 
       title = "Total sales by month with 95% confidence interval")

# Repeat for customer counts
scalingFactorForControlCust <- preTrialMeasures[STORE_NBR == trial_store & YEARMONTH < 201902, sum(nCustomers)] / 
                              preTrialMeasures[STORE_NBR == control_store & YEARMONTH < 201902, sum(nCustomers)]

measureOverTimeCusts <- measureOverTime
scaledControlCustomers <- measureOverTimeCusts[STORE_NBR == control_store, ][ , 
                                              controlCustomers := nCustomers * scalingFactorForControlCust
                                              ][, Store_type := ifelse(STORE_NBR == trial_store, "Trial",
                                                           ifelse(STORE_NBR == control_store, "Control", "Other stores"))]

percentageDiff <- merge(
  scaledControlCustomers[, c("YEARMONTH", "controlCustomers")],
  measureOverTime[STORE_NBR == trial_store, c("nCustomers", "YEARMONTH")],
  by = "YEARMONTH"
)[, percentageDiff := abs(controlCustomers - nCustomers) / controlCustomers]

stdDev <- sd(percentageDiff[YEARMONTH < 201902, percentageDiff])

pastCustomers <- measureOverTimeCusts[, nCusts := mean(nCustomers), by = c("YEARMONTH", "Store_type")
                       ][Store_type %in% c("Trial", "Control"), ]

pastCustomers_Controls95 <- pastCustomers[Store_type == "Control",
                                         ][, nCusts := nCusts * (1 + stdDev * 2)
                                         ][, Store_type := "Control 95th % confidence interval"]

pastCustomers_Controls5 <- pastCustomers[Store_type == "Control",
                                        ][, nCusts := nCusts * (1 - stdDev * 2)
                                        ][, Store_type := "Control 5th % confidence interval"]

trialAssessment <- rbind(pastCustomers, pastCustomers_Controls95, pastCustomers_Controls5)

ggplot(trialAssessment, aes(TransactionMonth, nCusts, color = Store_type)) +
  geom_rect(data = trialAssessment[YEARMONTH < 201905 & YEARMONTH > 201901,],
            aes(xmin = min(TransactionMonth), xmax = max(TransactionMonth), 
                ymin = 0, ymax = Inf, color = NULL), 
            fill = "light grey", alpha = 0.2, show.legend = FALSE) +
  geom_line() +
  labs(x = "Month of operation", y = "Number of customers", 
       title = "Number of customers by month with 95% confidence interval")
```

## Find Control Store for Trial Store 86

Repeating the process for trial store 86.

```{r store86_control}
trial_store <- 86

# Calculate correlations for sales and customer metrics
corr_nSales <- calculateCorrelation(preTrialMeasures, quote(totSales), trial_store)
corr_nCustomers <- calculateCorrelation(preTrialMeasures, quote(nCustomers), trial_store)

# Calculate magnitude distances for sales and customer metrics
magnitude_nSales <- calculateMagnitudeDistance(preTrialMeasures, quote(totSales), trial_store)
magnitude_nCustomers <- calculateMagnitudeDistance(preTrialMeasures, quote(nCustomers), trial_store)

# Combine correlation and magnitude for sales (with equal weighting)
corr_weight <- 0.5
score_nSales <- merge(corr_nSales, magnitude_nSales, by = c("Store1", "Store2"))[, 
                     scoreNSales := corr_weight * corr_measure + (1 - corr_weight) * mag_measure]

# Combine correlation and magnitude for customer count
score_nCustomers <- merge(corr_nCustomers, magnitude_nCustomers, by = c("Store1", "Store2"))[, 
                         scoreNCust := corr_weight * corr_measure + (1 - corr_weight) * mag_measure]

# Create a final score (equal weighting between sales and customer metrics)
score_Control <- merge(score_nSales, score_nCustomers, by = c("Store1", "Store2"))
score_Control[, finalControlScore := scoreNSales * 0.5 + scoreNCust * 0.5]

# Find the control store with highest score
control_store <- score_Control[Store1 == trial_store, ][order(-finalControlScore)][2, Store2]
print(paste("Control store for trial store 86:", control_store))

# Validate visually
measureOverTimeSales <- measureOverTime
pastSales <- measureOverTimeSales[, Store_type := ifelse(STORE_NBR == trial_store, "Trial",
                                             ifelse(STORE_NBR == control_store, "Control", "Other stores"))
                     ][, totSales := mean(totSales), by = c("YEARMONTH", "Store_type")
                     ][, TransactionMonth := as.Date(paste(YEARMONTH %/% 100, YEARMONTH %% 100, 1, sep = "-"), "%Y-%m-%d")
                     ][YEARMONTH < 201903, ]

ggplot(pastSales, aes(TransactionMonth, totSales, color = Store_type)) +
  geom_line() +
  labs(x = "Month of operation", y = "Total sales", title = "Total sales by month - Trial vs Control Store (86)")

# Assess trial impact
scalingFactorForControlSales <- preTrialMeasures[STORE_NBR == trial_store & YEARMONTH < 201902, sum(totSales)] / 
                               preTrialMeasures[STORE_NBR == control_store & YEARMONTH < 201902, sum(totSales)]

measureOverTimeSales <- measureOverTime
scaledControlSales <- measureOverTimeSales[STORE_NBR == control_store, ][ , 
                                          controlSales := totSales * scalingFactorForControlSales]

percentageDiff <- merge(
  scaledControlSales[, c("YEARMONTH", "controlSales")],
  measureOverTime[STORE_NBR == trial_store, c("totSales", "YEARMONTH")],
  by = "YEARMONTH"
)[, percentageDiff := abs(controlSales - totSales) / controlSales]

stdDev <- sd(percentageDiff[YEARMONTH < 201902, percentageDiff])

measureOverTimeSales <- measureOverTime
pastSales <- measureOverTimeSales[, Store_type := ifelse(STORE_NBR == trial_store, "Trial",
                                             ifelse(STORE_NBR == control_store, "Control", "Other stores"))
                     ][, totSales := mean(totSales), by = c("YEARMONTH", "Store_type")
                     ][, TransactionMonth := as.Date(paste(YEARMONTH %/% 100, YEARMONTH %% 100, 1, sep = "-"), "%Y-%m-%d")
                     ][Store_type %in% c("Trial", "Control"), ]

pastSales_Controls95 <- pastSales[Store_type == "Control",
                                 ][, totSales := totSales * (1 + stdDev * 2)
                                 ][, Store_type := "Control 95th % confidence interval"]

pastSales_Controls5 <- pastSales[Store_type == "Control",
                                ][, totSales := totSales * (1 - stdDev * 2)
                                ][, Store_type := "Control 5th % confidence interval"]

trialAssessment <- rbind(pastSales, pastSales_Controls95, pastSales_Controls5)

ggplot(trialAssessment, aes(TransactionMonth, totSales, color = Store_type)) +
  geom_rect(data = trialAssessment[YEARMONTH < 201905 & YEARMONTH > 201901,],
            aes(xmin = min(TransactionMonth), xmax = max(TransactionMonth), 
                ymin = 0, ymax = Inf, color = NULL), 
            fill = "light grey", alpha = 0.2, show.legend = FALSE) +
  geom_line() +
  labs(x = "Month of operation", y = "Total sales", 
       title = "Trial store 86 - Total sales with 95% confidence interval")
```

## Find Control Store for Trial Store 88

Repeating the process for trial store 88.

```{r store88_control}
trial_store <- 88

# Calculate correlations for sales and customer metrics
corr_nSales <- calculateCorrelation(preTrialMeasures, quote(totSales), trial_store)
corr_nCustomers <- calculateCorrelation(preTrialMeasures, quote(nCustomers), trial_store)

# Calculate magnitude distances for sales and customer metrics
magnitude_nSales <- calculateMagnitudeDistance(preTrialMeasures, quote(totSales), trial_store)
magnitude_nCustomers <- calculateMagnitudeDistance(preTrialMeasures, quote(nCustomers), trial_store)

# Combine correlation and magnitude for sales (with equal weighting)
corr_weight <- 0.5
score_nSales <- merge(corr_nSales, magnitude_nSales, by = c("Store1", "Store2"))[, 
                     scoreNSales := corr_weight * corr_measure + (1 - corr_weight) * mag_measure]

# Combine correlation and magnitude for customer count
score_nCustomers <- merge(corr_nCustomers, magnitude_nCustomers, by = c("Store1", "Store2"))[, 
                         scoreNCust := corr_weight * corr_measure + (1 - corr_weight) * mag_measure]

# Create a final score (equal weighting between sales and customer metrics)
score_Control <- merge(score_nSales, score_nCustomers, by = c("Store1", "Store2"))
score_Control[, finalControlScore := scoreNSales * 0.5 + scoreNCust * 0.5]

# Find the control store with highest score
control_store <- score_Control[Store1 == trial_store, ][order(-finalControlScore)][2, Store2]
print(paste("Control store for trial store 88:", control_store))

# Validate visually
measureOverTimeSales <- measureOverTime
pastSales <- measureOverTimeSales[, Store_type := ifelse(STORE_NBR == trial_store, "Trial",
                                             ifelse(STORE_NBR == control_store, "Control", "Other stores"))
                     ][, totSales := mean(totSales), by = c("YEARMONTH", "Store_type")
                     ][, TransactionMonth := as.Date(paste(YEARMONTH %/% 100, YEARMONTH %% 100, 1, sep = "-"), "%Y-%m-%d")
                     ][YEARMONTH < 201903, ]

ggplot(pastSales, aes(TransactionMonth, totSales, color = Store_type)) +
  geom_line() +
  labs(x = "Month of operation", y = "Total sales", title = "Total sales by month - Trial vs Control Store (88)")

# Assess trial impact
scalingFactorForControlSales <- preTrialMeasures[STORE_NBR == trial_store & YEARMONTH < 201902, sum(totSales)] / 
                               preTrialMeasures[STORE_NBR == control_store & YEARMONTH < 201902, sum(totSales)]

measureOverTimeSales <- measureOverTime
scaledControlSales <- measureOverTimeSales[STORE_NBR == control_store, ][ , 
                                          controlSales := totSales * scalingFactorForControlSales]

percentageDiff <- merge(
  scaledControlSales[, c("YEARMONTH", "controlSales")],
  measureOverTime[STORE_NBR == trial_store, c("totSales", "YEARMONTH")],
  by = "YEARMONTH"
)[, percentageDiff := abs(controlSales - totSales) / controlSales]

stdDev <- sd(percentageDiff[YEARMONTH < 201902, percentageDiff])

measureOverTimeSales <- measureOverTime
pastSales <- measureOverTimeSales[, Store_type := ifelse(STORE_NBR == trial_store, "Trial",
                                             ifelse(STORE_NBR == control_store, "Control", "Other stores"))
                     ][, totSales := mean(totSales), by = c("YEARMONTH", "Store_type")
                     ][, TransactionMonth := as.Date(paste(YEARMONTH %/% 100, YEARMONTH %% 100, 1, sep = "-"), "%Y-%m-%d")
                     ][Store_type %in% c("Trial", "Control"), ]

pastSales_Controls95 <- pastSales[Store_type == "Control",
                                 ][, totSales := totSales * (1 + stdDev * 2)
                                 ][, Store_type := "Control 95th % confidence interval"]

pastSales_Controls5 <- pastSales[Store_type == "Control",
                                ][, totSales := totSales * (1 - stdDev * 2)
                                ][, Store_type := "Control 5th % confidence interval"]

trialAssessment <- rbind(pastSales, pastSales_Controls95, pastSales_Controls5)

ggplot(trialAssessment, aes(TransactionMonth, totSales, color = Store_type)) +
  geom_rect(data = trialAssessment[YEARMONTH < 201905 & YEARMONTH > 201901,],
            aes(xmin = min(TransactionMonth), xmax = max(TransactionMonth), 
                ymin = 0, ymax = Inf, color = NULL), 
            fill = "light grey", alpha = 0.2, show.legend = FALSE) +
  geom_line() +
  labs(x = "Month of operation", y = "Total sales", 
       title = "Trial store 88 - Total sales with 95% confidence interval")
```

## Conclusion

```{r conclusion}
# Summary of findings
summary_table <- data.table(
  Trial_Store = c(77, 86, 88),
  Control_Store = c(control_store_77 = score_Control[Store1 == 77, ][order(-finalControlScore)][2, Store2],
                    control_store_86 = score_Control[Store1 == 86, ][order(-finalControlScore)][2, Store2],
                    control_store_88 = score_Control[Store1 == 88, ][order(-finalControlScore)][2, Store2]),
  Trial_Impact = c("Significant increase in sales and customers",
                   "No significant change in sales, but significant increase in customers",
                   "Significant increase in sales and customers")
)

print(summary_table)
```

Our analysis shows:

1. For trial store 77, we found a suitable control store and observed a significant increase in both sales and customer count during the trial period, indicating the trial was successful.

2. For trial store 86, while there was no significant change in total sales during the trial period. However, there was a significant increase in customer count. This suggests the trial attracted more customers but didn't necessarily increase per-customer spending.

3. For trial store 88, both sales and customer count increased significantly during the trial period, suggesting the trial was successful.

These findings provide valuable insights for the Category Manager to assess the effectiveness of the trial and make informed decisions about potential rollout to other stores.