Custom question templates

This guide is intended for W2H implementation leads. It includes links to questions that have been created for other programs and studies so they can be copied and modified, or modeled after.

Enrollment

Pie chart of participant status using variables

1. Create a list variable with values like Deceased, Graduated, Extended

2. Enable the Ppt Variables ETL for the study and wait overnight

3. Create a pie chart based off of that variable

Example: https://lens.waytohealth.org/question/1379-count-by-location

Open

(Note: this question was made for COVID Watch where it worked differently. The instructions above are how to replicate this.)

Enrollment funnel

Sample question: https://lens.waytohealth.org/question/108-funnel

Enrollment funnels require SQL. It’s fairly simple SQL though.

Instructions:

1. Define (with study team) what are the points they want to be in the funnel. Typically it’ll be something like total accounts, # of people who completed step 3 in enrollment, # of people who completed step 5, # of people randomized, etc.

2. Write a SQL query selecting a label for each point as well as a number. The snippet below is an example. In this case, everything from the Be Active funnel comes from the enrollment_be_active table, but it doesn’t have to - you could also include e.g. how many participants triggered the Received Device incident or whatever.

   select 'Total', count(*) FROM `enrollment_be_active` UNION SELECT 'Informed Consent', sum(`informed_consent` = 'Complete') FROM `enrollment_be_active` UNION SELECT 'Enrollment Survey', sum(`enrollment_survey` = 'Complete') FROM `enrollment_be_active`

3. If you created the question from scratch (rather than copying an existing funnel), change visualization type to funnel.

Open

Enrollment map

These aren’t usually useful. But they’re easy. If someone wants them, they’re easy to make.

Sample: https://lens.waytohealth.org/question/250-be-active-patients-map

Sample: https://lens.waytohealth.org/question/1093-all-by-zip

Patient activity

Patient Adherence

For some programs, there will be a fairly easy metric that makes sense to track - if the program is structured around daily conversations, it’s easy enough to make a graph showing completion percentage of the Daily Conversation Event. If it’s structured around event blocks/modules and there’s a single event whose completion means that they complied with the module, that works too. If there isn’t an obvious daily/weekly/monthly adherence metric, skip this.

Example: 1193 which is summarizing raw data from 1192

This will usually require custom SQL, since we need a raw data view consisting of one row per ppt per day. Ideally the event can give us that, though if we’re gathering unprompted data it might not work out nicely. If we’re doing a lot of these, we should come up with a standard ETL to make it easier.

Patient data feed

For programs structured around an inbound data feed (e.g. results reporting, a program that gets a feed of red/green passes, etc.) it may make sense to track number of form submissions per day, or even tracking results, e.g. grouping by test_result and date.

Example: https://lens.waytohealth.org/question/1020-quaker-results-per-day

Instructions:

1. Turn on data source ETL for the source in question and wait overnight.

2. Create a new question from that table.

3. Typically you’ll want to group by Timestamp and count rows. Sometimes you’ll want to also group by result. Area graph, stacked bar chart, line chart, are all acceptable options.

Milestone completion

Refresh frequency: Daily

Dashboard category: 3 - on demand

Note: “milestone” is defined as a non-repeating multi-day event. Typically only applies to research. For clinical programs where you want to track e.g. how many patients have graduated, you would use the “ppt status v2 using variables” approach above.

Instructions:

• Enable the Milestones ETL from study advanced config, and wait overnight for it to run

• Use https://lens.waytohealth.org/question/131-6-month-survey/notebook as an example, use the Milestones table for the study, group by the event in question.

  • The table has a column for each “milestone” event, so if you want to know how many people have completed/etc the 12 month survey, select that in the Group By on the bottom right, and then count on the bottom left.

• You can also filter out certain statuses with the top purple filter icon (the one right below the table name - not the one right above the Visualize button) if you want to hide certain statuses from the output, or if you only want e.g. the 12 month survey status for people that already completed the 6 month.

• You may want to add a mini-bar-graph to the display for added sexiness. Settings → gear next to Count → toggle “Show a mini bar chart”.

Patient satisfaction (Net Promoter Score)

For any study that gathers NPS during the program.

Instruction for all of the below:

• Turn on the Data Source ETL for the source that has the NPS data (e.g. End of Study Survey)

  • Wait overnight

  • This will create a table like data_STUDYNAME_end_of_study_survey with columns for participant ID and each survey response

NPS overall (Single Number)

Instructions:

• Turn on the ETL per instruction above, and wait overnight.

• Click “Ask a question”, then select “Custom Question”. Find the data source by searching for the table name (it will be under Analytics Main). The format will be data_STUDYNAME_end_of_study_survey

• Toggle to view in SQL, then convert to SQL.

• Copy the custom SQL below, replacing data_STUDYNAME_end_of_study_survey with the actual table name, and question_asking_for_nps with the question/column name.

  SELECT CAST((( SUM(CASE WHEN question_asking_for_nps BETWEEN 9 AND 10 THEN 1 ELSE 0 END) - SUM(CASE WHEN question_asking_for_nps BETWEEN 0 AND 6 THEN 1 ELSE 0 END) ) / COUNT(*) * 100) AS int) AS nps FROM data_STUDYNAME_end_of_study_survey WHERE question_asking_for_nps IS NOT NULL

• Select the visualization type as Gauge, with the ranges configured as follows:

   

• Add to dashboard

NPS Score distribution

Example: https://lens.waytohealth.org/question/628-nps-distribution

Instructions:

• Turn on the ETL per instruction above, and wait overnight.

• Start from the sample question (628), but change the SQL to something like this. Replace data_STUDYNAME_end_of_study_survey with the actual table name, and question_asking_for_nps with the question/column name.

  SELECT cast(nps_rating as int) as nps_rating, count(*) as nps_rate_count, case when nps_rating between 9 and 10 then 'Promoters' when nps_rating between 7 and 8 then 'Passives' when nps_rating between 0 and 6 then 'Detractors' end as friendly_nps_rating from data_STUDYNAME_end_of_study_survey where nps_rating is not null group by cast(nps_rating as int) order by cast(nps_rating as int)

• Add to dashboard

NPS by month

Example: https://lens.waytohealth.org/question/1339-nps-by-month-no-filters

Instructions:

• Turn on the ETL per instruction above, and wait overnight.

• Start from the sample question (1339), but change the SQL to something like this. Replace data_STUDYNAME_end_of_study_survey with the actual table name, and question_asking_for_nps with the question/column name.