SMS Message Stats
Download SQL file: SMSMessageStatsSMSMessageStatsSnow.sql
The example below queries the SMSMessageFact table for the number of unique SMS users
Code Block |
---|
language | sql |
---|
title | SMS Unique User Count |
---|
collapse | true |
---|
|
-- SMS Message Fact table
-- How many users are using SMS?
SELECT COUNT( distinct T.userId )
FROM (SELECT
smf_sendingUserId AS userId
FROM
[dbo].[SMSMessageFact] WITH( NOLOCK )
WHERE
FROM
SMSMessageFact
WHERE
smf_messageDirection = 'OUTBOUND' AND smf_sendingUserId is not NULL
UNION
SELECT
smf_receivingUserId AS AS userId
FROM
[dbo].[SMSMessageFact] WITH( NOLOCK )
WHERE
FROM
SMSMessageFact
WHERE
smf_messageDirection = 'INBOUND' AND smf_receivingUserId is not NULL) AS T
|
The example below queries the SMSMessageFact table for a list of unique SMS users
Code Block |
---|
language | sql |
---|
title | SMS Unique User List |
---|
collapse | true |
---|
|
-- List of users that are using SMS
SELECT distinct T.userId
FROM (SELECT
smf_sendingUserId AS userId
FROM
[dbo].[SMSMessageFact] WITH( NOLOCK )
WHERE
FROM
SMSMessageFact
WHERE
smf_messageDirection = 'OUTBOUND' AND smf_sendingUserId is not NULL
UNION
SELECT
smf_receivingUserId AS AS userId
FROM
[dbo].[SMSMessageFact] WITH( NOLOCK )
WHERE
FROM
SMSMessageFact
WHERE
smf_messageDirection = 'INBOUND' AND smf_receivingUserId is not NULL) AS T
ORDER BY userId |
...
Code Block |
---|
language | sql |
---|
title | SMS Detailed Usage |
---|
collapse | true |
---|
|
-- Usage report
-- date sent, direction, SMS number, SMS UserId, other party
SELECT
CONVERT(varchar, TO_VARCHAR(smf_originationTimestamp::timestamp, 120'yyyy-mm-dd hh:mi:ss') AS ["Date Sent]", smf_messageDirection AS ["Direction]",
snd_number AS ["SMS Number]",
CASE WHEN smf_messageDirection = 'INBOUND' THEN smf_receivingUserId ELSE smf_sendingUserId END AS ["SMS UserId]",
CASE WHEN smf_messageDirection = 'INBOUND' THEN smf_fromContactNumber ELSE smf_toContactNumber END AS ["Other Party]"
FROM
[dbo].[SMSMessageFact] LEFT OUTER JOIN [dbo].[SMSNumberDimension] WITH( NOLOCK ) ON CASE WHEN smf_messageDirection = 'INBOUND' THEN smf_toNumberFk ELSE smf_fromNumberFk END = snd_id
WHERE
smf_originationDateLocal >= 20210101 AND smf_originationDateLocal < 20220101
ORDER BY smf_originationTimestamp; |
...
SMS Contact Stats
Download SQL file: SMSContactsStatsSMSContactsStatsSnow.sql
The example below queries the SMSMessageFact for a count of unique SMS Contacts
Code Block |
---|
language | sql |
---|
title | SMS Unique Contacts |
---|
collapse | true |
---|
|
-- How many unique contacts (external phone numbers) have been texted?
SELECT COUNT(DISTINCT contact) AS ["Contact Count]"
FROM (SELECT
smf_toNumber AS contact
FROM
[dbo].[SMSMessageFact] WITH( NOLOCK )
WHERE
FROM
SMSMessageFact
WHERE
smf_messageDirection = 'OUTBOUND' AND smf_sendingUserId is not NULL
UNION
SELECT
smf_fromNumber AS contact
FROM
[dbo].[SMSMessageFact] WITH( NOLOCK )
WHERE
FROM
SMSMessageFact
WHERE
smf_messageDirection = 'INBOUND' AND smf_receivingUserId is not NULL) AS T; |
...
Code Block |
---|
language | sql |
---|
title | SMS Unique Contact List |
---|
collapse | true |
---|
|
-- List of unique contacts that have been texted
SELECT DISTINCT contact AS [Contacts]
FROM (SELECT
smf_toNumber AS contact
FROM
[dbo].[SMSMessageFact] WITH( NOLOCK )
WHERE
FROM
SMSMessageFact
WHERE
smf_messageDirection = 'OUTBOUND' AND smf_sendingUserId is not NULL
UNION
SELECT
smf_fromNumber AS contact
FROM
[dbo].[SMSMessageFact] WITH( NOLOCK )
WHERE
FROM
SMSMessageFact
WHERE
smf_messageDirection = 'INBOUND' AND smf_receivingUserId is not NULL) AS T
ORDER BY contact; |
...
SMS Stop Request Stats
Download SQL file: SMSStopRequestStatsSMSStopRequestStatsSnow.sql
The example below queries the SMSStopRequestFact table for a count of SMS Stop Requests by SMS number
Code Block |
---|
language | sql |
---|
title | SMS Stop Requests by SMS Number |
---|
collapse | true |
---|
|
-- Stop request counts by SMS number
SELECT
ssrf_smsNumber AS ["SMS Number]", COUNT(*) ["Total Request Count]",
SUM( CASE WHEN ssrf_restartTimestamp IS NULL OR ssrf_restartTimestamp > GETDATE() OR ssrf_restartTimestamp >= ssrf_stopTimestamp THEN 1 ELSE 0 END ) AS ["Active Request Count]",
SUM( CASE WHEN ssrf_restartTimestamp IS NULL OR ssrf_restartTimestamp > GETDATE() OR ssrf_restartTimestamp >= ssrf_stopTimestamp THEN 0 ELSE 1 END ) AS ["Inactive Request Count]"
FROM
[dbo].[SMSStopRequestFact] SMSStopRequestFact
WHERE
ssrf_stopDateLocal >= 20210101 AND ssrf_stopDateLocal < 20220101
GROUP BY
ssrf_smsNumber
ORDER BY ssrf_smsNumber; |
...
Code Block |
---|
language | sql |
---|
title | SMS Stop Request Detail |
---|
collapse | true |
---|
|
-- Usage report
-- SMS number, SMS UserId, requesting number, stop request date, start request date, is an active request
SELECT
ssrf_smsNumber AS ["SMS Number]",
ssrf_userId AS ["SMS UserId]",
ssrf_fromNumber AS ["Requesting Number]",
CONVERT(varchar, TO_VARCHAR(ssrf_stopTimestamp::timestamp, 120 'yyyy-mm-dd hh:mi:ss') AS ["Stop Request Date]",
CASE WHEN ssrf_restartTimestamp IS NOT NULL THEN CONVERT(varchar, TO_VARCHAR(ssrf_restartTimestamp::timestamp, 120 'yyyy-mm-dd hh:mi:ss') ELSE 'N/A' END AS ["Start Request Date]",
CASE WHEN (ssrf_restartTimestamp IS NULL) OR ssrf_restartTimestamp > GETDATE() OR ssrf_restartTimestamp >= ssrf_stopTimestamp THEN 'true' ELSE 'false' END AS ["Active Request]"
FROM
[dbo].[SMSStopRequestFact] SMSStopRequestFact
WHERE
ssrf_stopDateLocal >= 20210101 AND ssrf_stopDateLocal < 20220101
ORDER BY ssrf_stopDateLocal; |
...