Competition - drinks promotions
  • AI Chat
  • Code
  • Report
    • Beta
    Spinner

    Where should a drinks company run promotions?

    Background

    My company owns a chain of stores across Russia that sell a variety of alcoholic drinks. We recently ran a wine promotion in Saint Petersburg that was very successful. Due to the cost to the business, it isn’t possible to run the promotion in all regions. The marketing team would like to target 10 other regions that have similar buying habits to Saint Petersburg where they would expect the promotion to be similarly successful.

    It is important to recognize that as of recent, St. Petersburg is not the top region for sales. That being said, we should focus our marketing campaign in regions which reflect the highest sales figures over time which may show the best potential for our promotion to be the most effective.

    The data

    The marketing team has sourced you with historical sales volumes per capita for several different drinks types.

    • "year" - year (1998-2016)
    • "region" - name of a federal subject of Russia: blast, republic, krai, autonomous okrug, federal city and a single autonomous oblast
    • "wine" - sale of wine in litres by year per capita
    • "beer" - sale of beer in litres by year per capita
    • "vodka" - sale of vodka in litres by year per capita
    • "champagne" - sale of champagne in litres by year per capita
    • "brandy" - sale of brandy in litres by year per capita

    Libraries

    Datacamp will not support: rmapshaper, sf, tmap. Cannot show map presentation.

    library(GGally)
library(ggdark)
library(ggstream)
library(janitor)
library(kableExtra)
library(tidyverse)
tidyverse_logo()

# library(rmapshaper)
# library(sf)
# library(tmap)

    Exploratory Data Analysis

    Some regions have no data, about 60 observations (Crimea, Chechen, Sevastopol, set values to 0)
    # Some regions have no data, (Crimea, Chechen, Sevastopol, set values to 0
data <- read_csv('/work/files/workspace/data/russian_alcohol_consumption.csv') %>% replace(is.na(.), 0)

glimpse(data)

# alcohol colors: beer, brandy, champagne, vodka, wine 
ac <- c('#F28E1C', '#87413F', '#F7E7CE', '#BFC0EE', '#B11226')

corrplot::corrplot(cor(data[,c(1,3:7)]))

GGally::ggpairs(data[,c(1,3:7)])

    Beer is the biggest seller, followed by Vodka. Overall, sales appear to be declining.

    
suppressMessages(data %>%
    pivot_longer(wine:brandy) %>%
    group_by(year, name) %>%
    summarize(litres = sum(value), .groups = 'keep') %>%
    ggplot(aes(year, litres, color = name, fill = name)) +
    geom_line(alpha = 0.42, lwd = 1.42) +
    geom_smooth(method = 'loess', formula = 'y ~ x') +
    scale_x_continuous(breaks = seq(1998, 2016, 1)) +
    scale_y_continuous(breaks = seq(0, 6000, 500)) +
    scale_color_manual(values = ac)+
    scale_fill_manual(values = ac)+
    
    dark_theme_minimal() +

    theme(axis.text.x = element_text(angle = 45, hjust = 1),
          panel.grid.major = element_blank(), # element_line(color = 'gray7'),
          panel.grid.minor = element_blank(), # element_line(color = 'gray7'),
          legend.position = "bottom") +
    labs(y = 'Litres Sold', x = '', fill = 'Alcohol Type', color = 'Alcohol Type') +
#         subtitle = "Geom Smooth method = 'loess'") +
    ggtitle("Total Alcohol Sales: Litres per Year"))

    Top two regions each year

    
data %>%
    pivot_longer(wine:brandy) %>%
    group_by(year, region) %>%
    summarize(litres = sum(value), .groups = 'drop') %>%
    group_by(year) %>%
    arrange(desc(litres, region)) %>%
    slice(1:2) %>%
    rename(Year = year, Region = region, Litres = litres) %>%
    kbl(align = "c") %>%
    kable_classic() %>%
    row_spec(row = 0, background = ' #4682B4', bold = TRUE) %>%
    row_spec(row = c(1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37), background = "#D8D8D8") %>%
    row_spec(row = c(2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38), background = "#BEBEBE") %>%
    column_spec(1, bold = TRUE)

