Data Analysis, Data Visualization

R/Medicine Talk Recap: Automating Patient Inquiry Tracking in Pharma with Shiny

Variety of chemotherapy drugs in vials and an IV bottle.

By Posted byStephanie

Last month, TCB’s Tanya Cashorali gave a talk at R/Medicine 2025, covering a recent project where we automated patient inquiry tracking with R, Shiny and Smartsheet for a pharmaceutical company. In this post, we’ll cover the main highlights from the talk, including the workflow challenges that led to this solution, business and technical overviews of the app, and the benefits of the integrated and automated solution.

Here’s a link to the full video: https://www.youtube.com/watch?v=sRNxjGXZ0_0

In the complex world of rare disease treatment, coordinating support for small, high-touch patient populations is anything but simple. One rare disease pharmaceutical company found itself bogged down in a chaotic workflow: managing patient support and prescription fulfillment through scattered Excel files and sprawling email threads. The result? Human error, wasted time, and risk to patient care.

The Problem: A Workflow in Crisis

Prior to implementing the new app, each team member maintained their own version of an Excel file, leading to inconsistencies and version control issues. Weekly status updates were laborious, requiring users to manually copy and paste notes from spreadsheets into lengthy, error-prone email threads. Preparing for case meetings often took more than 30 minutes per user, just to ensure information was somewhat up to date. Compounding the issue, there was no consolidated view of open tickets or patient statuses, making collaboration slow and fragmented.

Specialty pharmaceutical company operations

There was no single source of truth. Critical decisions were made based on potentially fragmented and outdated information.

The Solution: A Custom Shiny App as a Central Operations Hub

To solve this problem, we built a custom Shiny application that completely reimagined how patient support was tracked and managed. The app served as a centralized operational hub, integrating multiple data sources and streamlining every aspect of the case tracking workflow.

Key Features

  • A unified dashboard accessible by market access field teams, showing live data in real time.
  • Powerful filters and export options allowed users to slice patient records by region, vendor, days’ supply remaining, and more.
  • Support metrics like shipment history and open tickets were tracked directly in the app.
  • Users had self-serve access to analytics and operational tools, with data visibility controlled by roles and territory via roster pins.

Data Integration

  • Excel-based prescription data was automatically ingested and versioned daily using R Studio pins, allowing for managed storage and scheduled updates.
  • Live Smartsheet API feeds were used to track tickets and status changes in real time.

Reimagined and Automated Workflows

With the new Shiny app and Smartsheet integration in place, the workflow has transformed. Excel data is now automatically versioned and refreshed nightly using pins, eliminating manual file management. Ticket creation, editing, and closure are handled seamlessly through the Smartsheet API, all within a user-friendly Shiny interface. Users can easily filter records by vendor, territory, supply status, and more. The app features an interactive reactable table with custom tooltips, expandable patient history, and built-in filters to enhance data exploration. Ticket modals allow users to take action directly from patient records, replacing inefficient email threads. Behind the scenes, scheduled R jobs update patient history and ticket status every 10 minutes, ensuring real-time accuracy and reliability.

Workflow process improvements with Shiny

Under the Hood: Technical Implementation

Tech Stack

  • R Shiny: frontend and integration
  • Reactable: interactive, extensible tables (replacing DT)
  • R Studio Pins: storing and refreshing data from S3/Excel
  • SmartsheetR (a custom R package): API integration with Smartsheet (CRUD operations)
  • Custom R scripts: syncing Excel data with Smartsheet tickets and updating patient histories

Authentication & Permissions

  • A pin-based user roster maps email to region and territory, enforcing granular access.
  • PHI is managed securely with masked patient identifiers and territory-based restrictions.

UX Enhancements

  • Ticket actions (create/close) are embedded directly in patient modals.
  • All communication with specialty pharmacies is logged in ticket comment fields.
  • Patient lists can be exported back to Excel for traditionalists, though most users rarely need to.

Results: Tangible Gains Across the Board

User Adoption

Since rollout, the app has seen strong engagement, with over 36 users across sales, patient access, and executive teams actively using Shiny, and adoption continues to grow.

Efficiency Gains

The new Shiny and Smartsheet ecosystem now handles 95% of all case tracking, drastically reducing the reliance on manual tools. Weekly case prep calls, which used to take up to 30 minutes, have been trimmed down to just 10. To date, over 400 tickets have been tracked in the system, with an average of 19 new tickets created each week. Additionally, the platform now supports active monitoring of key operational metrics, including time-to-resolution, giving teams more visibility into their performance.

Business Value

Field teams now work from a single source of truth, accessing real-time, actionable data with a built-in audit trail. Coordination with specialty pharmacies has significantly improved, thanks to faster and more structured communication. Importantly, the entire solution is lightweight, flexible, and vendor-agnostic. It’s delivered without the need for expensive SaaS contracts or prolonged implementation timelines. It’s a scalable, cost-effective tool that’s already delivering measurable value.

 

Lessons Learned

  1. Start with a Sketch, Not a Spec
    User-centered development made all the difference. Building alongside the team, rather than handing down a finished product, allowed for rapid feedback and iteration.
  2. Don’t Over-Engineer
     Instead of a heavy relational database, a combination of pins, the Smartsheet API, and optimized R scripts delivered huge returns. Simplicity wins.
  3. Plan for Security and Compliance
    We designed the app with least-privilege access in mind, supporting PHI masking and territory-based restrictions from day one.
  4. Support Change Management
    Resistance to new tools was minimal, thanks to strong leadership support and the safety net of an Excel export for those who weren’t quite ready to let go.
Posted byStephanie •