Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

logiemeister
7 - Meteor

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 IDProduct CodeSale_StartUPDATE_ONPhase 1Date 1Phase 2Date 2Phase 3Date 3Phase 4Date 4
11240501/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 PM
29007022/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 3
1124051/11/2018Online Form1Sales Contact0Financial Clearance63
29007022/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

4 REPLIES 4
DavidP
17 - Castor
17 - Castor

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

estherb47
15 - Aurora
15 - Aurora

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
17 - Castor
17 - Castor

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

logiemeister
7 - Meteor

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!

Labels