SAQ Marketing Information

Download of Raw Data

Technical Specifications

Table of contents

• 1.     CONTEXT
• 2.      FILE DESCRIPTIONS
• 2.1     WEEKLY SALES
• 2.2     INVENTORIES BY OUTLET
• 2.3     INVENTORIES BY WAREHOUSE
• 2.4     ORDERS
• 2.5     BAR CODES
• 2.6     PRODUCTS
• 2.7     OUTLETS
• 2.8     WAREHOUSE
• 2.9     SUPPLIERS
• 2.10   AUTHORIZED DISTRIBUTORS
• 2.11   MY PRODUCTS
• 2.12   RELATED DATA DEFINITIONS
• 2.13   RELATED DATA
• 2.14   SALES SUMMARY ALL PRODUCTS (CSV)

1.    Context

This document provides detailed technical specifications for the Marketing Information raw data files of the Société des Alcools du Québec (SAQ). These files can be downloaded using the « Marketing Information » function on SAQ-B2B web sites.

The Marketing information will be updated once a week. The date of the most recent information update will be displayed on the Marketing Information start up page.

2.    File descriptions

The files produced are in CSV format. The separator used is a semicolon ( ;).
All files generated are compressed (ZIP). A ZIP file may contain several CSV files.
The file names for the SAQ Marketing Information are not translated. The following documentation provides the English description for each file, but the actual file names will use the French labelling.

2.1  Weekly sales (Ventes hebdomadaires)

File Name : Ventes_YYYYPPW.csv (where YYYY=Year, PP=Period, et W=Week)

Element	Type	Length
SAQ CODE (IPC)	Character	8
SUPPLIER NO	Numeric	8
NO SUCCURSALE	Character	8
TYPE CLIENT	Character	3 (see Related data files)
NO DA (Distributeur autorisé)	Numeric	8
ANNÉE	Numeric	4
PÉRIODE	Numeric	2
SEMAINE	Numeric	1
QTÉ BOUTEILLE	Numeric	12
MONTANT	Numeric	15 (digits including 2 decimals)

The AMOUNT field represents the number of units sold (QTY OF BOTTLES) multiplied by the products current sale price.

2.2  Inventories by Outlet (Inventaires par succursale)

File Name : Inventaires succursales.csv

Element	Type	Length
OUTLET NO	Character	8
SAQ CODE (IPC)	Character	8
INVENTORY QTY (in bottles)	Numeric	13 (digits including 4 decimals)
YEAR	Numeric	4
PERIOD	Numeric	2
WEEK	Numeric	1
DATE	Date	(YYYY/MM/DD)

2.3  Inventories by Warehouse (Inventaires en entrepôt)

File Name : Inventaires entrepots.csv

Element	Type	Length
WAREHOUSE NO	Character	8
SAQ CODE (IPC)	Character	8
INVENTORY QTY (in cases)	Numeric	13 (digits including 4 decimales)
YEAR	Numeric	4
PERIOD	Numeric	2
WEEK	Numeric	1
DATE	Date	(YYYY/MM/DD)

2.4  Orders (Sous-commandes)

File Name : Sous-commandes.csv

Element	Type	Length
SAQ CODE (IPC)	Character	8
ORDER NO	Character	50
ORDER DATE	Date	YYYY/MM/DD
QTY ORDERED	Numeric	9
ORDER UNIT	Character	10
EXPECTED DELIVERY DATE	Date	YYYY/MM/DD
PURCHASE PRICE	Numeric	9 (including 2 decimales)
CURRENCY CODE	Character	3 (see following liste)
YEAR	Numeric	4
PERIOD	Numeric	2
WEEK	Numeric	1

CURRENCY CODES

---

