Categories
Healthcare IT Medical Marketing

How to use CMS data for medical marketing

Not many healthcare marketers can afford to buy healthcare data from the big guys like definitivehc.com, IQVIA etc. There’s no substitute for the clean, organized data that they have (of course, they have spent millions on preparing data that way).

Not many healthcare marketers can afford to buy healthcare data from the big guys like definitivehc.com, IQVIA etc. There’s no substitute for the clean, organized data that they have (of course, they have spent millions on preparing data that way).

However, you can “sort of” get by with CMS NPI database (available for download).

Here are the steps that healthcare marketers in USA can use to get a workable database. Some of these a wee bit technically advanced and you might need a healthcare IT person to help out for a little bit.

Download the NPI database (NPPES Downloadable File)

Go to CMS data dissemination website here. Find this link to download the data (NPI_Files).

Keep in mind that the data is large. Moreover, when you expand the zip file, it will become even larger, so be prepared for that.

Once you download and extract the zip file, you will have these files (keep in mind that this can change at any point)

  1. npidata_pfile_xxxx.csv
  2. npidata_pfile_xxxx_FileHeader.csv
  3. pl_pfile_xxxx.csv
  4. pl_pfile_xxxx_FileHeader.csv
  5. endpoint_pfile_xxxx_FileHeader.csv
  6. endpoint_pfile_xxxx.csv
  7. othername_pfile_xxxx_FileHeader
  8. othername_pfile_xxxx.csv

One thing to keep in mind before proceeding is that NPI database is not really that great. A lot of the information is old and has not been updated in years (even though CMS asks providers to keep their information up to date). In addition to that, you will notice that providers are not the best at clean, error free data entry either.

Deal with it – this is one of the best shots you have got (there’s another way, of course, wherein you can get even better information.. from payers’ member directories, but a tad more technically advanced).

How to import this NPI data into a database

This is a bit of a tough one. For this, we would recommend that you download the free MySQL database. After this, create a schema

e.g.

CREATE DATABASE npi-20050523-20200607 /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;

Once you do that, just copy paste the following SQL statements to create the tables (otherwise, it is a bit of a nightmare)

Create the NPI table like this

