January 2020 Awesome Customer Migration

# connect to BigQuery
con <- dbConnect(
  bigrquery::bigquery(),
  project = "buffer-data"
)

# define sql query
sql <- "
  select distinct
    a.*
    , u.billing_plan
    , u.billing_plan_number
    , u.is_on_new_publish as is_on_np
    , max(p.original_timestamp) as last_active
  from temp_google_sheets.awesome_migration_jan20 a
  left join dbt_buffer.publish_users u
   on a.publish_user_id = u.id
  left join segment_publish.page_viewed p
    on p.user_id = a.global_account_id
    and p.event = 'page_viewed'
    and p.context_page_title = 'Buffer Publish'
  group by 1,2,3,4,5,6,7,8,9,10,11
"
  
# query bigquery
users <- dbGetQuery(con, sql)

How Many Are on New Publish?

We’ll first see how many users were active after a February 3 (when the changes went into effect), and then calculate the proportion that have been active and are now on Buffer Classic.

users %>% 
  filter(last_active >= "2020-02-03") %>% 
  group_by(is_on_np) %>% 
  summarise(users = n_distinct(publish_user_id)) %>% 
  mutate(percent = users / sum(users))

## # A tibble: 2 x 3
##   is_on_np users percent
##   <fct>    <int>   <dbl>
## 1 false       51  0.0813
## 2 true       576  0.919

Only around 20% of these Awesome users have loaded a Publish page since the changes went into effect, and around 73% of those are still on New Publish.

How Many Churned?

Around 56 (2.8%) of these Awesome customers have churned.

users %>% 
  mutate(churned = billing_plan_number < 2 | is.na(billing_plan_number)) %>% 
  group_by(churned) %>% 
  summarise(users = n_distinct(publish_user_id)) %>% 
  mutate(percent = users / sum(users))

## # A tibble: 2 x 3
##   churned users percent
##   <lgl>   <int>   <dbl>
## 1 FALSE    1903  0.957 
## 2 TRUE       86  0.0432

How Many Are on Each Plan?

Let’s see how many of these former Awesome customers are on each plan.

users %>% 
  group_by(billing_plan) %>% 
  summarise(users = n_distinct(publish_user_id)) %>% 
  mutate(percent = users / sum(users))

## # A tibble: 4 x 3
##   billing_plan users percent
##   <fct>        <int>   <dbl>
## 1 awesome       1896 0.953  
## 2 individual      86 0.0432 
## 3 premium          3 0.00151
## 4 small            4 0.00201