# function for determining how many complete yrs of data, used in fn_yr_qtr <-function(data) {# create variable to identify/remove partial yrs by counting qtrs and select yrs with 4 yr_qtr <- data %>%group_by(year, qtr) %>%summarize(count=1) yr_qtr <- yr_qtr %>%group_by(year) %>%summarize(count=n()) %>%filter(count==4) data_cy <- data %>%filter(year %in% yr_qtr$year)return(data_cy)}lmr_data_cy <-fn_yr_qtr(lmr_data)

Data shown here goes back to beginning of 2016 (BC LDB fiscal yr 2016 Q4).

As mentioned in previous articles:my expertise is in data analysis, not the liquor industry, so the emphasis is on exploring what the data can tell us. Industry-insider context may be lacking. In the interest of promoting data analysis and learning, I am sharing most of the R code used to process the data - hence the expandable ‘Code’ options.

Beer Sales Trends in BC

Overview

We’ll start with an annual overview across all beer categories and then look at trends for individual categories further down below.

Code

## function for summarizing data on various dimensionsfn_trend_yr_smry <-function(data, grp1=year, grp2) { grp1 <-enquo(grp1) grp2 <-enquo(grp2)# ttls for yr to use for % of ttl calcs used with various dimensions trend_yr_ttl <- data %>%group_by(!!grp1) %>%summarize(ttl_netsales=sum(netsales),ttl_litres=sum(litres) )# summarize data by cal. yr & secondary dimension - if available trend_yr <- data %>%group_by(!!grp1, !!grp2) %>%summarize(netsales=sum(netsales),litres=sum(litres) )# add % chg YoY, $/l# determine lag based on how many items in dimension (must be consistent) trend_yr <- trend_yr %>%ungroup()if(!is.null(grp2)) { grp <- trend_yr %>%group_by(!!grp2) %>%summarize(count=n()) n_items <-nrow(grp) } else { n_items <-1 }# calc % chg YoY, by dimension if applicable trend_yr <- trend_yr %>%mutate(pc_chg_sales=netsales/lag(netsales, n=n_items)-1,pc_chg_litres=litres/lag(litres, n=n_items)-1,dollar_per_litre=netsales/litres,pc_chg_d_per_l=dollar_per_litre/lag(dollar_per_litre)-1 )# calc cumulative chg over period - based on no. of periods and dimensions (if applciable) n_yrs <-max(trend_yr$year)-min(trend_yr$year) trend_yr <- trend_yr %>%mutate(pc_chg_sales_cum = netsales/lag(netsales, n=(n_yrs*n_items))-1,pc_chg_lt_cum = litres/lag(litres, n=(n_yrs*n_items))-1 )# calc % of annual total for each dimension (will be 1 for yr, if no additional dimensions) trend_yr <-left_join(trend_yr, trend_yr_ttl, by='year')# calc % trend_yr <- trend_yr %>%mutate(litres_pc=litres/ttl_litres,netsales_pc=netsales/ttl_netsales)return(trend_yr)}

Code

trend_yr <-fn_trend_yr_smry(data=lmr_data_cy)

Beer sales have been pretty stable over the last few years, with net $ sales peaking in 2017, recovering in 2022 in line with inflation. Overall slight downward trend, most noticeable in litre consumption.

Code

