Guides

Spruce CSV Data Export

Overview

This document describes the structure and fields of a periodic Spruce data export. The files listed below will be packaged into a zipped folder and delivered to you via an SFTP endpoint. The reports can be delivered daily, weekly or monthly.

Requirements

  • The Data Export tool is only available on a case by case basis. Please reach out to your Account Manager or Spruce Support to learn if your account is eligible for the data export tool.
  • You will need an SFTP server with basic authentication (username and password) to which Spruce can deliver the data export tool.
  • Please create a folder in the root directory (e.g. /spruce) on the SFTP host with write permissions so that Spruce can write a file in the folder
  • Please share the following information with Spruce:
    • SFTP endpoint
    • Username
    • Password
    • Folder in the root directory (e.g. /spruce) with write permissions
    • Frequency of export delivery (daily, weekly or monthly)

Things to Note

  • The data will be delivered in the CSV format.
  • entity_id is an opaque identifier that maps to a user in our system
  • timestamp1 is in the RFC 3339 Nano format (e.g. 2006-01-02T15:04:05.999999999Z07:00)
  • phone numbers are of format +12222222222
  • sip_uri is of format sip:user@domain

CSV Files

metadata.csv

This file contains any metadata related to the report.

  • start_timestamp (timestamp)
  • end_timestamp (timestamp)

entities.csv

This is a list of all non-deleted entities (providers, patients, user-groups, etc.) in the organization.

Note that in a periodic data export, this file will always contain a comprehensive list of entities.

  • entity_id (string)
  • first_name (string)
  • last_name (string)
  • display_name (string)
  • company_name (string)
  • dob (string) (in format 1986-03-27)
  • type (string)
    • PROVIDER - represents a member of the providing team
    • PATIENT - represents a patient using the Spruce the app
    • USER_GROUP - represents a group of providers
    • EXTERNAL - represents an entity that a provider can interact with over sms or email or phone
    • PHONE - represents a VoIP device in the organization that is used to make/receive phone calls.
  • invite_pending (boolean) - whether the entity has an invite pending to create a spruce account
  • entity_created_timestamp (timestamp) - the time at which the entity was created (in UTC)

entity_tags.csv

This is a list of tags associated with each non-deleted entity.

Note that in a periodic data export, this file will always contain tags for all non-deleted entities referenced in entities.csv.

  • entity_id (string)
  • tag (string)

entity_contact_info.csv

This is a file that contains an entity’s contact information if we have any.

Note that in a periodic data export, this file will always contain a comprehensive list of entities.

  • entity_id (timestamp)
  • value (string) (phone number or email or fax number or sip endpoint)
  • type (string) (PHONE or EMAIL or FAX or SIP_URI)
  • label (string)

user_group_members.csv

This file contains a list of members in each user group.

Note that in a periodic data export, this file will always contain a comprehensive list of entities.

  • group_entity_id (string)
  • member_entity_id (string)

patient_id_mappings.csv

This is a mapping of a patient to an external ID (such as patient EMR chart ID) for the systems that we integrate with.

  • entity_id (string)
  • type (string) (elation, hint, etc.)
  • external_id (string) (elation_id, hint_id, etc.)

conversations.csv

This is a list of all non-deleted conversations in the organization.

Note that in a periodic data export, this file will only contain conversations that have a message exchanged in the time period. In other words, this file will NOT be a comprehensive list of all conversations but an incremental one based on the time period involved in the export.

  • conversation_id (string)
  • primary_entity_id (string): if present, represents the ID of the entity that is being communicated with (in the case of 2 participants). If empty, then it means that the conversation likely has 2+ participants.
  • type (string) (TEAM, STANDARD, SECURE)
  • created_timestamp (timestamp)
  • message_count (int64)
  • lastmessage_timestamp (timestamp) - this is the last time that there was any activity in the thread
  • assigned_entity_id (string) - entity the conversation is currently assigned to
  • deleted (boolean) - indicates whether conversation is deleted or not
  • internal_endpoint (string) - represents the actual endpoint value (phone/fax number or email address) for standard conversations or the Spruce Link ID for secure conversations used in the conversation.
  • external_endpoint (string) - represents the actual endpoint value (phone/fax number or email address) for standard conversations or the organization ID on the other side of the conversation for secure conversations. Note that if external_endpoint is empty, it either means that there are 2+ participants involved in the conversation or we are dealing with a conversation where it was not possible to determine the external_endpoint due to legacy reasons.

conversation_tags.csv

This is a list of tags associated with the conversation.

Note that in a periodic data export, this file will only contain tags for conversations that have a message exchanged in the time period. In other words, this file will NOT be a comprehensive list of all conversation tags but an incremental one based on the time period involved in the export.

  • conversation_id (string)
  • tag (string)

