Dave MacLean from BC Lions came to us with an incentive he wanted to visualize for his two teams. If they hit certain revenue milestones during the week both individually and as a team, they would get out of work early on Friday afternoon. This was a weekly challenge that needed to be updated automatically and reflect who was getting out early and what early out time they achieved.
We devised a Google Sheet that would bring in the Revenue for the players via Engage that was updated from their CRM (Salesforce). From there, we calculated what the leave time was for each person based on their revenue and the total team revenue. We then pulled the people who had earned an early out time over to another tab using a query and displayed that on the screen using a data table. For a downloadable copy of the Google Sheet with the calculations, click here.
This was a multi-step process that is outlined below.
Step 1: Bring in the revenue from a data source to the system. In this case, we used Salesforce which was on a 15-minute sync.
Step 2: Send the revenue out to a Google Sheet via the Google Integration in the system. For information on how to do this, click here. The data brought in can be found in the “Information from Engage” tab of the Google Sheet.
Step 3: Calculate time out for each person based on the rules of the incentive. For this, we calculated what time the person should get off based on multiple criteria. For this use case, the specific rules are on the “Rules” tab of the Google Sheet and the calculations were done on the “Calcs” Sheet.
Step 4: Write a query to include anyone who has achieved an early leave time, but not include anyone who has not. This can be seen in the “Time Out On Friday” tab of the Google Sheet.
Step 5: Map your data table via the Engage Google Sheets integration. For information on how to do this click here.
Step 6: Display your data table in your selected channel.