Data Profiling - Reporting and the challenge of timed snapshots

1 Reporting and the challenge of timed snapshots

1.1 The setup

The timing of the reporting is important, especially when viewing a progressing record. For example, suppose we are selling house insurance to Nick Wheeler.

• Month 3 Week 4 Day 1: Tried to ring Nick, he was too busy
• Month 3 Week 4 Day 3: Got through to Nick but he asked for more information on paper
• Month 3 Week 4 Day 5: Quick follow-up with call and he’s rather interested but needs time to consider
• Month 4 Week 1 Day 2: Followed up again with Nick, he really wants to go ahead
• Month 4 Week 2 Day 4: Contract sent
• Month 4 Week 3 Day 3: Contract signed

So depending on the reporting and when the report is pulled we may have very different interpretations of this customer.

Commonly requested campaign statistics:

• Number of calls made
• Number of contacts
• Contact Rate % [Given as Actual Primary Contacts/No of calls with Contact = Spoke to Primary Contact]
• Number of sales
• Conversion Rate % [Given as Sales/No of contacts - (Policy Already Renewed + Policy Already Cancelled + Wrong Telephone Number + Property Sold)]

1.2 Contact count totals

The first and easiest number to get from bxp is a contact. Our interpretation is that a contact has occurred whenever an outcome is used. Each contact is a CCL record so counting them is very easy.

Main Menu > Data Profiling > Instant – Statistics > Data – Report by Period > Choose the time period and Select the campaign.

This will list all the CCLs, by outcome, that were created during the time frame requested.

1.3 Actual contact totals

How you set up outcomes hugely influences the effectiveness of reporting. By putting a Contact in front of the outcomes or No Contact, the system will list back all the outcomes, alphabetically sorted.

When the system reports on outcomes they will be alphabetically sorted:

• Call back again
• Call back later – appointment
• Escalation to manager
• No answer
• No details
• No sale not interested
• Sale
• Wrong number

This often means that using a prefix on the outcome will provide a much more sensible listing grouped.

• Contact - Call back again
• Contact - Call back later – appointment
• Contact - Escalation to manager
• Contact - No sale not interested
• Contact - Sale
• No contact - No answer
• No contact - No details
• No contact - Wrong number

Using this prefix concept, the grouping can be as extensive as you like. For example:

• Complete - No sale not interested
• Complete - Sale
• Contact - Call back later – appointment
• Contact - Details updated call back again - Afternoon
• Contact - Details updated call back again - Evening
• Contact - Details updated call back again - Morning
• Emergency - Escalation to manager
• No contact - No answer
• No contact - No details
• No contact - Wrong number

The levels of grouping can be extensive but beware that creating too many groupings can be restrictive. For example:

• Complete – Sale – Product 1
• Complete – Sale – Product 2
• Complete – Sale – Product 3
• Complete – Sale – Product 4

Where there is an extensive list it’s more practical to use a drop down list in the campaign itself. The general guiding principal is, excluding the number of contacts, what group of records should be separately actioned.

We might want to action evening calls differently to morning calls, but all sales calls would no longer be actioned.

The same report as before using the grouping will provide these numbers.

1.4 The meaning of outcomes

Another important concept is the meaning of outcomes and how data is stored. A CDA is a snapshot of the person (the record), a CCL is a snapshot in time of what happened (the contact). The next challenge in our examples is

• Contact Rate % [Given as Actual Primary Contacts/No of calls with Contact= Spoke to Primary Contact]

Using an outcome to indicate we have a call back does not necessarily mean that we were speaking to a primary contact. We also cannot put an extra field in the CDA because we will eventually get to chat to the primary contact, but we won’t know until that call which outcome will apply. You must therefore double all the outcomes to flag which ones are primary.

So in our example what we would initially rate as contacts would be anything that doesn’t start with No contact. Thus

Total not starting with No Contact / Total Contacts = Spoke to Primary Contact

1.5 The relationship of CDA and CCL’s

A common challenge of understanding in reporting is comparing the status of a customer with a period in time. The status of a customer may change but the contact (once made) is fixed.

• Conversion Rate % [Given as Sales/No of contacts - (Policy Already Renewed + Policy Already Cancelled + Wrong Telephone Number + Property Sold)]

This reporting field requires the policy status. Policy already renewed, Policy already cancelled, Wrong phone number, Property sold. These are collectively referred to as exceptions.

Treating these details as CDA data is wrong, as over time the value will change. For example Property sold will probably become true for all properties. Therefore this information must be captured in the CCL which means that your outcomes would have to contain even more detail.

