Forum Discussion
develop smart algorithm for customer service email response
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.
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([email protected])], "@phoenix.com" ), "Yes", "No" )From Company to Customer = IF ( CONTAINSSTRING ( 'Table'[From Address], "@phoenix.com" ) && NOT ( CONTAINSSTRING ( 'Table'[To Address([email protected])], "@phoenix.com" ) ), "Yes", "No" )Result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
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([email protected])], "@company.com" ), "Yes", "No" )From Company to Customer = IF ( CONTAINSSTRING ( 'Table'[From Address], "@company.com" ) && CONTAINSSTRING ( 'Table'[To Address([email protected])], "@gmail.com" ), "Yes", "No" )Get the correct result:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thank you. I realize in real life, there are hundreds of different customers emailing to my company, so their email addresses vary (i.e. [email protected], [email protected], [email protected], 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.
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 _ kalyjhi,
for example, my company (company name is phoenix, for example) has these addresses: [email protected], and [email protected], and customer service representatives' individual email like: [email protected], [email protected], etc
so basically, everything else that does not contain "phoenix" after @ is from the customer (i.e. [email protected], [email protected])
thank you very much.
