Skip to main content

Export Explorer Query Library

  • Updated

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.

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.

[details]

[summary]SQL: Email Events[/summary]

SELECT
  type,
  COUNT(*) AS number
FROM
  analyticsevents ae
WHERE
  ae.type LIKE 'Email%' --change to '%' to see all notifications
  -- AND participantidentifier = 'fffd1b82-d4e5-4373-b365-1635b7716d38' -- 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

[/details]

Modifications:

  • Change `'Email%'` to `'%'` and perform for all analytic 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 = 'fffd1b82-d4e5-4373-b365-1635b7716d38'` and replace `'fffd1b82-d4e5-4373-b365-1635b7716d38'` 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:

A white rectangular object with a green border Description automatically generated

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.

[details]

[summary]SQL: Email Events by Month[/summary]

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 = 'fffd1b82-d4e5-4373-b365-1635b7716d38' -- 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)

[/details]

Modifications:

  • Change `'Email%'` to `‘%’` and perform for all analytic 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 = 'fffd1b82-d4e5-4373-b365-1635b7716d38'` and replace `'fffd1b82-d4e5-4373-b365-1635b7716d38'` 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:

A white rectangular object with a yellow border Description automatically generated

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.

[details]

[summary]SQL: Bounced Email Addresses[/summary]

SELECT
  participantid,
  timestamp,
  element_at(PROPERTIES, 'MailDestination') AS bounced_email,
  element_at(PROPERTIES, 'BounceType') AS bounce_Type
FROM
  analyticsevents ae
WHERE
  TYPE = 'EmailBounce'
-- AND ae.participantidentifier = 'fffd1b82-d4e5-4373-b365-1635b7716d38' -- 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

[/details]

Modifications:

  • Remove `--` before `-- AND participantidentifier = 'fffd1b82-d4e5-4373-b365-1635b7716d38'` and replace `'fffd1b82-d4e5-4373-b365-1635b7716d38'` 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:

Group 37.png

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 Survery Results Export.

[details]

[summary]SQL: Readable Survey Results[/summary]

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='MDH0113-12'
ORDER BY
  sqr.startdate DESC,
  sqr.participantidentifier ASC,
  sr.surveyname ASC

[/details]

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:

A screenshot of a computer Description automatically generated

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.

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

[details]

[summary]SQL: Custom Field Changes[/summary]

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 = '[Participant Identifier]' -- update participant identifier here
  AND CompletedDate IS NOT NULL
GROUP BY
  participantidentifier,
  USER,
  TRIM([CompletedDate])
ORDER BY
  participantidentifier,
  MIN(timestamp) DESC

[/details]

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 the one instance of `'[Participant Identifier]'` with the participant identifier of interest (e.g., `'[Participant Identifier]'` would become `'MDH-99-999'`).

Sample Output:

Group 38.png

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.

[details]

[summary]SQL: Apple Health (HeathKit) Samples[/summary]

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

[/details]

Modifications:

  • Remove `--` before `-- and participantidentifier ='be37f986-2622-ec11-aaa4-9041aafba4ed'` 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:

A screenshot of a computer Description automatically generated

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.

[details]

[summary]SQL: Activities[/summary]

SELECT
  Source,
  activitytype,
  count(*) AS number
FROM
  (
    SELECT
      'Garmin' Source,
      Upper(activitytype) activitytype,
      participantidentifier
    FROM
      garminactivitysummary
    UNION
    SELECT
      'FitBit' Source,
      UPPER(logtype) activitytype,
      participantidentifier
    FROM
      fitbitactivitylogs
    UNION
    SELECT
      'Healthkit' Source,
      Upper(activitytype) activitytype,
      participantidentifier
    FROM
      healthkitv2workouts
    ) x
--WHERE x.participantidentifier = '65cd1aac-9c36-4923-8ae6-f94871f157d5'
--where source='Garmin'
GROUP BY
  Source,
  activitytype
ORDER BY
  Activitytype

[/details]

Modifications:

  • Remove `--` before `WHERE x.participantidentifier = '65cd1aac-9c36-4923-8ae6-f94871f157d5'` 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).

Sample Output:

A white rectangular object with a green border Description automatically generated

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.

[details]

[summary]SQL: Medical Record Connections[/summary]

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='[Insert Participant Identifier here]'
GROUP BY
  fhr.providername,
  ea.providercategory
ORDER BY
  count(DISTINCT fhr.participantidentifier) DESC

[/details]

Modifications:

  • Remove `--` before `-- and fhr.participantidentifier='[Insert Participant Identifier here]'` and replace `[Insert Participant Identifier here]` with the identifier of interest (e.g., `fhr.participantidentifier='MDH-99-001'`).

Sample Output:

A screenshot of a computer Description automatically generated

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.

[details]

[summary]SQL: FHIR Resources[/summary]

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

[/details]

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:

A screenshot of a computer Description automatically generated

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.

[details]

[summary]SQL: Segment History[/summary]

SELECT
  sph.participantidentifier,
  sph.segmentname,
  sph.adddate,
  sph.removedate
FROM
  segmentparticipanthistory sph
WHERE
  sph.participantidentifier = '65cd1aac-9c36-4923-8ae6-f94871f157d5'
  --and sph.segmentname='Device (most recent)'

[/details]

Modifications:

  • Update `sph.participantidentifier = '65cd1aac-9c36-4923-8ae6-f94871f157d5'` 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:

A screenshot of a computer Description automatically generated

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.

[details]

[summary]SQL: All Current Segments[/summary]

SELECT
  sp.participantidentifier,
  sp. segmentname,
  sp.adddate
FROM
  segmentparticipants sp
WHERE
  sp.participantidentifier = '65cd1aac-9c36-4923-8ae6-f94871f157d5'
  --and sph.segmentname='Device (most recent)'

[/details]

Modifications:

  • Update `sp.participantidentifier = '65cd1aac-9c36-4923-8ae6-f94871f157d5'` 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:

A white rectangular object with a green border Description automatically generated with medium confidence

Was this article helpful?