• Complete - No sale – Property sold
• Complete - No sale – Policy already renewed
• Complete - No sale – Policy already cancelled
• Complete - No sale – Other reason
• Complete - Sale
• No contact - Wrong number
• No contact - No answer
• No contact - No details

So calculating the totals is becoming quite complex, requiring a good understanding of outcomes. So, how can bxp handle this complexity without the need for programming. The answer is to take a step back and review the primary reason for the reporting in the first place.

1.6 Finding the key driver for the reporting

In a sales campaign the primary driver is a Sale and in more detail whether, if there was an opportunity to sell, was the deal closed. Each campaign will boil down to this primary metric and, with it identified, we can adapt the outcomes:

• No Opportunity - Contact - Policy already cancelled
• No Opportunity - Contact - Policy already renewed
• No Opportunity - Contact - Property sold
• No Opportunity - Contact - Information sent by post follow up required
• No Opportunity - Contact - Contract sent by post follow up required
• No Opportunity - Emergency - Escalation to Manager
• No Opportunity - No Contact - No Answer
• No Opportunity - No Contact - No Details
• No Opportunity - No Contact - Wrong number
• No Opportunity - ReContact - Call back later - appointment
• No Opportunity - ReContact - Details updated call back again - Afternoon
• No Opportunity - ReContact - Details updated call back again - Evening
• No Opportunity - ReContact - Details updated call back again - Morning
• Opportunity - No Sale - Benefit
• Opportunity - No Sale - Other
• Opportunity - No Sale - Price
• Opportunity - Sale

So considering the example of our contact with the customer again

Month 3 Week 4 Day 1: Tried to ring Nick, he was too busy

• No Opportunity - ReContact - Details updated call back again - Morning

Month 3 Week 4 Day 3: Got through to Nick but he asked for more information on paper

• No Opportunity - Contact - Information sent by post follow up required

Month 3 Week 4 Day 5: Quick follow-up with call and he’s rather interested but needs time to consider

• No Opportunity - ReContact - Call back later - appointment

Month 4 Week 1 Day 2: Followed up again with Nick, he really wants to go ahead

• No Opportunity - ReContact - Call back later - appointment

Month 4 Week 2 Day 4: Contract sent

• No Opportunity - Contact - Contract sent by post follow up required

Month 4 Week 3 Day 3: Contract signed

• Opportunity - Sale

So we have an outcome for all the scenarios of the case.

1.7 Timed Windows

On Monday, Nick is not a customer; by Friday he is a customer. If we pull a report the following Monday, what status did Nick have on the previous Wednesday? If your data is in the CDA you will only see the client’s current status. If the data is stored in the CCL you can get a snapshot of the customer’s status at that time. As you can see, the difference between CCL data and CDA data needs to be understood.

The outcome is the snapshot value used. If you need a status at a given point in time, that value needs to be incorporated into the outcomes.

Analysing the data using CDA data is powerful. You can group by area or by any field you care to add, however, bear in mind that these fields change with time when considering your reports. If the data in the CDA is unlikely to change and / is not a vital element of your report then its ok to be in the CDA. For example:

• Plays golf
• Has children
• Uses Product A
• Gives us marketing permission
• Is a customer
• Is dead

These examples would usually be ok to have as a CDA because they are not likely to change.

If however you’re using the database as a CRM then reporting must be in a snapshot form. This means taking a snapshot of the primary measuring fields and storing the information in a separate way. This can be done by the MetaData module but would usually only be used for extensive reporting and analysis. This reporting cannot be implemented retrospectively unless raw transaction information is available.

If you want to see how many people were customers on Monday last week, the CCL of the outcome Sale is the best metric. For how many conversions from all the records were there at the end of a campaign, you would look at the CDA data.

1.8 Expressions of count for timed windows.

If we look at the contacts for Nick, we have six contacts and only one of them is a sale so we would only want to show this as one sale. Reporting on this customer is the challenge of the timed window.

It is only a sale in quarter two but we have been dealing with him since quarter one so we need to be absolutely specific when interpreting Nick’s sale.

For sales reports, only when a sale is made does it count. Therefore only the outcome Opportunity – Sale should appear on the report. It is better to use the CDA record and ensure that Nick is only counted once. Depending on the reporting this poses complicated challenges as to what you want to express with the report. In form setup the reporter needs to be absolutely clear as to what you want to be reporting on.

1.9 Garbage in, Garbage out

It is vital that information going into the campaigns is complete and accurate. Because of this it is possible to use extensive validation on an outcome to ensure an agent enters data in every field. Leaving blank answers, choosing inappropriate outcomes or even not choosing an outcome at all will impact the validity of the count.

bxp is very clear on its reporting. If it’s in there, it will be counted, but of course bxp can’t be held responsible for input error.