Offices Data Layout
The fields listed below are considered the minimum requirement, unless noted as optional. Additional fields for miscellaneous site characteristics are acceptable and encouraged for the analysis or for display in the platform.
Field Name | Data Type | Required | Impact on Analysis | Notes |
EXTRACT_DATE | DATE: MM/DD/YYYY | Optional |
| File creation date, unnecessary if the date is in the file name. |
OFFICE_NBR | CHARACTER | Required | Defines unique offices. | Unique office number. Primary Key matches to the visits and encounters files. |
OFFICE_NM | CHARACTER | Required |
| Office name for display in SCOUT. |
ADDRESS1 | CHARACTER | Required | Used to identify the geographic location to be used in modeling. | Street address only. No mall names, shopping center names, or PO Boxes. Suite/unit numbers are acceptable. |
ADDRESS2 | CHARACTER | Optional | Used to identify the geographic location to be used in modeling. | Optional if needed. Street address only. No mall names, shopping center names, or PO Boxes. |
CITY | CHARACTER | Required |
|
|
STATE | CHARACTER | Required |
| Postal abbreviation |
ZIPCODE | CHARACTER | Required |
|
|
LATITUDE | DECIMAL | Optional |
| Buxton will geocode offices to a Lat/Lon if not provided. |
LONGITUDE | DECIMAL | Optional |
| Buxton will geocode offices to a Lat/Lon if not provided. |
OFFICE_TYPE | CHARACTER | Optional | Used to identify differences in performance based on office type, if applicable. | e.g., Franchise/Corporate, Freestanding/Mall/In-line, etc. Please include full descriptions, no abbreviations. Optional unless required for project scope. See your Buxton team for details. |
OFFICE_STATUS | CHARACTER | Required | Used to identify which offices to include in model sample set. | e.g., Open, Closed, Pipeline |
OPEN_DT | DATE: MM/DD/YYYY | Required | Used to measure stabilization or ramp period for new offices as well as impact on existing offices when a new one opens nearby. | The original open date of the office. No acquisition, remodel, or re-open dates (if available, provide as extra fields). |
CLOSE_DT | DATE: MM/DD/YYYY | Required | Used to measure impact on existing offices when a nearby one closes. |
|
Optional Office Characteristics | CHARACTER | Optional | May be tested as variables to explain differences in performance. | Optional, for SCOUT display or model testing. One variable per column. e.g., Equipped/Unequipped Operatories, FTE, Open hours, services offered, etc. |
Visits Data Layout
The fields listed below are considered the minimum requirement. Additional fields are acceptable if deemed necessary for this analysis or for display in the platform.
This file should be interpreted as monthly visits performance by office (and by service line, if required).
Example: Office 354 had 1,263 visits in January 2023.
Field Name | Data Type | Required | Impact on Analysis | Notes |
EXTRACT_DATE | DATE: MM/DD/YYYY | Optional |
| File creation date, unnecessary if the date is in the file name. |
OFFICE_NBR | CHARACTER | Required | Used to join to Office file. Any OFFICE_NBRs present in this performance file that are NOT in the offices file will not be loaded to the Buxton database for visualization and will not be used for any analysis. | Foreign key to OFFICE_NBR on office file |
VISITS_MONTH | DATE: MM/DD/YYYY | Required | Historic performance data is used to measure the stabilization or ramp period for new offices as well as measure the impact on existing offices when nearby ones open or close.
1 year of data is the minimum required. 3-5 years of data is preferred, but we can accept and use as much as you have available. | Use first day of month if visits are reported by calendar month (e.g., 1/1/2018). If visits are reported by fiscal period, use calendar start date of period and provide period calendar.
3-5 years’ worth of data is preferred (if available) |
SERVICE_LINE | CHARACTER | Optional | Will be used to define office performance by service line if necessary for project scope. | Type of service. e.g., Endodontic, Pediatric, Orthodontic, etc. Optional unless required for project scope. See your Buxton team for details. |
TOTAL_VISITS | DECIMAL | Required | Performance data provided in this file will be used as the dependent variable in modeling. This will be the metric by which we train the model to predict performance at future offices and identify high vs. low performers. | Total number of patient visits. |
Encounters Data Layout
The fields listed below are considered the minimum requirement. Additional fields for miscellaneous patient characteristics are acceptable if deemed necessary for the analysis or for display in the platform.
Field Name | Data Type | Required | Impact on Analysis | Notes |
EXTRACT_DATE | DATE: MM/DD/YYYY | Optional |
| File creation date, unnecessary if the date is in the file name. |
PATIENT_ID | CHARACTER | Required |
| Primary key |
FIRSTNAME | CHARACTER | Optional | Used to match to Buxton's internal household database to ensure higher matching accuracy. | Optional, but does increase matching accuracy. |
LASTNAME | CHARACTER | Optional | Used to match to Buxton's internal household database to ensure higher matching accuracy. | Optional, but does increase matching accuracy. |
ADDRESS | CHARACTER | Required | Home address of the patient informs drive time to an office and behavioral and psychographic traits of the patient. |
|
CITY | CHARACTER | Required | Home address of the patient informs drive time to an office and behavioral and psychographic traits of the patient. |
|
STATE | CHARACTER | Required | Home address of the patient informs drive time to an office and behavioral and psychographic traits of the patient. | Postal abbreviation |
ZIPCODE | CHARACTER | Required | Home address of the patient informs drive time to an office and behavioral and psychographic traits of the patient. |
|
OFFICE_NBR | CHARACTER | Required | Used to join to the offices file. Only encounters that join to the offices file will be included in analysis. Any OFFICE_NBRs present in the encounters file that are NOT included in the offices file will not be loaded for visualization or included in analysis. | Foreign key to OFFICE_NBR on the offices file. |
ENCOUNTER_DT | DATE: MM/DD/YYYY | Required |
| 1 year required, but 3-5 years of data is preferred (if available) |
SERVICE_LINE | CHARACTER | Optional |
| Type of service. e.g., Endodontic, Pediatric, Orthodontic, etc. Optional unless required for project scope. See your Buxton team for details. |
TRANS_AMOUNT | DECIMAL | Required | Used to determine the value of different patient segments. | Revenue |
PATIENT_TYPE | CHARACTER | Optional | These can be used to analyze differences in profiles (demographic and psychographic characteristics) as well as viewed as separate layers in SCOUT. | Optional unless required by project scope and if Patient Types are defined by encounter. Ex: payor types |
Common Additional Fields
Offices
Relocation/Acquisition Dates
Office Hours
FTE by Dentists, Orthodontists, Hygienists, etc.
Building Characteristics:
Square Footage
Visibility
Signage
Count of equipped/unequipped operatories
Services offered (e.g., Endodontic, Pediatric, Orthodontic, etc.)
Encounters
Insurance type
Member vs. Non-member
Data Audit Compliance Checklist
The following checklist denotes common points of failure for data audits. By confirming that your data complies with each of the items below, you can help ensure that your data is accepted and moves forward in the data transfer process. If you have any questions or concerns regarding any of the items below, please contact your Buxton team.
General
File headers are included in all files
Abbreviations include a description, either in the file or in a data dictionary
Offices
Address information is complete and provided for each office
Open date is provided for each office
Visits
All open offices are present in the visits file
Each office present in the visits file is also present in the offices file
At least 3 years of visits data is provided
Offices are not missing any monthly visits data
Data is not provided for future months
Service line breakouts, if required, are included
Encounters
Duplicate patient IDs are not present
Address information is complete for at least 90% of records
All office numbers in this file join to the offices file
Offices are not missing one or more days of encounters
Encounters occur after the office open date