374186_en-US

liliconcom

day difference in the same column of different scenarios

Hi, I have questions for different scenarios as below. Really appreciate any help!

Scenario A: I want to add this last column, calculating for that userID, what's the day difference from first web to first booking date. If he had no web action prior to booking action, then leave it empty. Could someone help me with what the formula should look like using the function but not jsl?

userID	action	date	first web date to first booking date
2523	web	2018-05-06	22
2523	web	2018-05-16	22
2523	booking	2018-05-28	22
2523	booking	2018-06-08	22
2523	web	2018-07-08	22
2523	booking	2018-08-08	22

Scenario B: based on the above table, I want to calculate another table like the below that contains for each userID, day difference between 'the first web action date' 'before bookings' to each booking date. Based on the logic, the ideal result with the above table as input will be as below, 3 instances of day difference for this userID. Same as in scenario A, we only consider when web is prior to booking.

userID	instance	calculation
2523	22	5/28 minus 5/6
2523	33	6/8 minus 5/6
2523	31	8/8 minus 7/8

Thank you!

AnalyticsData TableRe: day difference in the same column of different scenariosHi Joann,
Couple of questions to see if a simple solution could be developed:
1) Would you happen to have a column that would differentiate between the first sequence (rows 1 - 4) and the second sequence (rows 4 - 6); without this, it is a bit more challenging to create column formulas that would distinguishing the 2 set of transactions.
2) If I understand correctly, the second booking date (row 4) is not relevant to your question.
Let us know at your earliest convenience.
Best,
TSRe: day difference in the same column of different scenarios

Hi Joann,

 

After playing around a bit, I was able to produce a set of column formulas that produce the desired output (see attached). Importantly, the table has to be sorted by "userId" and "date" for the formulas to work properly.

Best,

TS

Re: day difference in the same column of different scenarios

I think the following formula will do for scenario A:

It is not yet optimized, but may show you how things like this can be done.
It uses some simple jsl in a formula, step by step.

 

Please see full script for table generation:

New Table( "20210404_user_web_booking_matrixexample",
 Add Rows( 12 ),
 Compress File When Saved( 1 ),
 New Column( "userID",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values(
   [2523, 2523, 2523, 2523, 2523, 2523, 2540, 2540, 2540, 2540, 2540, 2540]
  ),
  Set Display Width( 66 )
 ),
 New Column( "action",
  Character( 16 ),
  "Nominal",
  Set Values(
   {"web", "web", "booking", "booking", "web", "booking", "web", "web",
   "booking", "booking", "web", "booking"}
  ),
  Set Display Width( 73 )
 ),
 New Column( "date",
  Numeric,
  "Continuous",
  Format( "y/m/d", 12 ),
  Input Format( "y/m/d" ),
  Set Values(
   [3608409600, 3609273600, 3610310400, 3611260800, 3613852800, 3616531200,
   3647462400, 3647548800, 3647635200, 3647721600, 3647808000, 3647894400]
  ),
  Set Display Width( 92 )
 ),
 New Column( "first web date to first booking date",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [22, 22, 22, 22, 22, 22, ., ., ., ., ., .] ),
  Set Display Width( 150 )
 ),
 New Column( "formula",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Formula(
   Names Default To Here( 1 );
   dat_vec = :date << get values;
   web_vec = :action << get values;
   user_vec = :userID << get values;
   user_index = user_vec == :userID[Row()];
   web_index = Matrix(
    Substitute( :action << get values, "web", 1, "booking", 0 )
   );
   web_dat = dat_vec[Loc( user_index & web_index )];
   booking_dat = dat_vec[Loc( user_index & !web_index )];
   (Minimum( booking_dat ) - Minimum( web_dat )) / (3600 * 24);
  ),
  Set Selected
 )
)