How to crack open COINS

Dan Fitch


  • What is Piggybank?
  • How does it work?
  • What can it do?
  • Examples
  • Discussion

About Piggybank

  • Ruby library by Nate Vack
  • "Easily" scriptable
  • Pull out and glue together data you put in COINS
  • A multitool for making scripts to yank data out of COINS

How does it work?

  • Screen scraping
  • Various hacks

Okay, fine, whatever dude.

But seriously...

but what can it DO???


  • List studies
  • List subjects in a study
  • List subjects via metaportal
  • Get demographics by URSI
  • List instruments in a study
  • Get all assessments for a given study + instrument
  • Get all assessment DETAILS for a given study + instrument
  • Get all assessment DETAILS for a given study + instrument + URSI


Calculated dates

  • Link metaportal data to demographics
  • Do date math on enrolled dates
    (or in this case, anchor dates for BBB)

Config for calculated dates

:anchor_date_offsets => [-16, -12, -10, -8, -5, 4]


Demographics & assessment data

  • Link demographics with assessment data
  • Caveat: Confusing when multiple assessments happen for a single instrument...
    (the dreaded diagonalization problem)

Config for assessment data

:instrument_data => {
  "Tracker: FC-T2" => {
    :fields => {
      "TRCKFCT2A_058" => "Consent T1-T2",
      "TRCKFCT2A_030" => "Consent cord blood",
      "TRCKFCT2A_045" => "Consent saliva",

  "Demo FC" => {
    :fields => {
      "TODO" => "Hospital",
      "DEMOGRAPHT1_003" => "DOB_MOM",


NCCAM3 query builder tasks

  • Another way to combine demographics with assessment data
  • You can do fancy task-based querying and filtering


Config for NCCAM script

tasks = [
    :label => ["ursi", "name", "group", "WBIC", "cohort", "Preferred method of contact", "Agreed to SO interview?", "SO name", "SO relation to pp", "SO phone", "SO email", "Follow- up SO Interview Communications Log", "T3 date", "T3 status", "Follow up SO int. status", "Follow up SO int. notes", "Intervention status"].to_csv.strip,

    :filter => lambda { |row, today_date, piggybank|
      # This function must return true or false
      group = row['WS_ET_185'].to_i
      status_overall = row['WS_ET_187'].to_i #blank will turn into 0
      status_enrollment = row['WS_ET_328'].to_i #blank will turn into 0
      status_so = row['WS_ET_488'].to_i #blank will turn into 0
      status_overall == STATUS_ENROLLED and status_enrollment == ENROLLED_YES and status_so == YES and status_int == COMPLETED_INT and status_postint_so == BLANK and status_postT1_so == COMPLETED_SO or (status_T3 == YES and status_postT3_so == BLANK)

    :output => lambda { |row, today_date, piggybank|
      # This function must return a string
      ursi = row['queried_ursi'].to_s.strip()
      subject = piggybank.get_demographics_by_ursi(ursi)
      name = "#{subject.first_name} #{subject.last_name}"
      subject_group_int = row['WS_ET_185'].to_i

      out = [ursi, name, subject_group, wbic, cohort, preferred_contact, status_so, so_name, so_relation, so_phone, so_email, follow_up_so_comm_log, date_T3, status_postT3_so, status_T3, status_int]

    # AND SO ON

Possible Future Features

  • Easier reporting
  • Automating query builder (QBR)
  • ????

Possible Downsides

  • Annoying to COINS developers
  • Brittle (upside: force MRN to release API?)
  • Complexities involving HIPAA

Please keep in the back of your mind:

Scripts using Piggybank
can do more than just

CSV files


(or, discussion)

Thanks for your time.