Looking for a way to boost the happiness of your team? An in-company tournament is a great way to bring people together and improve morale*
The folks on Typeform’s Customer Success team are a competitive bunch: always trying to one-up each other. Our Technical Support Specialist, Sean Judd decided to throw down the gauntlet and ask the ultimate question: who’s the company’s best FIFA player?
Sean got a group of players together and set up the inaugural Typeform FIFA Challenge league. It was a moment that’s become as historic for us as the formation of the English FA in 1863 was for soccer.
The genius of Typeform’s FIFA Challenge is that it manages itself, leaving Sean and the rest of the crew to answer your Typeform-related questions (in between matches, of course). Here’s how it works:
Breakdown of the challenge:
- Staff sign up to the FIFA 19 Office Challenge via a typeform.
- Players do battle against each other at FIFA 18 on the office PS4.
- The loser of each match enters the score and writes a match report in a typeform.
- The score and match report is sent out to everyone in the league via Slack.
- This data is sent to Google Sheets and an algorithm assigns a score to each player.
- At the end of each Quarter the person with the highest score is crowned champion and the rest of the team must bow down and kiss their shoes.
Sean agreed to give up his secret sauce and explain how he created the Typeform FIFA 19 Office Challenge. As he points out in his story on our blog, the great thing is that this can be applied to any company tournament.
So, take an hour out of your day, and embrace your inner geek as we reveal how to engage your team with an in-company challenge like we did.
What you’ll need
Part One: Create the player signup typeform
You’ll need a typeform to sign players up to the tournament – if they dare! For our FIFA 19 Office Challenge we went for something simple but enticing. We had a designer create something that had a FIFA feel to it. Here’s what it looks like:
You can download the player signup template here. Here’s a breakdown of how this typeform was set up. You are free to start from scratch and customize it for your own office tournament:
2. Next we added question blocks to collect the information we needed from the new players: name, email address, and Slack handle. We made all of these blocks ‘Required’ because this is all essential data. Oh, and we threw in an animated GIF in the first question.
3. To finish off, we made a Thank You Screen and added a motivational image. We piped in the player’s name from the first question using the Variables feature. We also changed the button text, explaining that the button will allow the person to add a new player, if they need to.
Part Two: Create the match report typeform
The match report typeform is for players to submit the final scores of the matches they play. In the case of our FIFA 19 Office Challenge, it’s the loser who must fill out the score and match report via the typeform.
As Sean points out in his article on our blog, the team came up with the idea of adding a match report not just as a penance, but also as a way for the loser to vent about how they were robbed of victory.
You can download the match report template here. Here’s what the typeform looks like:
And here’s how it was made:
1. We made a Welcome Screen in the same way as we did with the signup typeform. We then made a Statement block in order to taunt the player about their defeat.
2. A Multiple Choice block asks who won the game, listing the names of all the players in the tournament as options. You’ll need to manually update this list whenever new people join the challenge, or you can use Webhooks to do it automatically.
A Number block is added underneath to ask how many goals the winner scored (we piped the player name variable into this question).
3. This process is repeated, adding a Multiple Choice and a Number block to collect the loser’s score. In our case, this will be the person completing the typeform.
4. Now to add in a Long text block for the loser to submit their match report. We made it clear that we want plenty of bile in the write-ups.
5. We wrapped up the match report typeform with a Thank You Screen, adding an awesome animated GIF Sean found on Giphy.
Part Three: Set up your spreadsheets
Now it’s time to set up the spreadsheets that will form the backbone of your challenge. For our FIFA 19 Office Challenge, we made three separate worksheets in a Google Sheets document.
Here’s a breakdown of what the three sheets are used for:
A. Results – contains information about each match played, pulled from the match report typeform (we’ll explain how to make this further down). This sheet records:
- Date of the match
- The players involved
- The score
- The infamous match report
Here’s an example:
B. Statistics – contains information about each contender, pulled from the signup typeform (again, we’ll show you this in a sec). It adds information from the Results spreadsheet and uses this to calculate each contender’s overall score. Fields include:
- Player name
- Number of games played
- Number of wins
- Goals for and against
- Seeding bias
- Overall player score (based on the formula)
C. Report – contains a pivot table that orders all contenders in order of score. Think of this as the league table.
Here’s an example:
Part Four: Connect the typeforms to Google Sheets
Now we’ll connect Google Sheets and Typeform together. We need to use Zapier to pass the information we collect in the typeforms to the spreadsheets and calculate the league table. In Zapier, these connections are known as “Zaps”. If you’re a newcomer to Zapier, first read this article on our Help Center.
OK, now this might look like you need a degree in quantum mechanics. Don’t worry, it’s possible to set this all up by carefully following these steps. You don’t need any specialist knowledge and can just copy and paste from the steps in the article into Zapier. If you are intrigued as to what’s going on behind the scenes, check out Sean’s explainer videos that we’ve linked to in the Tip boxes below.
Adding players’ details to the Google Sheet
Let’s start by sending the player information to the spreadsheet so that whenever a new person signs up they are added to the league.
1. Sign into Zapier (or create an account if you don’t already have one). Click the ‘Make a Zap!’ button to get started. Name your Zap “FIFA Challenge add player”(or something). Search for “Typeform” as the trigger app, and select it from the search results.
2. By default, Zapier will trigger the zap when a new typeform is submitted. So, just click Save + Continue here.
3. Connect your Typeform account when prompted, following the on-screen instructions. You’ll need to enter your API key (you’ll find this in the My Account section of Typeform).
4. Select your signup typeform from the drop-down options. Zapier will prompt you to test the connection with this typeform, so just follow the steps on-screen and hit Continue.
5. The next screen encourages you to pick a sample to set up your Zap. We recommend doing this. Make sure you have at least one entry in your player signup typeform and hit ‘Pull in samples‘. This will make it easier later to see what information is being sent to the sheets through Zapier.
6. Now click ‘Add a Step’ option and search for Google Sheets as your action app.
7. We want a new row added to our sheet each time a new player submits their details in our signup typeform, so choose ‘Create Spreadsheet Row’ and hit ‘Save + Continue‘.
7. Connect your Google Sheets account, by entering your Google login information.
8. Choose the Google sheet we told you to make a copy of in Part Three above (this one), selecting the ‘Statistics’ worksheet from the drop-down list of the three sheets.
9. Now you need to map the questions in the signup typeform to the columns in the Statistics spreadsheet. This information will be used to calculate the scoring and build the league table.
You’ll see all the columns from the Statistics sheet appear here. We just need to tell Zapier which information from the signup typeform we want to grab and where to put it. Here’s an overview of what you need to do:
a. Add the player name from the typeform into the ‘Contenders‘ column. To do this, click on the little “+” button and select the name fields from the drop-down list. Zapier helps makes things clearer by providing you with example data based on a recent entry (if you’ve had any).
b. Now pass the submission ID generated by the typeform into the ‘Token‘ column in the spreadsheet using the same method (i.e. by clicking the “+” button). The token is a unique identifier of the submission, which appears on all typeform submissions. In this case it’ll help us uniquely identify the player when performing the calculations (more on that later).
c. Pass the email question into the ‘Email‘ column of the spreadsheet:
d. Pass the Slack username into the ‘Slack‘ column in the spreadsheet. Click ‘Continue’ once you’re done:
10. Test that the player was added successfully by following the Test This Step wizard and checking that the row has been added with the player’s details, using the sample data Zapier will pull in from your Add Player typeform (make sure you have at least one entry submitted).
Looking up a player
Now we need to tell Zapier to ask Google Sheets to update a person’s score based on their results. To do this, we need to “look up” the row with the player’s details then update it with information taken from the match report typeform.
1. Create a new step, using the ‘Add a Step’ button. Pick Google Sheets as the action app, then choosing Lookup Spreadsheet Row as the action.
2. We need to look up the ‘Token’ value from the Statistics spreadsheet as we’re using this as the unique identifier of the row. Configure the Zap as follows. In the Lookup Value field, select the ID (Submission token) from the ‘New Entry‘ action in the drop-down.
3. You can test the step again to make sure it’s worked. You should look to make sure it’s found the player information (i.e. name, token, email, and Slack name).
Calculating the scores
Now we’re going to perform the calculations we need to add up each player’s score. Each player has their own row in the Statistics sheet. So, we’re going to use the row number of the player, which we looked up in the previous steps, to determine which cells to apply the calculations to. In this example, it’s Lucy, who’s in row 2 of the sheet.
1. Click ‘Add a Step’, pick Google Sheets as the action app, and choose Update Spreadsheet Row.
2. Select the FIFA Challenge spreadsheet and Statistics worksheet as this is what we’re updating. For the Row field we need to select the row number of the player. Select Use a Custom Value from the little drop-down option in the Row section and choose the ID from the spreadsheet row we just looked up.
We’ll call this value ‘RowID’, for now. This value will allow us to perform the calculations in the Statistics sheet by combining it with the column letters to create formulas.
3. Now copy and paste the following data and formulas into each field in order to make the appropriate calculations. Each time, we’re using the Row ID variable to identify who the player is. So where you read “RowID” in the following steps you should select the ID field from the Lookup Spreadsheet Row action like this:
a. Copy and paste =countif(Results!B:B,BRowID)+countif(Results!E:E,BRowID) into ‘Played‘. Remember to replace ‘RowID’ with the ID, as shown above. This counts all the mentions of the player in either the ‘Player 1’ or ‘Player 2’ columns of the Results sheet to calculate the number of games that person has played.
b. Copy and paste =COUNTIF(Results!F:F,BRowID) into ‘Wins‘. This counts the number of times the person’s name appears in the ‘Winner’ column of the Results sheet.
c. Copy and paste =if(CRowID=0,0,DRowID/CRowID) into ‘Win ratio’. This divides the number of matches won by the number of matches played, to calculate a person’s win rate.
d. Copy and paste =iferror(100-(CRowID/DRowID),0) into ‘100-(Played/Won)‘. This forms the basis of our scoring. The person’s win rate is subtracted from 100. A win rate of 1:1 would give a maximum score of 99, whereas if a player doesn’t win any of their games they would only score 98.
e. Copy and paste =Sumif(Results!B:B,BRowID,Results!C:C)+Sumif(Results!E:E,BRowID,Results!D:D) into ‘Goals for’. This adds up the total number of goals a person has scored by looking at the scores in the Home column (C) of the Results sheet if they are the winner and the Away column (A) if they are the loser.
f. Copy and paste =sumif(Results!B:B,BRowID,Results!D:D)+sumif(Results!E:E,BRowID,Results!C:C) into ‘Goals Against’. This does the same as the previous step but in reverse – it looks at the scores in the Away column if they are the winner and the Home column if they are the loser.
g. Copy and paste =iferror(GRowID/HRowID,GRowID) into ‘For/Against‘. This divides the number of goals a person has scored (column H) by the number of goals against (G) to give a ratio.
h. Copy and paste =IRowID*CRowID into ‘*Played‘. This multiplies the number of games played by the for/against goals ratio to give us a value that will be used to reward players with a greater goal difference.
i. Copy and paste =JRowID/50 into ‘/50‘. This divides that goal difference ratio value by 50 to give us a much lower number, and thus reduce the impact that has on a player’s overall score.
j. Copy and paste =sumifs(Results!$J:$J,Results!$F:$F,BRowID) into ‘Seeds‘. This is used to apply a seeding system. It takes the previous results and adds a coefficient to a player’s score depending on whether their opponent was higher placed than them. Sean explains this in the video below, if you’d like to know how the seeding system works.
k. Copy and paste =KRowID+FRowID+LRowID into ‘Score‘. This adds together the for/against ratio (column K), the played/won ratio (F), and the seeding coefficient (L).
Click Continue and you’re done with this Zap. You can send a test to Google Sheets to make sure it’s all working as planned.
If you’ve slipped up somewhere you can go back and change things by clicking Edit template then re-testing it.
Updating the results
Still with us? Just a little more tinkering in Zapier and we’re done. Admit it, you’re starting to enjoy all this, aren’t you?
Let’s dive in and connect up our match report typeform to Google Sheets by making a new Zap and naming this one “FIFA Challenge – Update results” (or something).
1. Set up Typeform as your trigger app. Connect to your Typeform account and select the signup typeform from the drop-down options.
2. Now set Google Sheets as your action app. Choose ‘Create Spreadsheet Row’.
3. Connect to your Google account and select the ‘Results’ worksheet from the Google Spreadsheet file.
4. Now you need to map the results information in the typeform to the corresponding columns in the spreadsheet:
- Pass the winner’s name into ‘Player 1’
- Pass their score into ‘H’
- Pass loser’s score into ‘A’
- Pass the losing player’s name into ‘Player 2’
- Pass the match report into ‘Match Report’
- Pass the ID (Submission token) into ‘Token’
- Pass the Submit Date into ‘Date Submit’
5. Test the step to make sure the results are passed to the Results tab of the spreadsheet.
6. Add a new step to ‘Lookup Spreadsheet Row’ in the same ‘Results‘ worksheet. Look up the token value in the token column.
7. Test this and make sure the values have been pulled in.
8. Now for the final action. Add a new step to Update a spreadsheet row.
9. Again, we’ll going to use the Row ID value we looked as our reference point. Let’s call it ‘RowID’. This time though, we’re looking up the row in the Results worksheet. Select ID from the Lookup Spreadsheet Row action under ‘Custom Value for Row ID‘, as shown below.
10. Add the following information into each field in order to make the appropriate calculations (you can copy and paste). Each time, we’re using the Custom Value for Row ID (“RowID”) as the identifier.
a. Copy and paste =datevalue(KRowID) into ‘Date‘. This sends the submission date information and applies date formatting.
b. Copy and paste =if(isblank(CRowID)=True,””, if(CRowID=DRowID,”Draw”, if(CRowID>DRowID,BRowID,ERowID))) into ‘Winner‘. This looks at the score columns (C and D) to determine who scored the most and sends the winner’s name to the Winner column.
c. Copy and paste =if(isblank(CRowID)=True,””, if(CRowID=DRowID,”Draw”, if(CRowID>DRowID,ERowID,BRowID))) into ‘Match Reporter‘. This looks at the result to determine who is the loser (and therefore the person filling out the match report).
d. Copy and paste =if(iferror(countifs(F:F,FRowID,A:A,”<“&ARowID)/countifs(G:G,FRowID,A:A,”<“&ARowID),countifs(F:F,FRowID,A:A,”<“&ARowID))<(iferror(countifs(F:F,GRowID,A:A,”<“&ARowID)/countifs(G:G,GRowID,A:A,”<“&ARowID),countifs(F:F,GRowID,A:A,”<“&ARowID))),0.25,0) into ‘Score‘. This calculates a player’s seeding weighting based on the number times they’ve won or lost up to that day.
Test it out
Wasn’t too painful, right? Once you’re done setting up the Zaps you should spend a bit of time testing it all out. Go back to your typeforms and do a few test runs of adding players and submitting results. Then go to your Google Sheet and make sure all the tabs are populating properly. If not, go back and check all of your Zaps are set up as above.
Part Five: Connect to Slack with Zapier (optional)
If you really want to wow your team, you can send a Slack message to all the players in the challenge announcing the latest results. Sean created a channel in the Typeform Slack account called #fifa_tf_challenge, where scores and match reports are sent automatically every time the loser fills out the match report typeform:
Here’s how it’s done:
1. Make a new Zap, setting Typeform as the trigger app.
2. Now add the Action step, selecting Slack as the app, and choosing the ‘Send Channel Message’ option.
3. Connect to your company Slack account then find the channel you set up for the challenge from the drop-down list.
4. Now add in the variables you want to include in your Slack message by selecting the appropriate answer blocks from the drop-down under ‘Message Text’. In our case, we include “winners name”, “winners score” followed by a dash; then “loser’s name”, “loser’s score”. Underneath this, we pipe the match report, so everyone can enjoy the loser’s bitterness.
5. You can choose to send the message as a bot, specifying its name and even giving it an icon, if you like.
Over to you…
And there you have it, an end-to-end solution for running your own in-company tournament. We hope you enjoy setting up and running it. We’d love to hear about your implementation and how your team are enjoying the challenge. Please complete this typeform if you’d like to submit your typeform solution to be featured by us.
You can grab the starter pack needed for this project here: