USL General Information
INTRODUCTION
The Universal Store List (USL) is the global list of store locations used for all work at BDS Marketing. This list feeds all of the systems at BDS that need store address and store status information. Since most of the work conducted by BDS on behalf of their clients takes place in stores the health of the store list is vital for the success of BDS.
This document is a review of the rules and processes set up to maintain the health of the USL for use by all the teams at BDS.
USL GENERAL INFORMATION
Stores are constantly being added and updated in the USL.
GENERAL STORE DATA FIELDS
The following data fields are maintained for each location in the USL. Here is the list with a brief explanation. This is not an exhaustive list but the most commonly used data points.
- BDSStoreID – Commonly called BDS Number. A unique number for each location in the list.
- ChainID – Lookup to the BDS Chain list denoting the Chain associated with this location.
- ChainStoreNumber – The retailer chain store number for this location. Note: Each chain has its own store number 1 which can be the same as other chains therefore BDS implemented the BDSStoreID which is a unique store identifier.
- StoreName – Listed name for the store. For example the chain may be Verizon however the stores name is BusyBee Wireless.
- AddressLine1 - store address – “12 Mulberry St.”
- City – Name of the city for this locations address
- State –State for this location – for example “WI”
- PostalCode – 5 digit numeric and 6 character alphanumeric code for sorting mail for this address.
- Country –Country for this location. – for example “France”
- PhoneNumber – xxx-xxx-xxxx primary phone number for this address MallName – Name of Mall that this location is located within.
- Latitude – Latitude for the location as provided by Microsoft Bing Spatial Data Services. Expressed as a number: 43.181501
- Longitude - Longitude for the location as provided by Microsoft Bing Spatial Data Services. Expressed as a number: -88.004499
- MarketID – Lookup to the Market list for this locations market.
- CBSA - Market designation for USA locations. Core Based Statistical Area is the official term for a functional region based around an urban center of at least 10,000 people
- FSA - Market designation for Canadian locations. About 850,000 postal codes exist in Canada, using Forward Sortation Areas.
- DMA - Market designation defined as Designated Market Area. The DMA rank is a metric that is used to prioritize media markets by the size of their TV and radio audiences and is displayed with a market name.
- Market Rank - Numeric ranking of all CBSA markets based on census population numbers
- Status - Open, Temporally Closed, Permanently Closed
- Store Type - Retail Store, Headquarters, Warehouse/Distribution Center, International, Training Center, Kiosk, Dealership, Online Store, Call Center
- Store Last Updated Date - The date and time a store location was updated.
ADDRESS STANDARDIZATION
Before stores are added or compared to the USL they are first updated using a BDS Address Standardization algorithm, based loosely on the United States postal standard, to convert them into the BDS standard address format. This standard allows addresses from outside the USL to be compared with stores inside the USL and matches to be found.
For example:
From Client List Changed to standard in USL
23498 Erie Street North 23498 Erie St N
ADDRESS GEOCODING
How and when do stores get geocoded?
Geocoding a location provides a more precise location on the earth compared to utilizing the store address. This precision is needed for mapping and calculation of distances for mileage. There are two methods for geocoding stores:
Automated – Most stores in the USL get geocoded using Microsoft Bing Spatial Data
Services. An automated process sends stores electronically to Bing and returns them with Longitude, Latitude, and a Geocode confidence indicators. This is the same process for people. When a new person or store is added to the database an indicator is set that it needs to be geocoded and the process grabs those locations and returns the geocode information.
Manual – There are times when the automated process provides geocode information that isn’t as precise as necessary for mapping or mileage. In those cases the address is entered into the Bing mapping website manually and the database is updated with the correct longitude and latitude.
CELLULAR & AUTO CHAIN ORGANIZATION
To accommodate the unique organizational needs of the Cellular and Auto industries the locations under these “dealers” and “carriers” are stored in specific ways that need to be understood to use them appropriately.
Cellular
Below is a list of chain names in the USL for major carriers, 8 for now. It is categorized this way because one cellular store can carry multiple carriers like Sprint, Verizon, and ATT, and so on. Some carriers have three chains like Verizon, Verizon Indirect Exclusive, and Verizon Indirect. The “Verizon” chain is for Verizon corporate-owned stores. The “Verizon Indirect Exclusive” chain is normally for franchise retailers that only carry Verizon service and products. The “Verizon Indirect” chain is for franchise “mom-and-pop” stores that carry Verizon in addition to other carriers. A carrier store can only be in one of these chains in the USL.
Chains of different carriers are independent of each other even though they are in the same physical store.
For example:
If Bob’s cellular carries AT&T, Verizon, and Sprint then the address will appear under 3 different chains, “AT&T Indirect”, “Verizon Indirect” and “Sprint Indirect” chains. Each instance is treated independently with their own BDS number. So, if a rep reported Bob’s cellular is closed in “Verizon Indirect”, that doesn’t mean Bob’s cellular in the other two chains will be closed automatically. It just means Bob’s cellular no longer carries Verizon, but is still carrying AT&T and Sprint. If a Bob’s cellular store is physically shutting down, all reps for each carrier will report to close Bob’s cellular for the three carriers.
Verizon
Verizon Indirect Exclusive
Verizon Indirect
ATT
ATT Indirect Exclusive
ATT Indirect
T-Mobile
T-Mobile Indirect Exclusive
T-Mobile Indirect
U.S. Cellular
U.S. Cellular Indirect Exclusive
U.S. Cellular Indirect
Sprint COR
Sprint Preferred
Sprint Other
MetroPCS
MetroPCS Indirect
Cricket Wireless
Cricket Indirect
Boost Indirect
Boost Indirect Exclusive
Auto
Auto dealerships often have one physical address however sell and service many different brands of vehicles at this address. To properly categorize a dealership in the USL, the dealership address is entered into the USL with a unique BDS number for every brand it services. An example of this is a Land Rover, Jaguar dealership in Laguna Niguel, CA. There is one entry in the USL for the Land Rover chain and another for the Jaguar chain under different BDS numbers.
CLUSTER AND GENERIC LOCATIONS
Cluster
i:c was designed to capture data regarding visits to store locations associated to Chains (Best Buy, Target, etc.) in the USL. When teams want to visit locations that are not a “store” to do a training or event and still want that events details captured on a call form and associated with a specific Chain then a cluster BDS # is available for each chain in the USL.
Example:
BDS #
Chain Name
Store Name
Store Type
Address
City
State
Zip
120033
6th Avenue Electronics
6th Avenue Electronics - Cluster
Training Center
Training Ctr
Anywhere
TR
00000
Generic
Generic BDS numbers are similar to the Clusters however they are not associated with a chain. Generic locations are used when an event is not at a store and is either for more than one chain or not for a chain. These BDS numbers should be used infrequently because they provide no geographic or chain information to clients.
There are 35 generic locations numbered like the example below sequentially.
BDS Store #
Store Name
516779
Generic Store 01
516780
Generic Store 02
516781
Generic Store 03