2 Replies Latest reply: Mar 16, 2012 12:49 AM by Rob Wunderlich RSS

    Aggregating Call Detail Records Per Call ID

      Hi,

       

      I do not know whether anybody has delt with such a problem in the past, but it is a classical problem in telecoms world. Maching call detail records per call id.

      In more detail:

       

      I have a bunch of call detail records that record that start of the call, the end of the call, and all the events during the call. As you can imagine, these are separate records. Many records per call. Each call is identified by the call_id. Here is the structure of each record:

       

      call_event_datetime, call_id, queue, agent, verb
      

       

      I have these events stored in a QVD file (after having fetched them from a MySQL database). And they are quite a lot. 20K records per day.

       

      The point here is that I want to answer questions like the following:

       

      "AVERAGE CALL LENGTH on a Daily Basis"

       

      What is the best way to do that?

       

      One could say. Go and build a table like the following

       

      call_id, start_datetime, end_datetime, call_length
      

       

      The calculate the AVERAGE on call_length after selecting the calls on a daily basis.

       

      Is that the solution? And how do I create such a table?

       

      Shall I approach this problem completely differently?

        • Aggregating Call Detail Records Per Call ID
          Diamantis Archontoglou

          Panagiotis, why don't you aggregate date based on the keys you define on a new QVD file? Then you can use Average formaulas on a graph layout object where you display the data. Diamantis.

          • Re: Aggregating Call Detail Records Per Call ID
            Rob Wunderlich

            I assume that field "verb" identifes the event like "CallStart", "CallEnd", etc. There are at least a couple of approaches. You are correct in assuming that's it's best to build up a singe row for each call.

             

            A simple approach is use a series of JOINs selecting each relevant event type in turn, and then doing a subtraction of the new fields to derive the duration.

             

            Calls:

            LOAD

              call_id, queue, agent,

              call_event_datetime as Start_datetime

            RESIDENT CDR.qvd (qvd)

            WHERE verb='CallStart'   // or whatever the indicator is

            ;

             

            LEFT JOIN (Calls)

            LOAD

              call_id,

            call_event_datetime as End_datetime

            RESIDENT CDR.qvd (qvd)

            WHERE verb='CallEnd'   // or whatever the indicator is

            ;

             

            LEFT JOIN (Calls)

            LOAD

            call_id,

            interval(End_datetime - Start_datetime, 'hh:mm:ss') as call_length

            RESIDENT Calls

            ;

             

            // Calls table now contains call_id, queue, agent, Start_datetime, EndDatetime, call_length

             

            -Rob

            http://robwunderlich.com