In my continuing series of blog posts related to combining my personal interests with my passion for Microsoft Power BI, I was thinking of how analytics has been ingrained in professional sports. For example, the rise of Sabremetrics in Major League Baseball has changed the game immeasurably in terms of position player alignment, pitch selection, and pitching matchups. In football, the selection of plays, and draft selection in particular, has become much more scientific. Since football success is heavily based on draft success, I thought that creating a Power BI model and dashboard to analyze draft success would be interesting.
So I wondered, could I create a complete searchable database of the NFL draft using Power BI, and if so how long would it take? The answer is definitely yes, and it took about eight hours in total. The resulting report is shown below, along with a summary of how I developed the database and Power BI report. At the end of this post, I have provided a link to the embedded report, and a summary of what I learned about the NFL draft that I did not know before.
Sourcing Data and Creating Calculations
The primary data source for the NFL draft data can be found here. This data source provides NFL draft history data going back to 1936. Fortunately, the data is presented in tables by team within the webpages, which made it relatively straight forward to create queries using the Web connection within Power BI. Once I created queries for each team, I then appended them together using the Append Queries function within the Power BI Query Editor. The resulting consolidated table provided details on games played, passing and rushing statistics, and defensive statistics such as sacks and interceptions.
In order to evaluate the success of the draft selections, I thought it would be helpful to include All-Pro selections. Fortunately, Pro Football Reference also has data on All-Pro selections available as a table on the Web. I then used the same technique to create a query for All-Pro data.
I created two dimension tables (Team and Year) from the underlying data sources so that I could relate the draft history data with the All-Pro data.
Finally, I wanted to create a visual appealing slicer using the NFL team logos. The chiclet slicer in the Power BI Custom Visual library provides the capability to include images in the slicer. I saved the NFL logo images to a library in Photobucket and then added the URL reference to the image for each team as a column in the Team dimension.
Now having completed the data set, I needed to create a few calculations using the Power BI DAX language to provide counts. I also used the grouping capabilities of Power BI to group positions as the historical data did not always match the current list of positions.
Creating the Report
I used the Chiclet custom visual as the primary team slicer at the top of the report. I used dropdown slicers to provide search by player, name, and position, and bar charts to show average number of games played, All-Pro and Hall of Fame counts by team.
I thought it would intuitive to display the positions on a football field image rather than simply using a table. Card visuals show counts by position with connections to related tables to provide a list of top players at each position.
I created a drill-through filter so you can right-click on the bar charts by team name see the Draft Time Series for each team.
Anomalies, Insights and Summary
One of the anomalies of the data source is that the safety position is relatively new designation and began in 2015. Prior to 2015 safeties were grouped with defensive backs.
So what insights did I glean from analyzing this data?
- The teams that had the most successful drafts in the last 20 years in terms of average games played and average All-Pro counts were the: Steelers, Cardinals, 49ers, Colts, and Ravens. However, teams with successful drafts by these measures did not always result in successful win-loss records as those top five teams were the Patriots, Steelers, Packers, Colts and Broncos. One can surmise that the Patriots, Packers and Broncos success may have been the result of better coaching and/or astute acquisitions via free agency.
- Since the beginning of the NFL, the position with the highest number of drafted players is running back with over 4,000 players drafted. The next closest position was defensive back with nearly 3,000 players drafted. However, in the last 20 years (1998 – 2017), defensive back was the position with the most players drafted with 896. The next closed position was wide receiver with 619 players drafted.
- Ohio State has had the most players drafted in the past 20 years followed by Miami, Florida, LSU and Florida State. However, since beginning of the draft, USC has had the most players drafted followed by Notre Dame.
- The most successful draft class per team was by far the 1961 draft by the Los Angeles Rams. This resulted in a very successful run by the Rams in late 1960s and early 1970s. However, the next most successful draft class, the 2000 draft class of the Oakland Raiders, did not result in a great deal of team success. It wasn’t until 2016 when the Raiders finally were able to turn the corner with 12-win season.
Here is a link to the report. Feel free to share it with your friends and colleagues, especially if they have interest in analyzing the NFL draft.
Let us help you drive data and find insights. Learn more about our Business Intelligence & Analytics capabilities.