MedSpa Data Requirements | Buxton Knowledge Base

Stores Data Layout

The fields listed below are considered the minimum requirement, unless noted as optional. Additional fields for miscellaneous store 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.
STORE_NBR	CHARACTER	Required	Defines unique locations.	Unique store number. Primary Key matches to the sales and transactions files.
STORE_NM	CHARACTER	Required		Store 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 locations to a Lat/Lon if not provided.
LONGITUDE	DECIMAL	Optional		Buxton will geocode locations to a Lat/Lon if not provided.
STORE_TYPE	CHARACTER	Optional	Used to identify differences in performance based on store 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.
STORE_STATUS	CHARACTER	Required	Used to identify which locations 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 locations as well as impact on existing locations when a new location opens nearby.	The original open date of the location. 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 locations when a nearby location closes.
Optional Store Characteristics	CHARACTER	Optional	May be tested as variables to explain differences in performance.	Optional, for SCOUT display or additional model testing. One variable per column.

Sales 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 sales performance by store (and by department, if required).

Example: Store 354 had $1,876,456 in sales 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.
STORE_NBR	CHARACTER	Required	Used to join to Location file. Any STORE_NBRs present in this performance file that are NOT in the locations file will not be loaded to the Buxton database for visualization and will not be used for any analysis.	Foreign key to STORE_NBR on Store file
SALES_MONTH	DATE: MM/DD/YYYY	Required	Historic performance data is used to measure the stabilization or ramp period for new locations as well as measure the impact on existing locations when nearby locations 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 sales are reported by calendar month (e.g., 1/1/2018). If sales 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)
DEPARTMENT	CHARACTER	Optional	Will be used to define location performance by department if necessary for project scope.	Optional unless required for project scope. See your Buxton team for details.
TOTAL_SALES	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 locations and identify high vs. low performers.	Revenue
TOTAL_UNITS	DECIMAL	Optional		Number of transactions or items sold
UNITS_DESCRIPTION	CHARACTER	Optional		Description of TOTAL_UNITS. E.g., number of transactions, number of items sold, etc.

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
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 location and behavioral and psychographic traits of the customer.
CITY	CHARACTER	Required	Home address of the customer informs drive time to a location and behavioral and psychographic traits of the customer.
STATE	CHARACTER	Required	Home address of the customer informs drive time to a location and behavioral and psychographic traits of the customer.	Postal abbreviation
ZIPCODE	CHARACTER	Required	Home address of the customer informs drive time to a location and behavioral and psychographic traits of the customer.
STORE_NBR	CHARACTER	Required	Used to join to location file. Only transactions that join to location file will be included in analysis. Any STORE_NBRs present in the transactions file that are NOT included in the locations file will not be loaded for visualization or included in analysis.	Foreign key to STORE_NBR on store file
TRANSACTION_DT	DATE: MM/DD/YYYY	Required		1 year required, but 3-5 years of data is preferred (if available)
DEPARTMENT	CHARACTER	Optional		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.	Optional unless required by project scope and if Customer Types are defined by transactions.

Common Additional Fields

Stores
- Relocation/Acquisition Dates
- Store Hours
- Building Characteristics:
  - Square Footage
  - Visibility
  - Signage
  - Real Estate Type
Transactions
- Order method: In-Store vs. Mobile vs. Web
- Fulfillment Method: Pickup vs. Delivery
- Customer Type: Loyalty vs. Non-Loyalty, Members vs. Non-members, Active vs. Inactive, etc.

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

Stores

Address information is complete and provided for each location
Open date is provided for each location

Sales

All open locations are present in the sales file
Each location present in the sales file is also present in the stores file
At least 3 years of sales data is provided
Locations are not missing any monthly sales data
Data is not provided for future months
Departmental breakouts, if required, are included

Transactions

Duplicate customer IDs are not present
Address information is complete for at least 90% of records
All store numbers in this file join to the stores file
Locations are not missing one or more days of transactions
Transactions occur after location open date

Tags: MedSpa Data Requirements, Data Requirements, Required Data, MedSpa Data, MedSpa, Data Auditing, Data Audit, Auditing Data, Data