You are here:
Message Content and Keyword Insight Sample Queries
Use these queries to better understand the messages and keywords that your customers send through the WhatsApp channel. For example, you can identify the keywords that occur most frequently, or the keywords that most often result in transfers.
| Available in: Lightning Experience |
Available in:
|
Transfer Prompt
This query groups information by the keyword or message phrase sent before transfer. Information returned includes the transfer count, keyword or message phrase, journey name, and associated channel or number. You can filter using a specified time period.
SELECT
ssot__MessageText__c AS keyword
FROM
ssot__MessageEngagement__dlm
WHERE
ssot__EngagementChannelActionId__c = 'ROUTE' AND
ssot__EngagementNotesTxt__c IN ('SERVICE_CLOUD','DIGITAL_MARKETING') AND
ssot__EngagementDateTm__c BETWEEN from_iso8601_timestamp('START_DATE') AND
from_iso8601_timestamp('END_DATE')
GROUP BY
ssot__MessageText__cTransfer Prompt by Frequency
This query groups information by the keyword or message phrase sent before transfer. The results appear in order of the number of times that the keyword or phrase occurred. Information returned includes the transfer count, keyword or message phrase, journey name, and associated channel or number. You can filter using a specified time period.
SELECT
ssot__MessageText__c as keyword,
count(*) as count
FROM
ssot__MessageEngagement__dlm
WHERE ssot__EngagementChannelActionId__c = 'TRANSFER' AND
ssot__EngagementNotesTxt__c IN ('SERVICE_CLOUD', 'DIGITAL_MARKETING') AND
ssot__EngagementDateTm__c BETWEEN from_iso8601_timestamp('START_DATE') AND
from_iso8601_timestamp('END_DATE')
GROUP BY
ssot__MessageText__cTransfer Prompt by Journey and Activity
This query groups information by journey or activity. Information returned includes the transfer count, keyword or message phrase, journey name, and associated channel or number. You can filter using a specified time period.
SELECT
ssot__MessageText__c as keyword,
count(*) as count
FROM
ssot__MessageEngagement__dlm
WHERE
ssot__EngagementChannelActionId__c = 'TRANSFER' AND
ssot__EngagementNotesTxt__c IN ('SERVICE_CLOUD', 'DIGITAL_MARKETING') AND
ssot__EngagementDateTm__c BETWEEN from_iso8601_timestamp('START_DATE') AND
from_iso8601_timestamp('END_DATE')
GROUP BY
ssot__MessageText__cAll Transfer Prompts
This query orders the results by the number of times that the keyword or phrase has occurred. The information returned includes the keyword or message phrase, journey name, associated channel or number, and event timestamp. You can filter using a specified time period.
SELECT
ssot__MessageText__c as keyword,
ssot__EngagementDateTm__c,
ssot__Id__c
FROM
ssot__MessageEngagement__dlm
WHERE
ssot__EngagementChannelActionId__c = 'TRANSFER' AND
ssot__EngagementNotesTxt__c IN ('SERVICE_CLOUD', 'DIGITAL_MARKETING') AND
ssot__EngagementDateTm__c BETWEEN from_iso8601_timestamp('START_DATE') AND
from_iso8601_timestamp('END_DATE')All Opt-In, Opt-Out, and Help Requests
This query orders the results by the number of times that the keyword or phrase has occurred. The information returned includes the keyword, journey name, associated channel or number, and event timestamp. You can filter using a specified time period.
SELECT
ssot__EngagementChannelActionId__c AS eventType,
COUNT(*) AS totalRequest
FROM
ssot__MessageEngagement__dlm
WHERE
ssot__EngagementChannelActionId__c IN ('SUBSCRIBE', 'HELP', 'UNSUBSCRIBE') AND
ssot__EngagementDateTm__c BETWEEN from_iso8601_timestamp('START_DATE') AND
from_iso8601_timestamp('END_DATE')
GROUP BY
ssot__EngagementChannelActionId__c