Facilities 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. |
FACILITIY_NBR | CHARACTER | Required | Defines unique facilities. | Unique facility number. Primary Key matches to the visits and transactions files. |
FACILITIY_NM | CHARACTER | Required |
| Facility 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 facilities to a Lat/Lon if not provided. |
LONGITUDE | DECIMAL | Optional |
| Buxton will geocode facilities to a Lat/Lon if not provided. |
FACILITIY_TYPE | CHARACTER | Optional | Used to identify differences in performance based on facility type, if applicable. | e.g., General Practice, Specialty, Large Animal/Equine, Emergency, etc. or Freestanding, In-line, etc. Please include full descriptions, no abbreviations. Optional unless required for project scope. See your Buxton team for details. |
FACILITIY_STATUS | CHARACTER | Required | Used to identify which facilities 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 facilities as well as impact on existing facilities when a new one opens nearby. | The original open date of the facility. 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 facilities when a nearby one closes. |
|
Optional Facility 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., Exam rooms, 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 facility (and by service line, if required).
Example: Facility 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. |
FACILITIY_NBR | CHARACTER | Required | Used to join to facility file. Any FACILITIY_NBRs present in this performance file that are NOT in the facilities file will not be loaded to the Buxton database for visualization and will not be used for any analysis. | Foreign key to FACILITIY_NBR on the facility file |
VISITS_MONTH | DATE: MM/DD/YYYY | Required | Historic performance data is used to measure the stabilization or ramp period for new facilities as well as measure the impact on existing facilities 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 facility performance by service line if necessary for project scope. | e.g., service line, department, specialty, species, 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 facilities and identify high vs. low performers. | Total number of customer visits. |
Transactions Data Layout
The fields listed below are considered the minimum requirement. Additional fields for miscellaneous customer 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. |
CUSTOMER_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 customer informs drive time to a facility and behavioral and psychographic traits of the customer. |
|
CITY | CHARACTER | Required | Home address of the customer informs drive time to a facility and behavioral and psychographic traits of the customer. |
|
STATE | CHARACTER | Required | Home address of the customer informs drive time to a facility and behavioral and psychographic traits of the customer. | Postal abbreviation |
ZIPCODE | CHARACTER | Required | Home address of the customer informs drive time to a facility and behavioral and psychographic traits of the customer. |
|
FACILITIY_NBR | CHARACTER | Required | Used to join to the facilities file. Only transactions that join to the facilities file will be included in analysis. Any FACILITIY_NBRs present in the transactions file that are NOT included in the facilities file will not be loaded for visualization or included in analysis. | Foreign key to FACILITIY_NBR on the facilities file. |
TRANSACTION_DT | DATE: MM/DD/YYYY | Required |
| 1 year required, but 3-5 years of data is preferred (if available) |
SERVICE_LINE | CHARACTER | Optional |
| e.g., service line, department, specialty, species, etc. Optional unless required for project scope. See your Buxton team for details. |
TRANS_AMOUNT | DECIMAL | Required | Used to determine the value of different customer segments. | Revenue |
CUSTOMER_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. | e.g., species, insurance type, service type, etc. Optional unless required by project scope. |
Common Additional Fields
Facilities
Relocation/Acquisition Dates
Facility Hours
FTE
Building Characteristics:
Square Footage
Visibility
Signage
Exam room count
Services offered
Transactions
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
Facilities
Address information is complete and provided for each facility
Open date is provided for each facility
Visits
All open facilities are present in the visits file
Each facility present in the visits file is also present in the facilities file
At least 3 years of visits data is provided
Facilities are not missing any monthly visits data
Data is not provided for future months
Service line breakouts, if required, are included
Transactions
Duplicate customer IDs are not present
Address information is complete for at least 90% of records
All facility numbers in this file join to the facilities file
Facilities are not missing one or more days of transactions
Transactions occur after the facility open date
Tags: Veterinary, Veterinary Data, Data, Data Requirement, Vet, Vet Data.