0

I have a data extension that holds customer vouchers. I have different types of vouchers in that DE: WEL (welcome), BDY (Birthday)

Because Birthday is a reoccurring event, i reissue Birthday vouchers once a year, and I want to be able to hold only 1 voucher of a specific type in my Vouchers_DE data extension.

So, I have been trying to create an automation in automation studio

What I am trying to achieve:

1) Move vouchers that expired more than 3 months ago into Archive_DE

I am using simple SQL for that :

SELECT *
FROM Vouchers_DE
WHERE ( VOUCHER_TYPE = 'BDY' and VOUCHER_EXPIRATION_DATE < DATEADD(month, -3, getdate()) )

And there seem to be no issues at this step

2) Remove BDY vouchers that expired more than 3 months ago from my Vouchers_DE using a script, but I seem to have an issue here, as i am not sure how to define the "more than 3 months" condition :(

a)

<script runat="server">
var rows = Platform.Function.DeleteData('Vouchers_DE',['VOUCHER_TYPE', 'VOUCHER_EXPIRATION_DATE'],['BDY', '???']);
</script>

If i use a specific date as column 2 value the above script works, but because vouchers are issued every day this cant be the solution.

b) Tried the below:

var dataRows = LookupRows(‘Vouchers_DE’,‘VOUCHER_TYPE’,‘BDY’)

var threeMonthsBefore = DateAdd(Now(), -3, “M”));

if(dataRows && dataRows.length > 0 ) {
         for(var i=0; i<dataRows.length; i++) {
             if (dataRows[i][“VOUCHER_EXPIRATION_DATE”] < threeMonthsBefore) {

               var CUSTMER_ID = dataRows[i][“CUSTOMER_ID”];
               Platform.Function.DeleteData(‘Vouchers_DE’,[‘CUSTOMER_ID’],[CUSTOMER_ID]);
             }
         }
    }

But keep getting this error message:

"SSJS Activity Script failed validation!, Errors: Object reference not set to an instance of an object."

Any suggestions on how this can be achieved? Thank you!

  • 1
    For the b) answer, all quotes are wrong. Use ' or " but not those you ‘ ’ “ or those ” – Johannes Schapdick May 6 at 10:48
  • @JohannesSchapdick thank you! edited this, also spotted there was an extra closing bracket, still does not seem to delete the records – Kat May 6 at 12:44
0

I think possibly it might be that you just have a typo: var CUSTMER_ID

As an alternative solution, you could use DE data retention settings to automatically delete that data. For this to work if you only want to remove expired BDY vouchers, you would need separate DEs for your voucher types.

  • Thank you. The typo was also fixed earlier and separating vouchers by type is unfortunately not an option :( – Kat May 7 at 1:21
0

Managed to solve this, but in 3 steps instead of 2:

Step 1: (Move Expired Vouchers to Archive)

    SELECT *
    FROM Vouchers_DE
    WHERE ( VOUCHER_TYPE = 'BDY' and VOUCHER_EXPIRATION_DATE < DATEADD(month, -3, getdate()) )

Step 2 (Mark Expired Vouchers as Expired=True):

    SELECT
    CUSTOMER_ID,VOUCHER_TYPE,
    CASE WHEN ( VOUCHER_TYPE = 'BDY' and VOUCHER_EXPIRATION_DATE < DATEADD(month, -3, getdate()) )
    THEN
    1
    ELSE
    0
    END as EXPIRED
    FROM
    Vouchers_DE

Step 3 (Remove Expired Vouchers from Vouchers_DE):

    <script runat="server">
    var rows = Platform.Function.DeleteData('Vouchers_DE',['VOUCHER_TYPE', 'EXPIRED'],['BDY', 'True']);
    </script>`

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.