Visualizing intraday SEM performance with R
Dec 23, 2019
Christopher Yee
3 minute read

Aside from the base bid, Google SEM campaign performance can be influenced by contextual signals from the customer. These include but are not limited to: device, location, gender, parental status, household income, etc.

For this post we’ll focus on ad schedule (or intraday) and visualize how time of day and day of week is performing.

Load data

library(tidyverse)

# ANONYMIZED SAMPLE DATA
df <- read_csv("https://raw.githubusercontent.com/Eeysirhc/random_datasets/master/intraday_performance.csv")

Spot check our data

df %>% 
  sample_n(20)
## # A tibble: 20 x 5
##    account   day_of_week hour_of_day  roas conv_rate
##    <chr>     <chr>             <dbl> <dbl>     <dbl>
##  1 Account 2 Tuesday               9 0.781   0.0326 
##  2 Account 1 Tuesday               3 0       0      
##  3 Account 1 Wednesday             4 0.25    0.00685
##  4 Account 2 Monday               17 0.632   0.0238 
##  5 Account 3 Saturday              0 0.486   0.00980
##  6 Account 2 Monday               18 0.584   0.0244 
##  7 Account 3 Tuesday              20 0.887   0.0169 
##  8 Account 1 Thursday             21 0.650   0.0371 
##  9 Account 2 Monday                8 0.742   0.0344 
## 10 Account 2 Sunday                5 0.949   0.0333 
## 11 Account 1 Sunday               17 0.242   0.0167 
## 12 Account 1 Wednesday             6 0.492   0.0229 
## 13 Account 2 Monday               10 0.801   0.0303 
## 14 Account 2 Thursday              6 0.959   0.0329 
## 15 Account 1 Tuesday              23 0.713   0.00930
## 16 Account 2 Sunday               19 0.754   0.0272 
## 17 Account 1 Tuesday              22 0.227   0.00826
## 18 Account 3 Friday                5 0.678   0.0111 
## 19 Account 2 Monday               13 0.705   0.0280 
## 20 Account 3 Saturday             13 0.497   0.0182

Clean data

Convert to factors

The day_of_week is a character and time_of_day is a double data type. We need to transform them to factors so they don’t surprise us later.

df_clean <- df %>% 
  mutate(day_of_week = as.factor(day_of_week),
         hour_of_day = as.factor(hour_of_day)) 

Reorder day of week

If we plot our chart now it won’t be correct because day_of_week will not be arranged properly from Sunday to Saturday format.

levels(df_clean$day_of_week)
## [1] "Friday"    "Monday"    "Saturday"  "Sunday"    "Thursday"  "Tuesday"  
## [7] "Wednesday"

We can quickly refactor with a few lines of code:

df_clean$day_of_week <- factor(df_clean$day_of_week, 
                               levels = c("Saturday", "Friday", "Thursday",
                                          "Wednesday", "Tuesday", "Monday", "Sunday"))

Let’s check to make sure it did what we wanted:

levels(df_clean$day_of_week)
## [1] "Saturday"  "Friday"    "Thursday"  "Wednesday" "Tuesday"   "Monday"   
## [7] "Sunday"

Plot our results

df_clean %>% 
  ggplot(aes(hour_of_day, day_of_week, fill = conv_rate)) +
  geom_tile(color = 'white') +
  scale_fill_gradient2(low = 'grey', mid = 'white', high = 'steelblue') +
  labs(y = NULL, x = "Hour of Day", fill = "Conversion Rate") +
  theme_minimal() 

We can quickly see from this visualization that Tuesday & Wednesdays at midnight the conversion rate is 7% or higher. Conversely, 10AM on Saturday has a conversion rate close to the 0% range.

With this insight we can then amplify or dampen our intraday bid modifiers to improve overall campaign efficiencies.

And for the love of data…

segment!

df_clean %>% 
  ggplot(aes(hour_of_day, day_of_week, fill = conv_rate)) +
  geom_tile(color = 'white') +
  scale_fill_gradient2(low = 'grey', mid = 'white', high = 'steelblue') +
  labs(y = NULL, x = "Hour of Day", fill = "Conversion Rate") +
  theme_minimal() +
  facet_grid(account ~ .)