Analytics Query columns in Email Templates - body

[IAM_99]

Hi Team,

We have below analytics query

select users.userkey,users.firstname,users.lastname,users.email,users.username,account.lastlogondate from xxxxxx

We want to show to show  firstname,lastname,lastlogon ( sql results)  in email body and 

tried below but email is not generating

${users?.firstname} ${users?.lastname}    ${account.lastlogondate}

${users.firstname} ${users.lastname} ${account.lastlogondate}

[rushikeshvartak]

Refer Example 3 https://saviynt.freshdesk.com/support/solutions/articles/43000622153-managing-email-templates

[sk]

Update query as below 

select users.userkey,users.firstname as firstname ,users.lastname as lastname,users.email,users.username,account.lastlogondate as lastlogondate from xxxxxx

And you can use below parameters

ANALYTICSDATA.firstname

ANALYTICSDATA.lastname

ANALYTICSDATA.lastlogondate 

Refer link for more details: https://docs.saviyntcloud.com/bundle/SSM-Admin-v55x/page/Content/Chapter06-Configuring-SSM/Creating-...

[IAM_99]

Thanks for the response.  I am adding adding data format in the Query it works fine in Data analyzer.

but when we copy same in Analytics and run it says "Alert Fail" , Can we not use this below ?

DATE_FORMAT(a.lastlogondate, '%d %b %Y')

[rushikeshvartak]

DATE_FORMAT(a.lastlogondate, '%d %b %Y') as lastlogondat

[sk]

If I understand correctly if you run the query through analytics you are getting output but email is not triggering?

[IAM_99]

yes getting below , Query is this 

select users.userkey,users.firstname,users.lastname,users.email,users.username,
a.lastlogondate as "userlastlogondate", DATE_FORMAT(a.lastlogondate, '%d %b %Y') as "formateddate",

${ANALYTICSDATA.formateddate[0]} in email template

[rushikeshvartak · Accepted solution]

select users.userkey,users.firstname,users.lastname,users.email,users.username,
a.lastlogondate as "userlastlogondat", DATE_FORMAT(a.lastlogondat, '%d %b %Y') as "formateddat" from users 

column name should not ends with date keyword

${ANALYTICSDATA.formateddat[0]} in email template

[sk · Accepted solution]

Just remove the alias and try it should work. It worked when i tried the same.

If you need alias I would suggest not use date at then end of the name, you can try something like DATE_FORMATED, Same worked for me

[IAM_99]

Thanks Rushi and SK yeah it worked.

 i have one last question on this topic -  Can we send email (analytics email) with out attachment ?

[sk]

No not possible