The Law and Order Database: All 20 Seasons

450 people died to make this spreadsheet possible.

At Overthinking It, Law & Order is analyzed by two separate yet equally important groups: the people who watch the show and send in the data, and the people who build the spreadsheets. These are their findings…

In May 2010, I announced an effort to crowdsource a list of how all 456 episodes of Law & Order ended. I predicted that it was going to be a while before we could unveil the results:

Years from now, long after you’ve forgotten all the crazy revelations in the final episode of Lost, you’ll still be catching reruns of L&O after work, marveling at Dennis Farina’s mustache. And when you do, come back here and tell me if the good guys won that day.

Well, it’s “years from now,” and we did it. And by “we,” I mean mainly Josh Kyu Saiewitz, who has been watching the show in order and emailing me the results. Thanks to him, I reported on the first 10 seasons back in February 2011. Now we’ve got all 20 seasons plugged into Excel, and it’s time to bring the evidence to the grandest jury of all: you, the overthinkers.

First off, a note about how I categorized these. There are a lot of Law & Order episodes in which an “opening” prosecution leads to a more interesting “final” prosecution. (Think about all those episodes in which there’s a hitman, but also a person who hired him or incited him to violence.) In these cases, I only considered the “final” prosecution that the episode is leading up to. For instance, in the season 10 episode “Gunshow,” a man kills 15 people in Central Park. Police catch him relatively early and he pleads out. But then McCoy decides to go after the gun manufacturer, who he accuses of purposely making the semi-automatic weapon easy to convert to fully-automatic. He wins the jury verdict, but the judge throws the conviction out. So even though they successfully nail the actual killer, this episode goes into the database with an outcome of “Not Guilty.”

Okay, ready for some graphs? Chung-CHUNG.

For starters, let’s look at the frequency of each outcome. Since multi-part episodes are meant to be one story, I’m only counting the final part, giving us a total of 450 L&O stories to calculate percentages with.

Click to enlarge!

“Implied win” refers to episodes in which you don’t see a plea bargain or Guilty verdict, but it’s pretty clear that’s the way things are headed. For instance, if the killer’s wife tearfully agrees to testify against him and then the episode ends, it’s an “implied win.” We don’t know the outcome, but we are led to believe it’s going to be some flavor of Justice. (The rare cases where the result was completely unclear went into the Other category.)

Over the entire run of the show, more than a third of all the episodes ended in Guilty verdicts, while another third ended in plea bargains. 80% of episodes ended in solid wins: either Guilty verdicts, plea bargains, or implied victories. That’s not too shabby, considering that the actual NYPD has a homicide clearance rate of about 50%. (Although you have to figure Law & Order isn’t meant to represent every case these detectives investigated; in 20 seasons, I don’t think there was a single murder that didn’t result in an arrest.)

(UPDATE 12/10/12: One of the commenters on Reddit has pointed out that the “clearance rate” has nothing to do with convictions, only arrests. In that case, Law & Order‘s clearance rate would be nearly 100%, since even in the rare episodes without a trial somebody usually gets arrested. I guess I’d know this stuff if I had watched The Wire.)

Another thing that’s not realistic is that there are more Guilty verdicts than plea bargains. In real life, about 95% of all felony convictions are pleas. And going back to the data for just seasons 1-10, we see that the plea bargain used to be the most common outcome (by a smidge).

That means that pleas must have fallen off dramatically in seasons 11-20. Let’s look at the data in a different way.

We can see that after the first season, in which the writers barely knew the plea bargain existed, it quickly became the most popular outcome… until season 14, when it was eclipsed by the Guilty verdict and stayed down until season 18.

Speaking of season 14, check out how the rate of Not Guilty verdicts plunges and hits 0% in the show’s final season.

But in the interest of fairness, I should point out that since there are only a handful of Not Guilty verdicts per season, there’s not a lot of data going into this trend. Here’s another way to display the same chart.

Nevertheless, I have a theory to propose. Look at the Not Guilty rate plotted against the show’s Nielsen ratings.

Viewership peaks in season 12. Then it starts to drop season after season. By season 14, Dick Wolf feels like he has to respond. So what does he do? He cuts down on the Debby Downer Not Guilty episodes while simultaneously reducing the number of plea bargains in favor of clear-cut Guilty verdicts. In the final season, with the show hemorrhaging viewers and facing cancellation, he didn’t dare air a single episode where the bad guy gets away scot free.

It’s an interesting theory, but like I said, just looking at Not Guilty episodes doesn’t give us many data points. Instead, let’s turn to a different metric: the Success Rate. Success Rate is ALMOST the same as Guilty + Plea + Implied Win, but not quite. Sometimes in Law & Order there are wins that feel like losses and losses that feel like wins. Success Rate is basically a measure of whether the D.A. is satisfied with the outcome (irregardless of how the outcomes appears to the public). For instance, look at the finale of season 18, “Excalibur.” In order to convict the murderer, Jack McCoy needs the governor to testify, which would reveal that he has a thing for prostitutes. The governor engineers a plea bargain, thus avoiding the sex scandal. In this case, the outcome may be Plea Bargain, but it’s definitely not a success; McCoy’s case was completely derailed.

Or take the season 13 episode “Panic.” Towards the end, it starts to seem that the defendant’s daughter may be the real killer. The defendant then offers to take a plea in order to shield her from investigation. McCoy doesn’t want to do it, but in the absence of further evidence he has no choice. So once again it’s a plea bargain but not a success.

Success Rate is obviously subjective, but I think it’s a better measuring stick than simply adding up Guilty + Plea + Implied Win. It’s an attempt to look past the verdict and determine how the ending felt.  (You might say it’s a CSI solution to a Law & Order problem.) Here’s the Success Rate plotted against the show’s ratings.

Wow, look at season 17! That was a year in which the show’s ratings hit an all-time low. It was also a year in which every episode but one was a clear victory for the D.A.’s office. You could argue that the high Success Rate might have contributed to the low ratings, but note that the success rate dropped between seasons 13 and 14, and so did the ratings.

What it looks like to me is that starting in season 14 and peaking in season 17, Dick Wolf was trying to play it increasingly safe: no unsatisfying outcomes that could turn even more of his shrinking audience away. In fact, when Law & Order was renewed after season 17, the New York Times described it as “a reprieve for the show, which had seemed increasingly likely to be canceled over the last few weeks.” I’d say the high Success Rate was defensive ball.

So what happened in season 18, when the Success Rate dropped and ratings actually went up? Jeremy Sisto, Anthony Anderson, and Linus Roache happened. The show got some fresh blood, which won back some of the old viewers (or at least stopped the bleeding). That bought the writers enough breathing room to do a few more episodes with dark endings.

What I’d say the numbers show (or at least hint at) is that when the ratings are dropping, a showrunner feels like he has to do something. With a show like Law & Order, that means giving the people feel-good wins. But of course, that wasn’t what made the show a success in the first place, and it certainly wasn’t what was going to save it.

In other words, the 95% Success Rate was the Law & Order equivalent of Fonzie jumping the shark.

Want to take a crack at your own analysis? Download my spreadsheet and have at it! For now, I rest my case.

(UPDATE: Make sure to check out Part 2 of my analysis, where I see if the Law & Order success rate correlates with any data sets from the real world.)