Query for number of SMS and Direct mail messages sent | Community
Skip to main content
N
Neeharika_Shetty
Level 2
May 2, 2024
Solved

Query for number of SMS and Direct mail messages sent

  • May 2, 2024
  • 2 replies
  • 639 views

Hi all,

 

Can anyone please help me with the query for the number of SMS and Direct Mail messages sent for a month?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by IvanMi1

You can do this as a query as stated below, but more easily, couldn't you just use the built-in channel reports? With the added benefit of being able to export these reports. 

 

 

2 replies

Mohan_Dugganab
Adobe Employee
Mohan_DugganabAdobe Employee
Adobe Employee
May 2, 2024

You can query it on the following lines 

SELECT DATE_FORMAT(timestamp, 'yyyy-MM') AS rolluptimestamp, SUM( CASE WHEN _experience.customerjourneymanagement.messagedeliveryfeedback.feedbackstatus = 'sent' THEN 1 ELSE 0 END) AS deliveredcount FROM cjm_message_feedback_event_dataset WHERE _experience.customerjourneymanagement.messageprofile.channel._id = 'https://ns.adobe.com/xdm/channels/sms' GROUP BY DATE_FORMAT(timestamp, 'yyyy-MM') ORDER BY rolluptimestamp desc; Alter the channel Id accordingly for direct mail as https://ns.adobe.com/xdm/channels/direct-mail

 Reference - https://experienceleague.adobe.com/en/docs/journey-optimizer/using/data-management/datasets/datasets-query-examples 

    I
    Adobe Employee
    IvanMi1Adobe EmployeeAccepted solution
    Adobe Employee
    May 2, 2024

    You can do this as a query as stated below, but more easily, couldn't you just use the built-in channel reports? With the added benefit of being able to export these reports. 

     

     

      Terms & ConditionsAccessibility statement

      Loading footer...