Guides

Spruce CSV Data Export

Interacting with the Spruce Data Export that can be delivered to an SFTP endpoint

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)
  • actor_entity_id (string)- indicates who did the action
  • event (string) - (possible values ARCHIVE,DELETE,OPENED,UPDATE_MEMBERS,UPDATE_ASSIGNMENT)
  • 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)
  • message_id (string)- id of the message that contains the page
  • resolved (boolean) - whether or not page has been resolved
  • resolved_timestamp (timestamp) - time at which page was resolved
  • resolved_by_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)
  • entity_id (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
  • transcription_job_id (string) - AI transcription job ID for an audio attachment on the message (e.g. voicemail, call recording). When a message has multiple transcribed audio attachments the job IDs are newline-separated within the cell, in attachment order. Empty for messages without a transcription. See transcriptions.csv for per-transcription status and text.

transcriptions.csv

This is a list of AI transcriptions for audio attachments (voicemails, call recordings). One row per transcription job. Summarization data is nested as additional columns on the transcription row since each transcription has at most one summarization.

Note that in a periodic data export, this file will only contain transcriptions whose parent message was exchanged in the time period.

  • transcription_job_id (string) - primary key
  • message_id (string) - back-pointer to messages.csv
  • conversation_id (string) - back-pointer to conversations.csv
  • deleted (boolean) - whether the transcription has been deleted. Text is suppressed when deleted.
  • status (string) - AI transcription status. Possible values: pending, completed, failed, no_speech.
  • summarization_job_id (string) - AI summarization job ID. Empty when the transcription has no summarization.
  • summarization_status (string) - AI summarization status. Possible values: pending, completed, failed. Empty when no summarization.
  • summarization_deleted (boolean) - whether the summarization has been deleted. false when no summarization.
  • text (string) - PHI. Full transcription body as plain text. Empty when deleted=true.
  • summary_text (string) - PHI. Full summarization body as plain text. Empty when no summarization or when summarization_deleted=true.

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_by_entity_id (string) - best-effort entity ID of the team member who answered the call. Cross-reference with entities.csv to get the display name. May be empty even when answered_by is set if the entity could not be resolved.
  • 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)