Home › Sales Data Transmission Protocol for Beer Finder

Sales Data Transmission Protocol for Beer Finder

Introduction

Beer Finder helps consumers to locate particular beers near the zipcode or Lon/Lat geolocation. The database table is filled with sales location data from distributors. Distributors periodically provide the sales data in the given format (see below), and the data is kept for 60 days.

File Name and Format

The file name contains a three letter abbreviation of the distributor (Capital Letters Only) and month and day numbers referring to the first date of data within the file. The format is CSV file with "csv" or "CSV" as the file extension. Example: BEK1123.csv.

File Content

  • The first line of the file contains the date range for the sales entries. Example:2009.11.23-2009.11.30. The starting date of the date range should match the month and day numbers in the file name. Also, the starting date will be used to determine if the data is older than 60 days, which should be removed from the database table.
  • The second line to the forth line are the space for comments, which could be empty.
  • The fifth line of the file contains the column headers, which are:
    1. "ACCT#"
    2. "DBA"
    3. "ON OFF"
    4. "ADDRESS"
    5. "CITY"
    6. "STATE"
    7. "ZIP"
    8. "PHONE"
    9. "ITEM#"
    10. "BRAND"
    11. "DESCRIPTION"
    12. "WEB ID"
    13. "QUANTITY"
  • The order of the columns matters, but the actual labels for the columns do not matter. Descriptions of each field appear below:
    • "ACCT#" is the account number from the distributor's internal accounting system. This number will be prefixed with the distributor's three letter abbreviation prior to being uploaded to the database.
    • "DBA" is the name of the retail account.
    • "ON OFF" can only contains a single value: "ON PREMISE" or "OFF PREMISE".
    • "ADDRESS" is the street address of the retail account.
    • "CITY" is the name of the city of the retail account.
    • "STATE" is the two letter abbreviation of the state of the retail account.
    • "ZIP" is the five digit zip code of the retail account.
    • "PHONE" is the phone number of the retail account.
    • "ITEM#" is the distributor-assigned item number for the product.
    • "BRAND" is the brand name of the product (frequently the brewery name, but for larger brewers with several brands - i.e. Anheuser-Busch - it is the specific brand name - i.e. Michelob)
    • "DESCRIPTION" is the description of the product (product name, package size)
    • "WEB ID" The unique Web ID of the product, the Web ID is generated by GreatBrewers.com automatically when a new product is set up on the website.
    • "QUANTITY" The quantity sold at the place. This is used only to confirm a positive quantity (not a pick-up). If you prefer to keep this information private, you may replace all quantities with the number 1.
  • The rest of the lines include sales data.

File Verification

Since different distributors will transfer the beer finder files over internet, it is important to make sure the transportation does not corrupt the files, which might cause damage or even corruption to the database, which could drive GreatBrewers.com offline. Therefore, a CRC Checksums of the file needs to be generated in a SFV format, using MD5 algorithm, please see the documentation here. The SFV file should be transferred together with the beer finder data, and the verification needs to be done at reception.

If the file does not meet the specification of this protocol, it cannot be uploaded and notices will be sent out.

Upload/Remove Frequency

The sales data is uploaded to the database every Tuesday (or Wednesday if anything delays unexpectedly). Any data older than 60 days is manually removed after the uploading process.

Troubleshooting

  • Sales data file is too large to upload
    • If a .csv file is too large to upload, the recommended practice is zipping the file and transmitting the zipped file. To do this (on a PC), right click the .csv file, select "Send To" and choose "Compressed (zipped) Folder".

Live Statistics