Telegram - ?

Merion Networks

Client service and ways to improve it tasks which almost all companies trying to solve, especially the ones who really love their clients. How to assess customer satisfaction after his call to contact-center? The answer is quite easy just ask him.

: In our article we are going to show how to set this function up and you will be able to use it for any kind of customer survey.

  • Client and agent are talking;
  • After the end of the call agent asks client to evaluate his satisfaction about this call;
  • Agent hungs up;
  • Automated service asking client to put the number from 1 to 5, depending on his satisfaction by this call;
  • Entered information is being saved to MySQL table and sent to Telegram group chat, where this information can be checked by supervisors;;

Hurry up to set it your manager is definitely going to like it a lot :)

This solution was tested on FreePBX Distro (FreePBX 13 + 13.10.0)

Queue setup

Create the usual call queue from FreePBX, but without any agents (page Queue Agents) we want to do it manually through the config files.

You did it? Great. Now go to the server console and open the file /etc/asterisk/queues_post_custom.conf and add following info there:

member=Local/111@customer-survey-ivr/n,0,111,hint:111@ext-local

Where 111 - is an internal extension, which should be the part of this queue.


Dialplan setup

Open the file /etc/asterisk/extensions_custom.conf and add following data:

[customer-survey-ivr]
exten => _X.,1,NoOp(Post call survey beginning)
exten => _X.,n,DIAL(SIP/${EXTEN},,trg) //we added option g  when caller hungs up, we continue to execute commands in this context;
exten => _X.,n,GotoIf($[${DIALSTATUS}=BUSY]?busy:answered) //check if the call was answered
exten => _X.,n(busy),Hangup()
exten => _X.,n(answered),Goto(assessment,${EXTEN},1)


[assessment]
exten => _X.,1,Answer
exten => _X.,n,Wait(1)
exten => _X.,n(skip),Set(IVR_MSG=en/beep)
exten => _X.,n(start),Set(TIMEOUT(digit)=1)
exten => _X.,n,Background(/var/lib/asterisk/sounds/ru/custom/ocenite-8khz,m)  //asking client to evaluate;
exten => _X.,n,WaitExten(5,)

exten => 1,1,AGI(survey.php, ${CDR(src)}, ${DIALEDPEERNUMBER}, ${NODEST}, 1) //if the client puts 1, then we send the data through AGI to the script which should send it to Telegram and SQL;
exten => 1,n,Playback(/var/lib/asterisk/sounds/ru/custom/bye-8khz,skip) //we thank the customer and say a goodbye to him;

exten => 2,1,AGI(survey.php, ${CDR(src)}, ${DIALEDPEERNUMBER}, ${NODEST}, 2)
exten => 2,n,Playback(/var/lib/asterisk/sounds/ru/custom/bye-8khz,skip)

exten => 3,1,AGI(survey.php, ${CDR(src)}, ${DIALEDPEERNUMBER}, ${NODEST}, 3)
exten => 3,n,Playback(/var/lib/asterisk/sounds/ru/custom/bye-8khz,skip)

exten => 4,1,AGI(survey.php, ${CDR(src)}, ${DIALEDPEERNUMBER}, ${NODEST}, 4)
exten => 4,n,Playback(/var/lib/asterisk/sounds/ru/custom/bye-8khz,skip)

exten => 5,1,AGI(survey.php, ${CDR(src)}, ${DIALEDPEERNUMBER}, ${NODEST}, 5)
exten => 5,n,Playback(/var/lib/asterisk/sounds/ru/custom/bye-8khz,skip)

exten => t,1,Playback(/var/lib/asterisk/sounds/ru/custom/bye-8khz,skip)
exten => e,1,Playback(/var/lib/asterisk/sounds/ru/custom/bye-8khz,skip)
  • /var/lib/asterisk/sounds/ru/custom/ocenite-8khz - prerecorded file with following info: Please, grade call-center operators work from 1 to 5;
  • /var/lib/asterisk/sounds/ru/custom/bye-8khz - > - prerecorded file with following info: Thanks a lot for evaluating! Goodbye!;

