How to Use the Loyalty Cohort Tool

Create a cohort filter, defining the patients on which to compute loyalty scores. The three columns are:
- patient_num: patient_num from the i2b2 tables
- cohort_name: a name for the cohort. You can optionally compute several cohorts separately, but specifying different values for this.
- index_dt: a date which is a reference point in time at which to compute the loyalty score. It is suggested to select a common recent point in time or to choose each patient's most recent visit date, for example.
Run the USP_DT_LOYALTYCOHORT stored procedure with the following parameters.

Parameter	Example / Default Value	Description
@site	DEMO (no default)	A short (e.g., 3-character) identifier for your site.
@LOOKBACK_YEARS	1	A number of years for lookback. The original algorithm used 1 year, but we have found 3- or 5-years are more accurate, because some preventative care like PSA and Pap Smears do not occur every year.
@DEMOGRAPHIC_FACTS	0	Set to 1 if demographic facts (e.g., age, sex) are stored in the fact table (rather than patient_dimension).
@GENDERED	0	Set to 1 to create a summary table (and cutoffs) that do not include male-only facts for female patients in the denominator and vice-versa.
@COHORT_FILTER	@cfilter (no default)	Specify a table variable of (PATIENT_NUM, COHORT_NAME) in the second parameter.
@OUTPUT	1	If 1, pretty-print the loyalty_dev_summary percentages after the run.

Simple Example

The following examples create a cohort filter of the first 1000 rows in patient_dimension, and computes the loyalty cohort with an index date of 01 Feb 2021 and a one-year lookback.

MSSQL:

    DECLARE @cfilter UDT_DT_LOYALTY_COHORTFILTER;

     INSERT INTO @cfilter (PATIENT_NUM, COHORT_NAME, INDEX_DT)
     SELECT top 1000 psc.PATIENT_NUM, 'TEST', CONVERT(DATETIME, '20210201 ')
      from patient_dimension psc
     EXEC USP_DT_LOYALTYCOHORT @SITE='STE', @LOOKBACK_YEARS=1,  @demographic_facts=0, @GENDERED=0, @COHORT_FILTER=@cfilter, @output=0

  Note that the cohort filter must be created in the same transaction as the procedure execution.

Oracle:

     DELETE FROM DT_LOYALTY_COHORTFILTER WHERE COHORT_NAME='TEST'

     INSERT INTO DT_LOYALTY_COHORTFILTER (PATIENT_NUM, COHORT_NAME, INDEX_DT)
     SELECT psc.PATIENT_NUM, 'TEST2', to_date('2021-02-01', 'YYYY-MM-DD')
      from patient_dimension psc where rownum < 1000;
       BEGIN USP_DT_LOYALTYCOHORT('TEST', 1, 0, 0, 0); END;

Execute the following statement on your database to print the output that can be shared: select * from loyalty_dev_summary where Summary_Description='PercentOfSubjects'
This will create two tables on your db, DT_LOYALTY_RESULT (line level data with variables and score presented for each patient) and DT_LOYALTY_RESULT_SUMMARY (summary table).

Advanced Example

A cohort filter can be created from a patient set query in i2b2. Below is an example that looks up the cohort from a patient set query named "patient set test1" run by user "demouser".

MSSQL:

DECLARE @cfilter UDT_DT_LOYALTY_COHORTFILTER;

INSERT INTO @cfilter (PATIENT_NUM, COHORT_NAME, INDEX_DT)
select patient_num, 'EXAMPLE', CONVERT(DATETIME,'20200101') AS index_dt from
(select result.DESCRIPTION,pset.PATIENT_NUM from QT_QUERY_MASTER master
inner join QT_QUERY_INSTANCE instance on master.QUERY_MASTER_ID=instance.QUERY_MASTER_ID 
inner join QT_QUERY_RESULT_INSTANCE result on instance.QUERY_INSTANCE_ID=result.QUERY_INSTANCE_ID
inner join QT_PATIENT_SET_COLLECTION pset on result.RESULT_INSTANCE_ID=pset.RESULT_INSTANCE_ID
where master.name='patient set test1' and master.user_id='demouser') x

-- Edit for your site
EXEC [dbo].[USP_DT_LOYALTYCOHORT] @site='DEMO', @LOOKBACK_YEARS=3, @DEMOGRAPHIC_FACTS=0, @GENDERED=1, @COHORT_FILTER=@cfilter, @OUTPUT=0

Oracle:

1. Populate DT_LOYALTY_COHORTFILTER with patient cohort to be included:


INSERT INTO DT_LOYALTY_COHORTFILTER (PATIENT_NUM, COHORT_NAME, INDEX_DT)
SELECT psc.PATIENT_NUM, 'TEST', to_date('2021-02-01', 'YYYY-MM-DD')
FROM (
SELECT PSC.PATIENT_NUM
FROM QT_QUERY_MASTER QM
INNER JOIN QT_QUERY_INSTANCE QI ON QI.QUERY_MASTER_ID=QM.QUERY_MASTER_ID
INNER JOIN QT_QUERY_RESULT_INSTANCE QRI ON QRI.QUERY_INSTANCE_ID=RESULT.QUERY_INSTANCE_ID
INNER JOIN QT_PATIENT_SET_COLLECTION PSC ON PSC.RESULT_INSTANCE_ID=QRI.RESULT_INSTANCE_ID
WHERE QM.NAME='patient set test1' AND QM.USER_ID='demouser') X;

2. Execute USP_DT_LOYALTYCOHORT stored procedure

BEGIN USP_DT_LOYALTYCOHORT('TEST', 1, 0, 0, 0); END;
This will create two tables on your db, DT_LOYALTY_RESULT (line level data with variables and score presented for each patient) and DT_LOYALTY_RESULT_SUMMARY (summary table).