Effect of AppCues Flow on Reply Trial Conversions

Data Collection and Tidying

The process for collecting and tidying this data is quite messy, as there is not a clear way to associate Reply user ids with org ids, and there is no user id present in Reply’s trials data table. Here I use the memberships table to associate user ids with org ids, but I do not know what a membership is and what exactly the relationship is. All we know is that there is some association.

# read data from csv
appcues_users <- read_csv("~/Downloads/appcues_users.csv")

There are 438 users in this dataset. All of them have been exposed to the AppCues onboarding flow. They will be our experiment group. To get our control group, we will need to get all Reply users that started trials on or after July 19 that are not in this dataset.

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

# define sql query
sql <- "
  select distinct
    t._id as trial_id
    , p.user_ref as user_id
    , t.org_ref as org_id
    , t.stripe_customer_id as customer_id
    , t.stripe_subscription_id as subscription_id
    , timestamp_seconds(cast(t.started_at as int64)) as trial_start
  from trials t
  left join memberships as p
    on t.org_ref = p.org_ref
  where date(timestamp_seconds(cast(t.started_at as int64))) >= '2019-06-19'
"
  
# query BQ
trials <- dbGetQuery(con, sql)

There are 1227 trials that started in this period. Let’s find all that are present in the AppCues dataset.

# create experiment group
experiment <- trials %>% 
  inner_join(appcues_users, by = "user_id") %>% 
  select(trial_id, customer_id, subscription_id, trial_start) %>% 
  unique() %>% 
  mutate(group = "experiment")

Now let’s create the control group and merge the two dataframes.

# create control group
control <- trials %>% 
  anti_join(experiment, by = "customer_id") %>% 
  select(trial_id, customer_id, subscription_id, trial_start) %>% 
  unique() %>% 
  mutate(group = "control")

# merge dataframes
both <- rbind(control, experiment)

There are 8 customers that started multiple Reply trials in this time period, but there are no customers in multiple groups, which is important. In order to determine whether these trials converted, we’ll need to collect some more data from the dbt_buffer schema.

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

# define sql query
sql <- "
  select 
    id as event_id
    , trial_end_at
    , subscription_id
    , plan_id
    , product
    , converted
  from stripe_trials 
  where date(trial_start_at) >= '2019-06-19'"
  
# query BQ
all_trials <- dbGetQuery(con, sql)

Now we’ll join it to the both dataframe to determine if the trial converted or not.

# join all trials
experiment_trials <- both %>% 
  inner_join(all_trials, by = "subscription_id")

Now we should be able to run a proportion test and analyze the results of this experiment.

Proportion Test

Let’s begin by calculating the proportion of trials that converted in each group.

# calculate conversion rates
trials %>% 
  group_by(group, converted) %>% 
  summarise(trials = n_distinct(subscription_id)) %>% 
  mutate(percent_of_trials = trials / sum(trials))

## `summarise()` regrouping output by 'group' (override with `.groups` argument)

## # A tibble: 4 x 4
## # Groups:   group [2]
##   group      converted trials percent_of_trials
##   <chr>      <lgl>      <int>             <dbl>
## 1 control    FALSE        805           0.995  
## 2 control    TRUE           4           0.00494
## 3 experiment FALSE        406           0.978  
## 4 experiment TRUE           9           0.0217

As we can see, there is a very small sample of conversions. There are twice as many trials in the control group as there are in the experiment group. However, the conversion rate of those in the experiment group is much higher than the conversion rate of those in the control group.

# run proportion test
n_conversions <- c(4, 9)
n_trials <- c(809, 415)
prop.test(n_conversions, n_trials)

## 
##  2-sample test for equality of proportions with continuity correction
## 
## data:  n_conversions out of n_trials
## X-squared = 5.8103, df = 1, p-value = 0.01593
## alternative hypothesis: two.sided
## 95 percent confidence interval:
##  -3.338928e-02 -9.546059e-05
## sample estimates:
##      prop 1      prop 2 
## 0.004944376 0.021686747

This is a two-sample proportion test of the hypothesis that the true conversion rates are equal for each population of users. Using a significance level of 0.05, we conclude that two population conversion rates are not equal.

The observed difference in conversion rates is 0.017. The observed conversion rate for the first group is 0.49%, and the observed conversion rate for the experiment group group is 2.2%. The confidence interval for the difference in conversion rates is (-0.033, 0). This interval will contain the difference in population conversion rates 95 times out of 100.

The p-value for this test is 0.016. This is defined as the probability – if the proportions are truly not different – of observing this difference in sample proportions that is more extreme that what is observed in this data set. In this case, this is the probability of observing a difference in sample conversion rates that is greater than 0.017 or less than -0.017."