none
Finding timegap between Customer and Company Communications in Claims Data RRS feed

  • Question

  • 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 :

    1) 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'

    2) Number of communications that happen from the company end -this can be identified in the title column with the line that contains 'Customer - '

    3) 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


    Arun

    Friday, December 6, 2019 10:30 AM

Answers

  • Here is a solution. I introduced a temp table, since I believe this will be more efficient to filter out the rows after the claim has been filed.

    I had some problems with my query not producing the expected results, but there appears to be a problem with the sample data for the first order - the fist inquiry from the customer had a date several days later than the reply from Amazon. Also, it appears that the expected values for max and avg were just made up. Or at least I was not able to understand how these values would be achieved.

    CREATE TABLE #classified (orderid int      NOT NULL,
                              created datetime NOT NULL,
                              action  char(4)  NOT NULL,
                              rowno   int      NOT NULL,
                              PRIMARY KEY (orderid, action, rowno)
    )
    
    
    ; WITH classification AS (
       SELECT orderid, created, 
              CASE WHEN lower(title) LIKE '%amazon customer%' THEN 'Cust'
                   WHEN lower(title) LIKE 'customer %' THEN 'Ama'
                   WHEN lower(title) LIKE '%atoz-guarantee-no-reply@amazon.com%' THEN 'Stop'
              END AS action
       FROM   #orders
    )
    INSERT #classified (orderid, created, action, rowno)
       SELECT orderid, created, action, row_number() OVER(PARTITION BY orderid, action ORDER BY created) AS rowno
       FROM   classification
       WHERE  action IS NOT NULL
    
    
    DELETE #classified
    FROM   #classified a
    WHERE  EXISTS (SELECT *
                   FROM   #classified b
                   WHERE  b.orderid = a.orderid
                     AND  b.action = 'Stop'
                     AND  b.created <= a.created)
    
    
    
    ; WITH pivoted AS (
      SELECT orderid, rowno, 
             MIN(CASE action WHEN 'Cust' THEN created END) AS Cust,
             MIN(CASE action WHEN 'Ama'  THEN created END) AS Ama
      FROM   #classified
      GROUP  BY orderid, rowno
    )
    SELECT orderid, 
           COUNT(Cust) AS no_of_cust_contacts,
           COUNT(Ama) AS no_of_ama_contacts,
           convert(decimal(10, 2), AVG(1E0 * datediff(SECOND, Cust, Ama) / 86400)) AS avg_resp_time,
           convert(decimal(10, 2), MAX(1E0 * datediff(SECOND, Cust, Ama) / 86400)) AS max_resp_time
    FROM   pivoted
    GROUP  BY orderid


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, December 7, 2019 10:50 AM

