- Contents
Interaction Dialer Manager Help
Penetration Rate Report Data
Feature Overview
Customers can develop penetration rate reports to show the number of contacts that were contacted (the numerator) contrasted with a number of records in a contact list (the denominator). The penetration rate calculation is simple but input criteria must be carefully considered.
For instance, there are several ways to define what a "penetrated" contact is. This could mean one of a contact's phone numbers had been dialed, the contact was reached, and Dialer obtained a final disposition (Success or Failure) or the call was dispositioned with a given wrap up code or category or a set of them. That's for the numerator. Likewise, the denominator might be more complicated than the number of contacts. Some customers may desire to use the number of entries in a given contact column, the number or unique entries in a given contact column, or even a range of values in an arbitrary contact list column.
Requirements
It is necessary to uniquely identify each contact list upload to group contacts that were uploaded together. This establishes a correspondence between the state of a contact list at a certain point in time and call completion data. This identifier, called UploadId hereafter, is a sequential number stored in the contact list. A new UploadId is created each time new contact columns are assigned to a contact list. (By default, the UploadId identifier is assigned a value of -1.) Each contact in a contact list is assigned an UploadId. Contact lists are scanned regularly to ensure that all contacts have an UploadID. This covers contacts that were added manually or by priority dialing; the Just In Time (JIT) stored procedure.
The denominator can be:
-
The number of contacts in a contact list table.
-
The number of non-null, non-empty phone numbers in a given contact column of a contact list table.
-
The number of non-null, non-empty and unique phone numbers in a given contact column of a contact list table.
-
Ranges of values in an arbitrary column of the contact list, combined with any of the above (to be implemented in a future SU)
The numerator can be the number of contacts that:
-
Had been dialed
-
Had been dialed by a given campaign or set of campaigns
-
Had been dialed by a given site or set of sites
-
Had been dialed and calls were completed with a given set of wrap up categories
-
Had been dialed and calls were completed with a given set of wrap up category/code pairs
-
Any combination of the above
Example Reports
The example below uses "pseudo-SQL" to illustrate a simple report that calculates penetration rate by comparing the number of dialed contacts to all contacts:
SELECT count(1) FROM numerator WHERE UploadId = @N / SELECT NumItems FROM I3PenetrationData WHERE UploadId = @N AND PenetrationType = 'Contacts'
Another report might compare the number of successes to all contacts:
SELECT count(1) FROM numerator WHERE UploadId = @N AND WrapUpCategory = 'Success' / SELECT NumItems FROM I3PenetrationData WHERE UploadId = @N AND PenetrationType = 'Contacts'
A more complicated report could be created for an administrator who wants to know how many unique contact home phone numbers had penetrated with wrap up categories 'Success' or 'Failure' assuming that the column name of the home phone number is 'PHONENUMBER':
SELECT count(1) FROM numerator WHERE UploadId = @N AND WrapUpCategory IN ('Success', 'Failure') AND PhoneNumberColumn = 'PHONENUMBER' /
SELECT NumItems FROM I3PenetrationData WHERE UploadId = @N AND PenetrationType = 'Unique Phone Number' AND PenetreationSubType = 'PHONENUMBER'
Upload Identifier (UploadId)
The upload identifier (UploadId) is a sequential number created every time a new contact list is uploaded or contacts are added to an existing contact list using the Import Contact List wizard. Upload ID is then assigned to the new contacts. This assignment is performed using a column named "i3_upload_id" in the contact list. In parallel, a new row is added in the Upload table. Whenever a new contact column is added using the contact list configuration, it is associated with all existing upload IDs associated with the current table name/contact list ID pair. The numerator and the denominator data must reside in the same DBMS. See Penetration Rate Tables ERD.
DialerTranServer is responsible for storing call results and for managing penetration data tables using the same database connection as the one used for the contact list. The call results are stored in a new table called I3_<ContactList>_PEN. This table resides in the same database as the contact list. It contains a subset of the current CallHistory table. A new row is added in this table for every call completion. This makes it possible to base reports on actual observation or experimental data.
Penetration Rate Tables ERD
The Entity Relationship Diagram (ERD) below shows all tables involved with penetration rate and their relationship.
Legend: PK = Primary Key FK = Foreign Key Bold = Character Fields. Required columns cannot be NULL. |
Penetration Rate Table |
Description |
Contact List table |
The contact list is listed to show its relationship with the other tables and the existence of the new column named i3_upload_id. Most contact list columns are not shown for brevity sake. |
I3_<ContactList>_PEN table |
This is the numerator. It contains one row for each completed call of the corresponding contact list table. |
I3PenetrationUpload table |
An integer value in this table is incremented every time a new row is added in this table. That occurs when contacts added to a contact list. |
I3PenetrationRange table |
This table is reserved for future use. |
I3PenetrationData table |
This table contains the actual penetration data, specifically two rows per contact column (one for all values and one for unique values) plus one row per contact list to account for all contacts. The fields are: |
To ensure the integrity of penetration data, Dialer polls Contact list tables every 30 minutes. If any Upload IDs are assigned a value of -1 (the default value), new Upload IDs are created and assigned to those contacts. This helps ensure that priority calls have Upload IDs.