Example configuration Microsoft Power Automate to send a notification whenever a customer is on hold for longer than x amount of seconds

Introduction

This example shows you how you can use Microsoft Power AutomatePreviously named Microsoft Flow. to automatically send you a notification whenever the threshold for a customer to be on hold is reached.

Preview:

 

Prerequisites

Before creating the Microsoft Power Automate solution, run the following script on the database:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[onholdtimeFlow] @Holdtime INT

AS

SELECT correlationid,

message1 AS agent,

cd.time,

ISNULL(cd2.time, ss.endtime) AS endtime,

DATEDIFF(s, cd.time, GETDATE()) AS duration

FROM UCCUCC stands for Unified Contact Center and consists of a queue that can be handled by Agents Each Contact Center has its own settings, interactive voice response questions and Agent with specific skills. Agents can be member of, or sign up to, one or more Contact Centers._CallDetail AS cd WITH(NOLOCK)

OUTER APPLY

(

SELECT TOP (1) time

FROM UCC_CallDetail AS cd2 WITH(NOLOCK)

WHERE cd.correlationid = cd2.correlationid

AND type = 'StopOnHoldMusic'

AND cd2.time > cd.time

ORDER BY time

) AS cd2

OUTER APPLY

(

SELECT endtime

FROM UCC_SessionSummary AS ss WITH(NOLOCK)

WHERE cd.correlationid = ss.correlationid

) AS ss

WHERE type = 'StartOnHoldMusic'

AND ISNULL(cd2.time, ss.endtime) IS NULL

AND CONVERT(DATE, cd.time) = CONVERT(DATE, GETDATE())

AND DATEDIFF(s, cd.time, GETDATE()) > @Holdtime;

GO

Configuration

  1. From your Office Portal, go to your Power Automate App:

  2. Click "My Flows":

  3. Click "New" and "Create from Blank":

  4. Type "Recurrence" and provide the recurrence rate in seconds.

    Note Microsoft Flow limits the recurrence rate to a minimum of 60 seconds

  5. Add an Action and type Initialize Variable. This variable will be used as an Array off all the calls on Hold. Name the variable, change its type to "Array" and leave the value field empty

  6. Add an Action and type Initialize Variable. This variable will be used as KPI value, the amount of seconds you want to receive notifications for. Name the variable, change its type to "Integer" and fill the value slot with your KPI minimum (in seconds)

  7. Add an Action and type Initialize Variable. This variable will be used as the text for the notification. Name the variable, change its type to "String" and leave the value field empty

  8. Add an Action and type Execute stored procedure. This action will run the stored procedure on your SQL database; if you have installed this procedure, please navigate to the top of this page. If you started using Microsoft Power Automate for the first time, or you have never connected to your SQL environment, provide your connection details. If you are using a on-premises database, please use the Microsoft Gateway.
    Select your stored procedure and under the "Hold Time" parameter select the variable from step 6

  9. Add an Action and select "Control" and then "Condition". In the left slot under the Expression tab, enter:

    "not(empty(body('Execute_stored_procedure')?['ResultSets']))"

    As operator select "Is not equal to" and in the right slot, under the Expression tab, enter: "true"

  10. The following steps should be filled under the "If No" side of the control action. The "If Yes" side will be left empty

    Add an Action and select "Apply to Each". As Output from the previous steps enter under the expressions tab:

    body('Execute_stored_procedure')?['ResultSets']?['Table1']

  11. Add an Action within the Apply to Each action; type "Compose". Under the expressions tab enter:

    concat(item()?['agent'], ' on hold for ',item()?['duration'], ' seconds')

  12. Add an Action within the Apply to Each action; type "Compose". Under the "Dynamic Content " tab, select Output and the variable you created at "Step 7". Seperate them with a line break.

  13. Add an Action within the Apply to Each action; type "Set Variable ". Select the variable from the previous step (created at step 7) and under value select the output from the previous step

  14. Add an Action within the Apply to Each action; type "Append to array variable". Select the variable from step 5 and under value select "Current Item"

  15. Add an Action outside the Apply to Each action; type "Compose". As value, select the variable you created at step 7

  16. Add an Action and type Notification or Email and select the type of notification you want to receive. For this guide we choose an email from outlook

    For the topic enter a value such as "Warning you currently have @{length(variables('Variable of step 5'))} agent(s) with high hold times!" If the expression section does not copy correctly, remove it and copy the expression in the field under the Expression tab. This will place the value at the selected spot in the string.

    In the body select the variable you created at step 7 and filled in the for each section. Fill the body with more information for the receiver if necessary. If you are using an Outlook email action in MS Power Automate, you are able to configure a sender. If you choose not to configure a sender, please note that the notification will be sent under your name.