I have the following challenge.

The best thing you can do is look at the photo so you can understand it better. I want to dynamically display in Google Spreadsheet a distinction between booking appointments.

The following appointments exist: = New Sales & Winback.

1. New Sales = It is a new appointment, if there was none before, so there is no "lead_id" or if there is already the "lead_id", the appointment must be longer than 30 days in the past.
2. Winback = If the same "lead_id" is booked again within 30 days.

So I want if "lead_id" exists, then the last date from the "lead_id" -> "oppt_booked_on" minus the current date "oppt_booked_on".

I have tried a lot, vlookp, match function etc. But I just can't manage to write a dynamic formula. It is also important that always the last booking is used, like in the example (photo). I hope someone can help me. Thanks a lot in advance.

CodePudding user response：

Not sure if this is entirely what you're looking for, but the following formula:

=datedif(index(sort(filter(F:F,A:A=A2)),match(F2,sort(filter(F:F,A:A=A2)),0)-1),F2,"d")

in the 'Days between_booked on' column (your column J) will return the number of days between the row's date, and the date of the previous instance of the same lead_id (column A).

Thereafter, the following formula:

=if(and(countif(A:A,A2)>1,isbetween(J2,1,30)),"Winback","New Sales")

in the 'Outcome' (your column I) will return "Winback" if there are more than 1 instances of the lead_id of the current row, and the duration between the current row and the previous instance of the same lead_id is less than or equal to 30 days.

CodePudding user response：

Not sure if it makes sense, but here is the script:

function main() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getActiveSheet();
  const range = sh.getDataRange();
  const [header, ...rows] = range.getValues();

  // get indexes of the columns
  const col = {};
  col.id      = header.indexOf('lead_id')
  col.outcome = header.indexOf('Outcome');
  col.date    = header.indexOf('oppt_booked_on');
  col.days    = header.indexOf('Days Between booked_on');

  
  // create the object 'data' from the rows
    
  const data = {};
  for (let row in rows) {
    let id = rows[row][col.id];
  
    try {
      data[id].dates.push(rows[row][col.date]);
    }
    catch(e) {
      data[id] = {
        outcome: 'New Sales',
        days: '',
        dates: [rows[row][col.date]],
      }
    }
  }
  
  // change the rows
  
  for (let row in rows) {
    let id = rows[row][col.id];
    let obj = data[id];

    rows[row][col.outcome] = obj.outcome;
    rows[row][col.days] = obj.days;

    if (obj.dates.length > 1) {
      obj.days = days_between(obj.dates.shift(), obj.dates[0]);
      obj.outcome = (obj.days < 30) ? 'Windback' : 'New Sales';
    }
  }

  // set the columns 'Outcome' and 'Days...' back on the sheet

  const outcome = rows.map(x => [x[col.outcome]]);
  sh.getRange(2, col.outcome 1, outcome.length, 1).setValues(outcome);

  const days = rows.map(x => [x[col.days]]);
  sh.getRange(2, col.days 1, days.length, 1).setValues(days);

}


// function returns number of days between two dates

function days_between(date1, date2) {
  let msec = Math.abs(date1.getTime() - date2.getTime());
  let days = Math.floor(msec/(1000*60*60*24));
  return days;
}

Feel free to modify it for your spreadsheet.