Communicator Inbox Messages

These tables track the two-way communication transactions between Communicator app and Badge app. From Communicator, an administrator can create two types of communication messages, (1) a survey question with a list of responses; (2) a notification which the can only be confirmed. These notifications are sent badges on the Badge app where the user can respond.

In addition to the initial message, a follow-up notification can also be sent. This is tracked using the Parent Message.

In the Platform Analytics project, there is no project schema based off the warehouse tables. To build a custom report, use the Communicator Inbox Messages data import cube.

lu_usher_inbox_messages

This table stores the list of messages and the parent message sent from Communicator. It also stores the sender device and location information at the time each message was sent.

Column	Description	Data-Type
message_id	This is the message ID generated by Identity server.	bigint(20)
message_desc	The text of the survey or notification message the administrator sends to the Badge.	varchar(255)
creation_timestamp	This is the UTC timestamp from when the message was sent.	datetime
latitude	The latitude of the sender.	double
longitude	The longitude of the sender.	double
parent_message_id	The message_id of the original parent message. This is populated if the user sends a follow up message from Communicator. If no follow-up message is sent, this value will be null.	bigint(20)
network_id	The numeric ID of the network for which the sender belongs.	bigint(20)
sender_account_id	The numeric account ID of the Communicator administrator who sent the message. In Platform Analytics, each Badge has a unique account ID.	bigint(20)
sender_badge_id	The numeric badge id of the Communicator administrator who sent the message.	bigint(20)
app_id	The numeric ID of the Communicator app version from which the message was sent.	int(11)
usher_device_id	The numeric ID of device from which the message was sent.	bigint(20)
os_id	The numeric ID of OS from which the message was sent.	int(11)

lu_usher_inbox_responses

This table stores the list of possible responses for each message sent from Communicator.

There are two types of messages, the first type is confirmation. This is when the admin states a question and the Identity server user would reply by hitting the key Confirm. Confirm is the default value we use if there are no options given. The second type of message is one sent with options. For example, if the Communicator admin asked “Do you like red or blue?" This table would then get two entries, one for red with its own response_id and one for blue with its own response_id with both having the same message ID.

Column	Description	Data-Type
response_id	This ID is auto generated for the possible choices the responder selects in Badge.	bigint(20)
response_desc	This is the response of the user using the Badge.	varchar(25)
message_id	This is the message id generated by the Identity server.	bigint(20)

fact_usher_inbox_messages

This table stores each of the Communicator messages sent to the individual Badge users. It also stores the time each message was sent.

Column	Description	Data-Type
message_id	This is the message ID generated by Identity server.	bigint(20)
responder_account_id	For Platform Analytics only, this is the account for the person who received the message.	bigint(20)
responder_badge_id	For Platform Analytics only, this is the badge for the person who received the message.	bigint(20)
sent_timestamp	This is the UTC timestamp from when the message was sent.	datetime
local_sent_timestamp	This is the local timestamp from when the message was sent.	datetime
sent_date	This is the UTC date from when the message was sent.	date
local_sent_date	This is the local date from when the message was sent.	date

fact_usher_inbox_messages_view

This View is used to track who responded to a specific message over the last 14 days only.

Column	Description	Data-Type
message_id	This is the message ID generated by Identity server.	bigint(20)
responder_account_id	MicroStrategy internal use. This is the account for the person who received the message.	bigint(20)
sent_timestamp	The UTC timestamp from when the message was sent.	datetime
local_sent_timestamp	The local timestamp when the message was sent to the user.	datetime

fact_usher_inbox_responses

This is the fact table that tracks each Communicator response. If an admin sends 100 messages but only 50 people reply, this table would have 50 entries.

Column	Description	Data-Type
message_id	This is the message ID generated by Identity server.	bigint(20)
response_id	This ID is auto generated in Lu_usher_inbox_responses. It is how we know what option the Identity server user selected.	bigint(20)
responder_account_id	For Platform Analytics only, this is the account for the person who received the message.	bigint(20)
responder_badge_id	For Platform Analytics only, this is the badge for the person who received the message.	bigint(20)
latitude	Latitude of the responder.	double
longitude	Longitude of the responder.	double
app_id	The numeric ID of the Communicator app version from which the message was sent.	int(11)
user_device_id	The numeric ID of device from which the message was sent.	bigint(20)
os_id	The numeric ID of os from which the message was sent.	int(11)
facility_address_id	The numeric ID of facility address from which the message was sent.	bigint(20)
network_id	The numeric ID of the network for which the sender belongs.
response_timestamp	This is the UTC timestamp from when the reply to the message was sent.	datetime
local_response_timest amp	Local timestamp from when the reply to the message was sent.	datetime
response_date	This is the UTC date from when the reply to the message was sent.	date
local_response_date	This is the local date from when the reply to the message was sent.	date

fact_usher_inbox_responses_view

This view is to help easily track which message is replied to by which users over the last 14 days.

Column	Description	Data-Type
message_id	This is the message id generated by Identity server.	bigint(20)
response_id	This ID is auto generated in Lu_usher_inbox_responses. It is how we know what option the Identity server user selected.	bigint(20)
responder_account_id	For Platform Analytics only, this is the account for the person who received the message.	bigint(20)
latitude	Latitude of the user.	double
longitude	Longitude of the user.	double
User_device_id	The numeric ID of device from which the message was sent.	bigint(20)
response_timestamp	The UTC timestamp from when the reply to the message was sent.	datetime
local_response_timest amp	The local timestamp from when the reply to the message was sent.	datetime