Metrics Request- Add Table for Count of Uninvited Recruits by Site

[mnataraj92]

Hi @KELSEYDOWLING7

Could you please add a table to the Operations Report for count of uninvited recruits by site, and @jacobmpeters to account for this in the QC? These individuals should get deleted everyday once they're marked 'yes' for the Uninvited Recruits variable.

Variables: Uninvited recruits that have been added to the data frame or made active by mistake (RcrtSI_UninvitedRcts_v1r0). Concept ID d_288972510. Site: Concept ID d_827220437.

[mnataraj92]

@brotzmanmj are there any restrictions on which participants this should be run on? And title specifications for the table?

[jacobmpeters]

The QC Report that I set up a few months ago is essentially a SQL query that generates a CSV with the fields token, Connect_ID, RcrtSI_UninvitedRcts_v1r0, Site

Participants are included when RcrtSI_UninvitedRcts_v1r0 = "Yes".

I can set this up as a scheduled query that exports a CSV to Box on a schedule. Which box folder should this report be delivered to? Should it be run daily or weekly?

SELECT
  token,
  Connect_ID,
  CASE
    WHEN d_288972510 = '353358909' THEN 'Yes'
    WHEN d_288972510 = '104430631' THEN 'No'
    ELSE 'NA'
  END AS RcrtSI_UninvitedRcts_v1r0,
  CASE
    WHEN d_827220437 = '531629870' THEN 'Health Partners'
    WHEN d_827220437 = '548392715' THEN 'Henry Ford'
    WHEN d_827220437 = '125001209' THEN 'KP-Colorado'
    WHEN d_827220437 = '327912200' THEN 'KP-Georgia'
    WHEN d_827220437 = '300267574' THEN 'KP-Hawaii'
    WHEN d_827220437 = '452412599' THEN 'KP-Northwest'
    WHEN d_827220437 = '303349821' THEN 'Marshfield'
    WHEN d_827220437 = '657167265' THEN 'Sanford'
    WHEN d_827220437 = '809703864' THEN 'UChicago'
    WHEN d_827220437 = '472940358' THEN 'Baylor Scott & White Health'
    WHEN d_827220437 = '517700004' THEN 'NCI'
    WHEN d_827220437 = '181769837' THEN 'OTHER'
    ELSE 'NA'
END AS Site
FROM
  `nih-nci-dceg-connect-prod-6d04.FlatConnect.participants_JP`
WHERE
  d_288972510='353358909'

[brotzmanmj]

Now that we are going live with deleting uninvited recruits, wanted to make sure we have in place what we need to monitor this. Is this done? @KELSEYDOWLING7 @jacobmpeters @mnataraj92

[jacobmpeters]

@brotzmanmj @mnataraj92 I added a rule to the Recruitment QAQC to flag any participants for which RcrtSI_UninvitedRcts_v1r0 [288972510] = YES [353358909] so that we are aware if there are any undeleted "uninvited recruits".

Is this sufficient?

The alternative would be to implement the report I mentioned above (comment from Feb 6) that generates a CSV of the token, Connect_ID, RcrtSI_UninvitedRcts_v1r0, and Site for any uninvited recruits (d_288972510='353358909'). I would need just need to know which Box folder to deliver it to and when it should be run.

[KELSEYDOWLING7]

@brotzmanmj We currently have a this table in the Operations report for Invited vs Uninvited counts per Site

[brotzmanmj]

I defer to @mnataraj92 as to how she prefers to monitor this

[brotzmanmj]

@KELSEYDOWLING7 the thing about the table is that it should always be '0' for all sites because the data get deleted at 1am the same night when they send us the flag. So on the one hand, it's good to have in the operations report because that way if it is ever not '0' then we know the 1am process is probably not working. But I don't think that in itself is sufficient for Madhuri to monitor how many uninvited flags we are getting and from which sites.

[KELSEYDOWLING7]

@brotzmanmj That makes sense. Do you think the QC rule is sufficient for monitoring or do you want a new table created in the operations report?

[brotzmanmj]

I don't think there is anything we can add to the operations report that would capture this. The only thing I can think of is to run what Jake suggested: The alternative would be to implement the report I mentioned above (comment from Feb 6) that generates a CSV of the token, Connect_ID, RcrtSI_UninvitedRcts_v1r0, and Site for any uninvited recruits (d_288972510='353358909'). I would need just need to know which Box folder to deliver it to and when it should be run.

And have it run every day in an automated manner right before 1am eastern, say at midnight each day. Does that make sense? Then it would catch any new uninvited flags that have been submitted each day and by whom, before the records are deleted.

I'm slightly less concerned about this honestly now that we have the additional restrictions on the API for this flag, but I think we would still like this visibility into which sites are sending it and to what extent.

Thoughts?

[mnataraj92]

@brotzmanmj I agree, I like Jake's idea of the report and having it run at midnight. I think that'd be an easier way for me to monitor it too since the QC report is just run weekly.

As far as where it should go- would the QC folder make sense? Or if we also want that in the operations report folder?

[brotzmanmj]

@mnataraj92 I think the QC folder makes sense

[jacobmpeters]

@mnataraj92 @brotzmanmj I'll set up that report to run daily and have it automatically delivered to a subdirectory in the Recruitment QC folder.

I'll let you know here when that is done.