All replies

  • Here is a solution. I introduced a temp table, since I believe this will be more efficient to filter out the rows after the claim has been filed.

    I had some problems with my query not producing the expected results, but there appears to be a problem with the sample data for the first order - the fist inquiry from the customer had a date several days later than the reply from Amazon. Also, it appears that the expected values for max and avg were just made up. Or at least I was not able to understand how these values would be achieved.

    CREATE TABLE #classified (orderid int      NOT NULL,
                              created datetime NOT NULL,
                              action  char(4)  NOT NULL,
                              rowno   int      NOT NULL,
                              PRIMARY KEY (orderid, action, rowno)
    )
    
    
    ; WITH classification AS (
       SELECT orderid, created, 
              CASE WHEN lower(title) LIKE '%amazon customer%' THEN 'Cust'
                   WHEN lower(title) LIKE 'customer %' THEN 'Ama'
                   WHEN lower(title) LIKE '%atoz-guarantee-no-reply@amazon.com%' THEN 'Stop'
              END AS action
       FROM   #orders
    )
    INSERT #classified (orderid, created, action, rowno)
       SELECT orderid, created, action, row_number() OVER(PARTITION BY orderid, action ORDER BY created) AS rowno
       FROM   classification
       WHERE  action IS NOT NULL
    
    
    DELETE #classified
    FROM   #classified a
    WHERE  EXISTS (SELECT *
                   FROM   #classified b
                   WHERE  b.orderid = a.orderid
                     AND  b.action = 'Stop'
                     AND  b.created <= a.created)
    
    
    
    ; WITH pivoted AS (
      SELECT orderid, rowno, 
             MIN(CASE action WHEN 'Cust' THEN created END) AS Cust,
             MIN(CASE action WHEN 'Ama'  THEN created END) AS Ama
      FROM   #classified
      GROUP  BY orderid, rowno
    )
    SELECT orderid, 
           COUNT(Cust) AS no_of_cust_contacts,
           COUNT(Ama) AS no_of_ama_contacts,
           convert(decimal(10, 2), AVG(1E0 * datediff(SECOND, Cust, Ama) / 86400)) AS avg_resp_time,
           convert(decimal(10, 2), MAX(1E0 * datediff(SECOND, Cust, Ama) / 86400)) AS max_resp_time
    FROM   pivoted
    GROUP  BY orderid


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, December 7, 2019 10:50 AM
  • Hi Erland,

    Really appreciate your response!!...Thanks for your help!...Please find below the corrected input data and the code...The only issue is that  the average response time and the maximum response time between customer and company communications are coming to be the same for every orderid...Please find below the changed input data and code plus the snapshot of the output ...(I have calculated the time difference in hours)

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


    insert into #orders values
    ('3635763','2019-01-17 14:10:34:345','Inquiry from Amazon Customer '),
    ('3635763','2019-01-17 21:14:07:348','customer - order is getting delayed '),
    ('3635763','2019-01-17 23: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

    DROP TABLE  #ORDERS 


    CREATE TABLE #classified2 (orderid int      NOT NULL,
                              created datetime NOT NULL,
                              action  char(4)  NOT NULL,
                              rowno   int      NOT NULL,
                              PRIMARY KEY (orderid, action, rowno)
    )

    ; WITH classification2 AS (
       SELECT orderid, created, 
              CASE WHEN lower(title) LIKE '%amazon customer%' THEN 'Cust'
                   WHEN lower(title) LIKE 'customer %' THEN 'Ama'
                   WHEN lower(title) LIKE '%atoz-guarantee-no-reply@amazon.com%' THEN 'Stop'
              END AS action
       FROM   #orders
    )
    INSERT #classified2 (orderid, created, action, rowno)
       SELECT orderid, created, action, row_number() OVER(PARTITION BY orderid, action ORDER BY created) AS rowno
       FROM   classification2
       WHERE  action IS NOT NULL

       DELETE #classified2
    FROM   #classified2 a
    WHERE  EXISTS (SELECT *
                   FROM   #classified2 b
                   WHERE  b.orderid = a.orderid
                     AND  b.action = 'Stop'
                     AND  b.created <= a.created)


    ; WITH pivoted AS (
      SELECT orderid, rowno, 
             MIN(CASE action WHEN 'Cust' THEN created END) AS Cust,
             MIN(CASE action WHEN 'Ama'  THEN created END) AS Ama
      FROM   #classified2
      GROUP  BY orderid, rowno
    )
    SELECT orderid, 
           COUNT(Cust) AS no_of_cust_contacts,
           COUNT(Ama) AS no_of_ama_contacts,
           convert(decimal(10, 2), AVG(1E0 * datediff(SECOND, Cust, Ama) / 3600)) AS avg_resp_time,
           convert(decimal(10, 2), MAX(1E0 * datediff(SECOND, Cust, Ama) / 3600)) AS max_resp_time
    FROM   pivoted
    GROUP  BY orderid


    Arun

    Monday, December 9, 2019 7:20 AM
  • If there is only one contact back from Amazon, how could the max and the average be different? By necessity, they will be the same.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, December 9, 2019 7:39 AM
  • Awesome!!!...Thanks a lot Erland:)...What you said makes a lot of business sense!..

    Thanks,

    Arun


    Arun

    Monday, December 9, 2019 8:55 AM
  • @Erland Sommarskog : Just a quick question!...I am now changing the lookup texts for Cust .Ama --Meaning multiple lookup texts for customer and company communication.. When I do this I am getting the average time of communication to be negative .....Can you please help here...Any insights/help would be appreciated!..Please find the changed code below:


    CREATE TABLE #classified30 (oid int      NOT NULL,
                              created datetime NOT NULL,
                              action  char(4)  NOT NULL,
                              rowno   int      NOT NULL,
                              PRIMARY KEY (oid, action, rowno)
    )

    ; WITH #classification30 AS (
       SELECT oid, created, 
       CASE WHEN lower(title) LIKE '%amazon customer%' THEN 'Cust'
                                           WHEN lower(title) LIKE '%customer called in requesting to return the item
    %' THEN 'Cust'
    WHEN lower(title) LIKE '%return requested for order%' THEN 'Cust' ---Communication from the customer
    WHEN lower(title) LIKE '%customer email%' THEN 'Cust'
    WHEN lower(title) LIKE '%return authorization request for order%' THEN 'Cust'
    WHEN lower(title) LIKE '%cst called in%' THEN 'Cust'
    WHEN lower(title) LIKE '%customer called%' THEN 'Cust'
    WHEN lower(title) LIKE '%an inquiry from one of our customers%' THEN 'Cust'     
    WHEN lower(title) LIKE '%request a refund%' THEN 'Cust'               
    WHEN lower(title) LIKE '%request a replacement item%' THEN 'Cust'
    WHEN lower(title) LIKE '%re: order #%' THEN 'Cust'
    WHEN lower(title) LIKE '%Title : Re:%' THEN 'Cust'
    WHEN lower(title) LIKE '- Order #' THEN 'Cust'


                   WHEN lower(title) LIKE 'customer %' THEN 'ama'   
                                                       WHEN lower(title) LIKE '%cust email%' THEN 'ama'
                                                       WHEN lower(title) LIKE '%called cust%' THEN 'ama'
                                                       WHEN lower(title) LIKE '%Email Sent%' THEN 'ama'

                   WHEN lower(title) LIKE '%atoz-guarantee-no-reply@amazon.com%' THEN 'Stop' 
                                                       WHEN lower(title) LIKE '%seller-guarantee@amazon.com%' THEN 'Stop'
                                                        WHEN lower(title) LIKE '%amazon a-to-z guarantee%' THEN 'Stop'
              END AS action
       FROM   ##temp3
    )

    INSERT #classified30 (oid, created, action, rowno)
       SELECT oid, created, action, row_number() OVER(PARTITION BY oid, action ORDER BY created) AS rowno
       FROM   #classification30
       WHERE  action IS NOT NULL


        DELETE #classified30
    FROM   #classified30 a
    WHERE  EXISTS (SELECT *
                   FROM   #classified30 b
                   WHERE  b.oid = a.oid
                     AND  b.action = 'Stop'
                     AND  b.created <= a.created)




    ; WITH pivoted AS (
      SELECT oid, rowno, 
             MIN(CASE action WHEN 'Cust' THEN created END) AS Cust,
             MIN(CASE action WHEN 'ama'  THEN created END) AS ama
      FROM   #classified30
      GROUP  BY oid, rowno
    )
    SELECT oid, 
           COUNT(Cust) AS no_of_cust_contacts,
           COUNT(ama) AS no_of_OJ_contacts,
           convert(decimal(10, 2), AVG(1E0 * datediff(SECOND, Cust, ama) / 3600)) AS avg_resp_time, ---Count in hours 
           convert(decimal(10, 2), MAX(1E0 * datediff(SECOND, Cust, ama) / 3600)) AS max_resp_time
    FROM   pivoted
    GROUP  BY oid


    Arun




    Thursday, December 12, 2019 11:04 AM
  • Do you have sample data that demonstrates the issue?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, December 12, 2019 11:29 AM
  • @Erland Sommarskog: Below find below the new data that demonstrates the issue:

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

    insert into #orders values
    ('3635763','2019-01-15 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','request a refund '),
    ('3635763','2019-01-18 21:20:34:789','Vendor Call '),
    ('3635763','2019-01-20 21:20:34:789','Email Sent '),
    ('3635763','2019-01-20 22:20:34:789','Internal Communications '),
    ('3635763','2019-01-23 22:20:34:789','Customer - Reminder Sent'),
    ('3635763','2019-01-25 22:20:34:789','return authorization request for order'),
    ('3635763','2019-01-29 22:20:34:789','customer - waiting time is over'),
    ('3635763','2019-01-30 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-09 06:27:56:321','Raising ticket for avoiding future issues'),
    ('4003456','2019-01-11 06:27:56:321','Title : Re: Avoiding Supply Chain Issues:Sam Adam'),
    ('4003456','2019-01-12 06:27:56:321','Calling the Vendor'),
    ('4003456','2019-01-15 06:27:56:321','Called Cust'),
    ('4003456','2019-01-17 08:30:44:316','Claim filed to : atoz-guarantee-no-reply@amazon.com'),
    ('4003456','2019-01-19 08:30:55:412','Customer - Reaching out again'),
    ('4003456','2019-01-21 08:30:33:512','Amazon:Atoz reponse regarding claim ')

    Also the Code used :

    CREATE TABLE #classified202 (orderid int      NOT NULL,
                              created datetime NOT NULL,
                              action  char(4)  NOT NULL,
                              rowno   int      NOT NULL,
                              PRIMARY KEY (orderid, action, rowno)
    )



    ; WITH #classification202 AS (
       SELECT orderid, created, 
       CASE WHEN lower(title) LIKE '%amazon customer%' THEN 'Cust'
                                           WHEN lower(title) LIKE '%customer called in requesting to return the item
    %' THEN 'Cust'
    WHEN lower(title) LIKE '%return requested for order%' THEN 'Cust' ---Communication from the customer
    WHEN lower(title) LIKE '%customer email%' THEN 'Cust'
    WHEN lower(title) LIKE '%return authorization request for order%' THEN 'Cust'
    WHEN lower(title) LIKE '%cst called in%' THEN 'Cust'
    WHEN lower(title) LIKE '%customer called%' THEN 'Cust'
    WHEN lower(title) LIKE '%an inquiry from one of our customers%' THEN 'Cust'     
    WHEN lower(title) LIKE '%request a refund%' THEN 'Cust'               
    WHEN lower(title) LIKE '%request a replacement item%' THEN 'Cust'
    WHEN lower(title) LIKE '%re: order #%' THEN 'Cust'
    WHEN lower(title) LIKE '%Title : Re:%' THEN 'Cust'
    WHEN lower(title) LIKE '- Order #' THEN 'Cust'


                   WHEN lower(title) LIKE 'customer %' THEN 'OJ'   
                                                       WHEN lower(title) LIKE '%cust email%' THEN 'OJ'
                                                       WHEN lower(title) LIKE '%called cust%' THEN 'OJ'
                                                       WHEN lower(title) LIKE '%Email Sent%' THEN 'OJ'

                   WHEN lower(title) LIKE '%atoz-guarantee-no-reply@amazon.com%' THEN 'Stop' 
                                                       WHEN lower(title) LIKE '%seller-guarantee@amazon.com%' THEN 'Stop'
                                                        WHEN lower(title) LIKE '%amazon a-to-z guarantee%' THEN 'Stop'
              END AS action
       FROM   #orders
    )

    INSERT #classified202 (orderid, created, action, rowno)
       SELECT orderid, created, action, row_number() OVER(PARTITION BY orderid, action ORDER BY created) AS rowno
       FROM   #classification202
       WHERE  action IS NOT NULL

          DELETE #classified202
    FROM   #classified202 a
    WHERE  EXISTS (SELECT *
                   FROM   #classified202 b
                   WHERE  b.orderid = a.orderid
                     AND  b.action = 'Stop'
                     AND  b.created <= a.created)


    ; WITH pivoted AS (
      SELECT orderid, rowno, 
             MIN(CASE action WHEN 'Cust' THEN created END) AS Cust,
             MIN(CASE action WHEN 'OJ'  THEN created END) AS OJ
      FROM   #classified202
      GROUP  BY orderid, rowno
    )
    SELECT orderid, 
           COUNT(Cust) AS no_of_cust_contacts,
           COUNT(OJ) AS no_of_OJ_contacts,
           convert(decimal(10, 2), AVG(1E0 * datediff(SECOND, Cust, OJ) / 3600)) AS avg_resp_time, ---Count in hours 
           convert(decimal(10, 2), MAX(1E0 * datediff(SECOND, Cust, OJ) / 3600)) AS max_resp_time
    FROM   pivoted
    GROUP  BY orderid


    Arun

    Thursday, December 12, 2019 1:28 PM
  • And the expected output being the following: Detailed explanation for order ID:3635763 ...the expected time is in hours :With the maximum response time being 96 hrs..



    Arun


    Thursday, December 12, 2019 1:31 PM
  • Not sure how you arrive at the figures 46, 72 and 96. You have these times:

    3635763   2019-01-15 23:10:34.347   Cust   1
    3635763   2019-01-17 21:14:07.347   OJ     1  -- 46 hours
    3635763   2019-01-17 21:20:34.790   Cust   2
    3635763   2019-01-23 22:20:34.790   OJ     2  -- 6 days + 1h = 145 hours
    3635763   2019-01-25 22:20:34.790   Cust   3
    3635763   2019-01-29 22:20:34.790   OJ     3  -- 4 days = 96 hours.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, December 12, 2019 10:35 PM
  • @Erland Sommarskog : Appreciate your response!...With regard to the second timeframe "Email Sent" is a company communication that happens on the 20th ...Also request a refund is a customer communication which happens on the 17th...So the difference being 3 days which is 72 hrs...

    Snapshot from the input data set...

    ('3635763','2019-01-17 21:20:34:789','request a refund '),
    ('3635763','2019-01-18 21:20:34:789','Vendor Call '),
    ('3635763','2019-01-20 21:20:34:789','Email Sent '),

    Thanks,

    Arun


    Arun



    Friday, December 13, 2019 2:44 AM
  • But that line is not being classified as neither customer or vendor.

    To debug this, I made this little modification:

    INSERT #classified202 (orderid, created, action, rowno)
       SELECT orderid, created, action, row_number() OVER(PARTITION BY orderid, action ORDER BY created) AS rowno
       FROM   #classification202
       WHERE  action IS NOT NULL

    SELECT * FROM #orders
    SELECT * FROM #classified202

          DELETE #classified202
          ...

    It may be better to make the action column in #classified202 nullable and drop the condition "WHERE action IS NOT NULL", and instead let the DELETE statement delete the rows with no action. It may also be worth adding the text to the #classified202 temp table. All this to make your debugging easier.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 13, 2019 12:51 PM
  • Thanks Erland!...Appreciate your response!..


    Arun

    Monday, December 16, 2019 10:53 AM