Finding timegap between Customer and Company Communications in Claims Data

arunc · December 6, 2019, 10:32am

Hi All,

Hope you are doing well!...I am working on a table that has the communications that happen between a customer who buys the product online from Amazon and the company which hosts the product on Amazon for a specific order...These communications happen as the customer is not satisfied with the product for some reason...I am trying to capture the following :

Number of communications that happen from the customer end - this can be identified in the title column with the line that contains 'Inquiry from Amazon Customer' or 'Inquiry from Customer'
Number of communications that happen from the company end -this can be identified in the title column with the line that contains 'Customer - '
The average and the maximum time gap between all customer and company communications for every order (in days and hours)..

I am looking at all the above for every order before the occurrence of the line that contains

atoz-guarantee-no-reply@amazon.com

Can you please help here!..Please find the DDL below:(both the input table and output table)

Create table #orders
(orderid varchar(20),
created DATETIME2,
title varchar(2000)
)

drop table #orders

insert into #orders values
('3635763','2019-01-19 23:10:34:345','Inquiry from Amazon Customer '),
('3635763','2019-01-17 21:14:07:348','customer - order is getting delayed '),
('3635763','2019-01-17 21:20:34:789','Shipping Inquiry from Amazon Customer '),
('3635763','2019-01-18 04:41:56:902','Claim filed to : atoz-guarantee-no-reply@amazon.com'),
('3635763','2019-02-17 14:43:36:567','Inquiry from Amazon Customer-following up again '),
('3635763','2019-02-18 15:13:29:456','customer - Final steps in sending out the order'),
('4003456','2019-01-02 03:46:56:341','Tracking order revision sent '),
('4003456','2019-01-04 06:27:43:342','Inquiry from Amazon Customer '),
('4003456','2019-01-06 06:27:32:267','Vendor: Sent parts Info '),
('4003456','2019-01-08 06:27:56:321','Customer - the order will be sent '),
('4003456','2019-01-10 08:30:44:316','Claim filed to : atoz-guarantee-no-reply@amazon.com'),
('4003456','2019-01-12 08:30:55:412','Customer - Reaching out again'),
('4003456','2019-01-13 08:30:33:512','Amazon:Atoz reponse regarding claim ')

CREATE TABLE #OUTPUT
(ORDERID varchar(20),
Numberoftimescustomerhascontacted int,
Numberoftimesourcompanyhascontacted int,
averagetimegapofcommunicationbetweencustomerandcompanyindays float,
maximumtimebetweencommunications float
)

insert into #output values
('3635763','2','1','3.96','7.9'),
('4003456','1','1','3.99','3.99')

Thanks,

Arun

harishgg1 · December 6, 2019, 11:02am

hi

i tried to do this !!

i got this far ..

please click arrow to the left for DROP CREATE data script

drop table #orders
go 

Create table #orders
(orderid varchar(20),
created DATETIME2,
title varchar(2000)
)
go 


insert into #orders values
('3635763','2019-01-19 23:10:34:345','Inquiry from Amazon Customer '),
('3635763','2019-01-17 21:14:07:348','customer - order is getting delayed '),
('3635763','2019-01-17 21:20:34:789','Shipping Inquiry from Amazon Customer '),
('3635763','2019-01-18 04:41:56:902','Claim filed to : atoz-guarantee-no-reply@amazon.com'),
('3635763','2019-02-17 14:43:36:567','Inquiry from Amazon Customer-following up again '),
('3635763','2019-02-18 15:13:29:456','customer - Final steps in sending out the order'),
('4003456','2019-01-02 03:46:56:341','Tracking order revision sent '),
('4003456','2019-01-04 06:27:43:342','Inquiry from Amazon Customer '),
('4003456','2019-01-06 06:27:32:267','Vendor: Sent parts Info '),
('4003456','2019-01-08 06:27:56:321','Customer - the order will be sent '),
('4003456','2019-01-10 08:30:44:316','Claim filed to : atoz-guarantee-no-reply@amazon.com'),
('4003456','2019-01-12 08:30:55:412','Customer - Reaching out again'),
('4003456','2019-01-13 08:30:33:512','Amazon:Atoz reponse regarding claim ')

go

i ordered by orderid , created

please click arrow to the left for SQL

;WITH cte 
     AS (SELECT Row_number() 
                  OVER( 
                    partition BY orderid 
                    ORDER BY orderid, created) AS rn, 
                * 
         FROM   #orders), 
     cte_atoz 
     AS (SELECT * 
         FROM   cte 
         WHERE  title LIKE '%atoz-guarantee%'), 
     cte_fin 
     AS (SELECT a.* 
         FROM   cte a 
                JOIN cte_atoz b 
                  ON a.orderid = b.orderid 
                     AND a.rn < b.rn) 
SELECT 'SQL so far', 
       orderid, 
       Count(CASE 
               WHEN title LIKE 'Inquiry from%' THEN 1 
             END) AS Numberoftimescustomerhascontacted, 
       Count(CASE 
               WHEN title LIKE 'Customer-%' THEN 1 
             END) AS Numberoftimesourcompanyhascontacted 
FROM   cte_fin 
GROUP  BY orderid

i ordered data by created ...

What do you mean by
averagetimegapofcommunicationbetweencustomerandcompanyindays
maximumtimebetweencommunications
???
Explain in terms of data and table andcolumns !!!

Hope this helps

arunc · December 6, 2019, 11:14am

@harishgg1 :Appreciate your response!...

By averagetimegapofcommunicationbetweencustomerandcompanyindays what I mean is that for every order the customer starts to communicate -the line which is identified by "Inquiry from" and correspondingly the company responds which is identified by "Customer - "...So I am trying to identify the timegap in the communication between company and customer if it occurs multiple times then the average of those timeframes and the maximum time among those communications..

harishgg1 · December 6, 2019, 11:30am

there are two lines one after another
1. Inquiry from Amazon Customer
2. Inquiry from Amazon Customer-following up again
which one to choose

also for the other one there are lines
1. Customer - the order will be sent
2. Claim filed to : atoz-guarantee-no-reply@amazon.com
3. Customer - Reaching out again
two customer lines ..which one to take

arunc · December 6, 2019, 11:36am

@harishgg1 : Thanks for getting back!...All the lines should be considered before atoz-guarantee-no-reply@amazon.com for calculation and counting purposes...Also following is the code change that I made

;WITH cte
AS (SELECT Row_number()
OVER(
partition BY orderid
ORDER BY orderid, created) AS rn,
*
FROM #orders),
cte_atoz
AS (SELECT *
FROM cte
WHERE title LIKE '%atoz-guarantee%'),
cte_fin
AS (SELECT a.*
FROM cte a
JOIN cte_atoz b
ON a.orderid = b.orderid
AND a.rn < b.rn)
SELECT 'SQL so far',
orderid,
Count(CASE
WHEN title LIKE '%Inquiry from Amazon Customer %' THEN 1
when title LIKE '%Shipping Inquiry from Amazon Customer %' THEN 1
END) AS Numberoftimescustomerhascontacted,
Count(CASE
WHEN title LIKE 'Customer - %' THEN 1
END) AS Numberoftimesourcompanyhascontacted
FROM cte_fin
GROUP BY orderid

arunc · December 6, 2019, 11:43am

Still there are issues with the number of times customer has contacted data output for order id 3635763 ...Can you please help with the same...i should be getting output 2 but I am getting 1 ...

harishgg1 · December 6, 2019, 12:21pm

the reason is because of the data ..

if you order the data by orderid,created ...then you will get 1
but if you dont order by orderid,created .. then you will get 2

depends on what you want to do ????
do you want to order the data by date created ??? or NOT ???

hope this helps

arunc · December 6, 2019, 12:22pm

Yes I want to order created ascending for every order id

arunc · December 6, 2019, 12:23pm

Even then I should be getting 2

harishgg1 · December 6, 2019, 12:43pm

how

this is also there

I am looking at all the above for every order before the occurrence of the line that contains atoz-guarantee-no-reply@amazon.com

arunc · December 6, 2019, 1:08pm

My bad!..Yes you are right !..So yes we have got the Numberoftimescustomerhascontacted
Numberoftimesourcompanyhascontacted

Create table #orders
(orderid varchar(20),
created DATETIME2,
title varchar(2000)
)

drop table #orders

insert into #orders values
('3635763','2019-01-17 23:10:34:345','Inquiry from Amazon Customer '),
('3635763','2019-01-17 21:14:07:348','customer - order is getting delayed '),
('3635763','2019-01-17 21:20:34:789','Shipping Inquiry from Amazon Customer '),
('3635763','2019-01-18 04:41:56:902','Claim filed to : atoz-guarantee-no-reply@amazon.com'),
('3635763','2019-02-17 14:43:36:567','Inquiry from Amazon Customer-following up again '),
('3635763','2019-02-18 15:13:29:456','customer - Final steps in sending out the order'),
('4003456','2019-01-02 03:46:56:341','Tracking order revision sent '),
('4003456','2019-01-04 06:27:43:342','Inquiry from Amazon Customer '),
('4003456','2019-01-06 06:27:32:267','Vendor: Sent parts Info '),
('4003456','2019-01-08 06:27:56:321','Customer - the order will be sent '),
('4003456','2019-01-10 08:30:44:316','Claim filed to : atoz-guarantee-no-reply@amazon.com'),
('4003456','2019-01-12 08:30:55:412','Customer - Reaching out again'),
('4003456','2019-01-13 08:30:33:512','Amazon:Atoz reponse regarding claim ')

SELECT * FROM #ORDERS

CREATE TABLE #OUTPUT
(ORDERID varchar(20),
Numberoftimescustomerhascontacted int,
Numberoftimesourcompanyhascontacted int,
averagetimegapofcommunicationbetweencustomerandcompanyindays float,
maximumtimebetweencommunications float
)

insert into #output values
('3635763','2','1','3.96','7.9'),
('4003456','1','1','3.99','3.99')

select * from #OUTPUT

;WITH cte
AS (SELECT Row_number()
OVER(
partition BY orderid
ORDER BY orderid, created ) AS rn,
*
FROM #orders),
cte_atoz
AS (SELECT *
FROM cte
WHERE title LIKE '%atoz-guarantee%'),
cte_fin
AS (SELECT a.*
FROM cte a
JOIN cte_atoz b
ON a.orderid = b.orderid
AND a.rn < b.rn)
SELECT 'SQL so far',
orderid,
Count(CASE
WHEN title LIKE '%Inquiry from Amazon Customer %' THEN 1
when title LIKE '%Shipping Inquiry from Amazon Customer %' THEN 1
END) AS Numberoftimescustomerhascontacted,
Count(CASE
WHEN title LIKE 'Customer - %' THEN 1
END) AS Numberoftimesourcompanyhascontacted
FROM cte_fin
GROUP BY orderid

harishgg1 · December 6, 2019, 1:11pm

you said

My Bad

thats U.S slang

how are you related to U.S ( united states of america )

arunc · December 6, 2019, 1:11pm

I had been there for sometime!

harishgg1 · December 6, 2019, 1:12pm

how many years ??

arunc · December 6, 2019, 1:12pm

Like around 4 years...

harishgg1 · December 6, 2019, 1:13pm

oh ok NICE

i was there in U.S for 20 years

harishgg1 · December 6, 2019, 1:27pm

what abt this !!!

By averagetimegapofcommunicationbetweencustomerandcompanyindays what I mean is that for every order the customer starts to communicate -the line which is identified by "Inquiry from" and correspondingly the company responds which is identified by "Customer - "...So I am trying to identify the timegap in the communication between company and customer if it occurs multiple times then the average of those timeframes and the maximum time among those communications..

In the DATA ...
there are two lines one after another

Inquiry from Amazon Customer
Inquiry from Amazon Customer-following up again
which one to choose

also for the other one there are lines

Customer - the order will be sent
Claim filed to : atoz-guarantee-no-reply@amazon.com
Customer - Reaching out again
two customer lines ..which one to take

arunc · December 9, 2019, 9:24am

@ Harishgg1: Sorry for the delay in the response!.. All the lines should be chosen before [atoz-guarantee-no-reply@amazon.com] for counting and calculation purposes...Also both the lines

Inquiry from Amazon Customer
Inquiry from Amazon Customer-following up again
which one to choose

should be considered..

arunc · December 13, 2019, 10:34am

@harishgg1: I have modified the problem now...can you please help here..

.I am trying to find out the maximum and the average timeframe between communications (for communication between the customer and the company -with the customer coming first followed by the response to the customer by the company)...

Suppose if there are multiple responses of the company after a customers questions only the timeframe between the customer question and the 1st company response is to be considered...

In my data set in the action column the communication by the customer is denoted by cust and the communication by the company is denoted by company and the rowno column arranged in ascending order for every order denotes the flow of conversations...

For example: If an order has multiple customer->company communications

then the maximum time among the multiple conversations and the average time among the multiple conversations is to be taken but the order is always customer followed by company ..the time to be determined is the time taken for the response by a company to the customers question...

Please find below the DDL and the expected output with the screenshot of working calculations...Can you please help here..

create table #orders
(orderid int,
created datetime2,
action varchar(20),
rowno int)

insert into #orders values
('3635763','2019-01-15 23:10:34:321','Cust','1'),
('3635763','2019-01-17 21:14:23:678','Company','2'),
('3635763','2019-01-17 21:20:45:789','Cust','3'),
('3635763','2019-01-20 21:20:45:789','Company','4'),
('3635763','2019-01-23 22:20:45:890','Company','5'),
('3635763','2019-01-25 22:20:34:367','Cust','6'),
('3635763','2019-01-29 22:20:32:567','Company','7'),
('4003456','2019-01-04 06:27:33:567','Cust','1'),
('4003456','2019-01-08 06:27:45:333','Company','2'),
('4003456','2019-01-11 06:27:35:890','Cust','3'),
('4003456','2019-01-15 06:27:33:678','Company','4')

--Output table

create table #output
(orderid int,
maximumtimeforcommunicationinhrs int,

averagetimeforcommunicationinhrs int)

insert into #output values
('3635763','96','71'),
('4003456','96','96')

Arun

harishgg1 · December 13, 2019, 12:20pm

Hi

Please see if this works for you

please click arrow to the left for drop create data script

drop table #orders 
go 

create table #orders
(orderid int,
created datetime2,
action varchar(20),
rowno int)
go 

insert into #orders values
('3635763','2019-01-15 23:10:34:321','Cust','1'),
('3635763','2019-01-17 21:14:23:678','Company','2'),
('3635763','2019-01-17 21:20:45:789','Cust','3'),
('3635763','2019-01-20 21:20:45:789','Company','4'),
('3635763','2019-01-23 22:20:45:890','Company','5'),
('3635763','2019-01-25 22:20:34:367','Cust','6'),
('3635763','2019-01-29 22:20:32:567','Company','7'),
('4003456','2019-01-04 06:27:33:567','Cust','1'),
('4003456','2019-01-08 06:27:45:333','Company','2'),
('4003456','2019-01-11 06:27:35:890','Cust','3'),
('4003456','2019-01-15 06:27:33:678','Company','4')
go

please see NEW SQL ..

select orderid, *, 
	case when action = 'Cust' then DATEDIFF(hh,created,lead(created) over(partition by orderid order by rowno))/24.0 end as timeTakenResponseCustomerDays ,
	case when action = 'Cust' then DATEDIFF(hh,created,lead(created) over(partition by orderid order by rowno)) end as timeTakenResponseCustomerHours
from  #orders
go

please click arrow to the left for ... Final Output SQL ...

;with cte as 
(
select  *, 
	case when action = 'Cust' then DATEDIFF(hh,created,lead(created) over(partition by orderid order by rowno))/24.0 end as timeTakenResponseCustomerDays ,
	case when action = 'Cust' then DATEDIFF(hh,created,lead(created) over(partition by orderid order by rowno)) end as timeTakenResponseCustomerHours
from  #orders
)
select orderid,MAX(timeTakenResponseCustomerHours),AVG(timeTakenResponseCustomerHours) from cte 
group by orderid