CVS Pharmacy Claims Intelligence – Retail Drug Utilization & Consumer Trends

**Overview**
Tap into one of the most comprehensive views of retail pharmacy activity in the U.S. with CVS Pharmacy Claims Intelligence. This HIPAA-compliant dataset includes millions of de-identified claims, capturing real-world prescription use and consumer behavior across CVS locations.What You Get:
Long-Term Market Trends: Understand how prescription patterns evolve over time, including shifts in volume, product mix, and cash-pay activity.

- Brand & Category Performance: Measure how products are performing at the retail level to inform positioning, pricing, and strategy.
- Regional Demand Insights: Identify where demand is growing, shifting, or underserved through geographic claim distribution.
- Designed for commercial, strategy, and analytics teams who need reliable, retail-based insights to support smarter decisions.
Ground your strategy in real-world data.

**Use cases**
Market Analysis
- Understand how prescription patterns evolve over time, including shifts in volume, product mix, and cash-pay activity.

Inventory Management
- Measure how products are performing at the retail level to inform positioning, pricing, and strategy.

Location Planning
- Identify where demand is growing, shifting, or underserved through geographic claim distribution.

**Product details**
- The below fields are part of the data product.

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

pharmacy_ncpdp_id: NCPDP (National Council for Prescription Drug Programs) ID

pharmacy_npi: National Provider Identifier for the pharmacy

pharmacy_address_1: Primary street address of the pharmacy

pharmacy_address_2: Additional address info (suite, etc.)

pharmacy_city: City where the pharmacy is located

pharmacy_state: State where the pharmacy is located

pharmacy_zip: ZIP code of the pharmacy

drug_id: Internal identifier for the drug

drug_name: Brand or generic name of the drug

drug_ndc: National Drug Code identifier

drug_gpi: Generic Product Identifier

drug_group_description_gpi_02: Drug class or group based on GPI-02 level

drug_therapeutic_class_name: Therapeutic class the drug belongs to

drug_strength: Strength or dosage of the drug (e.g., 10mg)

drug_form: Physical form of the drug (e.g., tablet, capsule)

drug_name_and_strength: Combined name and strength for display

drug_dea_code: DEA classification code (e.g., controlled substance)

drug_maintenance_code: Indicates maintenance vs acute medication

mony: Multi-Source Indicator code

brand_generic_code: Indicates if the drug is brand or generic

daw_code: Dispense As Written code (provider instruction)

speciality_non_speciality_code: Indicates if the drug is specialty or non-specialty

total_quantity: Total quantity dispensed in the claim

total_days_supply: Total number of days supplied

avg_quantity_day: Average quantity per day(total_quantity/total_days_supply)

net_rxs: Net claims after adjustments

total_rxs: Total number of prescriptions associated with the claim

gross_rxs: Paid Claim's Total Rxs when not reversed on the same day.

uc_rxs: Usual and Customary prescription count

total_drug_cost: Total drug cost billed

total_awp_cost: Total cost based on Average Wholesale Price

total_ingredient_cost: Total cost of the drug ingredients

total_dispensing_fee: Total pharmacy dispensing fee

total_sales_tax: Sales tax charged on the prescription

total_incentive_fee: Incentive fee paid to the pharmacy (if applicable)

total_plan_paid: Amount paid by the health plan

total_deductible: Deductible portion paid by the member

total_member_paid: Total paid out-of-pocket by the member

total_copay: Copayment amount

uc_amount: Usual and Customary amount

member_savings: Total savings for the member

member_savings_percent: Member savings as a percentage

awp_unit_cost: Unit cost based on AWP

wac: Wholesale Acquisition Cost

retail_30_90_days_supply: Indicates if supply is 30 or 90 days supply

transaction_provider_id: Internal identifier for the provider

transaction_bin_id: Internal identifier for the Bank Identification Number(BIN)

transaction_client_id: Internal identifier for the client

transaction_owner_id: Internal identifier for the owner

transaction_group_id: Internal identifier for the group

reversal_date_submitted: Date the paid claim was reversed (if applicable)

reversed: Boolean flag indicating if the claim was reversed

days_to_reverse: Number of days between paid and reversal date of a paid claim

paid_tx_id: ID of the original paid transaction (used in reversal linking)

created_master_member: Boolean flag indicating this claim created a new member in the database

For more details, refer to the embedded notebook.

**Sample SQL Query**
- Top 10 drugs by age group and genderid: Unique identifier for the claim

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.SAMPLE_CVS_CLAIMS
WHERE
DATE_SUBMITTED BETWEEN '2023-10-01' AND '2023-12-25'
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;