# sizes not ideal BUT...setting smaller (3, 5) makes it out of sync with plotly plots below# dual axis - not usually recommended but works ok here # - using ggplot because doesn't work well with ggplotlych_title <-"BC Beer: Net Sales $ + Litre Sales trend"plot <- trend_yr %>%ggplot(aes(x=as.factor(year), y=netsales, group=1))+geom_line(color=bar_col, size=2)+geom_smooth(aes(y=netsales), method='lm', se=FALSE, color='brown', linetype='solid', size=1)+geom_point(aes(y=netsales), color=bar_col, size=3)+geom_line(aes(x=as.factor(year), y=litres*3), size=2, color='royalblue')+geom_smooth(aes(y=litres*3), method='lm', se=FALSE, color='brown')+geom_point(aes(x=as.factor(year), y=litres*3), size=3, color='royalblue')+scale_y_continuous(name='net $ sales',labels=comma_format(prefix="$", scale=1e-9,suffix="B"), expand=expansion(mult=c(0,0.1)), limits=c(0,max(trend_yr$netsales)),sec.axis =sec_axis(trans=~./3, name='litres',labels=comma_format(scale=1e-6, suffix="M")))+labs(title=ch_title, x="")+theme_classic()+theme(axis.title.y =element_text(color = bar_col, size=13),axis.title.y.right =element_text(color ='royalblue', size=13) )plot

Code

## aggregate data by quartertrend_yr_qtr <- lmr_data_cy %>%group_by(end_qtr_dt) %>%summarize(netsales=sum(netsales),litres=sum(litres))

Breaking out litre sales by quarter confirms the steady downward trend within seasonal cycles.

Code

# plotly charts don't respond to in-line size settingsch_title <-"BC Beer Litre Sales by Qtr, with Trend"plot <- trend_yr_qtr %>%ggplot(aes(x=end_qtr_dt, y=litres, group=1))+geom_line(color=bar_col, size=1.4)+#geom_point(aes(y=litres), color=bar_col, size=3)+geom_smooth(method='lm', se=FALSE)+scale_y_continuous(labels=comma_format(scale=1e-6,suffix="M"), expand=expansion(mult=c(0,0.1)), limits=c(0,max(trend_yr_qtr$litres)))+labs(title=ch_title, x="")ggplotly(plot)

Code

# tried to use this code suggested by chatgpt for more appropriate sizing# size worked but left gap below; layout settings didn't do anything#ggplotly(plot, width=440, height=260) %>% # layout(autosize = TRUE, margin = list(l = 0, r = 0, b = 0, t = 0, pad = 0))

The flat trend/recent increase in net $ sales vs downward trend in litre sales shows up in gradual uptrend in net $ per litre sales. Brewers are selling fewer litres but getting more money for each of them (on average).

BC LDB puts beer into 3 major categories for purposes of the Liquor Market Report:

Domestic - BC Beer: beer produced in BC.

Domestic - Other Province Beer: beer produced in Canada outside BC.

Import Beer: beer imported from outside Canada.

Code

# summarize data by year and major category - using functiontrend_yr_cat <-fn_trend_yr_smry(data=lmr_data_cy, grp2=category)## simplify category names#unique(trend_yr_cat$category)trend_yr_cat <- trend_yr_cat %>%mutate(category=case_when( category=='Domestic - BC Beer'~'BC', category=='Domestic - Other Province Beer'~'Other Prov.', category=='Import Beer'~'Import' ))

Litre sales

Looking at litre consumption for these 3 main categories:

volume drops for BC and Import beer, with other province beer actually increasing.

in % terms, litre market share for BC producers remained steady just under 80%, while other provinces took their market share out of imports.

Year-over-Year % Changes

The year-over-year dynamics in litre consumption stand out even clearer when looking at % change:

mostly stable for BC producers.

BIG gains by other Canadian provinces - as high as almost 40% growth in 2021. Pandemic effect?

Imports as the big losers, with over 30% loss in 2021. Possibly related to pandemic and related supply chain issues?

Clearly, breweries in other provinces are winning that battle over imported beer for the roughly 20% of volume not accounted for by BC-produced beer.

Where do the dollars go?

Not surprisingly, the shift in volume from Imports to Other Provinces is reflected in dollar sales as well.

BC breweries have a similarly stable share of dollar sales, as with litre sales, although at a slightly lower % share: ~75%. Other provinces are progressively winning the battle for the remaining ~25%, more than doubling their share of $ during the period.

Regional View

BC LDB provides data for importers by country, allowing us to take a closer look at dynamics within this segment, relative to Other Provinces. (BC producers removed to zoom in on external producers)