CREATE TABLE npidata (
NPI int(10) NOT NULL,
Entity_Type_Code varchar(1) DEFAULT NULL,
Replacement_NPI varchar(10) DEFAULT NULL,
Employer_Identification_Number_EIN varchar(9) DEFAULT NULL,
Provider_Organization_Name_Legal_Business_Name varchar(70) DEFAULT NULL,
Provider_Last_Name_Legal_Name varchar(35) DEFAULT NULL,
Provider_First_Name varchar(20) DEFAULT NULL,
Provider_Middle_Name varchar(20) DEFAULT NULL,
Provider_Name_Prefix_Text varchar(5) DEFAULT NULL,
Provider_Name_Suffix_Text varchar(5) DEFAULT NULL,
Provider_Credential_Text varchar(20) DEFAULT NULL,
Provider_Other_Organization_Name varchar(70) DEFAULT NULL,
Provider_Other_Organization_Name_Type_Code varchar(1) DEFAULT NULL,
Provider_Other_Last_Name varchar(35) DEFAULT NULL,
Provider_Other_First_Name varchar(20) DEFAULT NULL,
Provider_Other_Middle_Name varchar(20) DEFAULT NULL,
Provider_Other_Name_Prefix_Text varchar(5) DEFAULT NULL,
Provider_Other_Name_Suffix_Text varchar(5) DEFAULT NULL,
Provider_Other_Credential_Text varchar(20) DEFAULT NULL,
Provider_Other_Last_Name_Type_Code varchar(1) DEFAULT NULL,
Provider_First_Line_Business_Mailing_Address varchar(55) DEFAULT NULL,
Provider_Second_Line_Business_Mailing_Address varchar(55) DEFAULT NULL,
Provider_Business_Mailing_Address_City_Name varchar(40) DEFAULT NULL,
Provider_Business_Mailing_Address_State_Name varchar(40) DEFAULT NULL,
Provider_Business_Mailing_Address_Postal_Code varchar(20) DEFAULT NULL,
Provider_Business_Mailing_Address_Country_Code_out_US varchar(2) DEFAULT NULL,
Provider_Business_Mailing_Address_Telephone_Number varchar(20) DEFAULT NULL,
Provider_Business_Mailing_Address_Fax_Number varchar(20) DEFAULT NULL,
Provider_First_Line_Business_Practice_Location_Address varchar(55) DEFAULT NULL,
Provider_Second_Line_Business_Practice_Location_Address varchar(55) DEFAULT NULL,
Provider_Business_Practice_Location_Address_City_Name varchar(40) DEFAULT NULL,
Provider_Business_Practice_Location_Address_State_Name varchar(40) DEFAULT NULL,
Provider_Business_Practice_Location_Address_Postal_Code varchar(20) DEFAULT NULL,
Provider_Business_Practice_Location_Address_Country_Code_out_US varchar(2) DEFAULT NULL,
Provider_Business_Practice_Location_Address_Telephone_Number varchar(20) DEFAULT NULL,
Provider_Business_Practice_Location_Address_Fax_Number varchar(20) DEFAULT NULL,
Provider_Enumeration_Date varchar(20) DEFAULT NULL,
Last_Update_Date varchar(20) DEFAULT NULL,
NPI_Deactivation_Reason_Code varchar(2) DEFAULT NULL,
NPI_Deactivation_Date varchar(20) DEFAULT NULL,
NPI_Reactivation_Date varchar(20) DEFAULT NULL,
Provider_Gender_Code varchar(1) DEFAULT NULL,
Authorized_Official_Last_Name varchar(35) DEFAULT NULL,
Authorized_Official_First_Name varchar(20) DEFAULT NULL,
Authorized_Official_Middle_Name varchar(20) DEFAULT NULL,
Authorized_Official_Title_or_Position varchar(35) DEFAULT NULL,
Authorized_Official_Telephone_Number varchar(20) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_1 varchar(20) DEFAULT NULL,
Provider_License_Number_1 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_1 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_1 varchar(1) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_2 varchar(20) DEFAULT NULL,
Provider_License_Number_2 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_2 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_2 varchar(1) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_3 varchar(20) DEFAULT NULL,
Provider_License_Number_3 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_3 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_3 varchar(1) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_4 varchar(20) DEFAULT NULL,
Provider_License_Number_4 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_4 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_4 varchar(1) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_5 varchar(20) DEFAULT NULL,
Provider_License_Number_5 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_5 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_5 varchar(1) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_6 varchar(20) DEFAULT NULL,
Provider_License_Number_6 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_6 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_6 varchar(1) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_7 varchar(20) DEFAULT NULL,
Provider_License_Number_7 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_7 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_7 varchar(1) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_8 varchar(20) DEFAULT NULL,
Provider_License_Number_8 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_8 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_8 varchar(1) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_9 varchar(20) DEFAULT NULL,
Provider_License_Number_9 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_9 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_9 varchar(1) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_10 varchar(20) DEFAULT NULL,
Provider_License_Number_10 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_10 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_10 varchar(1) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_11 varchar(20) DEFAULT NULL,
Provider_License_Number_11 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_11 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_11 varchar(1) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_12 varchar(20) DEFAULT NULL,
Provider_License_Number_12 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_12 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_12 varchar(1) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_13 varchar(10) DEFAULT NULL,
Provider_License_Number_13 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_13 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_13 varchar(1) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_14 varchar(10) DEFAULT NULL,
Provider_License_Number_14 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_14 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_14 varchar(1) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Code_15 varchar(10) DEFAULT NULL,
Provider_License_Number_15 varchar(20) DEFAULT NULL,
Provider_License_Number_State_Code_15 varchar(2) DEFAULT NULL,
Healthcare_Provider_Primary_Taxonomy_Switch_15 varchar(1) DEFAULT NULL,
Other_Provider_Identifier_1 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_1 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_1 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_1 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_2 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_2 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_2 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_2 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_3 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_3 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_3 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_3 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_4 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_4 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_4 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_4 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_5 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_5 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_5 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_5 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_6 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_6 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_6 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_6 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_7 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_7 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_7 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_7 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_8 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_8 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_8 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_8 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_9 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_9 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_9 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_9 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_10 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_10 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_10 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_10 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_11 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_11 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_11 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_11 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_12 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_12 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_12 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_12 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_13 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_13 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_13 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_13 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_14 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_14 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_14 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_14 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_15 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_15 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_15 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_15 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_16 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_16 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_16 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_16 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_17 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_17 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_17 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_17 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_18 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_18 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_18 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_18 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_19 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_19 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_19 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_19 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_20 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_20 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_20 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_20 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_21 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_21 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_21 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_21 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_22 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_22 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_22 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_22 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_23 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_23 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_23 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_23 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_24 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_24 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_24 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_24 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_25 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_25 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_25 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_25 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_26 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_26 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_26 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_26 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_27 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_27 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_27 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_27 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_28 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_28 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_28 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_28 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_29 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_29 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_29 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_29 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_30 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_30 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_30 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_30 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_31 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_31 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_31 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_31 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_32 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_32 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_32 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_32 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_33 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_33 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_33 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_33 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_34 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_34 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_34 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_34 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_35 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_35 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_35 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_35 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_36 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_36 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_36 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_36 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_37 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_37 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_37 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_37 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_38 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_38 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_38 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_38 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_39 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_39 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_39 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_39 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_40 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_40 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_40 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_40 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_41 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_41 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_41 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_41 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_42 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_42 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_42 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_42 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_43 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_43 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_43 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_43 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_44 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_44 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_44 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_44 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_45 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_45 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_45 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_45 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_46 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_46 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_46 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_46 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_47 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_47 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_47 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_47 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_48 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_48 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_48 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_48 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_49 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_49 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_49 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_49 varchar(80) DEFAULT NULL,
Other_Provider_Identifier_50 varchar(20) DEFAULT NULL,
Other_Provider_Identifier_Type_Code_50 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_State_50 varchar(2) DEFAULT NULL,
Other_Provider_Identifier_Issuer_50 varchar(80) DEFAULT NULL,
Is_Sole_Proprietor varchar(1) DEFAULT NULL,
Is_Organization_Subpart varchar(1) DEFAULT NULL,
Parent_Organization_LBN varchar(70) DEFAULT NULL,
Parent_Organization_TIN varchar(9) DEFAULT NULL,
Authorized_Official_Name_Prefix_Text varchar(5) DEFAULT NULL,
Authorized_Official_Name_Suffix_Text varchar(5) DEFAULT NULL,
Authorized_Official_Credential_Text varchar(20) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_1 varchar(60) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_2 varchar(60) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_3 varchar(60) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_4 varchar(60) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_5 varchar(60) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_6 varchar(60) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_7 varchar(60) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_8 varchar(60) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_9 varchar(60) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_10 varchar(60) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_11 varchar(60) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_12 varchar(60) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_13 varchar(60) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_14 varchar(60) DEFAULT NULL,
Healthcare_Provider_Taxonomy_Group_15 varchar(60) DEFAULT NULL,
Certification_Date varchar(20) DEFAULT NULL,
PRIMARY KEY (NPI),
KEY idx_Org_LBN (Provider_Organization_Name_Legal_Business_Name),
KEY idx_Provider_Last_Name_Legal_Name (Provider_Last_Name_Legal_Name),
KEY idx_Provider_First_Name (Provider_First_Name),
KEY idx_Provider_Other_Organization_Name (Provider_Other_Organization_Name),
KEY idx_Provider_First_Line_Business_Practice_Location_Address (Provider_First_Line_Business_Practice_Location_Address)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Then, create the Other Name Reference table like this (this table contains the “other names” that a practice does business as)

CREATE TABLE othername (
NPI int(11) DEFAULT NULL,
Provider Other Organization Name text COLLATE utf8mb4_unicode_ci,
Provider Other Organization Name Type Code int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Next, create the practice locations table (this gives you all the practice locations of a company that’s not listed as the main location

CREATE TABLE pl (
NPI int(11) DEFAULT NULL,
Provider_Secondary_Practice_Location_Address-Address_Line_1 text COLLATE utf8mb4_unicode_ci,
Provider_Secondary_Practice_Location_Address-Address_Line_2 text COLLATE utf8mb4_unicode_ci,
Provider_Secondary_Practice_Location_Address-City_Name text COLLATE utf8mb4_unicode_ci,
Provider_Secondary_Practice_Location_Address-State Name text COLLATE utf8mb4_unicode_ci,
Provider_Secondary_Practice_Location_Address-Postal_Code text COLLATE utf8mb4_unicode_ci,
Provider_Secondary_Practice_Location_Address-Country_Code text COLLATE utf8mb4_unicode_ci,
Provider_Secondary_Practice_Location_Address-Telephone_Number text COLLATE utf8mb4_unicode_ci,
Provider_Secondary_Practice_Location_Address-Telephone_Extension text COLLATE utf8mb4_unicode_ci,
Provider_Practice_Location_Address-Fax Number text COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Finally, create the endpoint (email address etc) table.

CREATE TABLE endpoint_pfile (
NPI int(11) DEFAULT NULL,
Endpoint Type text COLLATE utf8mb4_unicode_ci,
Endpoint Type Description text COLLATE utf8mb4_unicode_ci,
Endpoint text COLLATE utf8mb4_unicode_ci,
Affiliation text COLLATE utf8mb4_unicode_ci,
Endpoint Description text COLLATE utf8mb4_unicode_ci,
Affiliation Legal Business Name text COLLATE utf8mb4_unicode_ci,
Use Code text COLLATE utf8mb4_unicode_ci,
Use Description text COLLATE utf8mb4_unicode_ci,
Other Use Description text COLLATE utf8mb4_unicode_ci,
Content Type text COLLATE utf8mb4_unicode_ci,
Content Description text COLLATE utf8mb4_unicode_ci,
Other Content Description text COLLATE utf8mb4_unicode_ci,
Affiliation Address Line One text COLLATE utf8mb4_unicode_ci,
Affiliation Address Line Two text COLLATE utf8mb4_unicode_ci,
Affiliation Address City text COLLATE utf8mb4_unicode_ci,
Affiliation Address State text COLLATE utf8mb4_unicode_ci,
Affiliation Address Country text COLLATE utf8mb4_unicode_ci,
Affiliation Address Postal Code text COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Load the data into each one of these tables like this (change it to the file names you have)

LOAD DATA INFILE 'NPPES_Data_Dissemination_June_2020/pl_pfile_20050523-20200607.csv'  INTO TABLE pl  FIELDS TERMINATED BY ','  ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
 LOAD DATA LOCAL INFILE 'NPPES_Data_Dissemination_June_2020/endpoint_pfile_20050523-20200607.csv'  INTO TABLE endpoint_pfile
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 ROWS;
 LOAD DATA LOCAL INFILE 'NPPES_Data_Dissemination_June_2020/othername_pfile_20050523-20200607.csv' INTO TABLE othername
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 ROWS;
LOAD DATA LOCAL INFILE 'NPPES_Data_Dissemination_June_2020/npidata_pfile_20050523-20200607.csv'
 INTO TABLE npidata
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 ROWS;

How to find providers to market to

First, read this file NPPES_Data_Dissemination_Readme.pdf .. This gives you a LOT of information and helps you understand how to search the data you just imported.

Understand that if you want to market to providers directly, you will need to search for Entity_Type_Code = 1. Keep in mind that while there will be a phone number, the fax number might not be present. Also, you need to look at when the provider last updated their own CMS data. Based on when the provider last updated their data, what you are looking at, might be quite old (still does not mean that it is not valid).

Let’s say you want to find all the providers in Queens, NY. You could very well find out all the providers in NYS first like this

select * from npidata where
Provider_Business_Practice_Location_Address_State_Name like 'NY'
and Entity_Type_Code = 1
order by Provider_Business_Practice_Location_Address_City_Name;

Let’s see how many providers practice in NYS first

select count(*) from npidata where
Provider_Business_Practice_Location_Address_State_Name like 'NY'
and Entity_Type_Code = 1;
count = 415,960

After this, you can very easily download all the results as a CSV file and work further on this.

Here’s what you would get if you were to download this as a CSV file. You can import this into google sheets and work on it further for your marketing goals.

How to find providers of a certain specialty to market to?

For this, you are going to need a little bit more data – on Taxonomy codes. Head over to this page from NUCC and download the latest CSV. After that, you can import this CSV straight away into your database OR, you can simply import this into Google Sheets (or open it in microsoft).

Click here for the taxonomy spreadsheet from NUCC.

You are going to need to filter the data a little bit for the specialist you are looking for. E.g let’s say that you are looking to market to all dental providers, you can filter and use these codes

CodeGroupingClassificationSpecialization
125K00000XDental ProvidersAdvanced Practice Dental Therapist
126800000XDental ProvidersDental Assistant
124Q00000XDental ProvidersDental Hygienist
292200000XLaboratoriesDental Laboratory
126900000XDental ProvidersDental Laboratory Technician
125J00000XDental ProvidersDental Therapist
122300000XDental ProvidersDentist
1223D0001XDental ProvidersDentistDental Public Health
1223D0004XDental ProvidersDentistDentist Anesthesiologist
1223E0200XDental ProvidersDentistEndodontics
1223G0001XDental ProvidersDentistGeneral Practice
1223P0106XDental ProvidersDentistOral and Maxillofacial Pathology
1223P0221XDental ProvidersDentistPediatric Dentistry
1223P0300XDental ProvidersDentistPeriodontics
1223P0700XDental ProvidersDentistProsthodontics
1223S0112XDental ProvidersDentistOral and Maxillofacial Surgery
1223X0008XDental ProvidersDentistOral and Maxillofacial Radiology
1223X0400XDental ProvidersDentistOrthodontics and Dentofacial Orthopedics
1223X2210XDental ProvidersDentistOrofacial Pain
122400000XDental ProvidersDenturist

Now that you have the taxonomy codes, you can filter your provider data based on that taxonomy code. That’s how you would create a list of all providers serving a specialty to market to.

Here’s a sample SQL to run

select * from npidata where
Provider_Business_Practice_Location_Address_State_Name like 'NY'
and Entity_Type_Code = 1
and Healthcare_Provider_Taxonomy_Code_1 in ('125K00000X', '126800000X', '124Q00000X', '292200000X', '126900000X', '125J00000X', '122300000X', '1223D0001X', '1223D0004X', '1223E0200X', '1223G0001X', '1223P0106X', '1223P0221X', '1223P0300X', '1223P0700X', '1223S0112X', '1223X0008X', '1223X0400X', '1223X2210X','122400000X')
order by Provider_Business_Practice_Location_Address_City_Name
;

How to find practices/clinics to market to

If you are marketing to hospitals, you will need Entity_Type_Code=2.

So, your first step is to get the taxonomy codes from your taxonomy table. e.g

CodeGroupingClassificationSpecialization
273100000XHospital UnitsEpilepsy Unit
275N00000XHospital UnitsMedicare Defined Swing Bed Unit
273R00000XHospital UnitsPsychiatric Unit
273Y00000XHospital UnitsRehabilitation Unit
276400000XHospital UnitsRehabilitation, Substance Use Disorder Unit
287300000XHospitalsChristian Science Sanitorium
281P00000XHospitalsChronic Disease Hospital
281PC2000XHospitalsChronic Disease HospitalChildren
282N00000XHospitalsGeneral Acute Care Hospital
282NC0060XHospitalsGeneral Acute Care HospitalCritical Access
282NC2000XHospitalsGeneral Acute Care HospitalChildren
282NR1301XHospitalsGeneral Acute Care HospitalRural
282NW0100XHospitalsGeneral Acute Care HospitalWomen
282E00000XHospitalsLong Term Care Hospital
286500000XHospitalsMilitary Hospital
2865C1500XHospitalsMilitary HospitalCommunity Health
2865M2000XHospitalsMilitary HospitalMilitary General Acute Care Hospital
2865X1600XHospitalsMilitary HospitalMilitary General Acute Care Hospital. Operational (Transportable)
283Q00000XHospitalsPsychiatric Hospital
283X00000XHospitalsRehabilitation Hospital
283XC2000XHospitalsRehabilitation HospitalChildren
282J00000XHospitalsReligious Nonmedical Health Care Institution
284300000XHospitalsSpecial Hospital

Now, just run the query

select * from npidata where
Entity_Type_Code = 2
and Provider_Business_Practice_Location_Address_State_Name like 'NY'
and Healthcare_Provider_Taxonomy_Code_1 in ('273100000X', '275N00000X', '273R00000X', '273Y00000X',
'276400000X', '287300000X', '281P00000X', '281PC2000X', '282N00000X', '282NC0060X', '282NC2000X',
'282NR1301X', '282NW0100X', '282E00000X', '286500000X', '2865C1500X', '2865M2000X', '2865X1600X',
'283Q00000X', '283X00000X', '283XC2000X', '282J00000X', '284300000X'
);

Hopefully this helps you out.

Leave a Reply

Your email address will not be published. Required fields are marked *