Leverage Message Data to Understand Trends

Raw Data Pivot Tables

You can get raw data reports from Message by exporting your message history. This can be useful to discover trends and other data information using pivot tables in excel.

Start by exporting your message history. To see only messages sent from your program and exclude messages received to your program, filter to “sent” messages in the msg_direction column.

You can get a lot of great information (quickly!) from your analytics dashboard. For those who want to dig in further, you can create a Pivot Table!

Create a Pivot Table

What is a pivot table?

A pivot table is a data summarization tool. It allows you to reorganize and summarize selected columns and rows of data to gain different perspectives and insights. Pivot tables don't alter the original data, they just reorganize it for analysis. Learn more about pivot tables here. 

Create a pivot table with your message history report.

1. Open your message history and select the "Insert" tab.

    

2. Select "Pivot Table."

    

3. Select OK. You now have a new tab on your worksheet with a blank pivot table.

    

4. In order to fill in data, you will select PivotTable fields and drag them into one of four areas: Filters, Columns, Rows, Values. See below for some suggested configurations!  

Message Pivot Table Suggestions

1. Amount of contacts who received each template ID.

For each program message you submit, the name of the template is on the column titled row label. Texts sent through the Message platform are labeled with a long string of letters and numbers.

This method is great to understand how rolling event triggered messages, such as intro messages, or anything programmed with the message template, are sent over time. You can see how many of each message have gone out. 

For example, "How many intro messages were sent during Fall Semester 2025?" The table below indicates that 3 intro messages have been sent so far.

To create this table, drag and drop the following fields:

• Rows: template_id
• Values: sent_type

Note: (blank) = autoresponder, one off (ad hoc) messages, and handlers.

2. Message types sent during a particular time period.

This table helps answer questions like, "How many adhoc messages are being sent?", or "How often was the autoreponder used?"

To create this table, drag and drop the following fields:

• Rows: sent_type
• Values: msg_direction

3. Understand how many people received each handler

This table shows how many contacts responded with a certain choice when you send a handler message. This table is helpful if you don't use a Save Response custom field.

For example, you might ask "Are you still planning on registering for summer session? Reply yes or no." Replies to yes are "Great, let us know if you need any help registering!" and replies to no are "Sorry to hear that, would you mind sharing why?"

You can see on the table below, row 6 indicates 2 contacts replied yes and row 15 indicates 1 contact replied no.

To create this table, drag and drop the following fields:

• Rows: body
• Values: sent_type

Note: If any message has personalization (ie first_name) it will count as it's own row.

4. Understand how many messages are being sent vs. received within your program.

Sent = number of messages sent by counselors and admin. Received = number of messages received from your contacts. 

To create this table, drag and drop the following fields:

• Rows: msg_direction
• Values: sent_type

5. Breakdown of types of messages sent by each user.

This table includes counselors, program admin, and account admin. You can access the data on only counselors on your analytics dashboard.

To create this table, drag and drop the following fields:

• Columns: sent_type
• Rows: sent_by
• Values: msg_direction