SMS Dimension Tables

SMSNumberDimension

Column NameTypeDescription
snd_idintPrimary key
snd_numbervarchar(32)The SMS number
snd_active

bit

(1=true/0=false)

Indicates whether the number is active or not
snd_deliveryTypevarchar(10)

SMS event delivery type. Possible values:

  • WEBSOCKET
  • WEBHOOK
  • BOTH
snd_descriptionvarchar(128)The SMS number description
snd_eipIdvarchar(16)The account identifier
snd_maxMMSImageSizeintThe SMS max MMS image size in bytes
snd_maxMMSMessageSizeintThe SMS max MMS message size in bytes
snd_maxSMSMessageSizeintThe SMS max SMS message size in bytes
snd_parentEipIdvarchar(16)The parent account identifier
snd_payloadFormatvarchar(1024)

The formatting macro to format webhook payload.

For example:

{
"@type": "MessageCard",
"@context": "http://schema.org/extensions",
"themeColor": "0076D7",
"summary": "New Message From ${fromNumber}",
"sections": [{
"activityTitle": "New Message From ${fromNumber}",
"activitySubtitle": "MFA code sent",
"activityImage": "http://xml-app.evolveip.net/DTD/MFA.png",
"facts": [{
"name": "MFA code",
"value": "${body}"
}],
"markdown": true
}]
}

snd_productvarchar(16)

The product billing code

snd_providervarchar(64)The SMS provider
snd_redactAllowedDigitsintThe number of digits permitted if redaction is enforced
snd_redactionStylevarchar(8)

The redact style if applied. Possible value:

  • ENCRYPT
  • BLOCK
snd_urlvarchar(2048)URL for webhook delivery
snd_userIdvarchar(64)The SMS user id associated with this SMS number



SMSUserDimension

Column NameTypeDescription
sud_idintPrimary key
sud_firstNamevarchar(64)The SMS user first name
sud_lastNamevarchar(64)The SMS user last name
sud_emailvarchar(64)The SMS user email
sud_userIdvarchar(64)The user unique identifier
sud_enterpriseIdvarchar(32)The VoIP platform identifier
sud_eipIdvarchar(16)The account identifier
sud_parentEipIdvarchar(16)The parent account identifier



SMSProviderDimension

Column NameTypeDescription
spd_idintPrimary key
spd_namevarchar(64)The SMS provider name
spd_abbreviationvarchar(16)The SMS provider name's abbreviation
spd_apiClassNamevarchar(256)The SMS provider's API class name
spd_messageParserClassNamevarchar(256)The SMS provider's message parser class name



SMS Fact Tables

SMSMessageFact

Column NameTypeDescriptionReferencesCommentsIndexed
smf_idintPrimary key


smf_messageIdvarchar(128)The SMS message unique identifier


smf_messageTagvarchar(32)Provider specific message tag


smf_messageAddressvarchar(32)Provider specific message address


smf_messageStatusvarchar(16)

SMS message status. Possible values:

  • In Process
  • Queued
  • Delivered
  • Error
  • Unknown



smf_messageTypevarchar(4)

SMS message type. Possible values:

  • SMS
  • MMS
  • FLOW



smf_messageStatevarchar(16)

SMS message state. Possible values:

  • Send
  • Progress
  • Receive
  • Read
  • Delete
  • Change



smf_messageDirectionvarchar(16)

SMS message direction. Possible values:

  • Inbound
  • Outbound
  • Omni



smf_fingerprintvarchar(64)Provider specific message finger print


smf_flowTypevarchar(16)

SMS message flow type. Possible values:

  • Stop
  • Unstop



smf_clientIdvarchar(16)Client identifier
Deprecated
smf_providerIdvarchar(16)Abbreviation of the SMS message provider


smf_providerIdFkintForeign key to SMS message providerSMSProviderDimension
Yes
smf_providerMessageIdvarchar(64)Provider specific message Id


smf_fromContactFirstNamevarchar(64)The first name associated to the contact this message received from


smf_fromContactLastNamevarchar(64)The last name associated to the contact this message received from.


smf_fromContactCompanyvarchar(64)The company name associated to the contact this message received from


smf_fromContactNumbervarchar(32)The number associated to the contact this message received from


smf_fromContactTypevarchar(16)The contact type associated to the contact this message received from


smf_toContactFirstNamevarchar(64)The first name associated to the contact this message sent from


smf_toContactLastNamevarchar(64)The last name associated to the contact this message sent from


smf_toContactCompanyvarchar(64)The company name associated to the contact this message sent from


smf_toContactNumbervarchar(32)The number associated to the contact this message sent from


smf_toContactTypevarchar(16)The contact type associated to the contact this message sent from


smf_attachmentContentTypevarchar(16)The content MIME type of the attachment if exists


smf_attachmentContenttextBase64 encoded content of the attachment if exists


smf_toListvarchar(1024)To distribution list


smf_ccListvarchar(1024)CC distribution list


smf_bccListvarchar(1024)BCC distribution list


smf_sendingUserIdvarchar(64)Identifier of the SMS sending user 


smf_receivingUserIdvarchar(64)Identifier of the SMS receiving user 

Yes
smf_sendingUserIdFkintForeign key of the SMS user sending this messageSMSUserDimension
Yes
smf_receivingUserIdFkintForeign key of the  SMS user receiving  this messageSMSUserDimension

smf_fromNumberFkintForeign key of the SMS from numberSMSNumberDimension
Yes
smf_toNumberFkint

Foreign key of the SMS to number

SMSNumberDimension
Yes
smf_hashAbigintHash code used to identify related messages


smf_hashBbigintHash code used to identify related messages


