Case Study #2

Setup

# load packages
library(tidyverse)
library(ggthemes)
theme_set(theme_minimal())

# read in data
orders <- read_csv("casestudy.csv")

Data Cleaning and Manipulation

# create output data and add year to output
year <- orders %>%
  select(year) %>%
  unique()
output <- tibble(year)
output$year <- as.integer(output$year)

# compute total revenue
total_revenue <- orders %>%
  group_by(year) %>%
  mutate(total_revenue = sum(net_revenue)) %>%
  select(total_revenue, year) %>%
  unique()

# add total revenue to output
output <- output %>%
  left_join(total_revenue, by = "year")

# add boolean column new_customer to orders
orders <- orders %>%
  group_by(customer_email) %>%
  mutate(new_customer = case_when(year == min(year) ~ TRUE, year > min(year) ~ FALSE))

# compute new customer revenue
new_customer_revenue <- orders %>%
  filter(new_customer == TRUE) %>%
  group_by(year) %>%
  mutate(new_customer_revenue = sum(net_revenue)) %>%
  select(new_customer_revenue, year) %>%
  unique()

# add new customer revenue to output
output <- output %>%
  left_join(new_customer_revenue, by = "year")

# compute existing customer growth and add to output
output <- output %>%
  arrange(year) %>%
  mutate(existing_customer_growth = (total_revenue - new_customer_revenue) - (lag(total_revenue) - lag(new_customer_revenue)))

# compute revenue lost from attrition and add to output
output <- output %>%
  arrange(year) %>%
  mutate(revenue_lost_attrition = lag(total_revenue) - (total_revenue - new_customer_revenue))

# compute existing customer revenue and add to output
output <- output %>%
  mutate(existing_customer_revenue = total_revenue - new_customer_revenue)

# compute existing customer revenue prior year and add to ouput
output <- output %>%
  arrange(year) %>%
  mutate(existing_customer_revenue_prior = lag(total_revenue) - lag(new_customer_revenue))

# compute total customers and total customers prior
total_customers <- orders %>%
  group_by(year) %>%
  mutate(total_customers = n()) %>%
  select(total_customers, year) %>%
  unique() %>%
  ungroup() %>%
  arrange(year) %>%
  mutate(total_customers_prior = lag(total_customers))

# add total customers and total customers prior to output
output <- output %>%
  left_join(total_customers, by = "year")

# compute new customers
new_customers <- orders %>%
  filter(new_customer == TRUE) %>%
  group_by(year) %>%
  mutate(new_customers = n()) %>%
  select(new_customers, year) %>%
  unique()

# add new customers to output
output <- output %>%
  left_join(new_customers, by = "year")

# compute lost customers and add to output
output <- output %>%
  arrange(year) %>%
  mutate(lost_customers = lag(total_customers) - (total_customers - new_customers))

Final Output

# final table of information
output

Visualizations and Observations

output %>%
  ggplot() +
  geom_line(aes(x = year, y = new_customer_revenue/new_customers, color = "New")) +
  geom_line(aes(x = year, y = (total_revenue - new_customer_revenue)/ (total_customers - new_customers), , color = "Existing")) +
  labs(x = "Year", y = "Average Revenue per Customer", title = "Average Revenue for New and Existing Customers") +
  scale_color_manual("", breaks = c("Existing", "New"), values = c("Existing" = "red", "New" = "blue"))

Existing customers tend to generate more revenue on average over the sample period.

output %>%
  ggplot() +
  geom_line(aes(x = year, y = new_customers, color = "New")) +
  geom_line(aes(x = year, y = lost_customers, color = "Lost")) +
  labs(x = "Year", y = "", title = "Number of Customers Gained and Lost per Year") +
  scale_color_manual("", breaks = c("New", "Lost"), values = c("Lost" = "blue", "New" = "orange"))

In 2016, more customers were lost than gained, but in 2017, more customers were gained.

output %>%
  ggplot() +
  geom_area(aes(x = year, y = new_customers / total_customers), fill = "green", alpha = .5) +
  labs(x = "Year", y = "", title = "Percent of New Customers by Year")

The percent of total customers that are new customers is somewhat constant around 66% after the first year.