Alternatives to Dual-Axis Charts

In a previous blog post called 'Dual-Axis Charts: Temptations, Traps, Tips', I went through some of the pitfalls of using dual-axis charts. These are charts where you want to compare two metrics or data attributes but there are vastly different scales involved, so you reach for a layout with two y-axes in order to deal with the scales separately. I acknowledged this as a tempting choice, but fraught with danger due to at least a couple of common issues:

1. Misrepresentation due to mixed scales: changing relative scales arbitrarily can suggest different conclusions and imply relationships that may not be as strong (or weak) as they appear.
2. Difficultly in interpretation: these charts require extra mental effort to untangle the lines and associate them with their respective data points.

I also provided some basic tips on how to avoid/minimize these issues with dual-axis charts so here I want to try out some alternatives that may provide even better options for communicating effectively with data.

As noted in the previous post, there are two common scenarios where dual-axis charts come up and we will walk through alternatives for each of them.

1. Comparing trends in two (or more) similar data sets that have vastly different scales.
2. Comparing a volume metric with a related rate or ratio metric. (so, again, vastly different scales)

As usual, the examples here are produced using R, with ggplot2 package as the preferred visualization tool.

Scenario 1: Compare trends in similar metrics from two datasets

Same example as previous post: prices history of two different crypotcurrencies – Cardano, with its token ADA and Bitcoin (BTC).

To recap our initial setup:

As always, the visualization choice should be based on what questions we are trying to answer, what we are hoping to learn, and, ultimately what decisions we want to make.

If we are starting with general exploration, we still need to frame it up. Our first thought may be to compare prices over a recent period, to answer questions like:

• What are the relative changes in the currencies over time?
• Do the two follow a similar pattern of ups and downs?
• Are there any points where a general pattern breaks? (could provide focus for further investigation)
• Eventually: are there ways we can take advantage of these patterns to make investment decisions? (probably beyond initial scope but helps to have that broader perspective)

In this random sample of recent data (Cdn$), we can see the two sets of prices are on much different scales.

Line charts stacked vertically

cp01 <- crypto_data %>% ggplot(aes(x=date, y=BTC_CAD))+geom_line()+
  scale_y_continuous(labels=dollar_format())+
  labs(title='BTC (top) and ADA (bottom) prices (CDN$)', x='')+
  theme(axis.text.x = element_blank())
cp02 <- crypto_data %>% ggplot(aes(x=date, y=ADA_CAD))+geom_line()+
  scale_y_continuous(labels=dollar_format())+
  labs(x='')
#grid.arrange(cp01, cp02, nrow=2)
plot_grid(cp01, cp02, nrow=2, align='v')

Here we still have the issue of scale ratios, but we have some advantages:

• clear and easy to see which dataset is which.
• separating the lines puts the focus on general pattern comparison, doesn't create as strong an implication around magnitude of comparative changes and doesn't create distractions like cross-over points, which are meaningless.

We are able to focus on the comparison, not on bending our mind around untangling the lines. This might more easily lead to a follow-up question, like:

• seems to be some similarity in the trends but not super-consistent, I wonder what the correlation between the lines is?

corel <- cor.test(crypto_data$BTC_CAD, crypto_data$ADA_CAD)
corelcoef <- corel$estimate
corelci_lower <- corel$conf.int[1]
corelci_upper <- corel$conf.int[2]

Quick calculation shows r= 0.386, which is not that strong, and the 95% confidence interval is between 0.292 and 0.474, which seems pretty wide, lowering our sense of the strength of the relationship even further.

There's a whole other rabbit hole we can go down here, if we choose – creating scatterplots and all kinds of things – but for our purposes we'll continue with other visualization strategies.

% Change Comparison

Since a key part of what we are trying to understand is relative change in prices, a logical approach to get away from scale issue is to look at % changes. After calculation, we can get this view:

## calculate % changes day-over-day
crypto_data_pc <- crypto_data %>% mutate(BTC_CAD_pc=BTC_CAD/lag(BTC_CAD)-1,
                                         ADA_CAD_pc=ADA_CAD/lag(ADA_CAD)-1)
crypto_data_pc <- crypto_data_pc[-1,]
## produce chart of % changes for each currency
crypto_data_pc %>% ggplot(aes(x=date, y=BTC_CAD_pc))+geom_line(color='goldenrod')+
  scale_y_continuous(labels=percent_format())+
  geom_line(aes(y=ADA_CAD_pc), color='blue')+
  labs(title='Daily % Changes in Prices (gold=BTC, blue=ADA)', x="", y='Daily % Chg')

Pretty messy with this amount of data, but hopefully you can see how this approach could be useful in comparing the two currencies. This could be made more readable by either zooming in on shorter period OR aggregrating the data by week or month.

Here's the example with weeks (using Lubridate pkg to choosing weekday==7 ):

## use lubridate to get day of week for each date, filter for single day of week, calc WoW % chg
crypto_data_pc_wk <- crypto_data %>% mutate(
  weekday=wday(date)
) %>% filter(weekday==7) %>% mutate(
  BTC_CAD_pc=BTC_CAD/lag(BTC_CAD)-1,
  ADA_CAD_pc=ADA_CAD/lag(ADA_CAD)-1
)
crypto_data_pc_wk <- crypto_data_pc_wk[-1,] ## drop first row, since NA for % chg
## plot weekly change comparison
crypto_data_pc_wk %>% ggplot(aes(x=date, y=BTC_CAD_pc))+geom_line(color='goldenrod')+
  scale_y_continuous(labels=percent_format())+
  geom_line(aes(y=ADA_CAD_pc), color='blue')+
  labs(title='Weekly % Changes in Prices (gold=BTC, blue=ADA)', x="", y='Daily % Chg')

Can also try this view with bars, after a bit of manipulation to longer data shape for ease of bar chart comparison:

## pivot data longer to make it easier to display side-by-side bars with legend
crypto_data_pc_wk_lg <- crypto_data_pc_wk %>% select(date, BTC_CAD_pc, ADA_CAD_pc) %>% 
  pivot_longer(cols=c(BTC_CAD_pc, ADA_CAD_pc), names_to='currency', values_to = 'pc_chg')  
## side-by-side bar plot
crypto_data_pc_wk_lg %>% ggplot(aes(x=date, y=pc_chg, fill=currency))+
  geom_col(position = position_dodge2())+
  scale_y_continuous(labels=percent_format())+
  labs(title='Weekly % Changes in Prices', x="", y='Daily % Chg')+
  theme(legend.position = 'top', legend.title = element_blank())

Still some challenges with density of the chart, but highlights how bar/column charts can facilitate side-by-side comparisons, whereas line charts favour reading trends.

Difference in Difference

Once we see the comparison of % change week-over-week, we might want to go further to compare the differences in those changes. We can take the next logical step and calculate the difference between the % change in ADA and % change in BTC – a version of the 'difference in difference' approach used in statistics.

crypto_data_pc_wk <- crypto_data_pc_wk %>% mutate(
  ADA_BTC_diff=ADA_CAD_pc-BTC_CAD_pc
)
crypto_data_pc_wk %>% ggplot(aes(x=date, y=ADA_BTC_diff*100))+geom_col()+
  labs(title="Difference in % Difference: ADA-BTC", x="", y='Difference in Chg (percentage pts)')

This view highlights even further the lack of consistent relationship between changes in the two currencies:

• if the two had the same % changes (increase or decrease) week-over-week, the bars would be at/close to 0.
• if there was a consistent difference in difference, for example, if a 10% change in BTC accompanied an 15% change in ADA, and 5% change in BTC accompanied a 10% in ADA, the bars would all be at 5.

Although the bars appear to be somewhat centered around 0 there is a LOT of variation on either side. Getting back to our original questions, there is no identifiable pattern in comparative price changes and not even apparent changes in the relationship over time or at different periods.

Center and Spread

Looking at the relative patterns over time, one direction this could lead us is to ask questions around center and distribution of daily changes in the two currencies. This is going off on a tangent from the main goal of exploring alternatives to dual-axis time series charts, but I can't resist. 😉

## BTC-CAD summary

##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
## -0.1335 -0.0216 -0.0004  0.0024  0.0245  0.1855

## std deviation:  0.0422

## 
## ADA-CAD summary

##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##  -0.256  -0.027   0.003   0.009   0.032   0.322

## std deviation:  0.0692

Both appear to be pretty tightly-centered around 0, with Cardano being a bit more volatile. This could lead us into some distribution visualizations like histogram with changes in the two currencies overlaid each other…

crypto_data_pc %>% ggplot()+
  geom_histogram(aes(x=BTC_CAD_pc), fill='goldenrod', alpha=0.2)+
  geom_histogram(aes(x=ADA_CAD_pc), fill='blue', alpha=0.2)+
  labs(title='Distribution of Daily % Chg, gold=BTC-CAD, blue=ADA-CAD', x='')

…or, personal preference, boxplot…

## pivot data longer to make it easier to display side-by-side bars with legend
crypto_data_pc_lg <- crypto_data_pc %>% select(date, BTC_CAD_pc, ADA_CAD_pc) %>% 
  pivot_longer(cols=c(BTC_CAD_pc, ADA_CAD_pc), names_to='currency', values_to = 'pc_chg')
