Exploratory Analysis of Channel Groups

In Buffer you have the ability to create a group of channels that can be selected and posted to at one time. We call these channel groups.

This post will serve as a small exploratory analysis of how our users are utilizing channel groups. The specific questions we’ll aim to answer are:

• What’s the distribution of channel groups per user?

• And of channels per group?

• How many groups are typically added to a single post?

• What is the channel type per group (1 of each, multiple of the same?)

Data Collection

We’ll collect information about channel groups of active users, i.e. those that have sent a post in the last 60 days. This query returns approximately 2500 channel groups created by 726 users.

# write sql query
sql <- "
  select distinct
    g.* 
  from dbt_buffer.publish_channel_groups as g
  inner join dbt_buffer.segment_post_sent as p
    on g.account_id = p.user_id
    and p.timestamp >= timestamp_sub(current_timestamp, interval 60 day)
"

# get data from BigQuery
groups <- bq_query(sql = sql)

Distribution of Groups Per User

We’ll start by looking at the quantiles. This tells us the number of channel groups that 25%, 50%, and 75% of accounts have, as well as the minimumm and maximum.

# count groups per user
by_user <- groups %>% 
  group_by(account_id) %>% 
  summarise(groups = n_distinct(channel_group_id))

# calculate quantiles
quantile(by_user$groups, probs = seq(0, 1, 0.1))

   0%   10%   20%   30%   40%   50%   60%   70%   80%   90%  100% 
  1.0   1.0   1.0   1.0   1.0   2.0   2.0   3.0   4.0   6.5 316.0 

Unsurprisingly, the data is skewed. Most users have a small number of channels – 50% have two or fewer groups and 80% have four or fewer.

However, there is also a long tail that includes a small number of users with a large number of channel groups. Around 10% of accounts have 7 or more channel groups.

These are the top 10 accounts in terms of the number of channel groups.

# get users with most groups 
by_user %>% 
  arrange(desc(groups)) %>% 
  head(10)

# A tibble: 10 × 2
   account_id               groups
   <chr>                     <int>
 1 664cf1d66088eb8832149e6d    316
 2 5ca1d5551d99bc38dbf448c1     36
 3 5ca1d8171d99bc38dbfa09c3     32
 4 5ca1d7761d99bc38dbf8b812     29
 5 5ca1d63483663e38f395b35b     27
 6 5ccc335974cc8b9c779515c0     23
 7 5ca1d50ffd16da3982473f59     22
 8 5ca1db391d99bc38db03d132     20
 9 5ca1d7f57ace10391aea42af     19
10 5e63e6b920400a65156fd0b4     19

Let’s take a closer look at one of them. Many of these groups only contain two channels.

# look at top user 
groups %>% 
  filter(account_id == "5ca1d63483663e38f395b35b") %>% 
  select(channel_group_id, channel_ids, channel_count) %>% 
  head(10)

# A tibble: 10 × 3
   channel_group_id         channel_ids channel_count
   <chr>                    <list>              <int>
 1 62b40d71ea36d653bc624706 <chr [1]>               1
 2 606591e05f071111b368aaf2 <chr [2]>               2
 3 664703ed72234b48db02cd52 <chr [2]>               2
 4 5e5e30ad9baf31369c43acbe <chr [2]>               2
 5 5c45a062a4b3ff0cc2317e92 <chr [2]>               2
 6 635792a7c36b0a3e82685caa <chr [2]>               2
 7 65855ee54265e7a72f0618e6 <chr [2]>               2
 8 623c48de8a22eb71be13b3e3 <chr [2]>               2
 9 663da7b0d03eaa7ea803379a <chr [2]>               2
10 6385bf334639b82bb1015212 <chr [2]>               2

Distribution of Channels Per Groups

We’ll use a similar approach to get a sense of the number of channels per channel group.

# calculate quantiles
quantile(groups$channel_count, probs = seq(0, 1, 0.1))

  0%  10%  20%  30%  40%  50%  60%  70%  80%  90% 100% 
   1    1    2    2    2    2    3    3    4    5   77 

Around 50% of groups have 2 or fewer channels. Around 80% have 4 or fewer channels, and 10% include 6 or more channels.

It’s worth noting that around 10% of groups only include a single channel.

Channel Service Per Group

Next we’ll look at the specific channel types that are represented by in the channel groups.

# percentage of groups with distinct number of service types
groups %>% 
  group_by(service_type_count) %>% 
  summarise(groups = n_distinct(channel_group_id)) %>% 
  mutate(prop = percent(groups / sum(groups), .1)) %>% 
  arrange(service_type_count)

# A tibble: 10 × 3
   service_type_count groups prop 
                <int>  <int> <chr>
 1                  1    584 23.4%
 2                  2   1034 41.4%
 3                  3    589 23.6%
 4                  4    196 7.9% 
 5                  5     49 2.0% 
 6                  6     28 1.1% 
 7                  7      7 0.3% 
 8                  8      3 0.1% 
 9                  9      5 0.2% 
10                 10      1 0.0% 

Most channel groups have 3 or fewer service types. Around 23% only have a single service type, 41% have two, and 24% have three. After that there is a longer tail that have a larger number of service types.