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.
Data Collection
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 web.
-- 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 1The 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.
Exploratory Analysis
Next let’s visualize the number of users that completed each step of the onboarding flow.
## `summarise()` ungrouping output (override with `.groups` argument)
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.