## boxplot
crypto_data_pc_lg %>% ggplot(aes(x=currency, y=pc_chg))+geom_boxplot(fill='dodgerblue')+
  scale_y_continuous(labels=percent_format())+
  labs(title='Distribution of Daily % Chg', x='', y='Daily % Chg')

Re-Scale the Data

Another option we have is to rescale both sets of prices so that they are on a common scale, and therefore more camparable. This is the kind of thing that is often done in machine learning in order to balance the weights of features. There are a number of potential pitfalls, so it is best to proceed with caution, understanding of your data, and objectives in mind. This info is presented as demonstration, not necessarily endorsement. 😉

Two main approaches: Normalization and Standardization

This is a whole area unto itself and there are variations in the terminology used. I'm relying on the following references:

• Feature Scaling for Machine Learning: Understanding the Differences between Normalization and Standardization (Analyticsvidyha.com)
• About Feature Scaling and Normalization (Sebastian Raschka)
• Data Normalization with R (Nikhita Singh Shiv Kalpana on Medium)

Based on the above, there are two general approaches, described as:

• Normalization: scale the values from 0 – 1, using 'min-max scaling'. Doesn't treat outliers well. This is often described as maintaining the same distribution as original data, just shifting to different scale. However, it appears that will particularly volatile data, this is not always the case.
• Standardization: aka 'z-score': scale the values so that mean = 0 and standard deviation = 1. No upper or lower bound, so tends to be better at handling outliers. Skews the data toward normal distribution.

There are general guidelines but no hard and fast rules around when to use one or the other, so let's check them both out.

Normalization

According to info on analyticsvidhya.com, considered 'good to use when you know that the distribution of your data does not follow a Gaussian distribution'. Let's check:

## use density function to compare actual vs normal ideal
## BTC
hist01 <- crypto_data %>% ggplot(aes(x=BTC_CAD))+geom_histogram(aes(y=..density..))+
  ## function to calculate ideal normal dist based on mean and sd in the dataset 
  stat_function(fun=dnorm, args=list(mean=mean(crypto_data$BTC_CAD), sd=sd(crypto_data$BTC_CAD)), color='red')+
  labs(title='BTC_CAD price distribution')
## ADA
hist02 <- crypto_data %>% ggplot(aes(x=ADA_CAD))+geom_histogram(aes(y=..density..))+
  ## function to calculate ideal normal dist based on mean and sd in the dataset
  stat_function(fun=dnorm, args=list(mean=mean(crypto_data$ADA_CAD), sd=sd(crypto_data$ADA_CAD)), color='red')+
  labs(title='ADA_CAD price distribution')
## print both
grid.arrange(hist01, hist02, nrow=1)

This is pretty 'abnormal data' – hello cryptocurrency! – so another reason to exercise caution.

The formula for min-max normalization is pretty straightforward – basically yor each value in the data set you calculate the distance from the minimum value and then divide by the full range of data:

Xeach = (X – Xmin) / (Xmax-Xmin)

For fun, we can use the caret pkg, based on example code from JournalDev.com:

## use caret pkg functions for fun
library(caret)
process <- preProcess(crypto_data, method=c('range'))
crypto_norm <- predict(process, crypto_data)

## alternative methods:
## - mutate
crypto_data_minmax <- crypto_data %>% mutate(
  BTC_mm=(BTC_CAD-min(crypto_data$BTC_CAD))/(max(crypto_data$BTC_CAD)-min(crypto_data$BTC_CAD)),
  ADA_mm=(ADA_CAD-min(crypto_data$ADA_CAD))/(max(crypto_data$ADA_CAD)-min(crypto_data$ADA_CAD))
)
## - simple function with lapply
fminmax <- function(x){
  (x-min(x))/(max(x)-min(x))
  }
crypto_data_fminmax <- as.data.frame(lapply(crypto_data[,2:3], fminmax))

Create plot for display later:

## create a plot of normalized lines
p_norm <- crypto_norm %>% ggplot(aes(x=date))+
  geom_line(aes(y=BTC_CAD), color='goldenrod')+
  geom_line(aes(y=ADA_CAD), color='blue')+
  labs(title='Nrmlized Prices (gold=BTC, blue=ADA)', x='', y='normalized prices')

One thing to note is that contrary to info here (for example), the distribution of the newly normalized data is similar but not the same as original data:

Standardization

• use the built-in 'scale' function in R

## z-score scaling
crypto_scale <- as.data.frame(scale(crypto_data[2:3]))
crypto_scale <- crypto_scale %>% rename(
  BTC_CAD_scale=BTC_CAD,
  ADA_CAD_scale=ADA_CAD
)
## bind the values back to original data set, with dates
crypto_scale <- bind_cols(crypto_data, crypto_scale)

Here we see again that the distributions are similar but not the same before/after. This is expected with standardization: distributions shift more toward normal curve.

Create line plot:

p_std <- crypto_scale %>% ggplot(aes(x=date))+
  geom_line(aes(y=BTC_CAD_scale), color='goldenrod')+
  geom_line(aes(y=ADA_CAD_scale), color='blue')+
  labs(title='Stdized Prices (gold=BTC, blue=ADA)', x='', y='standardized prices')

Compare the two methods:

Very similar results, although interesting to see how the Standardized view on the right has a bit more spread for ADA relative to BTC, as values are not constrained between upper and lower bound. This may be a more accurate reflection of the higher volatility of ADA.

So if we focus on the Standardized version, what can we learn from this view, relative to the questions we want to answer? A few things to unpack so let me make take a stab at it:

• we see the overall upward trend in both data sets, with peaks and valleys along the way, at different points for each currency
• lots of volatility in each data set, with a bit relative volatility in ADA: including higher peaks and lower lows.
• ADA was slower off the mark than BTC at the beginning of the year, peaked a bit after, trended down in concert with BTC and then took off, before falling more consistently and harder than BTC at the end of the year.

Now, with dual-axis charts like the companion versions in the previous post, we can draw similar conclusions, depending on how we configure the two axes – which is the crux of the problem. The point is this:

with a standardized comparison, we can make these conclusions with more confidence.

Percentile comparisons

Data can also be scaled using percentiles, resulting in a scale between 0 and 100, based on the ranking of each value. Doesn't seem to be a recommended for machine learning feature engineering, but could have application for comparisons as alternative to dual axis charts, where a more even comparison is wanted.

The tidyverse has a handy 'percent_rank' function for easy calculation.

crypto_data_pctl <- crypto_data %>% mutate(
  BTC_CAD_pctl=percent_rank(BTC_CAD),
  ADA_CAD_pctl=percent_rank(ADA_CAD)
)

p_pctl <- crypto_data_pctl %>% ggplot(aes(x=date))+
  geom_line(aes(y=BTC_CAD_pctl), color='goldenrod')+
  geom_line(aes(y=ADA_CAD_pctl), color='blue')+
  scale_y_continuous(labels=percent_format())+
  labs(title='Prcntile Prices (gold=BTC, blue=ADA)', x='', y='percentile rank')
p_pctl

Also appears potentially usable although does seem to diverge from the other two approaches. Compare all three methods:

The percentile version stands out as having different relative patterns compared to the other two. Possibly because by definition data points for each of the currencies will include all the same percentile values (0 to 100 in increments of n/100 where n=number of rows in the data). The only difference in the two will be the order in which the values occur in time, by date. So the percentile approach is one to avoid if the relative amount, and not just rank position, matters.

Conclusion – Scenario 1

There are some viable alternatives to dual-axis charts when trying to answer questions about the relationships/trends between to metrics on wildly different scales. In particular, comparing percentage changes can provide insights without the hazards of dual-axis charts.

Scenario 2: Comparing a Count and a Ratio

In other cases, we may want to compare patterns in a volume or count metric with a related key indicator. Here's an example using some Google Analytics data for a website:

• daily users
• daily conversion rate

Interesting questions with these metrics can inlude:

• what is the relationship between patterns in site traffic and conversion rates?
• do increases in daily users correspond to decreases in conversion rates or vice versa?
• are there any points where breaking of the typical relationship between these metrics warrants further investigation?

Quick look at the data:

Approaches shown above can be applied to this data as well. So we'll focus on some further alternatives particularly well-suited to this type of data.

Line chart with Bar chart below

We saw above how line charts stocked above and below can be easier to interpret that dual-axis charts. In the previous blog post, there was an example of how a dual-axis chart can be improved by combining a line chart for a percentage/ratio metric with a bar chart for a volume metric. We can take from each of these examples and set up a line chart with a bar chart below it – similar to the typical way stock market charts display price data with volume data underneath.

Here we're abandoning any attempt to align the scales in favour of considering the metrics separately but within the same context of time frame. This also:

• frees us up to adjust the height ratio of the charts in order to focus on the key metric of interest.
• the separation reduces confusion over the scales, as there is less ambiguity.
• allows us to add context like a trend (regression) line without creating the confusion and clutter that we would have on a dual-axis chart.

My view is that these changes make it easier and more intuitive to arrive at the same conclusions from the previous blog post: conversion rates rising and fall out-of-sync with variations in user counts.

Again, this gets us quicker to follow-up questions and directed investigation, as well as enabling us to communicate what we are seeing in the data more clearly with others.

Conclusion – Scenario 2

As with previous scenario, there are viable alternatives to comparing a volume metric with a rate or ratio metric, even though they are on very different scales.

Overall Conclusion

