Prescription Discount Card Claims Data

**Overview**
This Prescription Discount Card Claims Data package offers a comprehensive view into consumer behavior and pharmacy activity related to prescription and over-the-counter medications in the U.S.

The dataset captures detailed claim-level transactions, reflecting real-world medication access patterns outside of traditional insurance channels. It includes demographic breakdowns of members, granular pricing data tied to specific National Drug Codes (NDCs), and geographic distribution of claim volumes across pharmacy locations. With insights into both the demand and cost dimensions of drug utilization, this dataset supports robust analysis for healthcare, pharmaceutical, and market research applications. All data is fully de-identified and HIPAA compliant.

**Use cases**
Market Analysis
- In-depth insights into prescription and over-the-counter drug usage among uninsured and underinsured patients in the U.S., with demographic details such as member gender, state, and age group.
- Comprehensive drug pricing data by National Drug Code (NDC), including metrics like Average Wholesale Price, Wholesale Acquisition Cost, Usual and Customary Price, and Total Member Paid.
- Claim volume by pharmacy location, including total prescriptions filled and full address details—enabling regional performance analysis and targeting of high-traffic sites.

**Product details**
Sample fields that describe the dataset:

- id: Unique identifier for the claim
- member_gender: Gender of the member ( M, F)
- member_state: State of residence of the member (e.g., CA, NV, NY)
- member_age_group: Age group bucket for the member (e.g., 0-24 Y, 25-34 Y, 35-44 Y, 45-54 Y, 55-64 Y, 65+ Y)
- master_member_date_submitted: Date the member first submitted a claim through us.
- master_repeat_usage: Indicates if the member has used us before (e.g., New, Repeat)
- unique_member: Indicates if the member information added is unique and entered properly (Boolean )
- date_filled: Date the prescription was filled
- date_submitted: Date the claim was submitted
- weekday: Day of the week the claim was submitted
- date_submitted_month: Month the claim was submitted as date, always first day of the month (e.g., 2023-01-12 = 2023-01-01)
- created_date: Date the record was created in the system
- changed_date: Date the record was last modified in the system
- rxclaim_actual_status: Final status of the claim (e.g., Paid, Reversed)
- refill_number: Refill sequence number of the prescription
- refill_maximum: Total number of refills allowed
- pharmacy_id: Internal identifier for the pharmacy
- pharmacy_name: Name of the dispensing pharmacy

**Sample SQL Query**
- Top 10 Drugs for uninsured and underinsured California Residents. Grouped by Member Age Group and Gender

WITH RankedDrugs AS (
SELECT
MEMBER_AGE_GROUP,
MEMBER_GENDER,
DRUG_NAME,
SUM(TOTAL_RXS) AS TOTAL_RXS_SUM,
ROW_NUMBER() OVER (
PARTITION BY MEMBER_AGE_GROUP, MEMBER_GENDER
ORDER BY SUM(TOTAL_RXS) DESC
) AS rn
FROM

PUBLIC.DE_IDENTIFIED_TRANSACTIONS_CLAIMS
WHERE
DATE_SUBMITTED BETWEEN '2023-01-16' AND '2023-01-22'
AND PHARMACY_STATE = 'CA'
AND RXCLAIM_ACTUAL_STATUS = 'Paid'
AND MEMBER_GENDER IN ('M', 'F')
GROUP BY
MEMBER_AGE_GROUP,
MEMBER_GENDER,
DRUG_NAME
)
SELECT
MEMBER_AGE_GROUP,
MEMBER_GENDER,
DRUG_NAME,
TOTAL_RXS_SUM
FROM
RankedDrugs
WHERE
rn <= 10
ORDER BY
MEMBER_AGE_GROUP,
MEMBER_GENDER,
TOTAL_RXS_SUM DESC;