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.

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.