Box scores and DCs: sharing my analysis spreadsheet for everyone to use
Posted: June 30th, 2024, 10:38 pm
Box scores and DCs: sharing my analysis spreadsheet for everyone to use
Everyone knows that one of the most critical parts of the game is optimizing your team’s DC. Knowing the right DC to use requires understanding the input variables (DC rotation and settings) to drive the desired output (individual player performance and team performance), which in turn requires analyzing your team’s performance in response to different DCs. The key to doing this analysis is being able to understand performance across small stretches of games as correlated to DC changes, which isn’t easy due to the layout of the website (which only shows individual box scores and season-long aggregate averages).
To help do this, over the past few seasons I've been refining a spreadsheet format that makes it as easy as possible to analyze DC behavior via box scores. This has helped me better understand the implications of DC changes/experiments throughout the season, how to use my players, and what DCs to set for each playoff matchup. The purpose of this article is to "open source" this spreadsheet so you can use it to analyze your own teams as well.
I'll be the first to tell you that I’m not an expert on DCs and that many (most) in the league are a lot better at it than me. In addition, some of you probably use more sophisticated tools (if so, I’d love to see them!). Others of you might think this is crazy, way too much effort, whatever - all I can tell you is that it’s a lot less effort than you think, likely way more interesting than you think, and some of the highest-ROI time I spend understanding what moves to make for my team.
Example use cases:
- Understanding a player’s performance with different DC settings - trap, press, pace, offense. Example learning: Guards tend to shoot a lower percentage when press is increased.
- Understanding a player’s performance at different positions. Example learning: Some natural SGs perform much better at SF, or vice versa.
- Understanding a player’s performance as an option or non option. Example learning: Certain players perform better/worse as an option (and it's often not who you think).
- Understanding a player’s performance alongside other players: Example learning: Certain players tend to play better at PF alongside one player but C alongside another.
- Understanding your team’s performance with different DC settings.
And so on - the potential depth of analysis is endless (e.g. picking apart Jimmy’s performance starting at the 2 next to Deron and Jerebko at the 1/3 in an always press defense with Hill as the third option). The analysis is the fun part - this sheet is meant to make the data entry as frictionless as possible.
Requirements:
1. Copy all the rows in your team’s box score with your team's player data and paste into column T - this should populate columns T through AI
2. Fill out columns O through S with the details specific to that game - day, opponent, opponent strength (I use playoffs / tanking / bubble), and diff
3. Drag down the row you just filled out for columns O through S to cover all rows for your team’s box score for that game
4. Repeat steps 1-3 for all games of that sim
5. Fill out columns C through N with your DC settings for that sim
6. Drag down columns A and B to cover the row you just filled out with columns C through N (so that the formulas in columns A and B pull through)
7. Drag down the row you just filled out for columns A through N to cover all rows for your team’s box scores for that entire sim
8. Start analyzing the data!
Here's the link to the spreadsheet. So you can play around with it, I've loaded it with my team's data for our first 10 games of the season and last 10 games (to this point). Feel free to copy and use for your own team:
Known limitations and future extensions:
This sheet has a number of obvious potential improvements. Of note:
- If you have a player subbing across multiple positions, it's impossible to know how many of that player's minutes are at each position (if there is a way to know, please let me know). Ergo, analyzing bench player performance requires some peanut-buttering of attribution across their different DC positions.
- This currently only aggregates offensive stats. I’m working on ways to 1/ map positional matchups without having to refer to each team’s individual depth chart, and 2/ understand the team-wide battle across rebounding, possessions, etc. Ideas welcome.
I hope you find this useful, and let me know what you think!
Everyone knows that one of the most critical parts of the game is optimizing your team’s DC. Knowing the right DC to use requires understanding the input variables (DC rotation and settings) to drive the desired output (individual player performance and team performance), which in turn requires analyzing your team’s performance in response to different DCs. The key to doing this analysis is being able to understand performance across small stretches of games as correlated to DC changes, which isn’t easy due to the layout of the website (which only shows individual box scores and season-long aggregate averages).
To help do this, over the past few seasons I've been refining a spreadsheet format that makes it as easy as possible to analyze DC behavior via box scores. This has helped me better understand the implications of DC changes/experiments throughout the season, how to use my players, and what DCs to set for each playoff matchup. The purpose of this article is to "open source" this spreadsheet so you can use it to analyze your own teams as well.
I'll be the first to tell you that I’m not an expert on DCs and that many (most) in the league are a lot better at it than me. In addition, some of you probably use more sophisticated tools (if so, I’d love to see them!). Others of you might think this is crazy, way too much effort, whatever - all I can tell you is that it’s a lot less effort than you think, likely way more interesting than you think, and some of the highest-ROI time I spend understanding what moves to make for my team.
Example use cases:
- Understanding a player’s performance with different DC settings - trap, press, pace, offense. Example learning: Guards tend to shoot a lower percentage when press is increased.
- Understanding a player’s performance at different positions. Example learning: Some natural SGs perform much better at SF, or vice versa.
- Understanding a player’s performance as an option or non option. Example learning: Certain players perform better/worse as an option (and it's often not who you think).
- Understanding a player’s performance alongside other players: Example learning: Certain players tend to play better at PF alongside one player but C alongside another.
- Understanding your team’s performance with different DC settings.
And so on - the potential depth of analysis is endless (e.g. picking apart Jimmy’s performance starting at the 2 next to Deron and Jerebko at the 1/3 in an always press defense with Hill as the third option). The analysis is the fun part - this sheet is meant to make the data entry as frictionless as possible.
Requirements:
- 10 minutes per sim for data entry
- A basic knowledge of Excel / Google Sheets, particularly pivot tables
- An interest in analyzing your own team
1. Copy all the rows in your team’s box score with your team's player data and paste into column T - this should populate columns T through AI
2. Fill out columns O through S with the details specific to that game - day, opponent, opponent strength (I use playoffs / tanking / bubble), and diff
3. Drag down the row you just filled out for columns O through S to cover all rows for your team’s box score for that game
4. Repeat steps 1-3 for all games of that sim
5. Fill out columns C through N with your DC settings for that sim
6. Drag down columns A and B to cover the row you just filled out with columns C through N (so that the formulas in columns A and B pull through)
7. Drag down the row you just filled out for columns A through N to cover all rows for your team’s box scores for that entire sim
8. Start analyzing the data!
Here's the link to the spreadsheet. So you can play around with it, I've loaded it with my team's data for our first 10 games of the season and last 10 games (to this point). Feel free to copy and use for your own team:
Known limitations and future extensions:
This sheet has a number of obvious potential improvements. Of note:
- If you have a player subbing across multiple positions, it's impossible to know how many of that player's minutes are at each position (if there is a way to know, please let me know). Ergo, analyzing bench player performance requires some peanut-buttering of attribution across their different DC positions.
- This currently only aggregates offensive stats. I’m working on ways to 1/ map positional matchups without having to refer to each team’s individual depth chart, and 2/ understand the team-wide battle across rebounding, possessions, etc. Ideas welcome.
I hope you find this useful, and let me know what you think!