Friday, April 3, 2015

Analyzing your Netflix usage

How Much Netflix Are you Watching?

It's a simple enough sounding question, how much Netflix am I watching?
How much is my wife watching?
Are we getting our $8 / month worth? I think I am, but how do we know?
Am I spending too much time watching Netflix? Or no?

All of these questions occurred to me. I wanted to be able to analyze whether I was watching too much, this proved to be much harder than I originally thought.

This Calls For A Spreadsheet

What I really wanted, was a Pivot Table, something that would let me see:
  1. How much am I watching per year? per week?
  2. How much of this is movies?
  3. How much is TV shows?
  4. What are the top shows or series that consume my time?

If one year looks like it had a lot more viewing, why? What shows was I watching that year which drove up viewing?

Step 1: Get the Data From Netflix

Sign into Netflix and select the "Profile" that you want to see results for.
Click "Your Account"

On the account screen click "Viewing Activity"

Netflix shows you what you've watched, but unfortunately doesn't indicate how long you spent watching it, if you finished it and what the duration is. This makes our work much harder. For example, I started watching "Transformers: Age of Extinction" but stopped after 2 minutes. I realized I didn't have time to start it. This is a 2 hour 45 minute movie. If I count that I completely watched it this will wildly skew the data.

Once fully scrolled down, press CTRL+A and then CTRL+C (on a PC) to select all and copy the data. It can then be pasted into Google Docs (what I used) or Excel.

Step 2: Getting The Data Into Google Docs

To paste, and get better rows, I had to Paste Special -> Paste Values Only

The data then looks as follows:

  1. Some unnecessary text
  2. Rows with the viewing history, containing
    1. A column for date, in dd/mm/yy or dd/mm/yyyy
    2. A column with the title
    3. two more columns with some un-necessary text
  3. More unnecessary text

Step 3: Spreadsheet Magic

I then proceeded to spend quite a bit of time, too much time according to my wife, creating various Google Sheets formulas to process this data.

In short I did the following:

  1. Cleanup the date, by using =ARRAYFORMULA(split(A8,"/")) to split the date text into three columns
  2. Use ImportXML command to query a website to Movie runtimes, where available
    • Q8 =
    • =importxml(substitute(Q8,"TITLE",P8),"root/movie/@runtime")
  3. Check if the title has "Series:", "Season" or "Vol." in it. Usually this determines the row is one episode a TV show or not. Unfortunately this doesn't always work, but if you've spent more of your time watching a few different shows, it'll get you close.
  4.  Building a Vlookup, that looksup the duration of a show, if it isn't a movie. This lets me do one entry in a table (ie. Star Trek: The Next Generation: is 45 minutes long) and have many rows pull that for their duration
  5. Create PivotTables and do some analysis!!
I've shared my GoogleSheet file, with my Netflix history removed. This is not completely refined, but it'll give you some information. If you're interested in a copy of this, simply send me an email at: findingoptimal AT GMAIL DOT COM.

Known issues with this approach are:
  1. You have to enter the time of each TV show, and some (like Doctor Who) don't have the word Season or Series in them, this requires entering the time for many many episodes
  2. Sometimes there are multiple movies with the same title, ie. Aeon Flux from 2005 with Charlize Theron is 93 minutes whereas if you query OMDb for just "Aeon Flux", it comes back with a movie from 2006 that is 21 minutes long
  3. Sometimes certain episodes are twice as long as normal, using a vlookup with the series name saves time, but ignores these

Step 4: Analysis!

Even with some of the data analysis issues previously mentioned, this spreadsheet still gives good directional information.

Looking at consumption, by year, by week, with COUNTA of Show/Movie - showing the number of episodes/movies started and SUM of Dur Hour showing the sum in hours of time spent watching, we see the following:

I've added an average calculation off the side, but you can clearly see 2015 is on pace to have much higher viewing than 2014. In 2014, there were 235 items watched, with an estimated 217 hours of content. Only 1/3 of the way through 2015 I'm at 165 (or 70% of 2014) items watched, for 117 hours of content (54% of 2014).

Consumption is definitely up.
The question of value for $8/ month is pretty easily answered, since I have had anywhere from 200 to 350 hours of viewing per year, (not including other family members who use separate profiles) for  $72, which works out to $0.20 to $0.35 per hour.

By Grouping by Show/Movie Title the sum of the time spent watching it, sorted descending, then by year, we can see which shows have taking the most time.

My wife, who never saw the original, and I are watching all of Star Trek The Next Generation, from the beginning. Not surprisingly, this is a lot of television.

A look at previous years, show that our movie consumption (the Is Movie = 1) has collapsed compared to previous years. This is in part due to Star Trek TNG crowding out other viewing, and with a new baby around it's harder to want to watch, and keep watching something that's more than 45 minutes long.

Looking at just most recent, the viewing is as follows (note, items with a 0 under SUM of Dur Hour, are shows where I didn't populate a time for the vlookup due to not watching it all the way through or because there just weren't many views.


While there are some definite accuracy issues, directionally this reviews the pace of TV watching.

Netflix is absolutely worth the money. Given the vast number of hours of content I'm viewing, nevermind what my wife watches by herself, it's definitely worth $8.

Typical weeks are averaging 9 hours. The original goal of one episode per day, equating to 5.25 hours per week. This viewing has been nearly on target, sometimes going over. However, adding a movie or two on top of this drives viewing higher than previous years. One should be careful when starting a new TV series, because it is very difficult to not just keep watching another, particularly after Netflix launched the auto-play next feature.


Post a Comment