Group by and send email to Dept. head | Community
Skip to main content
D
DattaSutar
New Participant
October 3, 2025
Question

Group by and send email to Dept. head

  • October 3, 2025
  • 1 reply
  • 245 views

Hi Team

We have a below use case, wanted to get your view about how best this can be achieved in Adobe Campaign Classic workflow please:

 

1. We get Segment Data (1 file) from source containing: studentEmail, studentLibEnrolStatus, studentDepartment, studentDepartmentHead, studentDepartmentEmail

2. If students have enrolled to library (studentLibEnrolStatus), we need to send them welcome email

3. If students have NOT enrolled to library (studentLibEnrolStatus), we need to 'conslidate' all such students and send notification email to the DepartMentHead; it has to be 1 email mentioning all those students whose enrollment is still pending.

4. Segment files flows throughout the day, every 1 hour and we need to send this consilidated email at the end of the every day.

5. We could start with query activity, but need guidance about possiblity of above use case to send a 'consolidated' email, at the end of the day. 

 

 

1 reply

SushantTrimukheD
SushantTrimukheD
New Participant
October 6, 2025

@dattasutar,

To achieve this use case by designing two interconnected workflows: an hourly one to process incoming segment files and handle immediate welcome emails for enrolled students, while accumulating non-enrolled student data in a persistent temporary table grouped by department or List. A separate daily workflow then aggregates this accumulated data, generates a consolidated notification email listing pending students per department, and sends it to the respective department heads at day's end.

Hourly Workflow Design - Start the hourly workflow with an scheduler activity then followed by file collect, data loading activity to load the segment data file into the workflow's working table, mapping fields like studentEmail, studentLibEnrolStatus, studentDepartment, studentDepartmentHead, and studentDepartmentEmail.
Follow with a Use a Split activity to branch based on studentLibEnrolStatus: one path for enrolled students (status = 'enrolled' or true) and another for non-enrolled.

Filter and validate the data if needed, ensuring no duplicates by checking against an existing recipient schema or using a unique key like studentEmail.
On the enrolled path, connect to a Delivery activity configured as an email template for the welcome message, personalizing it with studentEmail and other relevant fields like studentDepartment. Ensure the delivery targets the working table's enrolled subset, proving and sending immediately to comply with the hourly processing requirement.

For the non-enrolled path, use an Enrichment activity to add a processing timestamp (e.g., current date) and link to a custom temporary schema or list (create via Data Schemas if needed, with fields for student details and department grouping). Insert this data into the temporary table using a  Update Data activity, inserting by studentEmail to handle potential duplicates across hourly files.

Daily Consolidation Workflow

Schedule a second workflow to run once daily using a Scheduler activity set to "Daily" mode with end-of-day timing. Begin with a Query activity pulling from the temporary table, filtering for today's date and grouping by studentDepartment to aggregate pending students. Use an Aggregation activity to summarize per department, collecting lists of studentEmail, studentDepartmentHead, and studentDepartmentEmail into a single output per group.

Generating and Sending Consolidated Emails

In the daily workflow, you can use a Query Activity with a GroupBy function on studentDepartment to retrieve the required data.
Alternatively, you can use a JavaScript Activity to query the custom schema we’ve built, perform the grouping within the script, and generate the HTML email content for each department.

The script provided below is an example. You can adapt it to fit your schema and test it to ensure it works as expected.

 

var queryTemp = xtk.queryDef.create( <queryDef schema="custom:NonEnrolledTemp" operation="select"> <select> <node expr="@studentEmail"/> <node expr="@studentDepartment"/> <node expr="@studentDepartmentHead"/> <node expr="@studentDepartmentEmail"/> <node expr="@processingDate"/> </select> <where> <condition expr="@studentLibEnrolStatus != 'enrolled'"/> <condition expr="Year(@processingDate) = Year(Now()) and Month(@processingDate) = Month(Now()) and Day(@processingDate) = Day(Now())"/> </where> <orderBy> <node expr="@studentDepartment"/> <node expr="@studentEmail"/> </orderBy> </queryDef> ); var res = queryTemp.ExecuteQuery(); var html = 'Hi Department Heads,<br><br>'; if (res == '') { html += 'No students have pending library enrollments today.<br><br>'; } else { if (res.length() > 0) { html += 'Below is the consolidated list of students whose library enrollment is still pending, grouped by department:<br><br>'; // Group by department using a JavaScript object var depts = new Object(); for each (var record in res) { var dept = record.@studentDepartment; var head = record.@studentDepartmentHead; var headEmail = record.@studentDepartmentEmail; var email = record.@studentEmail; if (!depts[dept]) { depts[dept] = { head: head, headEmail: headEmail, students: new Array() }; } // Check for duplicates without indexOf if needed; simple loop alternative: var isDuplicate = false; for (var i = 0; i < depts[dept].students.length; i++) { if (depts[dept].students[i] == email) { isDuplicate = true; break; } } if (!isDuplicate) { depts[dept].students.push(email); } } // Build HTML sections per department using for...in for (var dept in depts) { html += '<h3>Department: ' + dept + '</h3>'; html += '<p>Department Head: ' + depts[dept].head + ' (' + depts[dept].headEmail + ')</p>'; html += '<TABLE CELLPADDING="0" CELLSPACING="0" BORDER="1" WIDTH="700"><TR bgcolor="#C0C0C0"><TH WIDTH="700">Pending Student Emails</TH></TR>'; for (var j = 0; j < depts[dept].students.length; j++) { html += '<TR style="text-align:left"><TD WIDTH="700">' + depts[dept].students[j] + '</TD></TR>'; } html += '</TABLE><br><br>'; } } } html += 'Kind regards<br>Adobe Campaign Team'; vars.html = html;


Thanks
Sushant Trimukhe

    Terms & ConditionsAccessibility statement

    Loading footer...