See llms.txt for all machine-readable content.

Back to Templates

Score Medicare Open Payments to physicians with Apify, OpenAI and Google Sheets

Created by

Created by: Matt Michels || mattmichels
Matt Michels

Last update

Last update 2 days ago

Categories

Quick Overview

This workflow runs daily to pull Medicare Open Payments physician payment records via an Apify actor, scores them with rule logic and OpenAI, stores results in Google Sheets, and routes hot items to Slack for approval and warm items to a Gmail digest, plus a weekly Gmail report.

How it works

  1. Runs on a daily schedule (or manual run), while a separate weekly schedule triggers a 7-day email report.
  2. On daily runs, starts the Apify actor for CMS Open Payments doctors and polls the Apify run status until it succeeds or fails.
  3. Downloads the Apify dataset results, validates required fields, and deduplicates records against prior runs using workflow static data.
  4. Scores each payment record with rule-based criteria (amount, payment type, manufacturer keywords, and specialty keywords) and then sends each row to OpenAI to generate a structured fit score, verdict, summary, outreach angle, and red flags.
  5. Merges the rule score and AI score into a final tier (hot/warm/cold), then upserts all records into Google Sheets using the record key to avoid duplicates.
  6. Sends hot records as a single Slack message that supports approve/dismiss, writes the review decision back to Google Sheets, and emails warm records as an HTML digest via Gmail while leaving cold records in the sheet only.
  7. On weekly runs, reads the Google Sheet, compiles a 7-day HTML summary (including tier counts and review outcomes), and sends it via Gmail.

Setup

  1. Create an Apify token and add an HTTP Header Auth credential (for example, Authorization: Bearer <token>) for the Apify HTTP requests.
  2. Connect Google Sheets credentials, create a spreadsheet tab for the payment records, and set the Google Sheet ID and tab name in the Config values.
  3. Connect an OpenAI credential (or replace the OpenAI chat model with your preferred LangChain-compatible chat model) to power the AI qualification and summaries.
  4. Connect Slack credentials, set the Slack channel ID in Config for hot-record approvals, and optionally update the separate Slack channel ID used by the error-alert branch.
  5. Connect Gmail credentials and set the digestEmail in Config for the daily warm digest and weekly report, then adjust the Apify actor input, scoring thresholds, and keyword lists in Config to match your monitoring needs.