i have a Google Spreadsheet table:
user | message_id | event | timestamp -------------------------------------------------------------------------------- john player | ekjf939e9313140_34k | delivered | 04/13/2018 12:56:30 john player | ekjf939e9313140_34k | opened | 04/15/2018 16:05:00 john player | ekjf939e9313140_34k | opened | 04/16/2018 22:15:20 john player | ekjf939e9313140_34k | opened | 04/16/2018 23:47:33 cristian dior | dsfsk0340344030fkjkj | delivered | 04/12/2018 18:45:21 cristian dior | dsfsk0340344030fkjkj | opened | 04/13/2018 15:40:17 cristian dior | dsfsk0340344030fkjkj | clicked | 04/13/2018 16:00:07 cristian dior | dsfsk0340344030fkjkj | clicked | 04/13/2018 16:04:10 cristian dior | dsfsk0340344030fkjkj | clicked | 04/14/2018 12:30:11
For a user, for each unique message_id, how do i create an Google spreadsheet pivot table that can display hours elapsed between when an email was delivered and when it was opened?
=QUERY( ArrayFormula( QUERY({A:C,value(D:D)}, "select Col1, Col2, sum(Col4) where Col1 is not null group by Col1, Col2 pivot Col3")), "select Col1, Col2, Col4 - Col3 label Col4 - Col3 'Time' format Col4 - Col3 'hh:mm:ss'")
The first query is to pivot columns by the event. The second is to calculate the difference.
The result:
user message_id Time
cristian dior dsfsk0340344030fkjkj 20:54:56
john player ekjf939e9313140_34k 03:08:30
Reference: https://developers.google.com/chart/interactive/docs/querylanguage
Edit
For multiple events, I suggest to modify the formula:
=QUERY(QUERY(filter({A:C,value(D:D)},REGEXMATCH(C:C,"delivered|opened")),"select Col1, Col2, sum(Col4) where Col1 is not null group by Col1, Col2 pivot Col3"), "select Col1, Col2, Col4 - Col3 label Col1 'user', Col2 'id', Col4 - Col3 'Time' format Col4 - Col3 'hh:mm:ss'")
it filters only needed events: REGEXMATCH(C:C,"delivered|opened")