Save the file. After this, execute the following command in server's console::

dialplan reload

Sorting out the SQL

I'm sure you agree that we need to save data for BI (business intelligence) and other things. We will be doing it with SQL. Create the table survey, complete it, create the user - the one who will have the right to change the table:

mysql
mysql> use asteriskcdrdb;
mysql> CREATE TABLE survey(num varchar(20),operator varchar(20),queue varchar(20),valuation varchar(20),date datetime);
mysql> CREATE USER 'login_mysql'@'localhost' IDENTIFIED BY 'password_mysql';
mysql> GRANT INSERT ON asteriskcdrdb.survey TO 'login_mysql';

Check what kind of table you have::

mysql
mysql> describe survey;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| num       | varchar(20) | YES  |     | NULL    |       |
| operator  | varchar(20) | YES  |     | NULL    |       |
| queue     | varchar(20) | YES  |     | NULL    |       |
| valuation | varchar(20) | YES  |     | NULL    |       |
| date      | datetime    | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+ 

Nice. We are almost done build an AGI script which should request Telegram API and SQL.


AGI script

:) Before we continue, small check do you already have a Telegram bot? If the answer is no, then follow by link below. You need to get the bot token and group chat ID :)

Hopefully, you got all required information. Let's continue to write our script:

#!/usr/bin/php -q
<?php
#parse the data from AGI
require('phpagi.php'); 
$agi = new AGI(); 
$num = $agi->request['agi_arg_1'];
$operator = $agi->request['agi_arg_2'];
$queue = $agi->request['agi_arg_3'];
$valuation = $agi->request['agi_arg_4'];
# Telegram API connection parameters and Group Chat ID
$token = "your_Telegram_API";
$chat_id = "Chat_ID";
#date
$date = date("Y-m-d H:i:s");
#array for Telegram supervisor chat
$arr = array(
'Grade is received:' => '',
'Caller:' => $num,
'Evaluated agent:' => $operator,
'Calingl queue:' => $queue,
'The grade received(1-5)' => $valuation,
'Evaluation date:' => $date,
);
foreach($arr as $key => $value) {
$txt .= "<b>".$key."</b> ".$value."%0A";
};
fopen("https://api.telegram.org/bot{$token}/sendMessage?chat_id={$chat_id}&parse_mode=html&text={$txt}","r");
#DB connection parameters
$hostname = "localhost";
$username = "login_mysql";
$password = "password_mysql";
$dbName = "asteriskcdrdb";
#parse SQL calling data
mysql_connect($hostname,$username,$password) OR DIE("Unable to connect");
mysql_select_db($dbName) or die(mysql_error());
$query = "INSERT INTO survey (`num`, `operator`, `queue`, `valuation`, `date`) VALUES ('$num', '$operator', '$queue', '$valuation', '$date');"; 
$res=mysql_query($query) or die(mysql_error());
?>

Download script

If you want to downoad the file then change file extension from .txt to .php

Don't forget to change the variables $token, $chat_id, $username and $password for yours. Save this script as survey.php and upload it to /var/lib/asterisk/agi-bin folder. WinSCP can help you with it, for example. Then you need to execute following commands on Asterisk server:

dos2unix /var/lib/asterisk/agi-bin/survey.php
chown asterisk:asterisk /var/lib/asterisk/agi-bin/survey.php
chmod 775 /var/lib/asterisk/agi-bin/survey.php

Great! Now the most important part comes in testing.


Final check

Make a call to the queue, talk with the agent, then agent should say good bye and hung up on you. What's happening? :) Weve been asked to put the grade, from 1 to 5. Enter the grade, the robot should say Good bye and hung up on you, and at the same time supervisors should receive the following information:

Call center agent evaluation by the customer

What do we see in SQL?

 Asterisk call center agent evaluation by the customer in SQL

?