Hi,
I’m hoping someone could help me automate my query a little more.
I have a Customer File that I’m matching to a Mail File to get activations counts. Currently I’m just using the address as a match.
The customer file has a date of when the activation happened and needs to fall within two months of the mail date of the mail file. So Customer File has an “Activation Date” and the Mail File has a “Mail Date”, so if it’s an address match and the Activation Date is within a 2 month window of the Mail Date, it’s an Activation.
I’m currently accomplishing this by putting a two-month filter on the Customer File before doing the match. This way I know I’m only looking at qualifying records. The problem with this approach is that I can only do one mail file at a time since all my mail files have different mail dates.
Ideally, I would like to do this match against multiple mail files at once. So my input might have 10 different mail dates and I’ll dynamically match on address and make sure the Activation date is also within a 2 month window of the mail date.
There is one more thing to make this a little bit more interesting.
If a record matches across two mail files, the attribution needs to be assigned to the mail file with the newest mail date (Last touch). Example. If Mail File A has a mail date of 1/1/23 and Mail File B has a mail date of 1/15/23 and a record in the Customer file has an activation date of 2/1/23 and matching address to both Mail files then it should only match to Mail File B and not A, since B had the Last Touch.
Before I did this as a separate step, but if I’m going to combine the Mail files, I’ll need to check for this condition also.
Ideally a record that does have multiple mail file matches would be flagged with all the matches and have a field that has the “Priority” match.
I’m not sure how to automate this besides splitting the Mail files as I have.
Any help appreciated,
Thanks.
