Creating a simple call tracking service, part 2

In the previous article, we implemented the generation and verification of the presence of the coockie used to substitute the number on the page, as well as adding logging of the displayed numbers with reference to the transition source. This article will discuss the process of creating an interface for displaying information about calls.



But first, in the getNumberFirstTime and generateuser_id functions, we will create a coockie to the site-with-ct-code.ru domain with an indication of the relative path so that the code of the same name is not created on several pages of the site with the coockie.

function getNumberFirstTime() { //.......... var t = xhr.responseText; var path = '/'; var domain = '.site-with-ct-code.ru'; document.getElementById("calltracking").innerHTML = t; setcookie("phone", t, new Date().getTime() + 60 * 60 * 1000, path, domain); //  }; } function generateuser_id() { var ct_user_id = makeid(33); var path = '/'; var domain = '.site-with-ct-code.ru'; setcookie("ct_user", ct_user_id, new Date().getTime() + 60 * 60 * 1000, path, domain); //  }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Call Log

Now let's turn to the call log. This is the core of our functionality, according to the comparison of incoming calls and the display of numbers, events will be created in GA, lead generation in CRM, sending letters to e-mail, graphing, etc.



It is assumed that we have access to information about calls from the PBX, or logging occurs by synchronizing the call log on the mobile phones of managers with an external database, in which case an application will be required to download the call log.



Let us dwell on the option in which we promptly receive an up-to-date call log for numbers used by us to display on the site and brought them into the format we used when creating the numbers table in the previous article .



We reflect this data in the incoming_calls table.

 CREATE TABLE `incoming_calls` ( `id` int(11) NOT NULL, `client_number` varchar(200) NOT NULL, `phone_number` varchar(200) NOT NULL, `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `end_call` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `incoming_calls` (`id`, `client_number`, `phone_number`, `time`, `end_call`) VALUES (1, '78123456789', '78000000005', '2019-11-01 17:33:10', '2019-11-01 17:43:32'), (4, '79876543210', '78000000004', '2019-11-05 11:52:54', '2019-11-05 11:53:40'), (5, '78888888888', '78000000005', '2019-11-01 18:37:44', '2019-11-01 18:38:43'); ALTER TABLE `incoming_calls` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `id` (`id`); ALTER TABLE `incoming_calls` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;COMMIT;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Matching Call Log Data

Pull the data from the numbers table by phone number and time period when the number was displayed on the site and a call was made.

 SELECT numbers.id, incoming_calls.time AS time_when_call_start, incoming_calls.end_call AS time_when_call_end, numbers.time AS time_when_phone_show, numbers.phone AS number FROM numbers INNER JOIN incoming_calls ON numbers.phone = incoming_calls.phone_number WHERE numbers.time BETWEEN incoming_calls.time AND incoming_calls.end_call GROUP by time_when_call_start
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Now we display the received data in a plate. To do this, create the file ct-server.ru/info.php , in which we place our request.

 <?php $pdo = new PDO('mysql:host=localhost;dbname=calltracking', 'root', ''); $sql = "SELECT numbers.id, incoming_calls.time AS time_when_call_start, incoming_calls.end_call AS time_when_call_end, numbers.time AS time_when_phone_show, numbers.phone AS number FROM numbers INNER JOIN incoming_calls ON numbers.phone = incoming_calls.phone_number WHERE numbers.time BETWEEN incoming_calls.time AND incoming_calls.end_call"; $st = $pdo->prepare($sql); $st->execute(); $result = $st->fetchAll(PDO::FETCH_NUM); echo "<table border = '1'><tr><th>time_when_call_start</th><th>time_when_call_end</th><th>time_when_phone_show</th><th>number</th></tr>"; foreach($result as $row){ echo "<tr><td>{$row[1]}</td><td>{$row[2]}</td><td>{$row[3]}</td><td>{$row[4]}</td></tr>"; } echo "</table>"; $pdo = null; ?>
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

As a result, we got a table by which you can determine which number was displayed on the site at the time of the call.