Pinterest Posting Frequency

Summary

Data from the past month shows that there are users that exceed the limit of 200 pins/day every day. This is a small group of around 30 users in total that shares more than 400 pins per day on average.

Data Collection

We’ll gather all Pinterest posts created by Publish users in the past month.

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

# define sql query
sql <- "
  select
    date(up.created_at) as created_date
    , up.user_id
    , count(distinct up.id) as posts
  from dbt_buffer.publish_updates up
  where up.synced_at >= '2020-04-15'
  and up.created_at >= '2020-04-15'
  and up.profile_service = 'pinterest'
  and up.status not in ('service', 'service_reply')
  group by 1, 2
"
  
# query BQ
pins <- dbGetQuery(con, sql)

# save data
saveRDS(pins, "pin_freq.rds")

How Often Do We Exceed API Limits?

Let’s count the number of times that a user has exceeded the 200 pins/day limit.

# count times posts > 200
pins %>% 
  mutate(exceeds_limit = posts > 200) %>% 
  group_by(created_date, exceeds_limit) %>% 
  summarise(users = n_distinct(user_id)) %>% 
  pivot_wider(names_from = exceeds_limit,
              values_from = users)

## # A tibble: 34 x 3
## # Groups:   created_date [34]
##    created_date `FALSE` `TRUE`
##    <date>         <int>  <int>
##  1 2020-04-15      1075      7
##  2 2020-04-16      1086     10
##  3 2020-04-17      1044      7
##  4 2020-04-18       618      8
##  5 2020-04-19       629      4
##  6 2020-04-20      1164      6
##  7 2020-04-21      1099      6
##  8 2020-04-22      1137      9
##  9 2020-04-23      1067      6
## 10 2020-04-24       999      6
## # … with 24 more rows

Every day there are a few users that exceed the 200 pins/day limit.

Distribution of Pins Per Day

Let’s try to get a better sense of how many pins users are sharing per day by plotting the distributions. We’ll separate those users that regularly exceed Pinterest’s API limits from those that do not.

These plots show us that most of the time, users share less than 20 updates per day to Pinterest. However, there is a small group of users that share well above 200 updates to Pinterest per day.

# summary stats
pins %>% 
  mutate(exceeds_limit = posts > 200) %>% 
  group_by(exceeds_limit) %>% 
  summarise(users = n_distinct(user_id),
            avg_pins = mean(posts, na.rm = TRUE),
            med_pins = median(posts, na.rm = TRUE),
            sd_pins = sd(posts, na.rm = TRUE))

## # A tibble: 2 x 5
##   exceeds_limit users avg_pins med_pins sd_pins
##   <lgl>         <int>    <dbl>    <dbl>   <dbl>
## 1 FALSE          4751     5.92        2    13.8
## 2 TRUE             32   432.        381   188.