Code

# need a new field for region - need to go back to lmr_datalmr_data_reg <- lmr_data_cy %>%mutate(region=ifelse(str_detect(subcategory, 'Domestic - BC'),'Canada - BC',ifelse(str_detect(subcategory, 'Domestic - Other'), 'Canada - Other', subcategory)))# remove BC to focus on regions outside BClmr_data_reg_xbc <- lmr_data_reg %>%filter( region!='Canada - BC')# summarize data by year and regiontrend_yr_reg <-fn_trend_yr_smry(data=lmr_data_reg_xbc, grp2=region)## remove 'beer' from region names for brevitytrend_yr_reg <- trend_yr_reg %>%mutate(region=str_replace_all(region, ' Beer',''))

Some interesting dynamics show above:

overall downward trend since 2017.

Canadian provinces other than BC have been the gainers while other regions, most notable Europe, Mexico, US have seen declines.

Regional breakout by litres, % change, $/litre

We can do a deeper dive on regional differences by comparing not only litre sales but also % change in litre sales over time, and $/litre (proxy for price):

BC producers excluded to zoom in on external producer dynamics.

‘Other Country’ excluded for simplicity, due to tiny amount of sales.

Code

excl <-"Other Country"# excluding 'other country' as minimalch_title <-"% Change in Annual Litre Sales over Period vs Latest Yr Litres"plot <- trend_yr_reg %>%filter(year==max(trend_yr_reg$year) & region!=excl) %>%ggplot(aes(x=litres, y=pc_chg_lt_cum, color=region, size=dollar_per_litre))+geom_point()+scale_x_continuous(labels=label_comma(), expand=c(0.02,0.05))+scale_y_continuous(labels=percent_format())+scale_size(range =c(1,6)) +geom_hline(yintercept =0, linetype='solid')+theme(axis.line.x =element_blank())+labs(title= ch_title, y="% chg in annual litre sales over period", x="litres sold in most recent year")ggplotly(plot)

With BC producers removed, Canada - Other is by far the most litres sold, by far the cheapest (smallest dot), and hugely positive growth in litre sales over the period.

All other producers have seen negative growth in litres sold, with US and Mexico particularly hard hit.

US decline is particularly noteworthy, dropping over 90%! Possibly due to more US brands being produced within Canada, such as Budweiser being brewed in Canada by Labatt (AB InBev) and Coors being brewed by Molson (now Molson Coors).

For a year-by-year view…

Code

# faceted view by year since animation doesn't workch_title <-"Year by Year Changes in Litres Sold"plot_yr <- trend_yr_reg %>%filter(region!=excl) %>%ggplot(aes(x=litres, y=pc_chg_litres, color=region, size=dollar_per_litre))+geom_point()+facet_wrap(~year)+scale_x_continuous(labels=label_comma()) +scale_y_continuous(labels=percent_format()) +scale_size(range =c(1,8)) +geom_hline(yintercept =0, linetype='solid') +labs(title = ch_title, y="% chg over period", x=paste0("annual litres sold: {frame_time}"))plot_yr

Beer: Wrap-up and Next Up

Some interesting dynamics happening with beer sales in BC, with a couple of the main themes being:

gradual downward trend in litre sales

stable market share for BC producers, around 80%.

strong growth (90%) in litre sales from producers in other Canadian provinces almost entirely at the expense of beer imported from outside Canada.

Next Up

Next article will drill into details of BC producer categories: Commercial beer, Regional beer, Microbrew beer.

Footnotes

Notes on ‘net $ sales’:

the report says “Net dollar value is based on the price paid by the customer and excludes any applicable taxes.”

calculating average net dollar value per litre for beverage categories gives unrealistically low numbers compared to retail prices in BC liquor stores. (Beer at average $4/litre? Not even the cheapest beer on the BC Liquor Stores website.)

there is likely additional factors related to BC LDB pricing structure, wholesaling, etc.

best to consider average net dollar value per litre referred to below as relative indicator.