How Premium and Pro Usage Differs

TLDR

• Premium users are significantly more likely to have been active in the past 30 days.
• Pro users are significantly more likely to have no profiles and no posts in the past 30 days.
• Premium users tend to create more Instagram posts.
• Premium users are significantly more likely to be online or physical stores.
• Premium users are significantly more likely to have 5-50 employees.

Data Collection

We’ll use the SQL query below to retrieve data from Bigquery.

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

# define sql query
sql <- "
  with customers as (
    select distinct 
      u.id as user_id
      , u.account_id
      , u.billing_plan
      , u.stripe_customer_id as customer_id
      , s.id as subscription_id
      , s.plan_id as plan_id
    from dbt_buffer.publish_users u
    inner join dbt_buffer.stripe_paid_subscriptions s
      on u.stripe_customer_id = s.customer_id
      and s.status = 'active'
    where s.plan_id in ('pro_v1_monthly', 'premium_business_v1_monthly')
    and s.created_at < timestamp_add(current_timestamp() , interval -30 day)
  )
  
  select
    c.user_id
    , c.account_id
    , c.plan_id
    , i.company_size
    , i.company_type
    , i.signup_attribution_source
    , count(distinct up.id) as posts
    , count(distinct case when not p.is_disabled then p.id else null end) as profiles
    , count(distinct case when not p.is_disabled and p.service = 'instagram' then p.id else null     end) as ig_profiles
    , count(distinct case when not p.is_disabled and p.service = 'instagram' and service_type =     'business' then p.id else null end) as ig_biz_profiles
    , count(distinct case when up.profile_service = 'instagram' then up.id else null end) as     ig_posts
    , count(distinct timestamp_trunc(up.created_at, week)) as weeks_with_posts
    , count(distinct timestamp_trunc(up.created_at, day)) as days_with_posts
    , count(distinct timestamp_trunc(t.original_timestamp, week)) as weeks_with_actions
    , count(distinct timestamp_trunc(t.original_timestamp, day)) as days_with_actions
  from customers c
  left join segment_login_server.identifies_view i
    on i.user_id = c.account_id
  left join dbt_buffer.publish_profiles p
    on c.user_id = p.user_id
  left join dbt_buffer.publish_updates up
    on p.id = up.profile_id
    and up.created_at > timestamp_add(current_timestamp() , interval -30 day)
    and up.synced_at > timestamp_add(current_timestamp() , interval -30 day)
  left join dbt_buffer.segment_tracks t
    on t.user_id = c.account_id
    and t.original_timestamp > timestamp_add(current_timestamp() , interval -30 day)
  group by 1, 2, 3, 4, 5, 6
"
  
# query BQ
users <- dbGetQuery(con, sql)

There are around 20 thousand customers in this dataset, and they’ve all been on their plan for at least 30 days. Let’s compare the Pro and Premium users.

Total Profiles

We can start by plotting the distribution of profiles for customers. The profile limit for Pro and Premium plans is 8 profiles.

We can see that there is a relatively high proportion of Pro customers that don’t have any profiles! Let’s ignore these users and only look at those that have profiles (that aren’t disabled).

This graph suggests that Premium users tend to have more profiles. Another way to visualize this data is by plotting the cumulative distribution functions (CDFs).

This graph shows us that the distribution of profiles is similar for Pro and Premium customers (that have at least one profile connected). Premium customers are more likely to have more profiles. For example, around 75% of Pro customers have 3 profiles or less (meaning 25% have 4 or more profiles). For Premium customers, around 68% have 3 profiles or less (32% have 4 or more).

Instagram Profiles

Now let’s look at the distribution of Instagram profiles.

For those customers that have at least one profile, around 59% of Pro users and 60% of Premium users have no Instagram profiles connected. Now let’s look at Instagram Business profiles.

Pro users are also ever-so-slightly more likely to have an Instagram Business account connected! The takeaway is that Pro users are more likely to not have any profiles connected. Those that do have a profile connected are slightly more likely to have Instagram and Instagram Business profiles connected.

Posts

Now let’s compare the posting habits of each group. Let’s compare the number of weeks in which a post was created for each group.

Here we can see that Pro customers are more likely to have not created a post in any of the past 5 weeks and Premium users are more likely to have created a post in each of the past five weeks. What if we only looked at users that have at least one profile connected? We see a similar pattern.

Now let’s visualize the number of Instagram posts that users in each group created.

It’s a little hard to see the differences in these two distributions, so plotting the CDFs might again be useful. We can see that Pro users are significantly more likely to have 0 Instagram posts in the past 30 days.

This shows us that around 27% of Premium customers have no IG posts in the last 30 days, compared to around 35% of Pro customers. Premium customers tend to share more Instagram posts in general.

Company Type

Let’s compare company types (for those that answered the onboarding survey).

Pro users are slightly more likely to be B2B brands, personal users, or give no response. Premium users are more likely to be online or physical stores.

Company Size

Let’s use the same approach to compare company size.

Pro users are significantly more likely to have less than 5 employees, whereas Premium users are significantly more likely to have 5-50 employees.

Instagram Follower Counts

Now lets compare the follower counts for the Instagram profiles of a random sample of Pro and Premium users. Let’s grab 20 Pro users first.

# get random sample of pro users
pro <- users %>% 
  filter(plan_id == "pro_v1_monthly" & ig_profiles >= 1) %>% 
  sample_n(size = 20, replace = FALSE) 

# get random sample of premium users
premium <- users %>% 
  filter(plan_id != "pro_v1_monthly" & ig_profiles >= 1) %>% 
  sample_n(size = 20, replace = FALSE)

# join datasets 
both <- pro %>% 
  bind_rows(premium)

Now let’s get the profile usernames from BigQuery so that we can manually check the follower counts.

library(dbplyr)

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

# get profile ids
user_ids <- build_sql(both$user_id, con = con)

# define sql query
sql <- "
  select distinct
    p.user_id
    , p.service_username as username
    , p.service
    , p.service_type as type
    , p.is_business
  from dbt_buffer.publish_profiles p
  where p.user_id in user_id_list
  and p.service = 'instagram'
"

# final query
final_query <- gsub("user_id_list", user_ids, sprintf(sql))
  
# query bigquery
profiles <- dbGetQuery(con, final_query)

Now let’s join the profiles back into the original dataset.

# join in profiles
joined <- both %>% 
  select(account_id, user_id, plan_id) %>% 
  inner_join(profiles, by = "user_id")

Now I’ll manually check the follower counts.

Now let’s plot the average follower counts for these profiles.

We can see that Premium users are more likely to have Instagram profiles with high follower counts. Let’s control for the fact that users may have multiple Instagram profiles with high follower counts and only count the IG profile with the highest follower count for each user.

This plot suggests that Premium users are much more likely to have at least one Instagram account with a relatively high number of followers.