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 * Depending on the complexity of the customer journey, there can be up to 100 phases/timestamps. * Each journey is different. The number of columns is defined by the lead with the greatest number of phases. * 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 * Get the data in the format of Table 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 IDProduct CodeSale_StartUPDATE_ONPhase 1Date 1Phase 2Date 2Phase 3Date 3Phase 4Date 411240501/11/201804/01/2019 3:11:29 PMOnline Form02/11/2018 4:34:23 AMSales Contact02/11/2018 10:07:22 AMSales Contact02/11/2018 12:23:45 PMFinancial Clearance04/01/2019 3:11:29 PM29007022/12/201802/01/2019 4:45:30 AM 32345127/11/201814/12/2018 12:44:38 PMOnline Form28/11/2018 4:33:07 AMFinancial Clearance28/11/2018 10:13:04 AM Table 2 Lead IDProduct CodeSale_StartPhase 1Days in phase 1Phase 2Days in phase 2Phase 3Days in phase 31124051/11/2018Online Form1Sales Contact0Financial Clearance6329007022/12/2018Application Received18 32345127/11/2018Online Form1Financial Clearance0 Can anyone help me with this? Happy to answer any other questions or clarify the problem if required. Thanks in advance, Chris Alteryx Community Phase Query.xlsx

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

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

Depending on the complexity of the customer journey, there can be up to 100 phases/timestamps.
Each journey is different. The number of columns is defined by the lead with the greatest number of phases.
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

Get the data in the format of Table 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

Alteryx Community Phase Query.xlsx

Reporting

Date Time

Events

Accepted answers

DavidP

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

time in each phase.yxmd

All comments

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?

time stages.png

time in each phase.yxmd

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.

DaysInPhase.yxmd

DavidP

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

time in each phase.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703