What Proportion of Active Users Are Tagged?

TLDR

• Around 44% of active Publish users have been tagged by the target customer survey.
• Around 48% of active Analyze users have been tagged by the target customer survey.
• Around 48% of active Reply users have been tagged by the target customer survey.

Paying Customer Identify Traits

Let’s start with Publish users. It should be noted that there are millions of inactive free accounts, so we don’t expect many of those to be tagged with a currentPublishPlan trait. Therefore we’ll only look at paying Publish customers.

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

# define sql query
sql <- "
  select distinct
    u.id as user_id
    , u.billing_plan
    , i.current_publish_plan
    , i.is_paying_publish_customer
  from dbt_buffer.publish_users u
  left join segment_authentication_service.identifies i
    on i.user_id = u.account_id
  where u.billing_plan != 'individual' 
  and u.billing_plan != 'free'
  and u.billing_plan is not null
"
  
# query BQ
users <- dbGetQuery(con, sql)

Now let’s calculate the proportion of all paying users that are tagged.

# calculate proportion that are tagged
users %>% 
  count(is_paying_publish_customer, name = "users") %>% 
  mutate(prop = percent(users / sum(users)))

## # A tibble: 3 x 3
##   is_paying_publish_customer users prop 
##   <lgl>                      <int> <chr>
## 1 FALSE                       1729 2%   
## 2 TRUE                       38981 56%  
## 3 NA                         29278 42%

Around 80% of paying Publish customers are not tagged by IsPayingPublishCustomer. In addition, around 1.6% of paying Publish customers are incorrectly tagged (i.e. they are given a value of FALSE instead of TRUE).

Now let’s look at Analyze customers.

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

# define sql query
sql <- "
  select distinct
    u.id as user_id
    , u.current_plan
    , i.current_analyze_plan
    , i.is_paying_analyze_customer
  from dbt_buffer.analyze_users u
  left join segment_authentication_service.identifies i
    on i.user_id = u.account_id
  where u.current_plan != 'free'
  and u.current_plan is not null
"
  
# query BQ
analyze_users <- dbGetQuery(con, sql)

# calculate proportion that are tagged
analyze_users %>% 
  count(is_paying_analyze_customer, name = "users") %>% 
  mutate(prop = percent(users / sum(users)))

## # A tibble: 2 x 3
##   is_paying_analyze_customer users prop 
##   <lgl>                      <int> <chr>
## 1 FALSE                         31 6%   
## 2 TRUE                         526 94%

All paying Analyze customers have a value in isPayingAnalyzeCustomer, but 31 (6%) are incorrectly labeled FALSE.

Target Customer Survey

We’ll use the SQL query below to retrieve data related to the target customer survey from BigQuery.

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

# define sql query
sql <- "
  select
    i.company_type
    , event
    , count(distinct t.user_id) as users
  from dbt_buffer.segment_tracks t
  left join dbt_buffer.segment_identifies i
    on t.user_id = i.user_id
  where t.original_timestamp > timestamp_add(current_timestamp() , interval -60 day)
  and event in ('post_created', 'report_module_added', 'conversation_closed')
  group by 1, 2
  order by 3 desc
"
  
# query BQ
tags <- dbGetQuery(con, sql)

Let’s see the overall proportion of active users that have responded to the target customer survey.

We can see that around 56% of users that have triggered a Segment event have not been tagged by the target customer survey.

Publish

Now let’s only look at Publish customers.

Around 56% of active Publish users don’t have a company type associated with them.

Analyze

Around 52% of active Analyze users do not have a company type associated with them.

Reply

Around 52% of active Reply users do not have a company type associated with them.