Exploring Channel Disconnections

TLDR

• Pinterest and Linkedin profiles are the most likely to experience a Channel Disconnected event.
• Around 14% of Instagram Business profiles experience a disconnection event.
• Most Channel Disconnection events for Instagram profiles occur on the same day that they are connected to Buffer.

Data Collection

We’ll gather Channel Connected and Channel Disconnected events from BigQuery.

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

# define sql query
sql <- "
  select
    c.id as connection_event_id
    , d.id as disconnection_id
    , date(p.created_at) as created_date
    , date(c.original_timestamp) as connected_at
    , date(d.original_timestamp) as disconnected_at
    , c.channel
    , c.channel_id
    , c.channel_type
    , d.channel_type as disconnected_type
  from segment_publish_server.channel_connected c
  inner join dbt_buffer.publish_profiles p
    on c.channel_id = p.id
  left join segment_publish_server.channel_disconnected d
    on d.channel_id = c.channel_id
  where c.original_timestamp >= '2019-11-01'
"
  
# query bigquery
events <- dbGetQuery(con, sql)

There are around 360 thousand profiles in this dataset.

Overall Disconnection Rate

Let’s begin by calculate the proportion of profiles that experienced a Channel Disconnected event.

# count disconnections
events %>% 
  mutate(disconnected = !is.na(disconnection_id)) %>% 
  group_by(disconnected) %>% 
  summarise(profiles = n_distinct(channel_id)) %>% 
  mutate(percent = profiles / sum(profiles))

## # A tibble: 2 x 3
##   disconnected profiles percent
##   <lgl>           <int>   <dbl>
## 1 FALSE          318233   0.876
## 2 TRUE            44991   0.124

Around 12% of the profiles (i.e. channels) in our dataset have had a Channel Disconnected event.

Disconnection Rate by Channel

Now let’s break these rates down by network (Facebook, Instagram, etc.).

Surprisingly, Pinterest profiles have the highest rate of disconnection. Twitter profiles are the least likely to have had a disconnection event. Let’s drill down a bit further and look at the channel types.

Pinterest and Linkedin profiles are the most likely to have had a Channel Disconnected event. Around 14% of IG Business profiles that were connected had a disconnection event. Let’s looke at when the disconnections occur.

When do Disconnections Occur?

Let’s plot the distribution of the number of days that elapse between connection and disconnection and focus on Instagram.

This plot suggests that most Channel Disconnected events occur on the same day that the profile is first connected to Buffer.