Export Explorer: Query Library

This document contains a collection of SQL queries that can be used with the Export Explorer. Unless otherwise noted, the SQL provided below can be directly pasted as-is for the most complete results or it can be further narrowed/specified by implementing changes noted as modifications.

Many of the queries in this article require you to specify one participant by their participantidentifier. If there are multiple participants that you'd like included in the output, you can change participantidentifer = '#' to participantidentifier in ('#1', '#2'), replacing #1 and #2 with the participant identifiers you want. You can keep adding additional participants with a comma and single quotes.

These queries are also available directly in Export Explorer by clicking on the "Library" button in the Queries menu. Select any query to create a new tab containing that query, make any modifications needed, and run the query.

Analytic Events

Email Events

Description: As written, the SQL below will list the count of all email-related events during the entire duration of the project for all participants. Based on the Analytics Events Export.

SQL: Email Events

SELECT
  type,
  COUNT(*) AS number
FROM
  analyticsevents ae
WHERE
  ae.type LIKE 'Email%' --change to '%' to see all notifications
  -- AND participantidentifier = 'MDH-1234' -- change to a participant identifier of interest
  -- AND ae.timestamp BETWEEN DATE '2024-03-01' AND DATE '2024-06-01' -- set the dates of interest
GROUP BY
  Type

Modifications:

Change 'Email%' to '%' to show all analytics events.
Change 'Email%' to 'Sms%' to show SMS events instead of email events.
Change Like 'Email%' to in (['EventType1',….]) and replace ['EventType1',…] with a list of event types (e.g., in ('SmsFailure', 'SmsOptout')).
Remove -- before -- AND participantidentifier = 'MDH-1234' and replace 'MDH-1234' with an identifier of interest to only show for this identifier.
Remove -- before -- AND ae.timestamp BETWEEN DATE '2024-03-01' AND DATE '2024-06-01' and change the dates to the dates of interest to only show events for a specific time frame.

Sample Output:

Email Events by Month

Description: As written, the SQL below will list the count of all email-related events during the entire duration of the project for all participants by month and year. Based on the Analytics Events Export.

SQL: Email Events by Month

SELECT
  TYPE,
  CONCAT(
    CAST(YEAR(timestamp) AS VARCHAR),
    '-',
    DATE_FORMAT(timestamp, '%M')
  ) AS year_month,
  COUNT(*) AS number
FROM
  analyticsevents ae
WHERE
  ae.type LIKE 'Email%' --change to '%' to see all notifications
  -- AND participantidentifier = 'MDH-1234' -- change to a participant identifier of interest
  -- AND ae.timestamp BETWEEN DATE '2024-03-01' AND DATE '2024-06-01' -- set the dates
GROUP BY
  TYPE,
  YEAR(timestamp),
  DATE_FORMAT(timestamp, '%M')
ORDER BY
  TYPE,
  YEAR(timestamp),
  MIN(timestamp)

Modifications:

Change 'Email%' to ‘%’ to show all analytics events.
Change 'Email%' to 'Sms%' to show SMS events instead of email events.
Change Like 'Email%' to in (['EventType1',….]) and replace ['EventType1',…] with a list of event types (e.g., in ('SmsFailure', 'SmsOptout')).
Remove -- before -- AND participantidentifier = 'MDH-1234' and replace 'MDH-1234' with an identifier of interest to only show for this identifier.
Remove -- before -- AND ae.timestamp BETWEEN DATE '2024-03-01' AND DATE '2024-06-01' and change the dates to the dates of interest to only show events for a specific time frame.

Sample Output:

Bounced Email Addresses

Description: As written, the SQL below will list all email addresses that have bounced during the duration of the project. Based on the Analytics Events Export.

SQL: Bounced Email Addresses

SELECT
  participantidentifier,
  timestamp,
  element_at(PROPERTIES, 'MailDestination') AS bounced_email,
  element_at(PROPERTIES, 'BounceType') AS bounce_Type
FROM
  analyticsevents ae
WHERE
  TYPE = 'EmailBounce'
-- AND ae.participantidentifier = 'MDH-1234' -- change to a participant identifier of interest
--AND ae.timestamp BETWEEN DATE '2024-03-01' AND DATE '2024-06-01' -- set the dates
ORDER BY
  timestamp DESC

Modifications:

Remove -- before -- AND participantidentifier = 'MDH-1234' and replace 'MDH-1234' with an identifier of interest to only show for this identifier.
Remove -- before -- AND ae.timestamp BETWEEN DATE '2024-03-01' AND DATE '2024-06-01' and change the dates to the dates of interest to only show events for a specific time frame.
Remove -- before -- AND ae.timestamp BETWEEN DATE '2024-03-01' AND DATE '2024-06-01' and change to AND ae.timestamp > '2024-09-01' to show only bounced email addresses after a certain date.

Sample Output:

Surveys & Survey Tasks

