Biospecimen Metrics Request- Package Conditions

[erincschwartz]

Please complete the following updates to the Weekly Biospecimen Metrics Report

1. Update the title of section 5 to: Baseline Collection-To-Receipt Times and Package Conditions

2. Create a new metric, which will be Table 5.5 (placed at the end of the section after Figure 5.13) showing a basic frequency count of all the site package conditions recorded by BPTL.

Title: Table 5.5: Baseline- Package Conditions Reported for Site Shipments
Column headers: Condition and Total Received Packages with Condition
Footnote: Note: The number of package conditions recorded may exceed the number of packages received from sites, as more than one condition may be selected.

This table will look similar to the following:

Current package conditions are:

Package in Good Condition (shipper and specimens) Concept ID: 238268405.679749262
Manifest/Vial/Paperwork info do not match Concept ID: 238268405.922995819
Improper Packaging (i.e. missing cold packs, filler, etc) Concept ID: 238268405.847410060
Cold Packs - Warm Concept ID: 238268405.595987358
Damaged Vials Concept ID: 238268405.387564837
Returned Empty Vials Concept ID: 238268405.631290535
No Connect Label on Vials Concept ID: 238268405.399948893
Shipment Delay Concept ID: 238268405.958000780
Damaged Shipper (outer and/or inner) Concept ID: 238268405.678483571
Other Concept ID: 238268405.933646000

[KELSEYDOWLING7]

@erincschwartz Do we know when we expect these variables to be live? I don't even see these variables in stage yet

[KELSEYDOWLING7]

Also is there any denominator or conditionality I need to include? Research vs clinical? I'm assuming only finalized collections and no null site.

[erincschwartz]

@KELSEYDOWLING7 Sorry, I did not realize you commented on this issue with questions.

We have been collecting data on site package conditions since the beginning of the study, see Boxes table. The conditions are nested under <238268405> Select Package Condition - Site Shipment

As for conditionality, all packages in Boxes table with <333524031>BioBPTL_ShipReceived_v1r0 (Shipment Received)= yes should be included.

Please let me know if more information is needed.

[KELSEYDOWLING7]

Ah ok, so this will be the first time we use the Boxes table in this report code. Can you remind me what overlapping variable we have between Boxes and Biospecimen? Or Boxes and Participants? Essentially how are you able to determine which Box belongs to which participant? The Boxes table doesn't have Connect_ID, token, or Collection ID (820476880)

[erincschwartz]

We do not need or want to associate this metric with participants or collections. We just want a count of conditions reported for each box. If this does not fit into the structure of the overall report, please run as ad hoc report (1x month is fine).

[KELSEYDOWLING7]

That would be great. This table would have no relation to the rest of the code and I would have a strong preference for this to be separate.

It would also be easiest to export this directly from BigQuery to Box instead of a whole formal report for one table. Please let me know if you have any concerns with an excel version of this table.

The variable needs to be corrected int he flattening and Eddie is working on that now.