With these viable alternatives to dual-axis charts, there should be few cases where an analyst needs to succumb to the temptation of the dual-axis monster. The solutions offered here, or variations on them to fit your needs, will usually provide more clarity and better reveal the answers to your data questions.

The Temptation of Dual-Axis Charts

Sometimes when we are trying to show relationships over time between two dimensions of the same metric, or two separate metrics, we run into a situation where differences in the scales involved make it difficult/impossible to really tell what is going on using a standard line chart. One solution we may be tempted to turn to: a dual-y-axis chart, with an axis on the left for the scale that fits one dimension or metric, and a separate scale on the right that fits the other dimension or metric.

Tempting, but risky. This post walks through a couple of scenarios and demonstrates why due care and attention is needed to avoid pitfalls of dual-axis charts. I will follow-up with a post that highlights some alternatives for consideration, instead of giving in to the dual-axis temptation.

The Trap of Dual-Axis Charts

The general idea here is that dual-axis charts should be avoided if possible, because they suffer from at least two flaws:

1. Misrepresentation due to mixed scales: changing relative scales arbitrarily can suggest different conclusions and imply relationships that may not be as strong (or weak) as they appear.
2. Difficultly in interpretation: these charts require extra mental effort to untangle the lines and associate them with their respective data points.

What the Experts Say: Avoid

Data visualization experts generally recommend against the use of dual-axis charts, for similar reasons cited above (and sometimes more). For example, in the book ‘Better Data Visualizations’* by Jonathan Schwabish, he has a section called ‘Avoid Dual-Axis Line Charts’ that covers similar territory to what is discussed here.

( affiliate link for a book I wholeheartedly recommend for any data viz professional)*

With that baseline-setting, let’s dig into some scenarios and examples to make the point more clearly. As usual, the examples here are produced using R, with ggplot2 package as the preferred visualization tool.

Two common scenarios for dual-axis charts

There are two common scenarios where dual-axis charts become tempting:

1. Comparing trends in two data sets that have vastly different scales.
2. Comparing a volume metric with a related rate or ratio metric. (so, again, vastly different scales)

Most charting tools allow for the creation of dual-axis charts. Most relevant for our purposes, this can be done in ggplot2:

ggplot2 dual-y-axis reference

Scenario 1: Compare trends in similar metrics from two datasets

Suppose we are interested in crypto currencies and are curious about how a crypto currency like Cardano, with its token ADA compares against Bitcoin (BTC).

As always, the visualization choice should be based on what questions we are trying to answer, what we are hoping to learn, and, ultimately what decisions we want to make.

If we don’t have a specific objective beyond curiousity and want to start with general exploration, we still need to frame up our exploration. Our first thought may be to compare prices over a recent period, to answer questions like:

• What are the relative changes in the currencies over time?
• Do the two follow a similar pattern of ups and downs?
• Are there any points where a general pattern breaks? (could provide focus for further investigation)
• Eventually: are there ways we can take advantage of these patterns to make investment decisions? (probably beyond initial scope but helps to have that broader perspective)

So we gather some price data (Cdn$). Here is a random sample of rows, along with summary data. We can see the two sets of prices are on much different scales.

Basic line chart

This is confirmed in a basic line chart produced in ggplot2: (click into code block and scroll/drag horizontally)

crypto_data %>% ggplot(aes(x=date))+geom_line(aes(y=BTC_CAD), color='gold')+
  geom_line(aes(y=ADA_CAD), color='blue')

This shows us the pattern in Bitcoin prices but due to difference in scales, doesn’t help much with any of our questions around comparing patterns between the two currencies.

Dual-axis option

A common approach, then, is to use dual y-axis, with different scales on each, to enable visibility of the data side by side. This can be a trap if not managed carefully, though. There are two key questions to ask:

1. what should the range of the second axis be?
2. How to make it as easy as possible for user to interpret, without having to wrap their heads around lining up different data with different axes.

Depending on your tool of choice, you may have a variety of options. For example, default two-axis chart in Google Sheets looks like this:

It takes a bit to get oriented and in this case depends on your knowledge that BTC has much higher prices, so must be the left axis, and ADA is on the right axis. (this could be handled with better axis labelling)

The thing I notice here is that once I get settled that the blue line is Bitcoin, I can easily line up the start against 40,000 or so and as I follow the trend over time and get near the end, my eye gravitates toward the closer axis on the right – just under 3. Then I realize that doesn’t make sense and I switch back to the left axis for reference. So my brain is spinning a bit. Similarly with the red ADA line, where my tendency at the start of the period is to associate it with the left axis, then adjusting to look all the way over to the right axis and continue from there.

Once we get through that, the dual-axis does provide a way to view the two data sets alongside each other with much more granularity than the previous chart. In terms of the patterns we are looking to discover, it shows a relatively close relationship in trends and some points, divergence at others. Both have trended up over time. Maybe Cardano is more volatile, prone to relatively higher peaks and lower troughs?

Before we go too far with our conclusions, though, there are some further subtleties that we should be aware of.

Relative Scales Matter

The above Google sheets chart is based on an automatically-selected ratio of 25000:1 in the two axes. This automatic selection may be suitable in some cases, but not necessarily all.

In R, ggplot2 includes the option to add a secondary y-axis and set the transformation from the left axis to the right axis. This provides flexibility, but also comes with a caution:

• the choice of relative scales can spin the interpretation of the data in different ways, as shown in the examples below: (based on 4 charts built with the code shown below but different transformation values)

## select a relevant transformation factor
transfm <- 5000
col_left <- 'darkgoldenrod3'
col_right <- 'blue'

ch_title <- paste0('BTC vs ADA (scale ratio: ', transfm,')')
cd1 <- crypto_data %>% ggplot(aes(x=date)) +
  geom_line(aes(y=BTC_CAD), color=col_left)+
  geom_line(aes(y=ADA_CAD*transfm), color=col_right)+
  # Custom the Y scales:
  scale_y_continuous(
    # Features of the first axis
    name = "BTC",
    # Add a second axis and specify its features
    sec.axis = sec_axis(~./transfm, name="ADA")
  )+labs(title=ch_title, x="")

grid.arrange(cd1, cd2, cd3, cd4, nrow=2)

Note: GOLD line = Bitcoin, BLUE line = ADA. The charts are simplified for this demo; in a more formal presentation, the data-axis match would be made more clear on the charts themselves.

In terms of relative scales, the smaller the ratio used, the more the secondary axis is stretched out. The result:

• top-left: the lowest ratio used (5000:1) compresses the ADA (blue) line, revealing some patterns but suggesting it is relatively stable compared to BTC (gold) line.
• cycling through a few variations shows the impact of various relative scales.
• lower-left: 25000:1 ratio as the Google sheets example.
• lower-right: by the time we get here, we have flipped the story to where ADA (blue) looks like the volatile one, soaring to great heights and then crashing back down, while BTC (gold) is relatively quiet.

I’m not sure there is a clear/correct/easy answer here. It is just a hazard that comes with these charts, something to watch out for when data is presented this way, and a reason to be wary of using dual-axis charts.

The bottom line is that the answers to our questions may vary with the ratio between the two scales, may lead us to different conclusions, may cause us to take or recommend different actions depending on which view we are looking at, even though it is the same data.

Ratio by Calculation

As far as finding a fair/reasonable transformation value, one way to go about it may be to calculate an overall ratio of the two datasets. For example, rather than picking a number:

transfm <- median(crypto_data$BTC_CAD)/median(crypto_data$ADA_CAD)

Median BTC vs ADA is 34242.71 so this could be useful for transformation, at least as starting point. You could 1) use this directly, as below, or 2) use it to guide you toward a nearby number that is a nice, round number to work with. For example, a reasonably close multiple in this case is 10,000 (chart in the top-right of the 4 charts above). This is much easier for a person to relate to when comparing the two axes, making it slightly less daunting.

ch_title <- paste0('BTC vs ADA (scale ratio: ', transfm,')')
crypto_data %>% ggplot(aes(x=date)) +
  geom_line(aes(y=BTC_CAD), color=col_left)+
  geom_line(aes(y=ADA_CAD*transfm), color=col_right)+
  annotate("text", x=date('2021-02-20'), y=75000, label='BTC', color='goldenrod', size=6)+
  annotate('text', x=date('2021-05-26'), y=100000, label='ADA', color='blue', size=6)+
  # Customize the Y scales:
  scale_y_continuous(
    labels=dollar_format(),
    # Features of the first axis
    name = "BTC",
    # Add a second axis and specify its features
    sec.axis = sec_axis(~./transfm, name="ADA")
  )+labs(title=ch_title, x="")

Conclusion – Scenario 1

The takeaway for me here is confirmation of the ‘best practice’ warnings to avoid the tempatation of dual-axis charts: both because of how the story in the data can be manipulated and for the mental gymnastics required to parse out what that story is.

Let’s see if this conclusion holds up for another scenario…

Scenario 2: Comparing a Count and a Ratio

In other cases, we may want to compare patterns in a volume or count metric with a related key indicator. Here’s an example using some Google Analytics data for a website:

• daily users
• daily conversion rate

Interesting questions with these metrics can include:

• what is the relationship between patterns in site traffic and conversion rates?
• do increases in daily users correspond to decreases in conversion rates or vice versa?
• are there any points where breaking of the typical relationship between these metrics warrants further investigation?

Quick look at the data:

Again, very different scales – no surprise there.

Dual-axis Lines

