develop smart algorithm for customer service email response

[k943]

I am trying to make 2 columns that determine whether each email transaction of a case is from customer to company or from company to customer. Here is a sample picture:

for example case ID#1, Andy the customer emailed customerservice Rep1 , so Yes in "From Customer to Company", but customer service Rep 1 forwarded the email to salesdepartment, so No and No for both columns. Then, Rep 2 emailed three people: Rep1, sales department (like cc), and Andy. So the third record has a Yes in From Company to Customer.

Any help is very much appreciated.

[k943]

thank you. I realize in real life, there are hundreds of different customers emailing to my company, so their email addresses vary (i.e. xxx@companyA.com, xxx@firmB.com, xxx@organizationC.com, etc).

In that case, the formula will not work  ecause each case ID is generated by different customers and they all have different email addresses.

How do you take this factor when developing the custom columns? thank you very much.

[v-yanjiang-msft]

Hi @k943 ,

Then what is the logic that distinguishes customers from companies. I mean, if we just handle it manually, what are the standards.

Best Regards,
Community Support Team _ kalyj

[k943]

hi,

for example,  my company (company name is phoenix, for example) has these addresses: steel.customerservice.america@phoenix.com, and salessupport.america@phoenix.com, and customer service representatives' individual email like: andy.lynch@phoenix.com, kathy.beckman@phoenix.com, etc

so basically, everything else that does not contain "phoenix" after @ is from the customer (i.e. xxx@deltagroup.com, xxx@sapen-mineral.com)

thank you very much.

[v-yanjiang-msft · Accepted solution]

Hi @k943 ,

Sorry for late back. According to your description, I moidfy the sample and the formula:

From Customer to Company =
IF (
    NOT ( CONTAINSSTRING ( 'Table'[From Address], "@phoenix.com" ) )
        && CONTAINSSTRING ( 'Table'[To Address(xxx@company.com)], "@phoenix.com" ),
    "Yes",
    "No"
)

From Company to Customer =
IF (
    CONTAINSSTRING ( 'Table'[From Address], "@phoenix.com" )
        && NOT ( CONTAINSSTRING ( 'Table'[To Address(xxx@company.com)], "@phoenix.com" ) ),
    "Yes",
    "No"
)

Result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yanjiang-msft]

Hi @k943 ,

According to your description, here's my solution. Create two calculated columns.

From Customer to Company =
IF (
    CONTAINSSTRING ( 'Table'[From Address], "@gmail.com" )
        && CONTAINSSTRING ( 'Table'[To Address(xxx@company.com)], "@company.com" ),
    "Yes",
    "No"
)

From Company to Customer =
IF (
    CONTAINSSTRING ( 'Table'[From Address], "@company.com" )
        && CONTAINSSTRING ( 'Table'[To Address(xxx@company.com)], "@gmail.com" ),
    "Yes",
    "No"
)

Get the correct result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.