Gamebook Data Spreadsheet - Weeks 1 & 2

[KB_fan]

I won't have time until this evening to write up any analysis or post excerpts of tables from my gamebook spreadsheet.  But for now, I wanted to at least post the spreadsheet here for people to download if they want it.  It contains data from the publicly available gamebooks downloaded from Panthers.com for the week 1 (JAX) and week 2 (HOU) games. 

Here's the link:  Gamebook_2015_02.xlsx

This data is manually entered, so there could be a few errors.  If you find any, give a shout.  There may be slight differences in the ways I've calculated or labeled something from the official NFL stats.

What this spreadsheet does, which is not something I've found elsewhere at any of the NFL stats sites, is allow looking at specific stats game by game, side by side, as the season progresses, i.e. moving beyond mere single game totals or season averages.

For now, there are no player stats, only team stats.

In addition to the data from the gamebook itself, I've set up certain formulas and created a number of personalized variables to track.  Here are a few examples: 

• I've added lines tracking "big plays" and big play differential.  Big plays are not listed on the gamebook specifically, but have to be calculated by reviewing the play by play details. 
• I've set up calculations to measure the "rushing effectiveness ratio"  (rushing yards % of total yards / rushing plays % of total plays) and "passing effectiveness ratio"  (passing yards % of total yards / passing plays % of total plays)
• I've created a stat I'm calling "field position efficiency" to measure the % of scores on drives begun in opponents territory.
• I've created a stat to measure "turnover scoring efficiency" to measure the % of scores on drives following a turnover

Etc. etc.

I'd welcome feedback of any kind including questions (if something is unclear), corrections if you find any errors, suggestions for other stats to calculate or ways to make something clearer, etc.

I know it's a LOT of data, with some stats more important or interesting than others.  But as this is my first season doing this, I decided to err on the side of comprehensiveness so I could get a feel for what data is truly most useful to track and view side by side each week in order to get a feel for the evolution of the season.

I'll try to post some comments later today about what has struck me comparing the HOUSTON game to the JAX game, and I may be able to highlight certain sections of the table.

Feel free to share this or adapt it for your own use as needed.  I'll try to post an updated version of this data each week.

[UpstatePanther]

Holy crap that's a lot of data! Pie and pie again my friend!

[shaka]

KB_Fan, I tried sending this to you in a message but I couldn't attach the spreadsheet so I've got to do it here.  Sorry!

 

Since you requested it, and you've been doing a ton of work I've been looking at, I figured I can offer some feedback on the formatting. Please take it with a grain of salt as presenting data to management is part of my job.  I also would hate to see such hard work/amazing data get less views because of formatting.  

 

• Use tabs to separate the stats.  Small manageable tabs are easier to sift through than one giant tab

 

• More white space and put it in some headers so we know what we're looking at

 

• Alternate colors between rows and use font color and size to emphasize interesting stats as opposed to cell highlighting

 

• This one depends on your comfort level with excel, but I prefer using named ranges to help when going cross tabs.  You have cell x4 as the number of games played, so I just made a named range called "Games_Played".  Now you can just type in Games_Played in any formula instead of having to reference that cell.  IE sum(cells containing rushing yards)/games_played.  Again just a suggestion to help with long term maintenance of spreadsheets.  

 

I've attached a small example here (Gamebook_2015_02.xlsx) of my suggestions for some offensive stats to help illustrate my points.  Or if you're opposed to doing a little re-formatting I can do some with your permission.  I think the amount of data you collected is incredible and I really appreciate the work you've been doing for the huddle.  Keep up the good work! 

 

Gamebook_2015_02.xlsx

[KB_fan]

Since you requested it, and you've been doing a ton of work I've been looking at, I figured I can offer some feedback on the formatting. Please take it with a grain of salt as presenting data to management is part of my job.  I also would hate to see such hard work/amazing data get less views because of formatting.  

Thanks, great suggestions, and I like the data grouping / formatting suggestions.  I'm very comfortable with Excel, so could do all you suggest as time permits. 

Normally I do try to make data presentation and formatting a priority.  I'd not yet invested much time / effort into the format of the spreadsheet because I was focused more on deciding content and figuring out what kind of data I could pull from the gamebooks beyond the obvious stats that are online everywhere.   I should note that if the order of the data groupings seems a bit strange, it's because it's more or less organized in the order the data appears in the gamebook which saves a lot of time in the data entry.  I can go page by page and just plug in the data without flipping back and forth.  But I realize it creates a pretty jumpy flow of information... with rushing and passing data found in two separate sections each, and offense and defense data constantly switching back and forth from one section to another...

The next two weeks are very busy ones for me, so probably all I'll have time for is just entering the data following the game, which takes about an hour for the simple data and than another 1 - 1.5 hours to pull the drive data / game at a glance data from the play by play section and drive charts.  But maybe in the bye week I can do some work on formatting upgrades.  Also by then we'll have a full quarter of the season to analyze.   I think what I'm likely to do is set up a separate page of the workbook which pulls data from ranges on the main page to create some nice tables and graphs tracking key stats game by game and quarter by quarter, or compares winning games and losing games (though hopefully that won't be possible this season since I hope we'll keep winning!)