transfm <- median(ga_data$users)/median(ga_data$conv_rate)

col_left <- 'blue'
col_right <- 'purple'

ch_title <- "Website users vs conversation rates"
ch_sub <- paste0("(Ratio: ",transfm,")")
ga_data %>% ggplot(aes(x=date)) +
  geom_line(aes(y=users), color=col_left)+
  geom_line(aes(y=conv_rate*transfm), color=col_right)+
  # Custom the Y scales:
  scale_y_continuous(
    # Features of the first axis
    name = "users", labels=comma,
    # Add a second axis and specify its features
    sec.axis = sec_axis(~./transfm, name="conversion %", labels=percent)
  )+labs(title=ch_title, subtitle=ch_sub, x="")+
  theme(axis.text.y.left = element_text(color=col_left, size=8),
        axis.title.y.left = element_text(color=col_left, size=12),
        axis.text.y.right = element_text(color=col_right, size=8),
        axis.title.y.right = element_text(color=col_right, size=12))

Looks pretty messy but there does seem to be some degree of opposite movement in these two metrics. There is also the precipitous drop in conversion rate at the start of the period that is probably worth looking into, especially since there is little change in volume of users at that point.

Dual-axis with Bar Chart

When working with two different types of metrics, a variation on the line charts that can help to bring out the message within the data is to combine bar chart and line chart.

• bar chart to represent count or volume metrics
• line chart for ratio or rate metrics

In the example below, I have changed the transformation ratio from the median calculation (~26 million) to 10 million. This maybe provides a more intuitive way to interpret the relationship: it is easy to see that when the scale doubles on one side, the other scale doubles as well.

transfm <- median(ga_data$users)/median(ga_data$conv_rate)
transfm <- 10000000
col_left <- '#009E73'
col_right <- 'darkblue'

ch_title <- "Website Users vs Conversion Rates"
ga_data %>% ggplot(aes(x=date)) +
  ## change line to bar chart for contrast
  geom_col(aes(y=users), fill=col_left)+
  geom_line(aes(y=conv_rate*transfm), color=col_right, size=1)+
  # Custom the Y scales:
  scale_y_continuous(
    # Features of the first axis
    name = "users", labels=comma,
    # Add a second axis and specify its features
    sec.axis = sec_axis(~./transfm, name="conversion %", labels=percent)
  )+labs(title=ch_title, x="")+
  theme(axis.text.y.left = element_text(color=col_left, size=10),
        axis.title.y.left = element_text(color=col_left, size=14),
        axis.text.y.right = element_text(color=col_right, size=10),
        axis.title.y.right = element_text(color=col_right, size=14))

It does seem somewhat easier to untangle the relationship using the bar and line combination, along with the even 10M:1 ratio. From this view, it looks like there is no consistent pattern in the trends between the two metrics, with conversion rate sometimes rising with user count increases, sometimes dropping. So that provides us with some answers to our initial questions about the relationship.

We are still left with the same challenge, initially at least, of wrapping our heads around what is going on here, which axis is which, what the relative values are.

Conclusion – Scenario 2

This second scenario confirms that even for a different use case, dual-axis charts are problematic. The competing scales are an issue and switch to a combo of bar and line may help, but doesn’t remove all the problems.

Tips

As the above examples highlight, if you are unable to resist the temptation to display your data in a dual-axis chart there are some things to do in order to make it as easy as possible for users to extract meaning:

• make sure both axes are carefully and clearly labelled, with color or other signals to associate the axis to the respective data.
• consider mixing bars (vol) and lines (ratios), although don’t rely on this to solve the major problems.
• be responsible: don’t contort scales to fit your pre-defined message or beliefs or hopes or other biases.
• make second axis a factor of 10 ratio if feasible, since it is easy for people to translate between the two scales.
• for full transparency, disclose the ratio between the two scales that is being used.

Alternatives to the Double-Axis

On the other hand, if we are to resist the temptation to dual-axis charts, what are the alternatives? I’ll share my thoughts – and examples – in the next blog post.

I get interesting answers – or just confused looks – when I ask job candidates about how they build durability and scalability into their work and this often helps me separate from the newbies from the more experienced analysts. That’s another reason I thought it would be worth discussing.

First let’s get clear on how we’re using these terms: What are we actually talking about when we say a project is ‘scalable‘ or ‘durable‘?

Scalable definition, according to Merriam Webster

Ok, we get that. Now, durable definition, also according to Merriam Webster

I like to think of it as boiling down to two dimensions:

• durability = time
• scalability = size

The idea here is that we should not only think about scale but also about durability when we design, structure and execute our analytics work. This creates additional opportunities for impact and pays dividends over time, especially for projects where scale may not be as much of a factor – which can be a frequent case for lots of analytics work:

• smaller projects that may be repeated in future or may grow with follow-up requests – or reproduced to confirm results.
• projects that may benefit from collaboration or hand-off to other team members.
• work that can be re-used or repurposed down the road to answer similar questions.
• work that helps to build a knowledge repository within the group to expand upon (rather than having to start from scratch).
• work that supports automated processes.
• work that may form MVP for larger scale project that may be undertaken further down the road.

The end result of incorporating durability into your analytics practice will include:

• Quicker turn-around time: using accessible, repeatable processes.
• Better standardization and consistency of deliverables: leveraging established structures, less need to re-think/design from scratch.
• Increased reliability of results, quality control: better reproducibility, less opportunity for manual error, stray formulas/references, over-written values.
• Improved clarity of communication: ease of understanding of your analytical logic by teammates, partners and stakeholders.
• Easier collaboration within the team: smoother transfer of work from one colleague to another, combination of work from different teammates, opportunities to build on work of each other.
• Progressive accumulation of capabilities and impact over time: building an expanding practice, less running in circles.

Ok, so how do we get there? The most obvious and critical step to increasing durability in your work:

• ditch those spreadsheets for R or other statistical processing software of your choosing!

There have been lots of words written and talks given on advantages of R over spreadsheets, so I’m not going to get into that here, other than to focus on durability and a specific and significant advantage.

Even if we have R skills, the spreadsheets can be sooo tempting: we can quickly throw some data together, do some manual manipulation if necessary, create some pivot tables, build some easy charts or attractive tables, send an email to stakeholders, and move on. When we get the follow-up question that requires a change of parameters or additional data, or need to walk a teammate through it, or get a similar question 3 months later…that’s where things fall apart. Yes, spreadsheets have their place but these and other scenarios demonstrate the point: with spreadsheets, you will never achieve as much durability as you can with R – and that durability has significant value.

Once you commit to moving your practice to R, it opens up all kinds of opportunities for increased durability:

• Organizing your code in a logical, easy-to-follow flow that can be understood and adapted over time.
• Use re-usable methods that can be easily modified as needed: such as using variables as often as possible, rather than hard-coding in values.
• Commenting your code for additional understanding of ‘why’ certain choices were made.
• Using version control (GitHub, GitLab, etc) for even small, individual projects to build up a coherent, accessible repository.
• Using RMarkdown to integrate reporting and analysis in with data-processing for even greater logical flow, ease of updating, transparency.
• Extending the workflow to integrated slide decks, flexdashboards, Shiny, etc.
• Providing complete source info within any end products, so origin can be tracked back by anyone.
• Sharing links to the end product stored in a standard location/format, rather than dropping tables or charts into email.

Of course, there are other practices along these lines, but you get the idea – the important thing is the ‘durability’ mindset.

Pretty much all of the above also supports and contributes to scalability as well. On this foundation, it becomes easy to add scalability features, such as:

• writing custom functions to avoid duplicating code.
• combining components from smaller projects to meet the demands of larger ones, since the components are well organized and documented.
• building on pre-existing components and incorporating into machine learning models and workflows.

Ultimately, durability and scalability are inter-twined. The point is not to draw a hard distinction but to promote the idea that even without the need for scalability, durability still matters – and can make a huge difference to your practice and to impact on the business over the long-term, both individually and as a team.

We all know that data is only tool, a resource, a means to an end, not an end in itself. It’s critical that, as analysts, we are able to not just assemble the data but also interpret responsibly it for our audiences. The best books I know for explaining how to do this effectively are Storytelling with Data: A Data Visualization Guide for Business Professionals by Cole Nussbaumer Knaflic, from the more technical data-visualization side, and Nancy Duarte’s DataStory: Explain Data and Inspire Action Through Story focusing on presentation structure.

We also all know that humans have strong biases and pattern-recognition abilities that can cause them to read a story in the data that may not actually be there. Or at least stretching the story beyond what is provided by the data. This is something we have to remain vigilant against. It’s one of the reasons The Scout Mindset: Why Some People See Things Clearly and Others Don’t is such a powerful book.

Take, for example, a recent article about the cryptocurrency Cardano. The key takeaway was summed up concisely in the title, “If You Day-Trade Cardano, It’s Best to Buy on Thursday”, and the article presents data supporting this recommendation. All of which is potentially valuable information.

Before even getting to the data, though, there are some sharp turns into more speculative territory that are not supported by data and don’t appear to stand-up well to logic. The author notes the well-known tendency for Bitcoin price to be more volatile on weekends than weekdays, which can create weekend buying opportunities, at least for short-term gains. This is put down to an Investing.com explanation that lower trading volume on weekends leads to trades of ‘weekday’ size moving prices more on the weekend. Ok, makes sense and verifiable with data.