smf_accountIdintForeign key of the account associated with this SMS messageAccountDimension
Yes
smf_eipIdvarchar(16)

The account identifier




smf_parentEipIdvarchar(16)Parent account identifier


smf_bodynvarchar(1024)The message body


smf_originationTimestampdatetime2(7)

Time at which the SMS was originated.

The origination time is shown using the UTC/GMT time zone.



Yes
smf_deliveredTimestampdatetime2(7)

Time at which the SMS was delivered.

The delivered time is shown using the UTC/GMT time zone.




smf_deletedTimestampdatetime2(7)

Time at which the SMS was deleted.

The deletion time is shown using the UTC/GMT time zone.




smf_readTimestampdatetime2(7)

Time at which the SMS has been read.

The read time is shown using the UTC/GMT time zone.




smf_scheduledTimestampdatetime2(7)

Time at which the SMS was scheduled.

The scheduled time is shown using the UTC/GMT time zone.



Yes
smf_originationDateLocalintForeign key of the the SMS message origination date in Local timeDateDimension
Yes
smf_originationDateUTCintForeign key of the the SMS message origination date in UTCDateDimension
Yes
smf_originationTimeLocalintForeign key of the the SMS message origination time of day in Local timeTimeOfDayDimension
Yes
smf_originationTimeUTCintForeign key of the  SMS message origination time time of day in UTC TimeOfDayDimension
Yes
smf_scheduledDateLocalintForeign key of the  SMS message scheduled date in Local timeDateDimension
Yes
smf_scheduledDateUTCintForeign key of the SMS message scheduled date in UTC DateDimension
Yes
smf_scheduledTimeLocalintForeign key of the  SMS message scheduled time time of day in Local timeTimeOfDayDimension
Yes
smf_scheduledTimeUTCintForeign key of the SMS message scheduled time time of day in UTC TimeOfDayDimension
Yes
smf_hasAttachments

bit

(1=true/0=false)

Indicates whether the SMS has attachments or not


smf_deleted

bit

(1=true/0=false)

Indicates whether the SMS has been deleted or not


smf_read

bit

(1=true/0=false)

Indicates whether the SMS has been read or not


smf_visible

bit

(1=true/0=false)

Indicates whether the SMS is visible or not


SMS Message StatsqueryquerySMSMessageStats.sql

SMSStopRequestFact

Column NameTypeDescriptionReferencesComments
ssrf_idintPrimary key


ssrf_fromNumbervarchar(32)The external number requesting the stop or unstop action


ssrf_smsNumbervarchar(32)The SMS number receiving this stop/unstop request


ssrf_smsNumberFkint

Foreign key to the SMS number receiving this stop/unstop request

SMSNumberDimension
Yes
ssrf_userIdvarchar(64)User Id associated with the SMS number receiving this stop/unstop request


ssrf_userIdFkint

Foreign key of the SMS user receiving this stop/unstop request

SMSUserDimension
Yes
ssrf_restartTimestampdatetime2(7)

Time in which the SMS Stop request has restarted (the user has sent unstop request).

The restart time is shown using the UTC/GMT time zone



Yes
ssrf_restartDateLocalintForeign key to the restart date in Local time (date portion only).DateDimensionAdd a foreign keyYes
ssrf_restartDateUTCintForeign key to the restart date in UTC (date portion only).DateDimensionAdd a foreign keyYes
ssrf_restartTimeLocalintForeign key to the restart time in Local time (time portion only).TimeOfDayDimensionAdd a foreign keyYes
ssrf_restartTimeUTCintForeign key to the restart time in UTC (time portion only).TimeOfDayDimensionAdd a foreign keyYes
ssrf_stopTimestampdatetime2(7)

Time in which the SMS Stop request has been sent to stop messaging.

The stop time is shown using the UTC/GMT time zone.



Yes 
ssrf_stopDateLocalintForeign key to the stop request date in Local time (date portion only).DateDimension
Yes
ssrf_stopDateUTCintForeign key to the stop request date in UTC (date portion only).DateDimension
Yes
ssrf_stopTimeLocalintForeign key to the stop request time in Local time (time portion only).TimeOfDayDimension
Yes
ssrf_stopTimeUTCintForeign key to the stop request time in UTC (time portion only).TimeOfDayDimension
Yes
ssrf_accountIdintForeign key of the account associated with this SMS stop requestAccountDimension
Yes
ssrf_eipIdvarchar(16)The account identifier


ssrf_parentEipIdvarchar(16)Parent account identifier


ssrf_active

bit

(1=true/0=false)

Indicates whether the the user has sent stop request (1) or restarted messaging by sending unstop (0).


SMS Stop Request StatsQueryQuerySMSStopRequestStat.sql

Contact Stats

Unique Contacts Count

--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
        smf_messageDirection = 'OUTBOUND' AND smf_sendingUserId is not NULL
    UNION
    SELECT
        smf_fromNumber AS contact
    FROM
        [dbo].[SMSMessageFact] WITH( NOLOCK )
    WHERE
        smf_messageDirection = 'INBOUND' AND smf_receivingUserId is not NULL) AS T;

Unique Contacts List

-- 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
        smf_messageDirection = 'OUTBOUND' AND smf_sendingUserId is not NULL
    UNION
    SELECT
        smf_fromNumber AS contact
    FROM
        [dbo].[SMSMessageFact] WITH( NOLOCK )
    WHERE
        smf_messageDirection = 'INBOUND' AND smf_receivingUserId is not NULL) 

DW Sample Queries


SMS Contacts Stats

SMSContactsStats.sql


SMS Message Stats

SMSMessageStats.sql


SMS Stop Request Stats

SMSStopRequestStats.sql



  • No labels