Customer next step

[manideep547]

Hi all,
My data look like below 

 CustomerId         DATE                        START                                                           END                    JOURNEY_STATE
   1111                  2020-01-02             2020-01-02 22:16:23.000            2020-01-02 22:19:02.000        Arrival
   1111                  2020-01-02              2020-01-02 22:19:03.000           2020-01-02 22:23:17.000        Enquiry
    2222                  2020-01-02             2020-01-02 15:48:21.000            2020-01-02 15:49:09.000         Arrival
    2222                  2020-01-02            2020-01-02 15:49:10.000               2020-01-02 15:52:28.000       Navigation1

Count of each unique customer who is in the "Arrival" step and reached to "Navigation1"
 The count should be based on a two-point relationship that showcases the customers who convert from "Arrival" to "Navigation1" as a direct link/flow my data set is very large its nearly 2CR 

the count I need is =1

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example and better data

Appreciate your Kudos.

[manideep547]

@amitchandak  Here 2222 customer  from Arrival  (2020-01-02 15:49:09.000  session end previous record ) directly converted to  in Navigation1(2020-01-02 15:49:10.000 ,session start next record)
count number of customers converted from arrival to Navigation1 =1

Thanks for your reply 

[manideep547]

@amitchandak  My data
CustomerId   DATE                        START                                             END                   JOURNEY_STATE
 1111             2020-01-02      2020-01-02 22:16:23.000    2020-01-02 22:19:02.000     Arrival
 1111             2020-01-02      2020-01-02 22:19:03.000      2020-01-02 22:23:17.000      Enquiry
  2222            2020-01-02     2020-01-02 15:48:21.000       2020-01-02 15:49:09.000      Arrival
  2222            2020-01-02      2020-01-02 15:49:10.000       2020-01-02 15:52:28.000     Navigation1

[amitchandak]

@manideep547 

I create a new column like :

Is nav = if(ISBLANK( MiNX(FILTER(journey,journey[START]>EARLIER(journey[end]) && journey[CustomerId]=EARLIER(journey[CustomerId]) && journey[JOURNEY_STATE] = "Navigation1"),journey[START])),"No","Yes")

You can modify as per need

File is attached below signature . if you need more help make me @

Appreciate your Kudos.

[manideep547]

@amitchandak 

I think we should not use the journey,  [START]>EARLIER(journey[end]) see the above fig.
co column, define the next step of the customer.  
In the above data, the customer started his journey at arrival 2:10:07 Pm and next he went to Enquiry 10:55:16 PM in that column co the value should be Enquiry I am not getting which constraint we have to add to get that values. 

[manideep547]

@amitchandak  Here customer Id is repeating so we can't us MAX &MIN function to get next values in co column 

In SQL we can achieve this by using a lag function it gives the next values of the same customer. 
If we use MAX then, we have to compare only two rows of the customer.

[amitchandak]

@manideep547 , Can you share this data with the expected output? Let me check again

[manideep547]

@amitchandak 
values in co column are like below   
1]Enquiry
2]Departure
3]Navigation2
4]Navigation2
5]Navigation2
6]Navigation2
7]Navigation2
8]Navigation2
9]Navigation2
10]Navigation2
11]Navigation2
12]Departure
13]Navigation2