It is the next step – more of a leap, really – that goes too far: the author quotes the Investing.com article’s claim that weekend price volatility is accentuated by the fact that banks are closed on the weekend.

“Banks are closed over the weekend and so investors are unable to add more money into their accounts….They are trapped if the price falls over the weekend and…unable to make a profit if prices surge…”

– Investing.com

Say what, now? Even though I’m not the most sophisticated person when it comes to finance, it has a been a very long time since I visited a bank any day of the week for anything other than a cash machine. So I somehow doubt that people who are actively participating in the world of DEFI – which has at its core purpose the disruption of traditional financial institutions – are somehow held back from trading crypto and ‘trapped’ by reliance on physical bank branches.

Side note: it’s not just me – the comments on the Investing.com article highlight the ridiculousness of this theory.

The Cardano article goes on to make the case that there are “different trading behaviours between Bitcoin and up-and-coming altcoins” which is laid out convincingly with data. Again, though, we leap beyond what is in the data.

These data-based insights are combined with the questionable “banks are closed on weekends” theory to somehow make Cardano the “people’s cryptocurrency” and leads to the declaration that “Cardano is a ‘Blue-Collar’ Crypto”.

“It’s the regular folks, those with nine-to-five job, that are buying ADA.” (Cardano coin)

– If you Day-Trade Cardano, It’s Best to Buy on Thursday

Well, maybe. But how would the author know that? How can we get there from the data? What other evidence supports this?

Further down, the author admits ‘it is not entirely clear‘ what causes the price patterns observed in the data and that his proposed explanation is an ‘opinion‘. Fair enough, but that is well after the theories are rolled out with an air of certainty that is just not there.

And the thing is: so what? What difference does it make? The data shows that there is, at least for now, a probabilistic ‘Thursday’ advantage to buying Cardano that we can profit from. Why do we need to invent reasons why this is the case? The human need for causal relationships is powerful, indeed.

Stories can be effective for turning data into knowledge and turning that knowledge into action. But as analysts, I believe we need to be extra careful that those stories are supported by data. I work in an exceptionally creative organization, where there is no shortage of storytellers with theories and opinions about what is causing all manner of outcomes. It makes for a vibrant culture and thought-provoking discussion. The way I see it, the reason we are invited to the table as data analysts is to speak for the data, to weave it into stories that bring the data to life, but to resist the very human temptation to go beyond. It can be a fine line, but our credibility depends on it.

• what topics are trending?
• what are trends for a given search term?
• how does this compare to other terms?
• what related terms are people using?
• how does interest vary by region?

And lots more.

Google Trends has recently passed its 15th birthday, prompting a Google blog post on “15 Tips for Getting the Most Out of Google Trends”. One thing they noted right at the start is that they used Google Trends to identify search queries related to Google Trends in order to prioritize content for a blog post. This is a classic use case – using Google Trends to fuel content decisions for your marketing.

An important point is that Google Trends does not represent or translate to an actual number of searches. This search interest presented is indexed between 0 and 100, where 100 indicates the peak search interest during the particular date range reported on. Everything is relative in Google Trends.

Going Beyond the Interface

Google Trends is a convenient, intuitive interface packed with info that can be great for playing around with, but comes with limitations for tracking trends over time, sharing with others, analyzing more deeply. You can export to a spreadsheet, but this comes with inefficiencies as well: you have to export data for each component in the interface separately and you may lose source information. And if you want to make any adjustments (change date range, geography, category, add terms) you have to go back in there and repeat the process.

Using R to work with Google Trends can provide a more efficient solution if you want to:

• quickly import Google Trends data into R for further analysis.
• grab all the modules from the Google Trends interface at once (interest over time, geo data, related topics, related queries).
• repeat Google Trends reporting to monitor trends over time.
• reproduce the same Google Trends data in future, based on detailed record of your query.
• integrate Google Trends reporting with other datasets or reporting structures.

Google Trends in R

Google does not provide an official API for Google Trends but the gtrendsR R package created by Philippe Massicotte is a major helper in the accessing Google Trends data within R for reporting and analysis. Along with all the benefits of using R to process and analyze data, the gtrendsR package provides some big advantages over using the Google Trends Interface:

• Durability: don’t have to go to the interface and fetch the data each time, you have an on-going reference with source info. You have code that can be referred to, re-used, and shared with others.
• Scalability: can expand on existing queries, going beyond the limit of 5 that are available in the tool.

Google Trends Parameters

Google Trends has a number of parameters that can be used to fine-tune your search: date ranges, geo data, categories, Google properties. These are available via the gtrendsR package, corresponding to the options in the Google Trend online interface. You just have to know how to tap into them:

• Dates:
  • “now 1-H”: last hour – by MINUTE
  • “now 4-H”: last 4 hrs – by MINUTE
  • “now 1-d”: last day – every 8 MINUTES
  • “now 7-d”: last 7 days – HOURLY data
  • “today 1-m”: last 30 days – DAILY data
  • “today 3-m”: last 90 days – DAILY data
  • “today 12-m”: last 12 months – WEEKLY data
  • “today+5-y”: last 5 yrs (default) – WEEKLY data
  • “all” since beginning of Google Trends 2004
  • “YYYY-MM-DD YYYY-MM-DD”: custom start / end date – granularity will depend on time spans above
• Geo:
  • use gtrendsR::countries to see complete list
  • close to 110,000 options, including country / state / city
  • code below shows how to filter for countries
  • geo=“” for all countries
• Categories:
  • use gtrendsR::categories
  • over 1,400 categories, with ids that are used in the query
  • category = 0 for all categories
• Google properties:
  • specify one or more of ‘web’, ‘news’, ‘images’, ‘froogle’, ‘youtube’
  • gprop=c(“web”, “youtube”) as example for web and youtube search

Setup – Libraries

There’s basically no setup required – no credentials, etc. Only need to load the gtrendsR package. (I’ve pre-loaded other packages I’m using for general purpose, such as ‘tidyverse’, etc.)

library(gtrendsR) ## package for accessing Google Trends - all you need to get going!

Single term query

Using the gtrendsR package to get Google Trends for a single search term.

## basic search
gt_results <- gtrends(keyword='cryptocurrency',
        geo="",
        time="now 7-d",
        gprop=c("web"),
        category=0)

The query returns a bundle of 7 data frames with different info, reflecting what is shown in the Google Trends interface:

names(gt_results)

## [1] "interest_over_time"  "interest_by_country" "interest_by_region"  "interest_by_dma"     "interest_by_city"   
## [6] "related_topics"      "related_queries"

(see screenshot of Google Trends interface for comparison)

Interest over time

The ‘interest_over_time’ data frame is the main data object, with relative search volume for the selected search term, country, period, property, and category.

chart_title <- "Searches for: cryptocurrency"
sub_title <- "Period: past 7 days; Geo: world; Prop: 'web'; Category: all"

## create chart based on search interest over time
gt_results$interest_over_time %>% ggplot(aes(x=date, y=hits, color=keyword))+geom_line()+
  labs(title=chart_title, subtitle=sub_title, x="", y="")

Related Topics

The ‘related_topics’ data frame holds data on queries related to the main search term (‘cryptocurrency’ in this case).

str(gt_results$related_topics)

## 'data.frame':    35 obs. of  5 variables:
##  $ subject       : chr  "100" "9" "8" "6" ...
##  $ related_topics: chr  "top" "top" "top" "top" ...
##  $ value         : chr  "Cryptocurrency" "Bitcoin" "Investment" "Coin" ...
##  $ keyword       : chr  "cryptocurrency" "cryptocurrency" "cryptocurrency" "cryptocurrency" ...
##  $ category      : int  0 0 0 0 0 0 0 0 0 0 ...
##  - attr(*, "reshapeLong")=List of 4
##   ..$ varying:List of 1
##   .. ..$ value: chr "top"
##   .. ..- attr(*, "v.names")= chr "value"
##   .. ..- attr(*, "times")= chr "top"
##   ..$ v.names: chr "value"
##   ..$ idvar  : chr "id"
##   ..$ timevar: chr "related_topics"

• subject: relative value to main search term
• related_topics: contains ‘top’ topics and ‘rising’ topics
• value: related topic
• keyword: main search term
• category: search term category, if applicable

  chart_title <- "crytopcurrency: related topics"
  ## 
  top <- gt_results$related_topics %>% filter(related_topics=='top' & !is.na(subject) &
                                                subject!='<1')
  ## convert value to factor and subject to numeric
  top$value <- as.factor(top$value)
  top$subject <- as.numeric(top$subject)
  ## PLOT related topics
  top %>% ggplot(aes(x=reorder(value, subject), y=subject))+geom_col()+
  coord_flip()+
    scale_y_continuous(expand=expansion(add=c(0,10)))+
    labs(title=chart_title, y='', x='')

Related Queries

Related Queries module has similar structure to Related Topics:

str(gt_results$related_queries)