By then too I think it will become clearer what are some of the key stats to really focus in on this season...

For now, this is really pretty much a raw data warehouse and sort of a fun personal experiment that I figured would be worth sharing with others.  But I will definitely plan to work on the "decorations" and increasing the utility and ease of navigating the spreadsheet and isolating key data soon! 

Thanks again @Shaka for taking the time to review this and give feedback and send along the formatting example.  It's helpful!

[KB_fan]

I've found a few things worth highlighting as I've continued to review the gamebook data from the 1st & 2nd weeks of the season.

There's really encouraging improvement in our rushing offense and our run blocking defense.  I find the increase in our running game especially impressive given that it came against the feared Houston D.

We had slightly fewer rushing attempts against Houston, but 67 more yards, meaning a huge 2.2 yard increase in average yards / rushing play.

On Defense, it was exactly the opposite.  Houston rushed more than JAX, but gained fewer yards against us. 

The 111 rushing yards differential between us and Houston is pretty amazing.   Great too to see that our number and % of big rushing plays (10+ yards) increased nicely. 

[KB_fan]

Here's what the passing data looks like for games 1 & 2:

Although Cam's pass completion % decreased from week 1 to week 2, our passing yards went up, and the yards / completion went up thanks to the increase in big plays, especially the two long TD passes. 

 

[KB_fan]

As I've mentioned in several threads, Brad Nortmann was a big part of our win against Houston.  From week 1 to week 2, he brought up his punt average by a pretty amazing 9 yards per punt, averaging just under 54 yards per punt on Sunday:

His two game average of 50.6 yards / punt is 4th best in the league right now.

 

(By the way, just a spreadsheet formatting comment.  The pale yellow lines don't have special significance.  They're just my way of distinguishing between formulas that calculate a figure automatically, rather than fields that need data entry.  Sorry for any confusion.)

 

[KB_fan]

Continuing to look at Special Team performance -

It's nice to see Ted Ginn making a difference on our punt returns.  We're averaging 45 yards per game on punt return right now, for an average of 12.9 yards / return - currently 7th best in the league.

Last year we ranked 18th in the league with an average of 8 yards / return and 5 fumbles.  Ouch.

Ginn does have a high # / % of fair catches.  Currently we're tied for the most fair catches in the league.  But I'll take a few more fair catches if it means no fumbles this year.

We seem to be doing better so far this year in return coverage as well, allowing only 9.6 yards on return. (ranked 17th).  That may not sound stellar, but last year we were worst in the league, allowing 15.5 yards / return.

http://espn.go.com/nfl/statistics/team/_/stat/returning/sort/yardsPerPuntReturn/position/defense/year/2014

 

[shaka]

Just wanted to say again I really appreciate this stuff and have probably spent more time lurking in your topics than is considered healthy.  

 

I'm really interested in watching the passing attempt differential stat through the season.  I think that will be one of those stats that indicate if we're playing the type of game we want.  

[KB_fan]

Just wanted to say again I really appreciate this stuff and have probably spent more time lurking in your topics than is considered healthy.  

 

I'm really interested in watching the passing attempt differential stat through the season.  I think that will be one of those stats that indicate if we're playing the type of game we want.  

Oh no... sounds like I've got a stalker!  LOL...

Seriously, thanks for the encouragement.  It's much more fun to be a data geek when you can find others who also enjoy digging into the numbers.  I'm glad to share what I'm doing.  It really makes it much more enjoyable for me, and I'm learning alot from the feedback and discussions.

[iBBB]

Holy stats Batman!

 

Incredible collection of stats. Although I am still going through the spreadsheet, I would also suggest you have a tab for graphs and charts. Make it a hell of a lot easier to understand for peope that don't like numbers.

[CLTPanther]

No pivot tables or pie charts (mmm pie)?.. jesus christ, excel fail!

[Murrican]

Love the work KB_Fan.  As a data geek myself, I respect the grind.

My day job has me staring at rows and columns all day long, so I haven't felt the urge to dig in just yet. Hopefully one of these weekends I can take a few hours and mess around with the data a little bit. 

[KB_fan]

OK, now changing gears a bit.  Let's look at 1st downs, and also 3rd & 4th down conversion efficiency:

Panthers are consistently getting 36% of our 1st downs on rushing plays.  (But rushing plays account for nearly 49% of our total plays, so proportionally we get a much higher % of 1st downs on passing plays).

Giving Houston 24% of their 1st downs on penalties last week was AWFUL.  I fully hope and expect that will decrease dramatically.

Our 3rd down conversion % dropped considerably from week 1 to week 2, but is still a pretty healthy 40%, and we've done a good job in limiting our opponents 3rd down conversions, holding them to only 26%.  That's a nice number - currently 2nd best in the league behind Denver.