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.
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.
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.
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)
|Successful business connection||4862||540||10%|
|Successful personal connection||6355||179||3%|
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.