data %>%
    pivot_longer(wine:brandy) %>%
    group_by(year, region) %>%
    summarize(litres = sum(value), .groups = 'drop') %>%
    group_by(year) %>%
    arrange(desc(litres, region)) %>%
    slice(1:2) %>%
    ggplot(aes(year, litres, fill = region, group = litres)) +
    geom_col() +
    geom_text(aes(label = region), position = position_stack(vjust = 0.5), color = 'black') +
    scale_x_continuous(breaks = seq(1998, 2016, 1)) +
    scale_y_continuous(breaks = seq(0, 500, 25)) +
    dark_theme_minimal() +
    theme(axis.text.x = element_text(angle = 45, hjust = 1),
          legend.position = 'none',
          panel.grid.major = element_blank(),
          panel.grid.minor = element_blank()) +
    labs(x = '', y = 'Litres') +
    coord_flip() +
    ggtitle("Top 2 regions with most litres sold per year")

    Darker colors are the regions with most sales for that year.

    Top region(s) each year. Some years tied.

    
data %>%
    pivot_longer(wine:brandy) %>%
    group_by(year, region) %>%
    summarize(litres = as.integer(sum(value)), .groups = 'keep') %>%
    group_by(year) %>%
    filter(litres == max(litres)) %>%
    kbl(align = "c") %>%
    kable_classic() %>%
    row_spec(row = 0, background = ' #4682B4', bold = TRUE) %>%
    row_spec(row = c(1,3,5,7,9,11,13,15,17,19,21), background = "#D8D8D8") %>%
    row_spec(row = c(2,4,6,8,10,12,14,16,18,20), background = "#BEBEBE") %>%
    column_spec(1, bold = TRUE)

data %>%
    pivot_longer(wine:brandy) %>%
    group_by(year, region) %>%
    summarize(litres = as.integer(sum(value)), .groups = 'keep') %>%
    group_by(year) %>%
    filter(litres == max(litres)) %>%
    ggplot(aes(year, litres, fill = region )) +
    geom_col() +
    dark_theme_minimal() +
    coord_flip() +
    scale_x_continuous(breaks = seq(1998, 2016, 1)) +
    scale_y_continuous(breaks = seq(0, 350, 25)) +
    labs(x = '', y = '') +
    theme(legend.position = 'none',
          panel.grid.major = element_blank(),
          panel.grid.minor = element_blank()) +
    geom_text(aes(label = region), position = position_stack(vjust = 0.5), color = 'black') +
    ggtitle("Top region sales")

    Top 10 regions aggregate sales.

    # top slice(1:10) regions overall
regions <- data %>%
    pivot_longer(wine:brandy) %>%
    group_by(region) %>%
    summarize(litres = sum(value)) %>%
    arrange(-litres) %>%
    slice(1:10) %>%
    pull(region)

regions

# bar top 10 --------------------------------------
# data %>%
#    filter(region %in% regions) %>%
#    pivot_longer(wine:brandy) %>%
#   ggplot(aes(year, value, group = region, fill = name)) +
#    geom_col()

# top 10 geom_stream ------------------------------
data %>%
    filter(region %in% regions) %>%
    pivot_longer(wine:brandy) %>%
    group_by(region) %>%
    ggplot(aes(year, value, fill = name)) +
    geom_stream() +
    facet_wrap(~ factor(sub(" .*", "", region))) +
    dark_theme_minimal() +
    theme(panel.grid.major = element_blank(),
          panel.grid.minor = element_blank(),
          axis.text.x = element_blank()) +
    labs(fill = 'Alcohol Type', y = '', x = '') +
    ggtitle("Top 10 Sellers of all alcohol types")

    Recommendation

    Based on my findings of the top 10 sellers historically, we should run the promotion at the following region store locations:

    • Moscow
    • Saint Petersburg
    • Yamalo-Nenets Autonomous Okrug
    • Tyumen Oblast
    • Komi Republic
    • Khanty–Mansi Autonomous Okrug – Yugra
    • Chelyabinsk Oblast
    • Vologda Oblast
    • Sverdlovsk Oblast
    • Ivanovo Oblast