Readable Survey Results

Description: As written, the SQL below will list all survey results in a tabular format that can be downloaded as a CSV for easy review in tools such as Microsoft Excel. This includes both the questions and answers for each survey step. Based on the Survey Results Export.

SQL: Readable Survey Results

SELECT
  sqr.participantidentifier,
  sr.surveyname,
  sr.surveyversion,
  ssr.stepidentifier,
  sqr.resultidentifier,
  sd.questiontext,
  sd.answerformat,
  sd.answerchoices,
  sqr.answers,
  sqr.startdate,
  sqr.enddate
FROM
  surveyquestionresults sqr
  JOIN surveystepresults ssr ON ssr.surveystepresultkey = sqr.surveystepresultkey
  JOIN surveyresults sr ON sr.surveyresultkey = sqr.surveyresultkey
  LEFT JOIN surveydictionary sd ON sd.surveykey = sr.surveykey
    AND sd.surveyversion = sr.surveyversion
    AND sd.stepidentifier = ssr.stepidentifier
    AND sd.resultidentifier = sqr.resultidentifier
    -- where sqr.startdate BETWEEN DATE '2024-02-01' AND DATE '2024-02-28'
    -- where sr.surveyname=''
    -- where sqr.participantidentifier='MDH-1234'
ORDER BY
  sqr.startdate DESC,
  sqr.participantidentifier ASC,
  sr.surveyname ASC

Modifications:

Remove -- before -- where sqr.startdate BETWEEN DATE '2024-02-01' AND DATE '2024-02-28' and change the dates to the dates of interest to only show results for a specific time frame.
Remove -- before -- where sr.surveyname='' and insert the name of a survey between '' to list results only for that survey.
If you wish to use multiple criteria, then only keep the first where and change the remaining where to AND.

Sample Output:

Participant Details

Custom Field Changes

Description: As written, the SQL below will list the various values of a custom field though time that have been performed by a coordinator for a specific participant. Based on the Participant Data Export.

The SQL below must be modified for it to work for the custom fields in your project.

SQL: Custom Field Changes


SELECT
  participantidentifier,
  USER,
  TRIM([CustomFieldName]) [CustomFieldName],
  MIN(timestamp) AS change_date
FROM
  (
    SELECT
      timestamp,
      PROPERTIES ['User'] AS USER,
      participantidentifier,
      CAST(
        json_extract_scalar(
          PROPERTIES ['Change'],
          '$.CustomFields.[CustomFieldName]'
        ) AS varchar
      ) AS CompletedDate
    FROM
      analyticsevents
    WHERE
      TYPE = 'ParticipantUpdated'
      AND PROPERTIES ['UserType'] = 'Coordinator'
  ) x
WHERE
  participantidentifier = 'MDH-1234' -- update participant identifier here
  AND CompletedDate IS NOT NULL
GROUP BY
  participantidentifier,
  USER,
  TRIM([CustomFieldName])
ORDER BY
  participantidentifier,
  MIN(timestamp) DESC

Required Modifications:

Each of the following must be updated for this to work in your project:

