Count of customers based on type of contact

[alexish]

Hello,

I'm loooking for help in solving a tricky (for me...) issue that I have.

Context :

We have customers, that are "visited" either Face-To-Face, or remotely (we call this "channel")

We track all those interactions, and the "channel" that is used.

See an example file included.

What I'm trying to do :

We would like to identify, by month, the number of Unique Customers, that have been visited F2F, or Remote... Or if both, "Hybrid".

Constraints : I can't modify the script here, I'm going to need a solution without it !

What I've done : (and that almost works...)

I've created 3 measures that look like this :

COUNT( DISTINCT
    {< CUSTOMER_ID = {"=
           COUNT({<INTERACTION_CHANNEL = {'Face-To-Face', 'INPR'}>}CUSTOMER_INTERACTION_CODE) > 0
           AND COUNT({<INTERACTION_CHANNEL = {'Remote','RemoteE-mail','RemotePhone-In-depth','RemotePhone-Regular','RemoteWeb'}>}CUSTOMER_INTERACTION_CODE) = 0
           "}
    >}
CUSTOMER_ID)

Where the red part changes : if ">" &  "=" : F2F interactions, if "=" then ">" : Remote, if both are ">", it's "Hybrid"

See qvf included

Where I have issues :

Left table is raw data

Right table is what i'm trying to achieve

It seems in my right table that the formula does not contextualize with the date, and I'm not sure why...

For example, for date 2/15/2020, I should have F2F=2  since Customer C2 & C7 have F2F interactions.

But since C2 has 1 interaction in Remote on 1/15/2020, it's displayed in hybrid. If I filter my date, the tables now works fine.

And I can't seem to find how to get the formula right. (or if I shoudl change totally the way I approach this)

Could you please help me ?

Thanks everyone

[tresesco]

F2F

Count(
{<CUSTOMER_ID={"=Concat(Distinct INTERACTION_CHANNEL)='Face-To-Face'"}>}
distinct CUSTOMER_ID)

Remote

Count(
{<CUSTOMER_ID={"=Not Index(Concat(Distinct INTERACTION_CHANNEL),'Face-To-Face')"}>}
distinct CUSTOMER_ID)

Hybrid

Count(
{<CUSTOMER_ID={"=Index(Concat(Distinct INTERACTION_CHANNEL),'Face-To-Face') and count(Distinct INTERACTION_CHANNEL)>1 "}>}
distinct CUSTOMER_ID)

[alexish]

Hello,

Thank you for the answer, but this does not work.
This seems to have the same issue as I have, where your formula is not contextualized by the date into the table.

Would you mind explaining how your formula works, and why it's supposed to solve my issue ?

Thanks

[tresesco]

Could you take up one of my expressions and explain the output you get (and why that is wrong comparing with the expected output)?

[NitinK7]

Hi,

Can you you paste here what is a right answer.

is this right  ??

Thanks,

Nitin.

[alexish]

Sorry for the bad writing.
Second table is with you expressions.

If I filter my table on Date, the result changes. It should not :

The issue with my solution and youurs I assume, is that it counts whatever the date.

When C2 has a Remote intercation on date 1, and a F2F interaction on date 2, it should be not be counted as hybrid for each day.

The expected result here would be this (also attached). I've made another table to explain why.

[alexish]

Hello,

I'm bumping back my question.
Anyone has any idea of how to do it ?

Thanks

[NitinK7 · Accepted solution]

Hi ,

try below Expressions, it is working for me

F2F

Count(Aggr(if(Count({<INTERACTION_CHANNEL={'Face-To-Face','INPR'}>}CUSTOMER_ID)>0 and
COUNT({<INTERACTION_CHANNEL={'Remote','RemoteE-mail','RemotePhone-In-depth','RemotePhone-Regular','RemoteWeb'}>}CUSTOMER_INTERACTION_CODE) = 0 ,
Count(CUSTOMER_ID)),CUSTOMER_ID,DATE))

REMOTE

Count(Aggr(if(Count({<INTERACTION_CHANNEL={'Face-To-Face','INPR'}>}CUSTOMER_ID)=0 and
COUNT({<INTERACTION_CHANNEL={'Remote','RemoteE-mail','RemotePhone-In-depth','RemotePhone-Regular','RemoteWeb'}>}CUSTOMER_INTERACTION_CODE) > 0 ,
Count(CUSTOMER_ID)),CUSTOMER_ID,DATE))

HYBRID

Count(Aggr(if(Count({<INTERACTION_CHANNEL={'Face-To-Face','INPR'}>}CUSTOMER_ID)>0 and
COUNT({<INTERACTION_CHANNEL={'Remote','RemoteE-mail','RemotePhone-In-depth','RemotePhone-Regular','RemoteWeb'}>}CUSTOMER_INTERACTION_CODE) >0 ,
Count(CUSTOMER_ID)),CUSTOMER_ID,DATE))

Thanks,

Nitin.

[tresesco]

So you are considering 'INPR' also as 'F2F' ?

[NitinK7]

Yes @tresesco ,