0

In Marketing Cloud -> Automation Studio -> Script Activity, I am trying to run a script that updates a column named "age" (dtype: integer) based on another column named "date of birth" (dtype: date). It is within a single data extension named "User".

The idea is to loop/iterate through all the subscribers/records, calculate the age of the given subscriber based on the column "date of birth" and then update the column "age" with the respective age. Thus, I want the update the column "age" based on a calculation on another column, "date of birth" (calculation is in the var "aged" within the code snippet). The primary key of the data extension is the SubscriberKey.

I have put together the following code, however it does not work and I feel I am missing something (I am totally new to SSJS).

  <script runat="server">
  Platform.Load('core', '1');

  var testDE = DataExtension.Init("User");
  var testDEData = testDE.Rows.Retrieve();

  for (var i = 0; i < testDEData.length; i++) {
      var subKey = testDEData[i].SubscriberKey;
      var dob = testDEData[i]."date of birth";
      var aged = testDE.FLOOR((TODAY() - "date of birth"__c)/365.2425);

      var processing = testDE.Rows.Update({age:aged}, ["SubscriberKey"], [subKey]);
  }
</script>

Do you have any suggestions? Highly appreciated! Please tell me if you need further information.

  • 1
    Does this have to be done with SSJS? A far simpler, faster and scalable solution would be to use an SQL Query Activity. – Macca Jun 8 at 3:24
  • Hi Macca, It certainly does not. Gortonington had a reponse I could use, thank you :) – Andreas Pedersen Jun 9 at 13:05
0

I honestly would instead recommend using a SQL query. You would run this in your automation instead of the Script Activity. This query would have the same source and target - your 'User' DE. The action for the query would be 'Update'.

Sample query:

SELECT SubscriberKey,
FLOOR(DATEDIFF(day,"date of birth", getdate()) / 365.2425) as age
FROM [User]

/* target: User */
/* action: update */

This is assuming that the field inside the DE containing the date of birth is literally named date of birth. If not, adjust this to the correct field name.

Now if you want to stick with SSJS, you should be able to correct your script like below:

  <script runat="server">
  Platform.Load('core', '1');

  var testDE = DataExtension.Init("User");
  var testDEData = testDE.Rows.Retrieve(); 
  //this will only pull 2500 rows max though

  for (var i = 0; i < testDEData.length; i++) {
      var subKey = testDEData[i].SubscriberKey;
      var dob = testDEData[i][date of birth];
      var today = new Date();
      var diffTime = today - dob; //returns difference in milliseconds
      var diffDays = diffTime / (1000 * 3600 * 24); //returns difference in days
      var age = Math.floor(diffDays / 365.2425);  //returns year difference, rounded down

      var processing = testDE.Rows.Update({"SubscriberKey":subkey}, ["Age"], [age]);
  }
</script>

The SQL will likely be more performant and does not have the max payload restriction that the script will. If you still want to stick with SSJS, I would look at using WSProxy to utilize the ContinueRequest feature, plus it will allow you to do bulk retrieve and uploads. As well, WSProxy is likely to be more performant than the Core functions.

| improve this answer | |
  • It could potentially be wrong in a lot of years because of the siderial year (even though you sometimes included it a bit) :D – Johannes Schapdick Jun 8 at 14:22
  • This is only a basic example, if there are other considerations or business rules that need to be added in to ensure accuracy, that would need to be handled prior to implementation. Accounting for sidereal year and 'leap years' requires a bit more complex math which could bog down a query/script for little return if not needed. – Gortonington Jun 8 at 15:35
  • 1
    Hi guys, It certainly does not need to SSJS. Your query (and script) works very well, however for sake of simplicity I am going on with the query - thank you!! I've given you an upvote and this thread is thus resolved. Thanks once again. – Andreas Pedersen Jun 9 at 13:06

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.