Replace all four instance of [CustomFieldName] with the name of your customfield (e.g., TRIM([CustomFieldName]) [CustomFieldName], would become TRIM(MyCustomField) MyCustomField.
Replace MDH-1234 in one instance of participant identifier = 'MDH-1234' with the participant identifier of interest.

Sample Output:

Device Data

Apple Health (HealthKit) Samples

Description: As written, the SQL will show sample counts, first sample date, and most recent sample date for each sample type for all participants for all time. Based on the Apple HealthKitV2 Samples Export.

SQL: Apple Health (HealthKit) Samples

SELECT
  devicemodel,
  TYPE,
  count(DISTINCT healthkitsamplekey) Samples,
  max(DATE) most_recent,
  min(DATE) oldest
FROM
  healthkitv2samples
WHERE
  devicemodel IS NOT NULL
  --and participantidentifier ='MDH-1234'
  --and date BETWEEN DATE '2023-01-01' AND DATE '2024-09-28'
GROUP BY
  devicemodel,
  TYPE
ORDER BY
  devicemodel DESC

Modifications:

Remove -- before -- and participantidentifier ='MDH-1234' and change the identifier to your indentifier of interest to look at a specific participant.
Remove -- before --and date BETWEEN DATE '2023-01-01' AND DATE '2024-09-28' and change the dates to the dates of interest to only show results for a specific time frame.

Sample Output:

Activities

Description: As written, the SQL will show activity counts for all participants for all time across garmin, applehealth, and fitbit workouts. Based on the Apple HealthKitV2 Workouts Export, Fitbit Activity Log Export, and Garmin Activity Summary Export.

SQL: Activities

SELECT
  Source,
  activitytype,
  count(*) AS number
FROM
  (
    SELECT
      'Garmin' Source,
      Upper(activitytype) activitytype,
      participantidentifier
    FROM
      garminactivitysummary
    UNION
    SELECT
      'FitBit' Source,
      UPPER(activityname) activitytype,
      participantidentifier
    FROM
      fitbitactivitylogs
    UNION
    SELECT
      'Healthkit' Source,
      Upper(activitytype) activitytype,
      participantidentifier
    FROM
      healthkitv2workouts
    ) x
--WHERE x.participantidentifier = 'MDH-1234'
--where source='Garmin'
GROUP BY
  Source,
  activitytype
ORDER BY
  Activitytype

Modifications:

Remove -- before WHERE x.participantidentifier = 'MDH-1234' and change the identifier to your identifier of interest to look at a specific participant.
Remove -- before --where source='Garmin' and update the source to Garmin, FitBit, or Healthkit. (capitlization matters).
Remove UNION SELECT 'Healthkit' Source, Upper(activitytype) activitytype, participantidentifier FROM healthkitv2workouts if your project is not configured to collect Workout data from Apple Health.

Sample Output:

Electronic Health Record Data

Medical Record Connections

Description: As written, the SQL will show the participant counts, most recent connection, and oldest connection for all Provider and External Health Plan (participant mediated) connections across all time. Based on the External Accounts Data Export.

SQL: Medical Record Connections

SELECT
  fhr.providername,
  ea.providercategory,
  count(DISTINCT fhr.participantidentifier) participants,
  max(ea.connectiondate) most_recent,
  min(ea.connectiondate) oldest
FROM
  "fhirbulkparticipantidentifiersmapping" fhr
  LEFT OUTER JOIN externalaccounts ea ON ea.providername = fhr.providername
WHERE
providercategory IN ('Provider', 'Health Plan')
-- and fhr.participantidentifier='MDH-1234'
GROUP BY
  fhr.providername,
  ea.providercategory
ORDER BY
  count(DISTINCT fhr.participantidentifier) DESC

Modifications:

Remove -- before -- and fhr.participantidentifier='MDH-1234' and replace MDH-1234 with the identifier of interest.

Sample Output:

FHIR Resources

Description: As written, the SQL will show the count of participants with each diagnosis obtained from either claims or provider electronic health records across all time and sorted from most common to least common. Based on the EHR Export.

SQL: FHIR Resources

SELECT
  resourcetype,
  code,
  display,
  count(DISTINCT participantidentifier) AS participants
FROM
  (
    SELECT
      fb.resourcetype,
      coding_item['code'] AS code,
      coding_item['display'] AS display,
      coding_item['userSelected'] AS userSelected,
      MAP.participantidentifier
    FROM
      fhirbulk fb
    JOIN fhirbulkparticipantidentifiersmapping MAP ON MAP.fhirpatientid = fb.fhirpatientid
    CROSS JOIN UNNEST (
      CAST(
        json_extract(resource, '$.code.coding') AS ARRAY < MAP < varchar,
        varchar > >
      )
    ) AS t (coding_item)
  WHERE
    resourcetype IN ('Condition') -- ‘Condition’=Diagnosis, 'Observation'=labs and vitals 'Procedure'=procedures
  ) x
WHERE
  userSelected = 'false'
GROUP BY
  resourcetype,
  code,
  display
ORDER BY
  count(DISTINCT participantidentifier) DESC

Modifications:

Choose one or multiple resources by updating IN ('Condition') to have one or multiple items from ‘Condition’=Diagnosis, 'Observation'=labs and vitals 'Procedure'=procedures (e.g., IN (‘Condition’, ‘Procedure’)).

Sample Output:

Segments

Segment History

Description: As written, the SQL will show the history of all segments that a given participant has been in, including the date they were added and the date they were removed. Based on the Segment Participant History Data Export.

SQL: Segment History

SELECT
  sph.participantidentifier,
  sph.segmentname,
  sph.adddate,
  sph.removedate
FROM
  segmentparticipanthistory sph
WHERE
  sph.participantidentifier = 'MDH-1234'
  --and sph.segmentname='Device (most recent)'

Modifications:

Update sph.participantidentifier = 'MDH-1234' to include your identifier of interest to look at a specific participant.
Remove -- before -- and sph.segmentname='Device (most recent)' and replace the segmentname with the segment name of interest.

Sample Output:

All Current Segments

Description: As written, the SQL will show the all of the segments that a given participant is currently on including, the date they were added. Based on the Segment Participants Data Export.

SQL: All Current Segments

SELECT
  sp.participantidentifier,
  sp. segmentname,
  sp.adddate
FROM
  segmentparticipants sp
WHERE
  sp.participantidentifier = 'MDH-1234'
  --and sph.segmentname='Device (most recent)'

Modifications:

Update sp.participantidentifier = 'MDH-1234' to include your identifier of interest to look at a specific participant.
Remove -- before -- and sph.segmentname='Device (most recent)' and replace the segmentname with the segment name of interest.

Sample Output: