The Marketing Efficiency Collapse

This chart shows how the shift from Social media advertisement (cyan) to Google Ads (salmon) caused the net income (pink) to crash.

promotions <- read_xlsx("promotions.xlsx")
income <- read_xlsx("Income_statement.xlsx")

promo_summary <- promotions %>%
  group_by(Year, Campaign) %>%
  summarise(Cost = sum(Cost), .groups = "drop")

income_clean <- income %>%
  select(year, Net_income) %>%
  rename(Year = year)

max_spend <- sum(promo_summary %>% filter(Year == 2023) %>% pull(Cost))
max_income <- max(income_clean$Net_income)
scale_factor <- max_income / max_spend * 0.8

ggplot() +
  geom_col(data = promo_summary, 
           aes(x = factor(Year), y = Cost, fill = Campaign), 
           alpha = 0.8) +
  
  geom_line(data = income_clean %>% filter(Year >= 2018, Year <= 2023), 
            aes(x = factor(Year), y = Net_income / scale_factor, group = 1), 
            color = "pink", linewidth = 2) +
  
  geom_point(data = income_clean %>% filter(Year >= 2018, Year <= 2023), 
             aes(x = factor(Year), y = Net_income / scale_factor), 
             color = "pink", size = 4) +
  
  scale_y_continuous(
    name = "Marketing Spend (€)",
    labels = label_currency(prefix = "€"),
    sec.axis = sec_axis(~ . * scale_factor, 
                        name = "Net Income (€) (Pink Line)", 
                        labels = label_currency(prefix = "€"))
  ) +
  
  scale_fill_manual(values = c("google ad" = "salmon", 
                               "social" = "cyan", 
                               "Special Partnership" = "gold")) +
  
  labs(
    title = "Why did revenues fall? The failed shift to Google Ads",
    subtitle = "Net income (pink) tracked with social spend (cyan), but crashed when Google Ads (salmon) took over.",
    x = "Year",
    fill = "Marketing channel"
  ) +
  theme_minimal() +
  theme(
    axis.title.y.right = element_text(color = "pink", face = "bold"),
    axis.text.y.right = element_text(color = "pink"),
    legend.position = "top",
    plot.title = element_text(face = "bold", size = 16)
  )