Grouping Records based on a Time Window

Question

Hi all,

I need help grouping data on a consumer level based on if they received communications within a 30 day window, with the 30 days starting from the 1st communication.  It is meant to study how effective sending multiple communications is to a Consumer. The challenge is to create a differentiating group - if the same consumer receives another communication 30 days from the 1st communication. The example raw data looks like this:

customeridcomm_datecomm_type12341/1/2022Email12341/2/2022Direct12341/15/2022Email12342/4/2022Email12342/5/2022Direct12343/10/2022Direct23451/1/2022Email23452/5/2022Email23452/6/2022Direct

The first step is to group together the following records and then have them assigned to a RecordID.

The 2/4/22 Email communication falls +30 days from the very first 1/1/22 Email Communication for Consumer "1234" and is thus, a "new" group for that consumer. Then the 3/10/22 Direct communication is another group because 3/10 is +30 days from 2/4/2022 in the previous group.  I am having a bit of a trouble dynamically creating this min_comm_date based on each group and starting the 30 day window from that point on.

The final dataset I wish to achieve, looks something like this:

customeridmin_comm_datemax_comm_dateno_touchesEmailDirectRecordID12341/1/20221/15/2022321112342/4/20222/5/2022211212343/10/20223/10/2022101323451/1/20221/1/2022110423452/5/20222/6/20222115

If this isn't explain well, please let me know if you have any questions.  Thanks all for your help!

Felipe_Ribeir0 · Accepted Answer

Hi @whitkrieng

One way of doing this

whitkrieng · Accepted Answer

Hi @Felipe_Ribeir0 , I noticed something may have gotten incorrectly grouped.  The result of the workflow mis-grouped some of the records.

customeridcomm_datecomm_typeRecordIDFirstDate_IdealEndDate_IdealRecordID_ResultFirstDateEndDate12347/6/2022Email17/6/20228/4/202217/6/20228/4/202212347/7/2022Direct17/6/20228/4/202217/6/20228/4/202212348/9/2022Email28/9/20229/8/202228/5/20229/3/2022123410/3/2022Email310/3/202211/2/202239/4/202210/3/2022123410/6/2022Direct310/3/202211/2/2022410/4/202211/2/2022123411/8/2022Email411/8/202212/8/2022511/3/202212/2/2022

RecordID is the desired the result, the current output I'm getting is in the "RecordID_Result".  We want to start the "count" again if the next communication is past 30 days from the first initial communication. So since the 10/3/22 communication is +30 days from 8/9/22 Email Communication, we want to group any communication together 30 days from 10/3/22, if that makes sense.  So the 10/3/22 and 10/6/22 communication need to be together.

Hope this makes sense, appreciate your help!