In datawookie/retail: Historical Retail Data from the 'Trundler' API

knitr::opts_chunk$set(echo = FALSE, warning = FALSE, message = FALSE, fig.width = 10, comment = NULL)

library(trundler)
library(dplyr)
library(stringr)
library(purrr)
library(tidyr)
library(ggplot2)
library(gt)
library(glue)

DATE_MIN <- as.Date("2020-03-01")
DATE_MAX <- strftime(Sys.Date(), format = "%Y-%m-%d")
RETAILER_ID_PICKNPAY <- 9

This is an example of how you can generate a report using the Trundler R package.

# You can get an API key from https://rapidapi.com/datawookie/api/trundler
# Keep your key safe by storing it in a .Renviron file
API_KEY <- Sys.getenv("TRUNDLER_KEY")
set_api_key(API_KEY)

# Get a dataframe of all available retailers
retailers <- retailer()

View the retailer catalog

Trundler collects data from wide range of retailers. Currently, there are r length(retailers$retailer) retailers in the Trundler catalog. Here are the first ten.

# View the first 10 retailers
gt(retailers[1:10, 1:4]) %>%
  tab_header(
    title = md("**Trundler Retailer Catalog**"),
    subtitle = md("*The first ten retailers in the catalog*")
  ) %>%
  cols_label(
    retailer_id = md("**Retailer ID**"),
    retailer = md("**Retailer**"),
    retailer_url = md("**URL**"),
    currency = md("**Currency**")
  )

Track the price of a product at a specific retailer

It's possible to search a retailer for a product of interest. Here, we've searched for Bovril products sold at Pick 'n Pay.

# Use the retailer_id to search for Bovril products at a specific retailer (e.g. Pick 'n Pay)
all_bovril <- retailer_products(retailer_id = RETAILER_ID_PICKNPAY, product = "Bovril") %>%
  select(product_id, product, sku)

# View the table
gt(all_bovril) %>%
  tab_header(
    title = md("**All Bovril Products**"),
    subtitle = md("*At Pick 'n Pay*")
  ) %>%
  cols_label(
    product_id = md("**Product ID**"),
    product = md("**Product**"),
    sku = md("**SKU**")
  )

As you can see, Pick 'n Pay sells a few different Bovril products. Let's have a look at the price of the 250g product. Here are the most recent price records.

# Using the corresponding product ID, you can get the product and price data you're interested in.
bovril_250g_id <- all_bovril %>%
  filter(product == "Bovril Beefy Spread 250g") %>%
  select(product_id) %>%
  unlist()

bovril_250g <- product(bovril_250g_id)

# Add the historical price data and the retailer information
bovril_250g_prices <- product_prices(bovril_250g_id) %>%
  inner_join(bovril_250g, by = "product_id") %>%
  inner_join(retailers, by = "retailer_id") %>%
  select(date = time, product, retailer, starts_with("price")) %>%
  mutate(date = as.Date(date))

# View the table
retailer <- bovril_250g_prices$retailer[1]
product <- bovril_250g_prices$product[1]

bovril_250g_prices %>%
  select(date, starts_with("price")) %>%
  slice(1:10) %>%
  arrange(desc(date)) %>%
  gt() %>%
  tab_header(
    title = md(glue("**{product}**")),
    subtitle = md(glue("*At {retailer}*"))
  ) %>%
  cols_label(
    date = md("**Date**"),
    price = md("**Price**"),
    price_promotion = md("**Promotional Price**"),
    price_effective = md("**Effective Price**")
  )

It's easier to digest how the price of Bovril has changed over time if we plot the data.

# Plot the price history of Bovril 250g
ggplot(bovril_250g_prices, aes(x = date)) +
  geom_step(aes(y = price), lwd = 2, alpha = 0.5, colour = TRUNDLER_BLUE) +
  geom_point(aes(y = price), size = 4, alpha = 0.725, colour = TRUNDLER_BLUE) +
  scale_x_date(NULL, date_breaks = "2 weeks", date_labels = "%e %b", limits = c(DATE_MIN, DATE_MAX)) +
  scale_y_continuous("Price (ZAR)") +
  labs(
    title = bovril_250g_prices$product[1],
    caption = "Data from Trundler / trundler.dev"
  ) +
  theme_trundler() +
  theme(
    panel.grid.major.y = element_line(colour = TRUNDLER_GREY, size = 0.25),
    panel.grid.minor.y = element_line(colour = TRUNDLER_GREY, size = 0.25)
  ) +
  theme_trundler_title(subtitle = FALSE)

Track average prices over time

It can be useful to track how the average price of a product changes over time. Let's search for the 250g Bovril product at all South African retailers.

# Search the database
search_results <- products("bovril")

# Join the search results to the retailer dataframe.  Use currency field to select only SA retailers.
bovril_products <- search_results %>%
  inner_join(retailers, by = "retailer_id") %>%
  filter(
    str_detect(product, "250"),
    currency == "ZAR"
  ) %>%
  arrange(retailer)

# View the results
bovril_products %>%
  select(product, retailer, sku) %>%
  group_by(sku) %>%
  slice(1) %>%
  ungroup() %>%
  gt() %>%
  tab_header(
    title = md("**Bovril 250g**"),
    subtitle = md("*Sold at South African retailers*")
  ) %>%
  cols_label(
    product = md("**Product**"),
    retailer = md("**Retailer**"),
    sku = md("**SKU**")
  )

As you can see, it is sold at a number of South African retailers. Now, let's retrieve the price data from each retailer, interpolate any missing prices, and find the daily average price.

# Get the price data for these bovril products, interpolating prices where necessary.
bovril_prices <- map_dfr(bovril_products$product_id,
  price_complete,
  date_min = DATE_MIN,
  date_max = DATE_MAX
) %>%
  unnest(cols = c(price)) %>%
  inner_join(bovril_products, by = "product_id") %>%
  select(date, product_id, sku, product, retailer, starts_with("price"))

# Get the average daily price of Bovril in South Africa
bovril_summary <- bovril_prices %>%
  group_by(date) %>%
  summarise(avg_price = mean(price))

# View the table
bovril_summary %>%
  arrange(desc(date)) %>%
  slice(1:10) %>%
  gt() %>%
  tab_header(
    title = md("**Bovril 250g: Average Price**"),
    subtitle = md("*Past ten days*")
  ) %>%
  cols_label(
    date = md("**Date**"),
    avg_price = md("**Average Price**")
  )

Finally, let's plot the daily average price of a 250g jar of Bovril sold in South Africa.

# Plot the average price
ggplot(bovril_summary, aes(x = date)) +
  geom_line(aes(y = avg_price), lwd = 2, alpha = 0.75, colour = TRUNDLER_BLUE) +
  scale_x_date(NULL, date_breaks = "2 weeks", date_labels = "%e %b") +
  scale_y_continuous("Price (ZAR)") +
  labs(
    title = "Bovril 250g: Average Price",
    subtitle = "At South African Retailers",
    caption = "Data from Trundler / trundler.dev"
  ) +
  theme_trundler() +
  theme_trundler_title() +
  theme(
    panel.grid.major.y = element_line(colour = TRUNDLER_GREY, size = 0.25),
    panel.grid.minor.y = element_line(colour = TRUNDLER_GREY, size = 0.25)
  )