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.

2
  • 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, 2020 at 3:24
  • Hi Macca, It certainly does not. Gortonington had a reponse I could use, thank you :) Jun 9, 2020 at 13:05

1 Answer 1

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.

3
  • It could potentially be wrong in a lot of years because of the siderial year (even though you sometimes included it a bit) :D Jun 8, 2020 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. Jun 8, 2020 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. Jun 9, 2020 at 13:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .