Records take up to 30 days from request to be sent to members. For every member, there is an Release of Information (ROI) that is emailed to them. If a member does not complete the ROI a record will not be sent to them. Therapy records typically take longer due to the level of privacy when releasing records. The therapist who was working with the member is required to review the member's documents and approve them before they can be released to the member.

Instructions for Tier 2 Team on Data Requests

Occasionally, a member may contact customer support to ask for a copy of their data. There are two categories of requests;

• A member requesting an offline copy of the data they entered into the app, e.g. metrics and food logs. These come to the security team to manage.
• A member requesting a copy of their medical records (e.g. provider notes, care plan) or any of their data to be sent to a third party, such as a physician. These go to the clinical QA team who manage this through a release of information request.
• This guide outlines how to handle the requests that come to the security team.

1. Obtain the UUID, Team Code and timezone of the user

Lookup the user in Vida Admin based on the email address they contacted Vida with. The search result page in Vida Admin will contain the UUID the the user detail page will contain the timezone under “Personal Info”.

Some queries may be team code specific (e.g. chat logs). For these, look up the team in Vida Admin again based on the email address they contacted Vida with. Only use “Care Team” or “Care Team Solo” team codes, never “Group” or “Care Group”.

2. Lookup and export the user’s data in BigQuery

2.1 Metrics

Navigate to the accessible data project in BigQuery and run the following query:

SELECT 
 tm.day_tz as date,
 FORMAT_DATETIME("%F %X", DATETIME(tm.metric_date,"America/Los_Angeles")) as timestamp,
 tm.metric_name as metric,
 IFNULL(SAFE_CAST(ROUND(tm.value_float, 2) as string), tm.value_str) as value,
 IFNULL(tm.subsource, tm.SOURCE) as source,
 tm.is_manual

FROM `accessible-data.daily_base.tracking_metrics` tm
JOIN `accessible-data.standardized_data.member_rollup` USING(member_id)
WHERE member_uuid = "UUID"
AND metric_name not in ("thought-log", "coaching-alliance-1", "coaching-alliance-2", "coaching-alliance-3", "phq-8", "gad-7", "phq-2", "mindful-minutes", "prediabetes-risk" )
AND source != 'recognized_food'
ORDER BY metric_date, metric

Note: Please do not copy code directly from the Zendesk Knowledge Base (KB) side panel view. Instead, open the KB article in a separate preview or published window to ensure the correct formatting is displayed, and copy the code from that window.

• Replace “UUID” with the member’s UUID looked up in step 1
• Replace “America/Los_Angeles” with the user’s timezone in TZ naming format.
• The member may have requested a subset of metrics, e.g. just blood pressure. If so, add an additional condition into the WHERE clause, AND metric_name IN ("…"). The following table outlines the metric names to use for various metrics requested;

Note: Not an exhaustive list, but the main categories that are generally requested.

 2.2 Food Logs

Navigate to the accessible data project in BigQuery and run the following query: 

SELECT
 IFNULL(FORMAT_DATETIME("%F %X", DATETIME(rf.consumed_at,"America/Los_Angeles")), CONCAT(FORMAT_DATETIME("%F", DATETIME(date,"America/Los_Angeles")), " ", SUBSTR(fi.time,0,8))) as timestamp,
 fe.meal_name,
 fi.description,
 rf.food_name,
 rf.tag_item as tag,
 rf.calories,
 rf.fat,
 rf.sodium,
 rf.carbohydrate,
 rf.fiber,
 rf.sugar,
 rf.protein,
 rf.cholesterol,
 rf.potassium,
 rf.saturated_fat,
 rf.user_set_serving_qty as serving_quantity,
 rf.user_set_serving_unit as serving_unit,
 IFNULL(fi.subsource, fi.SOURCE) as source,
FROM `accessible-data.accessible_database.food_foodlogentryitem` fi
JOIN `accessible-data.accessible_database.food_foodlogentry` fe ON fi.food_log_entry_id = fe.id
JOIN `accessible-data.accessible_database.food_recognizedfood` rf ON rf.food_log_entry_item_id = fi.id
JOIN `accessible-data.standardized_data.member_rollup` mr ON fi.user_id = mr.member_id
WHERE member_uuid = "UUID"
ORDER BY date, meal_name

Note: Please do not copy code directly from the Zendesk Knowledge Base (KB) side panel view. Instead, open the KB article in a separate preview or published window to ensure the correct formatting is displayed, and copy the code from that window.

• Replace “UUID” with the member’s UUID looked up in step 1
• Replace “America/Los_Angeles” with the user’s timezone in TZ naming format.

2.3 Thought Tracker

Navigate to the accessible data project in BigQuery and run the following query:

WITH thought_trackers AS
(
 SELECT
   *EXCEPT(answer),
   ARRAY_TO_STRING(answer, ", ") AS answer
 FROM
 (
   SELECT
       grouped.*,
       instance.user_id AS member_id,
       question.text AS question_text,
       ref.surveyquestion_id,
       ARRAY_AGG(IFNULL(freetext.text, response.free_response) IGNORE NULLS) as answer,
       response.question_group_instance_id,
       TIMESTAMP_TRUNC(response.created, MINUTE) AS answer_time_utc,
       DATETIME_TRUNC(DATETIME(response.created, mr.timezone), MINUTE) AS answer_time_member_tz,
       EXTRACT(HOUR FROM DATETIME(response.created, mr.timezone)) as answer_hour_member_tz,
       CASE
         WHEN grouped.id = 2213 THEN "Level 1"
         WHEN grouped.id = 2214 THEN "Level 2"
         WHEN grouped.id = 2215 THEN "Level 3"
       END AS tracker_level
     FROM
       `fast-audio-134400.vida.customer_questions_surveyquestiongroup` grouped
     JOIN
       `fast-audio-134400.vida.customer_questions_surveyquestion_groups` ref
     ON
       grouped.id = ref.surveyquestiongroup_id
     JOIN
       `fast-audio-134400.vida.customer_questions_surveyquestion` question
     ON
       ref.surveyquestion_id = question.id
     JOIN
       `fast-audio-134400.vida.customer_questions_surveyquestiongroupinstance` instance
     ON
       grouped.id = instance.question_group_id
     JOIN
       `fast-audio-134400.vida.customer_questions_surveycustomerresponse` response
     ON
       instance.id = response.question_group_instance_id
       AND ref.surveyquestion_id = response.question_id
     LEFT JOIN
       `fast-audio-134400.vida.customer_questions_surveyanswerchoice` freetext
     ON
       response.answer_id = freetext.id
    JOIN
       `accessible-data.materialized_data.member_rollup` mr
     ON
       instance.user_id = mr.member_id
     GROUP BY
       grouped.id,
       grouped.uuid,
       grouped.created,
       grouped.modified,
       grouped.text,
       member_id,
       question_text,
       surveyquestion_id,
       question_group_instance_id,
       answer_time_utc,
       answer_time_member_tz,
       answer_hour_member_tz,
       tracker_level)
),
--Getting all the details regarding the member and tying it to the coach. Currently, the thought tracker only associates the answers to the member and not a team or program instance.
member_details AS
(
 SELECT
   member_id,
   tt.code AS team_code,
   tt.primary_coach_id,
   au.uuid AS coach_uuid
 FROM
   (
   SELECT
     member_id,
     team_id
   FROM
     `accessible-data.materialized_data.member_rollup`, UNNEST(teams_id) team_id
   ) mr
 JOIN
   `accessible-data.accessible_database.programs_programinstance` ppi
 USING
   (team_id)
 JOIN
   `accessible-data.accessible_database.teams_team` tt
 ON
   mr.team_id = tt.id
 JOIN
   `accessible-data.accessible_database.coaching_coachprofile` ccp
 ON
   tt.primary_coach_id = ccp.user_id
 JOIN
   `fast-audio-134400.vida.accounts_user` au
 ON
   tt.primary_coach_id = au.id
 WHERE
   ccp.coach_type in ("therapist","care-navigator")
)
SELECT
 thought_trackers.member_id,
 thought_trackers.question_group_instance_id,
 thought_trackers.id AS surveyquestion_group_id,
 member_details.team_code,
 member_details.coach_uuid,
 DATE(MIN(answer_time_utc)) AS answer_day_utc,
 DATE(MIN(answer_time_member_tz)) AS answer_day_member_tz,
 tracker_level,
 --Categorizing the time of day that the thought tracker was submitted, in the user's timezone
 CASE
   WHEN answer_hour_member_tz BETWEEN 6 AND 9 THEN "Morning"
   WHEN answer_hour_member_tz BETWEEN 10 AND 13 THEN "Midday"
   WHEN answer_hour_member_tz BETWEEN 14 AND 17 THEN "Afternoon"
   WHEN answer_hour_member_tz BETWEEN 18 AND 21 THEN "Evening"
   WHEN answer_hour_member_tz >= 22 OR answer_hour_member_tz <= 5 THEN "Overnight"
   END AS time_of_day,
 --Converting the "How are you" question asked at the beginning of the thought tracker to the Likert scale of 1-7
 MAX(CASE
       WHEN surveyquestion_id = 3725 AND answer = "Very Bad" THEN 1
       WHEN surveyquestion_id = 3725 AND answer = "Bad" THEN 2
       WHEN surveyquestion_id = 3725 AND answer = "Somewhat Bad" THEN 3
       WHEN surveyquestion_id = 3725 AND answer = "Neutral" THEN 4
       WHEN surveyquestion_id = 3725 AND answer = "Somewhat Good" THEN 5
       WHEN surveyquestion_id = 3725 AND answer = "Good" THEN 6
       WHEN surveyquestion_id = 3725 AND answer = "Very Good" THEN 7
     END) AS first_likert_score,
 --Converting the "How are you feeling now?" question asked at the end of the thought tracker to the Likert scale of 1-7
 MAX(CASE
       WHEN surveyquestion_id = 3731 AND answer = "Very Bad" THEN 1
       WHEN surveyquestion_id = 3731 AND answer = "Bad" THEN 2
       WHEN surveyquestion_id = 3731 AND answer = "Somewhat Bad" THEN 3
       WHEN surveyquestion_id = 3731 AND answer = "Neutral" THEN 4
       WHEN surveyquestion_id = 3731 AND answer = "Somewhat Good" THEN 5
       WHEN surveyquestion_id = 3731 AND answer = "Good" THEN 6
       WHEN surveyquestion_id = 3731 AND answer = "Very Good" THEN 7
     END) AS last_likert_score,
 --Taking the answers to each of the survey questions and aligning
 MAX(CASE WHEN surveyquestion_id = 3725 THEN answer END) AS how_are_you,
 MAX(CASE WHEN surveyquestion_id = 3726 THEN answer END) AS what_is_running_through_your_head,
 MAX(CASE WHEN surveyquestion_id = 3727 THEN answer END) AS what_was_happening,
 MAX(CASE WHEN surveyquestion_id = 3728 THEN answer END) AS which_emotions_came_up,
 MAX(CASE WHEN surveyquestion_id = 3729 THEN answer END) AS thinking_traps,
 MAX(CASE WHEN surveyquestion_id = 3730 THEN answer END) AS rethink_question,
 MAX(CASE WHEN surveyquestion_id = 3731 THEN answer END) AS how_are_you_now
FROM
 thought_trackers
JOIN
 member_details
USING
 (member_id)
WHERE 
 team_code = "TEAM_CODE"
GROUP BY
 thought_trackers.member_id,
 thought_trackers.question_group_instance_id,
 surveyquestion_group_id,
 time_of_day,
 member_details.team_code,
 member_details.coach_uuid,
 tracker_level

Note: Please do not copy code directly from the Zendesk Knowledge Base (KB) side panel view. Instead, open the KB article in a separate preview or published window to ensure the correct formatting is displayed, and copy the code from that window.

• Replace “TEAM_CODE” with the member’s therapy team code

2.4 Chat History

Never share the chat history of group chats (e.g. “Group” or “Care Group” team types)

Navigate to the Vida BigQuery DB project in BigQuery and run the following query:

SELECT
   FORMAT_DATETIME("%F %X", DATETIME(m.created,"America/New_York")) as timestamp,
   IFNULL(tm.role, IFNULL(tp.role,'vidabot')) as sent_by,
   m.text
FROM `fast-audio-134400.vida.messaging_message` m
JOIN `fast-audio-134400.vida.teams_team` t
ON t.id = m.team_id
LEFT JOIN `fast-audio-134400.vida.teams_membership` tm
ON tm.team_id = m.team_id AND m.sender_id = tm.user_id
LEFT JOIN `fast-audio-134400.vida.teams_pastmembership` tp
ON tp.team_id = m.team_id AND m.sender_id = tp.user_id
WHERE t.code = "TEAM_CODE"
AND m.text is not null
AND m.deleted_at is null
ORDER BY timestamp ASC

Note: Please do not copy code directly from the Zendesk Knowledge Base (KB) side panel view. Instead, open the KB article in a separate preview or published window to ensure the correct formatting is displayed, and copy the code from that window.

• Replace “TEAM_CODE” with the team code looked up in step 1.
• Replace “America/Los_Angeles” with the user’s timezone in TZ naming format.

3. Download results as a CSV

In BigQuery, save the results as a local CSV file to your local machine 

4. Lookup the user’s email address

Do not use the email address in the support ticket. Data must be sent to the email address that the user registered their account with as seen on Admin.

Lookup the user’s email address in Vida Admin based on the UUID used to obtain their data.

5. Send the user’s data via secure email

To add an alias to Gmail:

1. Go to Gmail settings: Click the gear icon, then "See all settings."
2. Navigate to the “Accounts and Import” tab,
3. Under “Send mail as”, click “Add another email address” to add a new alias,
4. Follow the verification steps to add support@vida.com under the name "Support"
5. Leave the "treat as an alias" box checked
6. It will send a confirmation to the support email with the subject “Vida Health Confirmation - Send Mail as support@vida.com.” Find that email on Zendesk and copy the code or click the link to confirm the addition.
7. Now you can send from the support group address: When composing an email, select the group from the "From" dropdown.

Send an email on gmail from "support@vida.com" to the user using Virtru containing the CSV file as an attachment. Since the user probably will not be familiar with Virtru, use the “Personal Introduction” feature to send an unencrypted message explaining what the email is and how to access the encrypted content.

The following messages should be sent in the encrypted email.

Intro message:

Hello [Member],
This secure message contains the data that you requested from your Vida account. As per our security and privacy policy, we are sending this data to the email address used to register your Vida account.

To view the contents of this message and download the attachment, please click on the "Unlock Message" button above.
If you have any questions, please feel free to reach out to our customer support team.

Encrypted message:

Hello [Member],
This secure message contains the data that was requested from your Vida account. The attachment is in a CSV format which you should be able to view in the spreadsheet software of your choice (e.g. Microsoft Excel, Apple Numbers, Google Sheets, OpenOffice Calc).

If you have any questions, please feel free to reach out to our customer support team.

Spanish Translations:

1. Hola, [Member name]:
   Este mensaje seguro contiene los datos que solicitó de su cuenta de Vida. De acuerdo con nuestra política de seguridad y privacidad, le enviamos estos datos a la dirección de correo electrónico que usó para registrarse en su cuenta de Vida.
   Para ver el contenido del mensaje y descargar el archivo adjunto, pulse el botón "Unlock Message" (Desbloquear mensaje) arriba.
   Si tiene preguntas, no dude en contactar con nuestro equipo de Soporte al cliente.
2. Hola, [Member name]:
   Este mensaje seguro contiene los datos solicitados de su cuenta de Vida. El archivo adjunto está en formato CSV. Podrá abrirlo en el software de hojas de cálculo que prefiera (por ejemplo, Microsoft Excel, Apple Numbers, Google Sheets, OpenOffice Calc). Las categorías de datos (como fecha/hora, tipo de medición y origen) se muestran en inglés, ya que así es como se procesan nuestros datos.
   Si tiene preguntas, no dude en contactar con nuestro equipo de Soporte al cliente.

6. Reply to the original request

To close the loop, send a reply to the original email from the member requesting this data. This could either be by replying directly to an email sent to privacy@vida.com, or responding on the Zendesk ticket where this request was made.

Use macro "Data Request Sent";

1. Hi [Member],
   Thank you for contacting Vida's privacy team. We have sent the data you requested via secure email. This is in a CSV format which you should be able to open in the spreadsheet software of your choice (e.g. Microsoft Excel, Apple Numbers, Google Sheets, OpenOffice Calc). Please follow the instructions in that email to obtain a download of your data.

SPANISH

1. Hola, [Member Name]:
   Gracias por contactar con el equipo de Privacidad de Vida. Le hemos enviado los datos que solicitó por correo electrónico seguro. Están en formato CSV. Podrá abrirlo en el software de hojas de cálculo que prefiera (por ejemplo, Microsoft Excel, Apple Numbers, Google Sheets, OpenOffice Calc). Siga las instrucciones de ese correo electrónico para descargar sus datos.
   Un cordial saludo.
   El equipo de Privacidad de Vida