Calculating time in various stages by aggregating the time in multiple columns

[logiemeister]

Hi Community,

I've searched the community for a solution, but my 'natural language processing' isn't up to scratch. 

I'm trying to calculate the number of days leads spend in each phase of our sales funnel. The data is currently in the format below Table 1, however, I'd like it to look like Table 2 below. The calculations are calculated based on today's date (9 Jan 2019).

Challenges

1. Depending on the complexity of the customer journey, there can be up to 100 phases/timestamps.
2. Each journey is different. The number of columns is defined by the lead with the greatest number of phases. 
3. A timestamp is created every time a lead is opened/actioned. There can be multiple status/phase change timestamps in the same phase (e.g. See phase 2 and phase 3 below).

Objectives

1. Get the data in the format of Table 2
2. Calculate the total time in each unique phase (i.e. if a lead multiple status/phase change timestamps in the same phase, then calculate the total time in that phase).

Table 1

Lead ID	Product Code	Sale_Start	UPDATE_ON	Phase 1	Date 1	Phase 2	Date 2	Phase 3	Date 3	Phase 4	Date 4
1	12405	01/11/2018	04/01/2019 3:11:29 PM	Online Form	02/11/2018 4:34:23 AM	Sales Contact	02/11/2018 10:07:22 AM	Sales Contact	02/11/2018 12:23:45 PM	Financial Clearance	04/01/2019 3:11:29 PM
2	90070	22/12/2018	02/01/2019 4:45:30 AM
3	23451	27/11/2018	14/12/2018 12:44:38 PM	Online Form	28/11/2018 4:33:07 AM	Financial Clearance	28/11/2018 10:13:04 AM

Table 2

Lead ID	Product Code	Sale_Start	Phase 1	Days in phase 1	Phase 2	Days in phase 2	Phase 3	Days in phase 3
1	12405	1/11/2018	Online Form	1	Sales Contact	0	Financial Clearance	63
2	90070	22/12/2018	Application Received	18
3	23451	27/11/2018	Online Form	1	Financial Clearance	0

Can anyone help me with this? Happy to answer any other questions or clarify the problem if required.

Thanks in advance,

Chris

[DavidP]

This doesn't deal with multiple timestamps in the same phase yet, but does calculate the number of days in each phase.

Do you want to test this on a bigger dataset to see if it works ok, then we can look at the rest?

[estherb47]

Hi @logiemeister,

Attached please find my solution. Although there's a tool container to reformat back horizontally, if you've got up to 100 phases, that will be a slow process (need to move manually in the join tool, or add a select tool)

Used a bunch of Multi-row formula tools to help with this. After transposing so you can compare dates to dates, the first Multi-row formula takes care of redundant phases - where something is in the same phase from one capture to the next. Only the last of those will pass through the workflow.

The next Multi-row formula tool accounts for the skipped phases above, and makes all phases number consecutively within each Lead ID.

Finally, the last Multi-row formula tool compares the dates. For all phase 1, comparison is to start date (and today's date will be used if the date field is blank). For all other phases, computed between start of the current phase and stamp of the previous phase.

[DavidP · Accepted solution]

Updated version attached that now aggregates multiple timestamps in the same stage.

[logiemeister]

Thanks so much for the solution - solves my problem in a very elegant way. All three posts would have done the job!

Thanks so much for your help!