conversation_events.csv

This is a list of actions undertaken at a thread level.

Note that in a periodic data export, this file will only contain conversation events that have occurred in the time period. In other words, this file will NOT be a comprehensive list of all conversation events but an incremental one based on the time period involved in the export.

  • conversation_id (string)
  • created_timestamp (timestamp)
  • actorentity_id (string)- _indicates who did the action
  • event (string) - (possible values DELETE, UPDATE_MEMBERS,UPDATE_ASSIGNMENT,ARCHIVE)
  • data (string) - json blob that contains event details

If event == ARCHIVE

{

"archive": bool, // true if archived, false if unarchived

"unassign": bool, // true if unasigned while archiving, only relevant when archiving

"implicit": bool, // true if unarchived due to a message post rather than an explicit user action

}

If event == UPDATE_MEMBERS

{

"add_entity_ids": [string], // users added

"remove_entity_ids": [string], // users removed`

}

If event == UPDATE_ASSIGNMENT

{

"assign_entity_id": string // if empty string then unassigned, else who conversation was assigned to

}

pages.csv

This is a list of @Pages in the system created by an entity and resolved by another user.

Note that in a periodic data export, this file will only contain Pages that have been posted in the time period. In other words, this file will NOT be a comprehensive list of all Pages but an incremental one based on the time period involved in the export.

  • page_id (string)
  • conversation_id (string)
  • messageid (string)- _id of the message that contains the page
  • resolved (boolean) - whether or not page has been resolved
  • resolvedtimestamp (timestamp) - _time at which page was resolved
  • resolvedby_entity_id (string) - _who resolved the page

paged_entities.csv

This is a list of entities referenced in a page.

Note that in a periodic data export, this file will only contain entities for Pages that have been posted in the time period. In other words, this file will NOT be a comprehensive list of all Pages but an incremental one based on the time period involved in the export.

  • page_id (string)
  • entityid (string) - _who the page has been assigned to

messages.csv

Note that in a periodic data export, this file will only contain messages that have been exchanged in the time period. In other words, this file will NOT be a comprehensive list of all messages but an incremental one based on the time period involved in the export.

  • message_id (string)
  • conversation_id (string)
  • created_timestamp (timestamp)
  • internal (boolean) - whether the message was internal to the organization or for the patient
  • destinations (string) (piped list of type:value e.g. SMS:+14155551212|EMAIL:[email protected])
  • actor_entity_id (string)
  • message_body (string)
  • message_type (string)
    • USER - generated by the user
    • EVENT - activity that results in an event in the conversation (eg. assignment changed, tag added/removed)
    • TRIGGERED - message triggered via user activity (eg. away message, automated welcome message)
    • SCHEDULED - message sent due to a scheduled message
  • deleted (boolean) - indicates whether conversation is deleted or not

inbound_calls.csv

Note that in a periodic data export, this file will only contain inbound calls that have been received in the time period. In other words, this file will NOT be a comprehensive list of all inbound calls but an incremental one based on the time period involved in the export.

  • created_timestamp (timestamp)
  • source (string)
  • destination (string)
  • answered (string)
  • answered_by (string) - if answered, phone number or username of SIP endpoint where call was answered. To identify the exact name of the phone line, see voip_phone_lines.csv
  • answered_timestamp (timestamp)
  • completed_timestamp (timestamp)
  • sent_to_voicemail (boolean)
  • left_voicemail_timestamp (timestamp)
  • phone_tree_node_id (string)

voip_phone_lines.csv

Note that in a periodic data export, this file will contain a comprehensive list of ALL VOIP based phone lines configured in the organization

  • username - username of SIP endpoint
  • name - name of phone line
  • entity_id - ID of the phone entity that shows up in messages. See entities.csv for the exact entity that maps to the phone entity.

outbound_calls.csv

Note that in a periodic data export, this file will only contain outbound calls that have been placed in the time period. In other words, this file will NOT be a comprehensive list of all outbound calls but an incremental one based on the time period involved in the export.

  • source (string)
  • destination (string)
  • outbound_caller_id (string)
  • created_timestamp (timestamp)
  • caller_entity_id (string)
  • completed_timestamp (string)

spruce_links.csv

Note that in a periodic data export, this file will contain a comprehensive list of ALL Spruce links configured in the organization

  • id (string) - unique ID to identify the Spruce Link
  • label (string) - label used to identify the Spruce Link.
  • url (string) - URL shared with patients to connect with the organization via the Spruce Link
  • deleted (string) - TRUE/FALSE value to indicate if the Spruce Link is deleted or not

Updated 3 months ago