Here’s The Solution To The Hardest Automation Problem
You know when you do something really cool but you try to play it off and pretend it’s not that cool so ultimately you can seem super cool? This is not one of those times. I just figured out the holy grail of automation and its….kind of a big deal.
I’ve gotten this questions several times in the past…
Is it possible to trigger an automation when something doesn’t happen?
To which I’ve always had to answer with “no” because all of the standard automation platforms out there IFTTT and Zapier are very clearly structured around a trigger and an action. Some trigger happens here, and as a result, some action happens somewhere else. The key, of course, is that something always has to happen in order to trigger something. By definition, something not happening just doesn’t cut it. So every six months or so I would get that question and do that thing where you suck air in hard through your teeth and cock your head to the side like you just stubbed your toe and give me a response in the negative. That was until someone asked me a few weeks ago and I couldn’t say no.
The person who asked me is a member of my Less Doing Leaders coaching program and I’m constantly asking them to bring me their most interesting business problems, and I always figure them out one way or another.
In the movie “The Prestige” we are told that every magic trick has three parts.
Every great magic trick consists of three parts or acts. The first part is called “The Pledge”. The magician shows you something ordinary: a deck of cards, a bird or a man. He shows you this object. Perhaps he asks you to inspect it to see if it is indeed real, unaltered, normal. But of course…it probably isn’t. The second act is called “The Turn”. The magician takes the ordinary something and makes it do something extraordinary. Now you’re looking for the secret… but you won’t find it, because of course you’re not really looking. You don’t really want to know. You want to be fooled. But you wouldn’t clap yet. Because making something disappear isn’t enough; you have to bring it back. That’s why every magic trick has a third act, the hardest part, the part we call “The Prestige”.
When I figure this one out, it felt like I had performed a magic trick, on myself.
My client owns a very successful group of pizza restaurants in Canada and every week on Monday they except the manager of each location to check in using and report on the state of the restaurant’s performance over the previous week. Since there are a growing number of locations in this pizza empire and they are expecting to continue to expand for the foreseeable future, they want a sustainable way to keep track of managers reports, as well as missed reports, in an automated way. So they want to create a trigger that automatically triggers when no report is received. As we started things off with, this seems like a very ordinary thing, but it’s a really big deal. And keep in mind that this specific example is merely to illustrate how this type of automation works but once you open your eyes to the possibility of starting automation based on the absence of an event the idea of becoming more replaceable ex[ands by orders of magnitude.
My head went through dozens of iterations of ways to tackle this problem. Initial ideas were extremely complicated and involved multiple automations running in parallel at the same time, crossing paths and diverging multiple times like fighter jets at an air show. At one point I got so stuck and frustrated that I was going to retreat to using virtual assistants to manually complete the task until…
So we start with a simple web-based form as or trigger. I like to use jotform but you can use any that you like. We put in all the info we want to the manager to report including a simple Likert scale to allow for some standardization across locations. As I’m always looking at ways of reducing errors I made the locations a drop-down menu so we don’t rely on a new manager or even a current one manually entering the location incorrectly. Once the form is submitted it kicks off the next step which is to locate a row in a Google Sheet that matches the info submitted in the form.
The first time we set up this sheet you only need to enter the location and the expected date of check-in (in this case, the following Monday). Once it finds the row that matches the location in the form submitted, it will look across the row and find the date of the check-in. At this point we need to slip in a Date Formatter function, native to Zapier, to take the current date in the spreadsheet (not the date of submital) and then add seven days to it because this is a weekly check-in. We don’t want to trigger it based on the submittal because if they submit early it would mess things up.
Now we update the spreadsheet with that new date. So effectively the person has “bought” themselves another week by submitting the form. Now comes the tricky part. We want the automation to delay until that new time that was entered (so a week from now) but since there isn’t byway to stop a delay in Zapier once it has begun, I sad of setting the delay to trigger the rest of the automation we delay and then have it find the row again.
Now we need to do a little bit of compare and contrast. Where we are currently is the manager did their job correctly and submitted the form, which added a week to the “due date” and now here we are, at the due date and we need to see what’s happened.
At this point, we need to create a fork in the road for this automation that looks at whether the current date is before the due date (good job manage) or the current date is after the date of expected check-in (bad job manager) and act accordingly.
So let’s take the first case where the manager DID submit. In that scenario, the current date, when compared with the date of expected next check-in will be before and thus we simply send an email to the manager thanking them for submitting.
Now let’s look at the other scenario, where the current date is after the expected date of check-in meaning they did not submit on time. In that case, we can do whatever we want but I chose to send a polite nudge by email to the manager with a cc to the owner. You could choose to do whatever you want, from a text message to slack notification or even a Trello card. Then we simply delay a day or two and check again, this time with a nested path that checks again and if they finally submit it does one thing and if they still haven’t submitted it does something else.
You repeat this for as many days as you want until you get your result.
I hope you can see how cool this is and the possibilities it opens up for your business. Keep in mind that the trigger can really be based on anything like form submission, a slack message, even the movement of a Trello card. Even if you never use this method In your work, my goal is for you to think differently about how you optimize, and automate the processes in your business.