3 min read

Effect of IG Connection Flow on Trial Conversions

In this analysis we’ll explore the effect that Instagram profile connections have on trialists. We’ll examine two groups of users: those that attempted and succeeded in connecting Instagram profiles, and those that attempted and failed to connect an Instagram profile.

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()
segment Converted_FALSE Converted_TRUE conversion_rate
Successful business connection 4240 515 10.8%
Failed attempt 2876 159 5.24%
Successful personal connection 5548 167 2.92%
No attempt 10702 291 2.65%

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.