In this post we will estimate how successful New Publish users are in connecting social accounts. We will define and calculate a measure of success for each social network, and look at how this success rate changes over time.
We define a connection “attempt” as the event that occurs when a user selects a profile type after clicking the “Add a New Social Account” button in Org Admin. An attempt is considered successful if a profile of the same social is created for that user within 30 minutes of the connection attempt event.
For the sake of simplicity, I’ve excluded Google+ and Pinterest profiles from this analysis.
Summary
Linkedin profiles have the highest connection success rate at around 80%, followed by Twitter profiles (75%), Facebook pages (71%), and finally Instagram profiles (52%).
The connection success rate of Linkedin profiles seems to be improving significantly over time, while the connection success rate of Instagram profiles seems to have decreased significantly in recent weeks.
Data Collection
We’ll use the SQL query below to collect the data from Redshift. We’ll only look at users that signed up on or after October 1, a somewhat arbitrary date.
For each network, we count the number of users that attempted to add a profile (they do this by clicking the “Add a Social Account” button in Org Admin), and we consider them successful if a new profile of the same type is created within 30 minutes of clicking the button.
select
date_trunc('week', date(a.created_at)) as attempt_week
, scope4 as network
, count(distinct a.user_id) as users_attempted_connection
, count(distinct p.user_id) as users_connected_profile
from dbt.users as u
inner join dbt.user_experiments as e
on u.id = e.user_id
and e.name like 'new_publish_new_buffer_free_users_phase%'
and e."group" = 'enabled'
left join dbt.actions_taken as a
on u.id = a.user_id
and a.full_scope like 'business organization_admin add_social_account_to_organization%'
and a.created_at >= '2018-10-01'
left join dbt.profiles as p
on p.user_id = a.user_id
and p.service = lower(a.scope4)
and p.created_at >= a.created_at
and p.created_at <= dateadd(minute, 30, a.created_at)
where u.created_at >= '2018-10-01'
group by 1, 2Exploratory Analysis
Let’s calculate the overall success rates for each social network. We do this by dividing the number of users that were able to successfully connect profiles for each network by the number of users that attempted to connect profiles from each network.
## `summarise()` ungrouping output (override with `.groups` argument)
Linkedin profiles have the highest connection success rate at around 80%, followed by Twitter profiles (74%), Facebook pages (71%), and finally Instagram profiles (52%). Instagram profiles have the lowest connection success rate by a wide margin.
We can also view how these connection rates have changed over time.
We can see here that the connection success rates are fairly consistent over time. Linkedin is consistently the highest and Instagram is consistently the lowest. Interestingly, the connection success rate of Linkedin profiles seems to be improving significantly over time, while the connection success rate of Instagram profiles seems to have decreased significantly in recent weeks.