Using Alteryx and Tableau to Bet on Super Bowls

If you have ever played the Super Bowl squares game you will love the information I’m about to give you.  If you haven’t played, you may still love this if you want to use this to make money in the future.  For those that haven’t played I will give you a brief description of what I’m talking about.

There is a 10×10 grid with numbers 0 through 9 across the top, and 0 through 9 on the side making 100 squares.  Each square corresponds to a certain score in the Super Bowl at the end of each quarter by taking the last digits of the score.  For example, say you had square ‘4-4’:  that square would pay out if the score was 14-14, 24-14, 34-14, 54-24, etc. (For future notice when I say a square such as “1-7” the first number represents the end digit for the NFC, the second the AFC)  Here is an example of what one looks like:

My Super Bowl Squares

I will note that people play it differently.  Sometimes you get to pick your squares, other times you buy a certain amount and it gets randomized after everyone has bought squares.  The one shown here we got to pick our squares.  Now besides our basic football knowledge no one knew the past squares’ payouts.  That is until I was interested in figuring out what the past Super Bowl payouts were and I’ll tell you why.

If you watched Super Bowl LI against the Patriots and Falcons you probably remember the scoreless first quarter (0-0 square payout).  At that point I wasn’t too concerned because I had the 0-7 square if they kept up the slow scoring pace and the Patriots scored a touchdown before the half.  Lo and behold the Falcons drop 3 touchdowns to go up 21-0 with about five minutes left in the half.  If you notice my squares above I had 1-0 and 1-7 so I  was thinking I was golden.  All I needed was Tom Brady to drive the Patriots down the field for a touchdown or the Falcons defense to hold strong for a 21-0 halftime lead.  The result:  a little bit of both.  Patriots drove down the field and on what would have set up a first and goal with about 30 seconds left, Martellus Bennett gets flagged for holding to push the Patriots back.  Then the Falcons defense held strong holding the Patriots to a field goal.  Not only did I miss out on the 1-0 or 1-7 payout, I also had the 1-4 square and it was the 1-3 square that paid out at the half.

To add to the misery, the 3rd quarter end 28-9 for the 8-9 payout square (I had 9-8) that I wrote in someone else’s initials for when we were splitting the last junk squares among ourselves.  If you notice I also had 5-9 so if Atlanta had scored a second touchdown in the 3rd (assuming a made PAT) I would have won that quarter (and maybe, just maybe the Falcons would have actually not blown their lead).

Enough of my almosts, the ultimate reason for explaining how close I came was that it led me to wonder just how often each square has paid out over time for every Super Bowl.  The results were rather shocking.  I will walk you through how I got there and what surprising results I discovered.  I first started by inputting all Super Bowls and the box scores into an excel spreadsheet.

Sample of the Super Bowl Box Scores Spreadsheet

 

Using that spreadsheet alone I was able to get a Tableau dashboard showing the Super Bowl Squares game payouts at the end of each quarter.  It looked like this:

Payouts by quarter

While this was cool in itself, it was not quite what I was looking for in my Tableau dashboard.  I wanted to merge them all together for more interactivity.  I fiddled around with many different calculations in Tableau and could not figure it out.  Not that it’s impossible, but it was too difficult for me to keep creating calculations on calculations so I caved and just went to Alteryx.  Best decision I made.  In about 20 minutes I was able to figure out exactly what calculations I needed to use in Alteryx.  Not to mention the ability to transpose portions of the dataset was the breakthrough I needed to get my desired dashboard.  Here are photos of the steps I took:

Alteryx end of quarter score calculation I created (4th Quarter)

Results from my quarter scores formulas

Created this formula in Alteryx for each end of quarter to get the payout squares

Result from my creation of the end of quarter payout formula

Transposed the payout squares results to make Tableau friendly dataset

Entire Alteryx workflow creating the end of quarter scores, separating them out to NFC/AFC, pulling the end digits for the payout squares, and transposing the result

 

The Alteryx workflow ran in a mere 0.8 seconds and I was able to quickly spit out this new dashboard with the combined results I was looking for:

Circled boxes were Super Bowl LI payouts

First thing that stood out to me was the Falcons/Patriots super bowl paid out the 1-3, 8-9, and 8-8 squares for the first time ever.  While I did not account for overtime since the pool I was a part of did not payout overtime (and it’s never happened before), had that been considered, the 8-4 square as also never paid out.  So 3 of 4 payout squares were new, and 4 of 5 if OT was paid out.

Even though this didn’t help me get any money, it at least made me feel better about having picked many squares that had actually paid out many times before.  Below are my squares of which only four had never paid out.

The historic payouts of the squares I chose for Super Bowl LI

Interact With My New Tool Yourself in Preparation for Next Year’s Super Bowl

If you would like to see my completed dashboard and interact with it, check out this link on Tableau Public:  Super Bowl Squares.  You can filter the payout squares by quarter, Super Bowl, and/or Super Bowl winning teams.

Please do not hold me accountable for your picking of the squares I discovered as the most common next year if they do not payout.  This is simply historic results and are of course no guarantees as Super Bowl LI proved.  Write below with any comments, questions, or concerns and good luck on your Super Bowl Squares betting next year.