ARS     Argentine Peso AUD    Australian Dollar CAD    Canadian Dollar CHF     Swiss Franc CLP     Chilean Peso CSK     Czech Koruna (sera remplacé par = CZK) DKK    Danish Krone EUR     Europe Euro GBP     United Kingdom Pound HKD    Hong Kong Dollar IEP       Irish Punt (avant 2002, remplacé par Euro	ILS     Israeli New Shekel JPY     Japanese Yen LBP     Lebanese Pound MXP    Mexican Peso NOK    Norwegian Kroner NZD    New Zealand Dollar SEK     Swedish Krona SKK    Slovak Koruna USD    US Dollar ZAR    South African Rand
Browse the full list

2.5  Bar codes (Codes à barres)

This file, along with the « Products » file, are grouped together within the same ZIP file. The file is named according to the following format «Produits_et_UPC_YYYYMMDD999999.zip», where YYYYMMDD is the date the file has been prepared, and 999999 is a sequential number.

It is important to note that a product number at the SAQ (IPC for Internal Product code) may have several bar codes in the bar code file. For example, a supplier may use a different bar code for a new vintage while the SAQ decides to keep the same product number from one year to the next for the same product. In this case, the bar code with the most recent «START DATE» will generally represent the latest bar code transmitted by the product supplier to the SAQ.

File Name : Codes barres.csv

Element	Type	Length
SAQ CODE (IPC)	Character	8
BAR CODE	Character	14
TYPE	Character	2
START DATE	Date	(YYYY/MM/DD)
END DATE	Date	(YYYY/MM/DD)

2.6  Products (Produits)

This file along with the «Bar code» file are grouped together within the same ZIP file named:
«Produits_et_UPC_YYYYMMDD999999.zip», where YYYYMMDD represents the date the file was generated, and 999999 is a sequential number.

File Name : Produits.csv

Element	Type	Length
SAQ CODE (IPC) (CIP)	Character	8
DESCRIPTION	Character	60
SUPPLIER NO	Numeric	8
PUBLISHED SUPPLIER NO	Numeric	8
SALES UNIT	Character	6
COUNTRY OF ORIGIN CODE	Character	7
DESTINATION COUNTRY CODE	Character	7
COLOR CODE	Character	2
PRODUCT SALES PRICE	Numeric	8 (including 2 decimales)
LOCAL/IMPORTED INDICATOR	Numeric	1 (0 local 1 imported)
ALCOHOL %	Numeric	5 (including 2 decimales)
CURRENT VINTAGE	Numeric	4
PURCHASING VINTAGE	Numeric	4
FORMAT	Character	7
CATEGORY	Character	3
PRODUCT TYPE	Character	1
LISTING TYPE	Character	3
DISTRIBUTION NETWORK	Character	8
UNIT OF MEASURE	Character	2
REGULATED DESIGNATION	Character	4
AROMAS	Character	3
GRAPE VARIETY	Character	4
NATURE OF PRODUCT	Character	5
PRODUCT STATUS	Character	2
SUGAR RATE	Character	1
CLASS OF FORMAT	Character	2
QUALITY	Character	4

2.7  Outlets (Succursales)

This file, and all following files, are grouped under one ZIP file, named «Autres_donnees_connexes_YYYYMMDD999999.zip» (which means «other related data»), where YYYYMMDD represents the date the file was generated, and 999999 is a sequential number.

File Name : Succursales.csv

Element	Type	Length
OUTLET NO	Character	8
OUTLET NAME	Character	100
BANNER	Character	3
ADDRESS	Character	40
CITY	Character	25
PROVINCE	Character	25
POSTAL CODE	Character	7
PHONE NO	Character	25
SALES MANAGEMENT REGION	Character	3
OUTLET STATUS	Character	10

2.8  Warehouse (Entrepôts)

File name : Entrepots.csv

Element	Type	Length
WAREHOUSE NO	Character	8
WAREHOUSE NAME	Character	40

2.9  Suppliers (Fournisseurs)

File Name : Fournisseurs.csv

Element	Type	Length
SUPPLIER NO	Numeric	8
SUPPLIER NAME	Character	80

2.10  Authorized distributors (Distributeurs autorisés)

Nom du fichier : Distributeurs autorises.csv

Element	Type	Length
AD NO	Numeric	8
AD NAME	Character	80
ADRESS	Character	40
CITY	Character	25
POSTAL CODE	Character	7
PHONE NO	Character	25

2.11  My products (Mes produits)

This file is used to identify the products for which, according to the SAQ, your company is the supplier or his representative (agent). Please note that the downloaded Inventory and Order files will only contain information for products appearing in this list.

File Name : Mes produits.csv

Element	Type	Length
SAQ CODE (IPC)	Character	8
DESCRIPTION	Character	60
SUPPLIER NO	Numeric	8
AGENT NO	Numeric	8

2.12  Related data definitions (Définition des références)

This file is used to identify the key (SYSTEM + TYPE) required to locate in the «Related Data» file the values pertaining to codes to be converted. This file merely defines the links between specific elements of previous files against records listed in the «Related Data» file. Several element types of the previous files actually contain codes.

The actual text or definition of these codes is found under the «related data» file. You may need to refer to the actual file content for more information.

File Name : Definition des references.csv

Element	Type	Length
SYSTEM	Character	2
TYPE	Character	4
DESCRIPTION	Character	30
CODE LENGTH	Character	2

2.13  Related Data (Références)

This file contains the descriptions for each possible value of the different types of codes used in the Products, Sales and Branch files. Although the Description column mentions a possible length of 50 characters, the length of the descriptions varies for each type of code.

Please also see the description of the file «Definition of references».

File Name : References.csv

Element	Type	Length
SYSTEM	Character	2
TYPE	Character	4
CODE	Character	8
DESCRIPTION	Character	50

Here is an example :

The «Products» file contains a column named «Unit of Measure» (Unité de mesure ) That column contains, among others, the following values : BT, LT

The file «Related Data Definitions» contains an entry providing the applicable key for the codes of type «Unité de mesure».

The key appears below in bold:

SYSTEM	TYPE	DESCRIPTION	CODE LENGTH
0	UM	Mesurement units	2

Under that key the «Related data» file contains an entry for each value of the related column «Unit of Measure». Please note that these values are not translated .

SYSTEM	TYPE	CODE	DESCRIPTION
0	UM	BT	Bouteille
0	UM	LT	Litre

2.14  Sales Summary All products (CSV) (Sommaire des Ventes Tous les produits)

The subscription for download of this file type is found under an option of the same name located in the «Additional Reports» section of the Marketing Information subscription page.

File Name : Ventes_Sommaires AAAAPPS.csv (où AAAA=année, PP=période, et S=semaine)

Element	Type	Length
SAQ CODE (IPC)	Character	8
UPC	Numeric	8
DESCRIPTION	Character	60
SALES UNIT	Character	6
QTY OF BOTTLES	Numeric	12
YEAR	Numeric	4
PERIOD	Numeric	2
WEEK	Numeric	1