In this quick analysis we’ll try to get a better understanding of how many profiles lose connections to their respective social networks. The key field we’ll examine is oauth_broken which indicates whether a profile’s connection to its social network is broken at the time at which we try to send an update.
We’ll collect the data from the publish_base_profiles table in BigQuery.
# set project id
project_id <- "buffer-data"
# sql query
sql_string <- "
select
id
, created_at
, service
, oauth_broken
from dbt_julianwinternheimer.publish_base_profiles
where created_at >= '2019-01-01'
and service is not null
and service != 'appdotnet'
and service != 'google'
and oauth_broken in ('true', 'false')
"
# execute the query and store the result
profiles <- query_exec(sql_string, project = project_id, use_legacy_sql = FALSE, max_pages = 100)Overall Rates of Disconnection
Let’s plot the proportion of profiles with a oauth_broken value of true for each social network.
We can see that Instagram and Facebook profiles are the most likely to have an oauth_broken value of true. This seems to be consistent with our experience with Facebook and Instagram connection flows so far.
The last thing we’ll look at is how these rates have changed over time.
We can see that the proportion of Instagram profiles with a broken connection has increased in recent weeks relative to the other social networks. This may be partly because we have tried to post more updates recently for profiles that have been added in recent weeks.