PureConnect

Discussion Thread View
Expand all | Collapse all

Reporting on Emails Received

  • 1.  Reporting on Emails Received

    Posted 09-17-2019 11:37

    I have a question I hope someone can help with, related to reporting of emails received.

     

    We are on 2019 R2 and all of our reporting is directly on the Reporting database, we do not uses reports in IC Business Manager, only SQL queries directly on the database (technically, ,via log shipping, to a mirror of the production database, updated every 30 minutes).

     

    The primary source of information on any call is the table dbo.interactionSummary. When a telephone call is received, on completion, this is written to the table. Therefore a simple query can tell us calls received on-the-day or by various intervals (hourly, 30-minutes, etc.). The key part of the previous statement is "on completion"; with telephone calls, this is not a major problem, we are reporting usually on what happened the previous days, and as telephone calls are generally short-lived, this is an accurate measure. With emails though the situation is different, the email may be received and after routing, be sitting in a queue for hours or days. Problem is, the interaction is not written to the InteractionSummary table.

     

    With calls, the reporting is easy; Number of calls that go to Queue, Number of calls that are answered and the difference is Abandoned. But with emails, how do we know how many are received and waiting to be dealt with as the interaction is only written after the email is processed.

     

    Is there a statistics table that records all this? With historical data feeds (usually every 30-minutes) you can get a count of received calls, irrespective of whether they were answered or abandoned, so is the same thing possible with emails?


    #Reporting/Analytics

    ------------------------------
    Philip Last
    Arvato Limited
    ------------------------------


  • 2.  RE: Reporting on Emails Received

    Top 25 Contributor
    Posted 09-18-2019 09:30
    Philip,

    The Data dictionary is going to be your friend here: https://help.genesys.com/cic/datadictionary/content/Landing.html

    You can look at the IWrkgrpQueueStats view which should show you the queue statistics rolled up in 30 minute intervals (assuming you didn't adjust the interval settings in the queue. You are correct that all interactions only write to the Interaction Summary table after they de-allocate from the system.

    Can I ask why you don't use the reports that are built into CIC, a bunch of them are really useful for looking at interval data like you are referring to.

    Thanks,


    ------------------------------
    Mark Tatera
    ConvergeOne

    Opinions are my own and not the views of my employer. Any suggestions or programming changes I suggest come with no warranty and should be tried at your own risk.
    ------------------------------



  • 3.  RE: Reporting on Emails Received

    Posted 30 days ago
    Thank you Mark

    I see that view has Email Interactions in it, so I can experiment to see if I can get the data I need.

    The reason I don't use built-in reporting, except for problem analysis, is that we run all of our reporting automatically with a combination of SQL and SSIS with other automation to output reports to Excel or PowerBI, even emailing some automatically.

    ------------------------------
    Philip Last
    Arvato Limited
    ------------------------------



  • 4.  RE: Reporting on Emails Received

    Top 25 Contributor
    Posted 29 days ago
    Philip,

    There is a product from Genesys called scheduled reports which lets you schedule the canned reports and export them or email them on a schedule. Since it seems like you are getting everything you need from your current direct query process I think you would probably not benefit. You may want to pull down the help files for the canned reports as a bunch of reports used stored procedures to generate the data and it would be easy enough to execute the stored procedure yourself to get at the formatted data.

    Thanks,

    ------------------------------
    Mark Tatera
    ConvergeOne

    Opinions are my own and not the views of my employer. Any suggestions or programming changes I suggest come with no warranty and should be tried at your own risk.
    ------------------------------



  • 5.  RE: Reporting on Emails Received

    Top 25 Contributor
    Posted 29 days ago
    Also Philip -

    If you are directly querying the DB for email information already, make sure in Administrator -> System Configuration -> Mail -> Options tab, that "Add Conversation Identifier on Emails" is enabled.  This will provide a unique hash value in the DB, for example in the CallDetail_viw/InteractionSummary table, for email interactions between external parties and the agent.

    Essentially, with this value you'll be able to associate emails together in a "Threaded Email" manner, if that makes sense.

    Thanks,
    Trent.

    ------------------------------
    Trent Vance
    Avtex Solutions, LLC
    ------------------------------