## 'data.frame':    50 obs. of  5 variables:
##  $ subject        : chr  "100" "91" "66" "56" ...
##  $ related_queries: chr  "top" "top" "top" "top" ...
##  $ value          : chr  "cryptocurrency price" "crypto" "best cryptocurrency" "cryptocurrency news" ...
##  $ keyword        : chr  "cryptocurrency" "cryptocurrency" "cryptocurrency" "cryptocurrency" ...
##  $ category       : int  0 0 0 0 0 0 0 0 0 0 ...
##  - attr(*, "reshapeLong")=List of 4
##   ..$ varying:List of 1
##   .. ..$ value: chr "top"
##   .. ..- attr(*, "v.names")= chr "value"
##   .. ..- attr(*, "times")= chr "top"
##   ..$ v.names: chr "value"
##   ..$ idvar  : chr "id"
##   ..$ timevar: chr "related_queries"

  chart_title <- "crytopcurrency: related queries"
  ## 
  top <- gt_results$related_queries %>% filter(related_queries=='top' & !is.na(subject) &
                                                subject!='<1')
  ## convert value to factor and subject to numeric
  top$value <- as.factor(top$value)
  top$subject <- as.numeric(top$subject)
  ## PLOT related topics
  top %>% ggplot(aes(x=reorder(value, subject), y=subject))+geom_col()+
  coord_flip()+
    scale_y_continuous(expand=expansion(add=c(0,10)))+
    labs(title=chart_title, y='', x='')

You can see right there the possibilities for content marketing: if you’re a crypto currency blogger, for example, you may want to write about how to identify the ‘best cryptocurrency’, etc.

Multi-Term Query

The same approach used to query for single terms can be extended to multiple terms. The example below shows how to load up a collection of terms, as well as leveraging other variables for the query.

## create list of multiple search terms
srch_term <- c("cryptocurrency",
               "bitcoin",
               "ethereum",
               "stock market",
               "real estate")
period <- "today 12-m"
ctry <- "" ## blank = world; based on world countries ISO code
prop <- c("web")
cat <- 0 ## 0 = all categories

## user-friendly versions of parameters for use in chart titles or other query descriptions
ctry_ <- ifelse(ctry=="","world",ctry)
prop_ <- paste0(prop, collapse=", ")
cat_ <- ifelse(cat==0,"all",cat)

## use gtrendsR to call google trends API
gt_results <- gtrends(keyword=srch_term,
        geo=ctry,
        time=period,
        gprop=prop,
        category=cat)

The gt_results object returned is the same as with single query, just has more values for the ‘keyword’ variable in each data frame.

Interest over time

chart_title <- paste0("Search trends: ", paste(srch_term[1:2], collapse=", "), " +")
sub_title <- paste0("Period: ", period, "; Geo: ", ctry_, "; Prop: ", prop_, "; Category: ", cat_)

## create chart based on search interest over time
gt_results$interest_over_time %>% ggplot(aes(x=date, y=hits, color=keyword))+geom_line()+
  scale_y_continuous(expand=expansion(add=c(0,0)))+
  labs(title=chart_title, subtitle=sub_title, x="", y="")

As noted, there is the same limitation as the interface of maximum five terms at a time. Of course, one of the beauties of doing thing programmatically is that provides opportunities for combining queries to go beyond five. For that, you need to make sure there is a common term in each queries to calculate the relative values. This is a topic for another blog post.

Search Terms vs Search ‘Topics’

Tip #3 in “15 Tips for Getting the Most Out of Google Trends” mentions the importance of choosing search ‘topics’ when available for a given search term.

Using a topic version of the term has benefits, but also complications for programmatic access:

• you need to go to Google Trends to check if a topic is available (it may also be called something different than ‘topic’, like ‘currency’ for the term ‘Bitcoin’).
• the topic term is an indecipherable code, as circled in the URL in the browser bar above.
• comparisons may be skewed if mixing terms and topics.

The best way to handle this situation:

1. If in doubt, go to Google Trends and determine if there is a topic for your term.
2. If you want to use the topic, copy the code from the URL.
3. The term is encoded so you can drop it in a URL decoder OR…just replace the ‘%2Fm%2F’ with ‘/m/’ and use the rest as is.
4. Use this in your query – it will still work with gtrendsR…BUT…it won’t mix with search terms in the same query. You can still do multiple topic terms, even if identified as different topics, but you can’t mix terms and topics.

So you may have to decide between using the topic version or the basic search term, depending on your needs. As in the example above, both versions tend to trend the same, with the topic having higher volume. No guarantees, though.

Example with topics

## create list of multiple search terms using topic codes, separated by commas in URL and decoded
srch_term <- c("/m/0vpj4_b",
               "/m/05p0rrx")

srch_topic <- c("Cryptocurrency_topic",
                "Bitcoin_currency")

period <- "today 12-m"
ctry <- "" ## blank = world; based on world countries ISO code
prop <- c("web")
cat <- 0 ## 0 = all categories

## user-friendly versions of parameters for use in chart titles or other query descriptions
ctry_ <- ifelse(ctry=="","world",ctry)
prop_ <- paste0(prop, collapse=", ")
cat_ <- ifelse(cat==0,"all",cat)

## use gtrendsR to call google trends API
gt_results <- gtrends(keyword=srch_term,
        geo=ctry,
        time=period,
        gprop=prop,
        category=cat)

## replace codes with topics
## - extract interest_over_time data frame
gt_interest <- gt_results$interest_over_time
## - replace codes with corresponding terms
gt_interest <- gt_interest %>% mutate(
  keyword=ifelse(keyword==srch_term[1],srch_topic[1],
                 ifelse(keyword==srch_term[2], srch_topic[2],""))
)

## create chart based on search interest over time
pint1 <- gt_interest %>% ggplot(aes(x=date, y=hits, color=keyword))+geom_line(size=2)+
  scale_y_continuous(expand=expansion(add=c(0,0)))+
  scale_color_manual(values=c("red","blue"))+
  theme(legend.position = 'top')+
  labs(x="", y="")

pint2 <- gt_interest %>% group_by(keyword) %>% summarize(avg_int=mean(hits)) %>%
  ggplot(aes(x=keyword, y=avg_int, fill=keyword))+geom_col()+
  scale_y_continuous(limit=c(0,100))+
  scale_fill_manual(values=c("red","blue"))+
  theme(legend.position = 'none',
        axis.text.x = element_blank())+
  labs(x="Average", y="")

grid.arrange(pint2, pint1, nrow=1, widths=c(2,8))

Wrap-up and Additional References

There’s lots more you can do with Google Trends and gtrendsR. I hope to cover some more ideas in future blog posts. In the meantime, hopefully this is a helpful start!

References

Other useful references for working with gtrendsR and Google Trends:

• gtrendsR vignette
• https://blog.quiet.ly/industry/exploring-google-trends-explore-function-finding-keywords-queries/
• https://blog.google/products/search/15-tips-getting-most-out-google-trends/
  • the more authoritative and recent blog post mentioned above

Happy trending!

You may also find that, each time you set up a new R Markdown document, you find yourself deleting the standard template code and then typing in a lot of the same code each time to get started: header settings, code block options, adding your favourite libraries, and other tricks you have picked up over time. And of course that means you have to remember all that stuff – not hard, but, as programmers, we’re always looking for ways to cut corners, remove tediousness, get straight to work, right?

(For long-time readers who are wondering what this has to do with web analytics…a) this blog has expanded beyond web analytics; b) to see the surprise twist that relates to Google Analytics jump to the Support Files section

R Markdown Streamlining Options

There are a few options to avoid all the remembering/re-typing every time you start a new R Markdown document:

1. Copy another recent/similar document, keep what you need, delete everything else: all well and good but you’re a productive person with lots on the go, each project has its own nuance and it would be nice not to have figure out which project fits best, find it, copy files, remove extra code, etc.
2. Use a pre-configured template from a package that has one or more templates bundled in it: the Templates section in the R Markdown Gallery has some solid recommendations, several of which are geared toward specific purposes, like meeting the guidelines for the Journal of Statistical Sciences. But that may not quite fit with your jam.
3. CREATE YOUR OWN CUSTOM TEMPLATE: which takes a bit of work but can we well worth the effort, is what this article is about, and I’m assuming if you read this far, is what you are up for!

Custom Template Creation

The thing with R Markdown templates is that you need to create an R package to hold the template and to make it available within R Studio. This can sound intimidating to some folks – I know it did for me – but if you don’t have experience with packages, rest assured: it’s a straightforward process and once you go through it you will see the benefit.

One of the benefits is that you can roll multiple different markdown templates into the same package, so that you can access a collection for different use cases from that one source.

Essentials

The essential process for creating a template is:

1. Create a new package project in R Studio.
2. Add specific folder: inst > rmarkdown > templates > template folder name of your choosing > skeleton
3. skeleton.Rmd: In the ‘skeleton’ folder, add a skeleton.Rmd file and customize it to your purposes.
4. template.yaml: One level up, in the folder named after the template (‘template folder name of your choosing’) add a template.yaml file that specifies the name of the template, as you want it to appear in the template list for new file R Markdown files in RStudio.

And that’s basically it for the setup. From there, you can install the package on your local machine and once installed the template(s) from the package will be available in your list of templates when you go to start a new markdown document. You can also push the package to a Git repo for sharing with others, continue developing/expanding.

Details

So let’s breakdown the above into some more specifics.

1. Create a new package project in R Studio

To create a new project in R Studio that has out-of-the-box components for a package:

1. Create Project > New Directory > R Package using devtools
2. Directory Name: provide a name that you have chosen for your template package
3. Description [optional]: add details to the DESCRIPTION file if you like

2. Add a specific folder to the package

With the project setup, now you need to add a specific folder structure for R Markdown templates:

