HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

Custom SQL for automation rule?


#1

Hi folks,

We’re taking advantage of the new Speed to First Response report and hope to improve on the times. Basically, we’re trying to mirror the speed to first reponse report, but as an escalation rule, so that our staff will get a heads up that there is something in the queue that has not received a human response.

Can anyone help on creating an SQL for an automation rule that satisfies these conditions?

  1. Request submitted between 9:00am and 5:00pm on Monday through Friday.

  2. More than 90 minutes has elapsed with no public response.

  3. Submitted via email or the web.

I know that there are already pre-loaded conditions for numbers 2 and 3, but I didn’t see one for number one. Anything that will streamline the three conditions as best as possible would be much appreciated.

Thanks much!

Dan


#2

Hi Dan,

Right, what you need to do is take #1 out of the equation. That doesn’t need to be in the rule, to satisfy that part what you do is simply just run the rule via business hours. So you setup a special schedule for just this automation rule. This way it only runs during those hours.

Details on how to do that is here: http://www.userscape.com/helpdesk/index.php?pg=kb.page&id=288

You may also need to look at the number of public updates condition. So limit it to requests with just one public update.


#3

Thanks Ian,

Right now what we have is tasks2 running every 10 minutes. We created an automation rule with these conditions:

  • Minutes since opened: greater than 90
  • Contacted via email
  • Contacted via web form

Two follow-up questions:

  1. You write: “You may also need to look at the number of public updates condition. So limit it to requests with just one public update.” What is that trying to accomplish? Am I right to assume this is trying to satisfy the fact that there is no explicit condition that makes sure no public response is given before 90 minutes?

  2. If not, do you have any tips on how we can achieve that?

Thanks for your help, Ian (and potential others!)

Dan


#4

Right. As you have it now it’s going to match anything that’s been open more than 90 minutes no matter what. Also, I think you might end up matching nothing because you have contacted via email AND contacted via web form both of which cannot be true unless you’ve actually done that in a sub grouping under advanced at the bottom of the conditions list.


#5

Ian,

Here’s my revised conditions list:

  • Minutes since opened: greater than 90
  • ANY of the following are true: Contacted via email
  • ANY of the following are true: Contacted via web form
  • Number of public updates: Less than 1

Would this satisfy the automation?

Thanks again,
Dan


#6

I’m not sure number of public updates less than 1 will do what you want. Most requests have a public update when their created so maybe = 1 would be better. One thing to remember though is you need an action that prevents these conditions from being true forever and re-escalating continuously.

So you either need to make one of your actions public, like a public note (so that number of public updates no longer matches) or you need another condition. For example status = active and then in your actions change it to status = escalated (you can add status types in Admin=>Settings).

More on preventing recurring matches here: http://www.userscape.com/helpdesk/index.php?pg=kb.page&id=291