How much is a reddit upvote worth? We’ll find how much an upvote is worth, measured by imgur views.

Felipe Hoffa

Oct 24, 2016·4 min read

Previously, on reddit: I posted a visualization analyzing reddit’s happiness, which got more than 4,300 upvotes on /r/dataisbeautiful. This translated into more than 1.7 million imgur views, so I started wondering: How do upvotes translate into pageviews?

How-to

Step 1: Find the top 40 subreddits

Criteria: Let’s find the subreddits which posted most imgur pictures having a score>200, during August 2016.

Warning: Some of these subs are NSFW. We are only going to analyze them though, so all is good unless you choose to go explore them further.

#Find the 40 top subreddits by imgur posts
SELECT subreddit, COUNT(*) c
FROM [fh-bigquery:reddit_posts.2016_08] 
WHERE score > 200
AND url CONTAINS 'imgur'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 40

Step 2: Retrieve # of imgur pageviews for some sample posts

I don’t have the number of pageviews for each imgur url, but I can use imgur’s API to get those numbers. Their API is rate limited, so I will only be able to get a random sample of urls.

# Choose 12 random imgur posts for each of the 40 top subreddits.
SELECT subreddit, url, score
FROM (
  SELECT FIRST(subreddit) subreddit, url, COUNT(*) c, FIRST(RAND()) rand, ROW_NUMBER() OVER(PARTITION BY subreddit ORDER BY rand) rn,
    FIRST(score) score
  FROM [fh-bigquery:reddit_posts.2016_08] 
  WHERE score > 200
  AND subreddit IN (SELECT subreddit FROM (
    SELECT subreddit, COUNT(*) c
    FROM [fh-bigquery:reddit_posts.2016_08] 
    WHERE score > 200
    AND url CONTAINS 'imgur'
    GROUP BY 1
    ORDER BY 2 DESC
    LIMIT 40
  ))
  AND url NOT IN (
    SELECT url
    FROM [fh-bigquery:reddit_posts.2016_07] 
    WHERE score > 200
    AND url CONTAINS 'imgur'
    GROUP BY 1
  )
  AND url CONTAINS 'imgur'
  GROUP BY url
  HAVING c=1
)
WHERE rn<=12

Some Python to retrieve imgur’s stats with their API:

for x in res:
  path = re.search(r'com/([^\.\?]*)?', x['url']).group(1)
  if 'a/' in path or 'gallery/' in path:
    id = path.split('/')[1]
    if id in tot:
      continue
    tot[id]=x
    try:
      images = client.get_album_images(id)
      views = client.get_album_images(id)[0].views if images else 0
    except ImgurClientError as e:
      try:
        views = client.get_image(id).views
      except ImgurClientError as e:
        views=0
    print x['subreddit'], id
  else:
    id = path
    if id in tot:
      continue
    tot[id]=x
    try:
      views = client.get_image(id).views
    except ImgurClientError as e:
      views=0
    print x['subreddit'], path
  x['views']=views

Step 3: Chart average by subreddit

Step 4: discuss

Lowest energy: /r/the_donald averages more than a 1,000 upvotes per imgur submission, but in average these images get less than 21,000 views.

Top energy: /r/celebs, /r/gaming, /r/aww, and /r/interestingasfuck gather a lot of pageviews per upvote. Looking at the individual posts, this seems to be a product of some outliers with a lot of pageviews and few votes. I would remove these outliers (top left of the step 2 viz), as these pageviews probably came from a different source. Note that the query already removes links that where posted to multiple places in August and July, but that filter wasn’t enough. Still, even without those points, subs like /r/interestingasfuck continue to get top views per upvote.

Outliers removed: With the top left outliers removed, the top energy subreddits were /r/gifs, /r/interestingasfuck, /r/pokemongo, /r/aww, /r/WTF, and /r/Celebs.

Next steps: Go deeper!

Want more stories? Check my medium, follow me on twitter, and subscribe to reddit.com/r/bigquery. And try BigQuery — every month you get a full terabyte of analysis for free.

Thanks to /u/stuck_in_the_matrix for sharing and collecting this data! Check pushshift.io for a realtime stream of reddit posts and comments.