• inst > rmarkdown > templates > template name of choice > skeleton
  • as Chester Ismay pointed out in his helpful article on the same topic you can use dir.create function: dir.create(“inst/rmarkdown/templates/<template name>/skeleton”, recursive=TRUE)

3. skeleton.Rmd file

Add a file called ‘skeleton.Rmd‘ to the skeleton folder, same as you would any new R Markdown file. This is the file that will serve as the actual template. Customize it with whatever settings & code you would routinely be adding for your markdown files, so that it will be replicated when you use the template.

This might include things like:

• customized yaml headers …

• initial settings, default libraries to include…

• default text / code blocks …

…and much more to suit your needs!

Once you have the customizations in place, test by knitting as you normally would. When you have confirmed that the results are as expected, delete the skeleton.html output file – otherwise it will show up when you use the template.

4. template.yaml file

A file called ‘template.yaml‘ is required immediately within the template folder, at the same level of the ‘skeleton‘ folder to provide meta data for the template.

Content for template.yaml:

• name: the name of the template as you want it to appear in the R Markdown template list (seems to work either in quotes or not).
• description: apparently optional and i’m not even sure how it is used, but things ran smoother for me in several tests when I included it.
• create_dir: optional but useful because it specifies whether or not a new folder is created containing your .Rmd file based on the template.

Now your template is ready to use.

Using the Template

Using the template is as easy as:

1. Installing your new package by running: devtools::install(“<path>/<template package name>”)
2. Load the new package: library(<template package name>)

Should look something like this:

Extras

That hopefully gives you the idea and enough info to move forward with confidence. Of course, there is lots that can be done to extend the convenience of templates. A couple common enhancements are putting the package on Github and adding support files that work with the template. This is where the benefit of being able to bundle multiple templates in the same package comes in.

Github Repo

If you use Github (and I hope you do!), you know the benefits of code management, sharing, version control, etc. Once you have your package set up on your machine, there are a couple of options. I’m no Github expert, so I took the path of least resistance: created a new Github repo with same name as my R package project and followed the instructions from there.

Support Files (css etc)

Beyond the markdown settings/code that you add to the skeleton.Rmd file, you can also assemble support files that can be used in conjunction with skeleton.Rmd for extra bells and whistles. There are placed in the same directory as the skeleton.Rmd file and then referenced from within skeleton.Rmd. Some great reference info/examples on the R Markdown Templates website .

For example, I have a need for a template for web pages that use standard navigation and also Google Tag Manager code snippets for Google Analytics:

Conclusion

So you can see there’s lots of power in having your own custom R Markdown templates… so go forth and templatize!

Additional Resources

As always, gratitude to those who have provided guidance that I have leaned on:

• RStudio RMarkdown Template reference
• Creating a Basic Template Package in R
• Putting your R Package on Github

1. RWordPress pkg by Duncan Temple Lang (2012) – there was a helpful vignette but can't find it any longer
2. Publish blog posts from R + knitr to WordPress by Yihui Xie (2013)
3. How to Publish with R Markdown in WordPress by 3.14a (2015)
4. Blog Posting from R Markdown to WordPress by Heather Urry (2017)
5. How to Publish a Blog Post on WordPress using RMarkdown by Tobias Deinlin (2019)

So nothing new here, other than recap of the above and confirmation that although the code behind this was produced in 2012-13, it still works as of August, 2021.

Note that since my blog is on WordPress.com hosted site, I'm focusing on that environment. Resources above cover variations to the process required for a self-hosted WordPress.org site.

RMarkdown file available in this Github repo: https://github.com/jyuill/r-markdown-wordpress

Steps

Summary of steps to be taken to publish R Markdown on WordPress.

1. Install packages needed.
2. Do some R Markdown – example case below.
3. Publish to WordPress – by running separate code using RWordPress pkg.

Key thing to note here is that you are not directly uploading the html file that your RMarkdown produces. Rather you are knitting the .Rmd file in a way that results in a blog post being published to your WordPress blog (can be public or draft).

1. Install Packages

The key to the process is the RWordPress package, supported by the XMLRPC package, both of which you can get using the code below (along with knitr pkg):

## References
## http://tabvizexplorer.com/how-to-upload-r-markdown-directly-to-wordpress/

## Get required packages
if (!require('knitr')) {install.packages("knitr")}
if (!require('devtools')) {install.packages("devtools")}
if (!require('RWordPress')) {devtools::install_github(c("duncantl/XMLRPC", "duncantl/RWordPress"))}

As others have warned, the RWordPress package is no longer being maintained – so use at your own risk.

The other key is the 'knit2wp' function in the knitr package, hence the need to install that if not already installed, as well.

2. Produce R Markdown

As mentioned, this blog post in entirely created with RMarkdown. For demonstrating data analysis features, I'm walking through an example below based on my personal collection of Vancouver weather data ;).

The idea here is to demonstrate some typical Markdown capabilities that you may want to have in your blog post:

• weaving together text and data processing
• basic html like headings, bullet lists
• show code chunks (with horizontal scrolling)
• show data structure and summary
  • allow for side-scrolling
• data visualization – a couple of varieties
• integrate dynamic values into text flow (with )

Annual Temperature Data

To explore the data across the period covered, summarize by year. (mouseover the code block and drag to scroll horizontally)

## summarize annual temperature
annual_summary <- dataset %>% filter(Year<max(dataset$Year)) %>% ## remove most recent yr, since incomplete data
  group_by(Year) %>% summarize(max_temp=max(Max.Temp, na.rm=TRUE),
                               min_temp=min(Min.Temp, na.rm=TRUE),
                               mean_daily_temp=mean(Mean.Temp, na.rm=TRUE),
                               annual_precip=sum(Total.Precip, na.rm=TRUE)) 

Check out the data structure: (mouseover to scroll horizontally)

## tibble [51 × 5] (S3: tbl_df/tbl/data.frame)
##  $ Year           : num [1:51] 1970 1971 1972 1973 1974 ...
##  $ max_temp       : num [1:51] 30.6 29.4 27.2 28.9 27.2 27.8 25 30.5 30.1 28.7 ...
##  $ min_temp       : num [1:51] -7.2 -12.8 -11.1 -10.6 -8.3 -7.2 -6.7 -8.1 -13.5 -11.2 ...
##  $ mean_daily_temp: num [1:51] 9.43 9.09 9.07 9.59 9.91 ...
##  $ annual_precip  : num [1:51] 875 1335 1244 1000 1248 ...

Summary of the data:

##       Year         max_temp       min_temp      mean_daily_temp annual_precip 
##  Min.   :1970   Min.   :25.0   Min.   :-15.20   Min.   : 8.92   Min.   : 810  
##  1st Qu.:1982   1st Qu.:27.9   1st Qu.:-11.25   1st Qu.: 9.88   1st Qu.:1029  
##  Median :1995   Median :28.8   Median : -9.10   Median :10.27   Median :1207  
##  Mean   :1995   Mean   :28.8   Mean   : -9.42   Mean   :10.30   Mean   :1174  
##  3rd Qu.:2008   3rd Qu.:29.6   3rd Qu.: -7.70   3rd Qu.:10.80   3rd Qu.:1296  
##  Max.   :2020   Max.   :34.4   Max.   : -3.10   Max.   :11.45   Max.   :1522

Annual temperature patterns across the period:

(Name the code chunk with a meaningful name for the plot as this will be used to save the image file for uploading to WordPress)

## visualize
tplot1 <- annual_summary %>% 
  ggplot(aes(x=Year))+
  geom_line(aes(y=mean_daily_temp), color='black')+
  geom_line(aes(y=max_temp), color='red')+
  geom_line(aes(y=min_temp), color='blue')+
  labs(title="Annual Max., Min., and Mean (Daily) Temperature",
       y='Temperature (celsius)')+
  theme_light()

tplot1

I'm no climatologist, and this is very imprecise data exploration, but appears there might be a slight but gradual upward drift in temperatures over this relatively short period.

Annual Precipitation

Vancouver is famous for it's rainfall. How much rain has Vancouver received each year over the period covered? 1970 to 2020
(years shown are based on dynamic reference to data source)

That's right…Vancouver frequently gets over 1,000 mm – 1 meter – of rain in a year!

• average for last 50 years:1174.153 per year
• median over last 50 years: 1207 per year

(figures based on automatic calculations at run-time)

Monthly Precipitation Patterns

Surely the rain is not steady all year long? What are the monthly patterns?

Dry in summer, wet in winter – especially November, December and, to lesser extent, January. By May, we're usually safely out of the rainy season. Until October rolls around and the rains set in again.

3. Publish to WordPress

Once your R Markdown file is polished to your liking, you just need to run some simple code to send it to WordPress:

1. load libraries
library(RWordPress)
library(knitr)

2. set credentials
options(WordPressLogin= c(<username>='<pwd>'),
       WordPressURL=paste0('https://<blog name>.wordpress.com/xmlrpc.php'))

3. setup to upload plots
 - saves image files named after code chunks in WordPress Media section by date
opts_knit$set(upload.fun=function(file){library(RWordPress);uploadFile(file)$url;})

4. knit to WP
knit2wp("<markdown file name>",        ## markdown file to publish
        title = "<post title>",        ## title for the post in WordPress
        publish = FALSE,               ## FALSE to add as draft; TRUE to go direct to publish
        action = "newPost",            ## for new post; alternatives: "editPost", "newPage"
        #postid                        ## needed with editPost - get from WP interface
        shortcode= FALSE,              ## optional - affects how code is shown; default FALSE
        categories= c('R Markdown'))   ## set categories, if desired

