In this post we’ll look at the steps in a new Publish user’s journey up to the point of connecting a new profile. When someone signs up for Publish, they are shown a survey with questions about their business. This survey was added very recently, so we will exclude it from this analysis.
We’ll gather the data we need for this analysis with the SQL queries below. We only want to look at web signups because mobile signups have different flows, so we’ll join the
users data frame with the
signup_client data frame.
The first SQL query gathers all users that signed up for Publish since April 1, 2019. It also gathers some key actions, like viewing the Org Admin page and attempting to connect a profile.
The second query grabs the signup client of these users. We’ll only look at users with a signup client of
Buffer Publish or
-- users select u.id as user_id , count(distinct p.id) as profiles , count(distinct up.id) as updates , count(distinct case when a.full_scope = 'publish initial_loading profile_loading_redirect' or a.full_scope = 'publish default_page connect_social_account' then a.id else null end) as reached_profile_connect_page , count(distinct case when a.full_scope = 'business organization_admin page_view accounts connect' then a.id else null end) as org_admin_visits , count(distinct case when a.full_scope like 'business organization_admin add_social_account_to_organization%' then a.id else null end) as connection_attempts from dbt.users u left join dbt.actions_taken a on u.id = a.user_id and a.created_at <= dateadd(minute, 10, u.created_at) -- action taken within 10 minutes of signing up left join dbt.profiles p on u.id = p.user_id and p.created_at <= dateadd(minute, 10, u.created_at) -- profiles created within 10 minutes of signing up left join dbt.updates up on up.user_id = u.id and up.was_sent_with_buffer where u.created_at >= '2019-04-01' group by 1
The query above returns a dataset of 146 thousand users that have signed up since April 1.
-- signup clients select user_id , json_extract_path_text(extra_data, 'client_name') as signup_client from dbt.actions_taken where full_scope like 'signup%' and created_at >= '2019-04-01'
The query above returns a dataset of 145 thousand users with a signup client that have signed up since April 1.
# join the dataframes to only look at web signups signups <- users %>% inner_join(signup_client, by = "user_id") %>% filter(signup_client == "Buffer Publish" | signup_client == "web")
Once the two datasets have been joined, we have around 80 thousand web signups to work with.
Next let’s visualize the number of users that completed each step of the onboarding flow.
We can see that there is a big dropoff between the first and second steps. Only around 70% of users that sign up reached Org Admin. There is also a big difference between the number of users that connected a profile and the number of users that created an update. Only around 63% of users that connected a profile created an update with Buffer.