Skip to main content
MedSpa Data Requirements
J
Written by Jera Malone
Updated over 11 months ago

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

Did this answer your question?