5. Upload featured image / post thumbnail: option: wp_post_thumbnail=postThumbnail$id
postThumbnail <- RWordPress::uploadFile("figure/<image file>",overwrite = TRUE)

You can set up a standard file and substitute values for markdown file and title as needed. Of course, you want to keep it private since you have password in there – add to .gitignore if you have a public Github repo.

Limitations

Or at least things I haven't figured out how to do yet:

1. No interactive plotly plots: render fine in R Markdown but don't make it to WP. Probably because plots are published based on static image files.
2. Internal anchor links: tricky to set internal anchor links. Using {#anchor-name} beside heading works in R Markdown, but shows up as printed text in WP; using html like h2 tag with id=“anchor-name” works in WP and R Markdown knit but doesn't show up as heading in document outline – which can be a pain for long docs.
3. glimpse function: for some reason, using the glimpse() function seems to throw an error. (caused me several hrs of trouble-shooting)

If I find solutions to these – or uncover more limitations – I'll pass along updates.

Alternatives

At the end of the day, this works but not ideal and may not provide the flexibility everyone is looking for. There is also the element of risk inherent in the fact that the process depends on R packages that are no longer maintained.

I was keen to get this to work because I have a legacy WordPress blog that I want to build on, including accessing the features of the world's most popular content management system, and I want to extend it with work done in my primary environment – RStudio / RMarkdown.

Obviously, there are lots of other options, and warnings during process suggest you may want to check out blogdown for building blogs/websites with R Markdown.

So we'll see where this goes, but in meantime, hopefully some helpful tips here for others determined to make R Markdown work in WordPress.

And yet…we persist with the illusion that having text for people to read – while we’re talking – will reinforce our message and drive home our key points. It certainly can’t hurt to have a few words up there, we tell ourselves. It will help fill up the space so people aren’t staring into the abyss, we tell ourselves. It’s a win-win, we tell ourselves, because it will both help the audience get what we’re talking about AND give us some cues to stay on track.

Ah, there we have it: the real reason we have text on our slides is not to help our audience, it’s to help us. And that is the worst reason of all.

The curse of slide text hit home for me during a recent presentation from a colleague. There were some helpful diagrams and charts that set the context well for the customer lifetime value model that he was presenting. There was also a collection of bullet points. Not a lot of text, probably similar amount to what I have used myself many times in the past. But it was just enough so that as we was talking, I couldn’t help but try to read, and as I read I noticed I wasn’t understanding what I was reading, because he was talking. And as I switched to listening to him talk, to try to get back on track, I realized I had missed part of what he said because I was trying to read. So now I was completely lost. As he finished his explanation and asked if there were any questions, I was left staring at the screen in the realization that I had no clue of the point he was trying to make. Then on to the next slide, with more of the same, eventually leaving me (and I’m sure most of the others present) with only a vague idea of the project he was describing and an unsettling feeling that this was probably something we should know about. Maybe that was why there was only silence when he asked for questions at the end.

It was a wake-up call. All the advice over the years fully, finally sunk in. As did an immediate feeling of guilt for all the confusion and poor communication I had spawned over the years, doing exactly the same as my colleague, thinking I was being effective, when I should have known better.

Our job as communicators – whether analytics concepts or anything else – is to remove the friction between us, our message and our audience. We need to get out of our own way. Removing text from slides so the focus can be squarely on our speech is the first, easiest step. Here are some quick suggestions – consult an expert for more. (Basically just do whatever Nancy Duarte says ;))

1. Visuals: Slides are for images, charts, diagrams, helpful visual aids, not text.
2. Notes: Speaking notes go in the..notes section below the slide.
3. Separate decks for presenting and reading: If the deck will be distributed later for reference or for those not able to attend the presentation, provide a stand-alone version of the deck that has the text included. This could be as simple as copy/paste/format your speaking notes into the main slides.
4. Pause: If you need to have text on a slide for the audience to read during your presentation, pause and ask them to read it before moving on. That way there is at least less competition between your speech and the text.

Of course, there is a lot more that goes into a stellar presentation than ruthlessly removing text from slides. But it is a critical step, and one that I am planning to lean heavily on, starting with a presentation I have next week. Cheers!

[Wow, long time no blog post. Good to be back at it…all part of the New Year plan!]

Once of the most basic segmentation dimensions in web analytics the differentiation between new and return visits. Understanding the different types of behaviour of new vs return visitors can be critical in understanding how people are interacting with your site and then taking steps to optimize accordingly, based on your objectives. As always, different web analytics tools deal with new vs return visitor metrics in different ways. SiteCatalyst, for one, does not make it easy to tell at a glance your % of new/return visits.

With Google Analytics, % new visits is right there in the default dashboard. In GA v5, it even gets its own pie chart, in addition to spark line, right out of the box:

Meanwhile, in SiteCatalyst (at least up to v14), things are not as clear. This is not to say information on new vs return visits is not available: indeed, there are a slew of reports in the ‘Visitor Retention’ section, such as Return Frequency, Return Visits, Daily Return Visits, and Visit number that provide some great, detailed insight into visitor behaviour. But these suffer from lack of context or unnecessary level of granularity if I’m just trying to gauge the overall balance of site traffic in order to decide where to prioritize my efforts.

Probably the best option is the getNewRepeat plug-in that Omniture offers (search for ‘getnewrepeat’ in the Omniture Knowledge Base), but this requires additional implementation and may not be feasible.

So here is a little tip to demonstrate how to fiddle with SiteCatalyst just a bit to get an easy-access report on % New Visits:

1. Go to ‘Visitor Retention’>’Visit Number‘ – select ‘Visits (Report Specific)’ as your metric (of course this same process could be used for Revenue or other metric). If you select ‘Percent’ in the Graph options, you can see right away that x% of visits are ‘1st Visit’: i.e. NEW visits/visitors. But this is just a snapshot for the selected period, and it is usually more informative to see the trend over time.

2. Using the ‘Trended / Ranked’ option, select ‘Trended’ – now you can see how the % 1st Visit (NEW visits) is changing over time and take action accordingly.

3. Add to bookmark or dashboard for quick access in future and ‘Bob’s your uncle’.

A couple other related things:

• as bonus info, by default SiteCatalyst shows the % of visits for each of the top 5 visit numbers; you can use ‘Select Item’ on the ‘Report’ tab to filter on ‘1st Visit’ only if you want to focus attention, but having the higher visit numbers visible can signal where the increases/decreases in the % New Visits is coming from/going to.
• if you want to look beyond visits to outcomes (which of course we do!), it is simply a matter of selecting another metric using ‘Add Metrics’ and you can see what % of revenue, for example, came from new visits vs 2nd visits, etc.

So What?

So now that we are able to quickly monitor our % new visits, the big question becomes: what to do about it? Depending on your goals, starting point, and context of other metrics, the interesting pattern shown in the chart above may be good or bad. If you are prioritizing acquisition of new customers, a declining trend in % new visits may indicate lack of success in your efforts. If you are focused on developing relationships with customers and bringing them back to your site, a decrease in % new visits may be a reason to pat yourself on the back, and continue your visitor retention efforts. It’s all about balance, though: the trend toward the end may be a signal to keep an eye on this metric and watch for the point where it makes sense to step up the visitor acquisition initiatives to fuel longer-term growth.

A company I know was focused on attracting new visitors, and the trends for both new visits and overall visits were up up up. But a quick look at the % New Visits report showing near 80% New Visits highlighted the fact that few of these new visitors were returning: time to re-balance efforts to ensure that new visitors are finding content that will keep them coming back.

So being able to track your % New Visits can be quite useful, and in case you didn’t know how to do this easily within SiteCatalyst, now you do.

The Web Analytics Forum on Yahoo! Groups was started back in 2004 by one of the giants of the web analytics industry, Eric Peterson. Since then, it has been a major hub for web analytics community news, events, and discussion – so much so that Avinash Kaushik was included “You have not only heard of the Yahoo! Web Analytics group, but 20 minutes of each day is spent reading all the posts”  as item #9 in his Top Ten: Signs You are a Great Analyst.

After being out of the loop of the Web Analytics Forum for a while due to some email changes, I was recently reminded of the relevance of this forum and subscribed again for daily updates. While doing so, I noticed some interesting stats on message history that are shown at the bottom of the home page. Being the type of person who processes data much better when it is visually represented, rather than being shown as numbers in a table (as I believe most of us are), I dropped the data into Excel and created the quick chart shown above.

I was disheartened to see that although message volume on the forum grew fairly steadily from inception in 2004 up until April, 2008, it has trended down since then. I have to admit that I have never posted a message to the forum, so I am as much a part of the problem as anybody, but from a community member perspective it is disappointing to see this resource waning. No doubt it is at least partly a result of the rise of social media and the resulting fragmentation of communications, but the Web Analytics Forum has a valuable role to play, in my opinion, as a central repository of current – and historical – information related to the practice of web analytics.

So I will continue following the Web Analytics Forum, looking for opportunities to jump in when I have something useful to add, and I hope others will do the same.

And to the forum founder Eric Peterson, as well as all those who have followed in his footsteps and contributed valuable information and insights to the Web Analytics forum in the past, present, and future…THANK YOU!