Effect of IG Connection Flow on Trial Conversions

TLDR

Users that were able to successfully connect an Instagram profile converted at a rate 29% higher than users that tried and failed to connect an Instgram profile and 77% higher than users that did not attempt to connect an IG profile.

Data Collection

To analyze the results of this experiment, we’ll collect all of the active subscriptions of users that were placed in one of the two experiment groups. We use the SQL query below to retrieve this data from BigQuery.

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

# define sql query to get trials
trials_sql <- "
  select distinct
    u.id as user_id
    , u.billing_plan
    , t.customer_id
    , t.id as trial_id
    , date(t.trial_start_at) trial_start_at
    , date(t.trial_end_at) trial_end_at
    , t.subscription_id
    , t.plan_id
    , t.converted
  from dbt_buffer.stripe_trials t
  inner join dbt_buffer.publish_users u
    on t.customer_id = u.stripe_customer_id
  where date(t.trial_start_at) >= '2019-11-11'
"

# query BQ
trials <- dbGetQuery(con, trials_sql)

# define sql for channel connections
channel_sql <- "
  select distinct
    a.id as account_id
    , a.publish_user_id
    , count(distinct ca.id) as ig_connection_attempts
    , count(distinct cc.id) as ig_connections
    , count(distinct cu.id) as ig_business_connections
  from segment_publish_server.channel_connection_attempted ca
  left join segment_publish_server.channel_connected cc
    on cc.user_id = ca.user_id
    and cc.original_timestamp > ca.original_timestamp
    and cc.channel = 'instagram'
    and cc.product = 'publish'
  left join segment_publish_server.channel_updated cu
    on ca.user_id = cu.user_id
    and cu.original_timestamp > ca.original_timestamp
    and cu.channel = 'instagram'
    and cu.channel_type = 'business'
    and cu.product = 'publish'
  inner join dbt_buffer.core_accounts a
    on ca.user_id = a.id
  where ca.channel = 'instagram'
  and ca.product = 'publish'
  and date(ca.original_timestamp) >= '2019-11-11'
  group by 1,2
"

# query BQ
events <- dbGetQuery(con, channel_sql)

# merge datasets
users <- trials %>% 
  left_join(events, by = c("user_id" = "publish_user_id"))

There are around 11k users that have started Publish trials since November 11, the date on which we started tracking channel connections and updates.

Exploratory Analysis

Let’s start by determining whether users successfully connected Instagram profiles. Then, we’ll compare the trial conversion rates.

# compare conversion rates
users %>% 
  filter(trial_start_at < Sys.Date() - 3) %>% 
  mutate(segment = case_when(is.na(ig_connection_attempts) ~ "No attempt",
                             ig_connections == 0 & ig_connection_attempts >= 1  ~ "Failed attempt",
                             ig_business_connections >= 1 ~ "Successful business connection",
                             ig_connections >= 1 ~ "Successful personal connection")) %>% 
  group_by(segment, converted) %>% 
  summarise(users = n_distinct(user_id)) %>% 
  pivot_wider(names_from = converted, values_from = users, names_prefix = "Converted_") %>% 
  mutate(conversion_rate = Converted_TRUE / (Converted_TRUE + Converted_FALSE)) %>% 
  arrange(desc(conversion_rate)) %>% 
  mutate(conversion_rate = scales::percent(conversion_rate)) %>% 
  kable() %>% 
  kable_styling()

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

Unsurprisingly, those users that successfully connected an Instagram profile converted their trials at the highest rate, 8.60%. Users that did not attempt to connect an Instagram profile converted at the lowest rate, 4.84%, and those that tried and failed to connect an Instagram profile converted at a rate of 6.66%.

Conversions for the segment that were able to successfully connect an Instagram profile were approximately 29% higher than conversions for the segment that were not able to successfully connect.