Count id in Data Extension and Upsert to another Data Extension using SSJS

Question

This question might be a basic one but I am very new to SSJS so thank you for your understanding.

The data extension names JourneyA, JourneyB, JourneyC...infinity are the result of Journey Builder. Then, I got the data extension name AllJourneys from _Journey.

SELECT JourneyName as "JourneyName",
FROM _Journey j
INNER JOIN(select JourneyID, max(CreatedDate) as MaxDate FROM _Journey
GROUP BY JourneyID) sort on sort.JourneyID = j.JourneyID and j.CreatedDate = sort.MaxDate

After that, I would like to count the number of audiences in each journey and put the results in data extension name Summary using upsert. Additionally, data extension name Summary is a non-sendable data extension.

According to my understanding, the data extension name Summary can be done by SSJS (Script Activity in Automation Studio), right? or please suggest me if it can be done by any other way.

I have done some SSJS but cannot figure out what I sould do next.

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

  var AllJourneys = DataExtension.Init('AllJourneys');
  var AllJourneysData = testDE.Rows.Retrieve();
  
  var Summary = DataExtension.Init('Summary');

  for (var i = 0; i < testDEData.length; i++) {
      var JourneyName = AllJourneysData[i].JourneyName;
      var count = xxx
      var result = Summary.Rows.Upsert({"JourneyName":JourneyName}, ['Count(CusID)'], [count]);
  }
</script>

Answer 1

I suggest you to use the WSproxy to manage data extension data.

The main problem that you have to bypass is that SSJS cannot retrieve more than 2,500 rows per "call".

This tutorial explains how to retrieve more than 2,500 rows. https://ampscript.xyz/how-tos/how-to-retrieve-more-than-2500-records-from-a-data-extension-with-server-side-javascript/

Then I suggest you to use basic javascript function to check the length or simply manage your returned json to get the desired id count.

You can finally upsert that data with t

Answer 2

If you know the number of Journey DE's, you could also use SQL Query Activities in Automation studio to do this. Create your data extension for the Summary table, and then run a SQL query or series of queries to look up each of the journey DE's and update the table with the updated value.

I would recommend adding one additional field to your summary table for a REFRESHDATE, so that you can see in the data the last time it was updated.