Delegation Reminder Notification Through Analytics

[Padmavathi]

Hi Team,

We have a requirement of sending email notification to the Parent user and Delegate when the delegation end date is approaching.

We have built a query that will provide username, endday, parentuser, delegateuser.

we are facing below issues:

1. When we run the analytics, able to get the email with the analytics report as attachment.  But if we place any binding variable in the email template like below, unable to receive any email.

<p> ${ANALYTICSDATA.'EndDay'[0]}</p>
<p> ${ANALYTICSDATA.'ParentUser'[0]} </p>
<p> ${ANALYTICSDATA.'DelegateUser'[0]} </p>

2. If we place only ${ANALYTICSDATA} then email template is triggering with attachment but there is no data in email body.

3. If I use ${userEmail} in 'To address' then also no email is triggering

Can anyone please help us to get the functionality.

Thanks in advance!

Regards,

Padmavathi

[rushikeshvartak]

• Share query
• report configuration 
• email configuration screenshot 

[Padmavathi]

Hi @rushikeshvartak 

Thanks for the quick response!

Please find the details below:

Email Template:

Query:

SELECT
u1.username,
DATE_FORMAT(d.enddate, '%b %d, %Y') as 'EndDay',
(SELECT u1.username FROM users u1 WHERE d.userkey = u1.userkey) AS 'ParentUser',
(SELECT u2.username FROM users u2 WHERE d.delegateuserkey = u2.userkey) AS 'DelegateUser'
FROM
delegates d
INNER JOIN
users u1 ON u1.userkey = d.userkey
WHERE
u1.statuskey = 1
AND DATE(d.enddate) = DATE(DATE_ADD(CURDATE(), INTERVAL 23 DAY))

Report:

Report is generated for the attached email template under analytics with the below columns

Regards,

Padmavathi

[rushikeshvartak]

Report configs?

[Padmavathi]

Hi @rushikeshvartak 

Can you please help which one you are referring for report config?

Also, able to get email when we I used the same query in test environment.

But Need clarification on the email notification behavior when a new delegation is created. Here’s a summary of the current behavior:

For Parent User PU1:
When DU1 is added: An email is sent to PU1.
When DU2 is added: Emails are sent to PU1 and DU1.
When DU3 is added: Emails are sent to PU1, DU1, and DU2.

For Parent User PU2:
When DUU1 is added: An email is sent to PU2.
Additionally, another email is sent to PU1, DU1, DU2, and DU3 (email trigger is fine but DU3 was also added along with addresses PU1, DU1, and DU2. without creating a new delegate for PU1).

Could you please help me the process is correct, or this is a bug?

Regards,

Padmavathi

[Padmavathi · Accepted solution]

Hi @rushikeshvartak 

We understood the root cause, the email behavior is due to same start date for all the delegates under parent user.

Thanks,

Padmavathi