Longbow File Format and Upload Procedure

Introduction
In order to efficiently import and analyze your data, we have developed a standardized file format and upload procedure. The following information describes the required data as well as the optional information that you may send us to be analyzed. You will notice that we only require two files, the Transaction file and the Products file.

File Formats

1. All files should be text files.

2. Text qualifiers (quotation marks, etc.) should not be used. For example, the customer, ABC Hardware, should appear as ABC Hardware and not "ABC Hardware".

3. Use tabs, commas or pipes as field delimiters.We recommend tabs. Commas can cause problems if, for example, there is a comma in a name or address.

4. Once you decide which character to use as a column delimiter, do not change the delimiter in future uploads.

5. Use the same delimiter on all files.

6. All files need a header row. The column header is used to map the field into our database. The order of the columns is not important.

Field Names

1. Required fields must use the specified field names as outlined below.

2. Non required fields that have a field name (not xxx), must use the same field name as described below.

3. Non required fields that do not have a field name (xxx) can use any name as long as the name is less than 25 characters in length and only uses alphabetic characters a to z.

Transaction File
The transaction file should contain a customer identifier, transaction date, transaction amount and the itemid (or product sku) that was purchased. Returned items should be denoted by a negative amount.

file	field name	required	description
tx	custid	yes	the customer identifier
tx	itemid	yes	the item identifier, often called the sku
tx	txdate	yes	date of the transaction
tx	quantity	yes	number of items purchased
tx	amount	yes	amount
tx	xxx1	no	defined by user
tx	xxx2	no	defined by user

 

Products File
The Products file contains more information about the items being sold. Each item in this file must have an itemid, the same itemid as was used in the transaction file. There should be one record per itemid. The itemid is used to link the transaction to the extra information in the item file. The itemid must also have a description field that describes the item. All other fields in the file are optional, but can be useful.

file	field name	required	description
item	itemid	yes	the item identifier for the product being sold.
item	itemdesc	yes	the description of the item being sold
item	prodcat	yes	Product Category
item	prodclass	no	Product Class

 

Customer File
The customer file contains more information about the customers who are buying. (Note: fields that contain xxx would be replaced with your own field header names. It does not matter what they are as long as they make sense to you).

file	field name	required	description
customer	custid	yes	the customer identifier
customer	name	no	customer name
customer	address1	no	customer address 1
customer	address2	no	customer address 2
customer	state	no	state
customer	postalcode	no	zip code
customer	country	no	country
customer	sic	no	sic code
customer	salesrep	no	sales rep for this customer
customer	custtype	no	group customer is part of ____
customer	xxx1	no	defined by user
customer	xxx2	no	defined by user
customer	xxx3	no	defined by user

Example
A retail clothing store might sell 10,000 different items. The company sends Loyalty Builders two files; a basic transaction file that contains one extra user defined field called "channel", and a product file that contains a rich set of product categorizations. The files are tab delimited. The first figure represents the data in an easy to read table, the second figure shows that a snippet from the file.

Figure 1: tx data in table

custid	Itemid	amount	quantity	txdate	channel
aaa-001-1234	103021	199.99	2	1/1/2006	retail
aaa-001-1234	103029	12.55	1	12/1/2005	retail
bbb-001-1234	102041	92.02	1	12/14/2005	catalog
bbb-001-1234	123033	43.91	1	12/14/2005	catalog
cde-001-1234	143322	112.33	1	1/1/2006	retail
cde-001-1234	143322	-112.33	1	1/8/2006	retail

Figure 2: tx data in file

custid     itemid     amount      quantity     txdate     channel
aaa-001-1234     103021     199.99     2     1/1/2006     retail
aaa-001-1234     103029     12.55     1     12/1/2005     retail
bbb-001-1234     102041     92.02     1     12/14/2005     catalog
bbb-001-1234     123033     43.91     1     12/14/2005     catalog
cde-001-1234     143322     112.33     1     1/1/2006     retail
cde-001-1234     143322     -112.33     1     1/8/2006     retail

For clothes, each piece of clothing is categorized by gender and age; men's, women's, boy's, and girl's, or the season; winter, spring, summer, and fall. Clothing is further divided into pants, shirts, socks, coats and dresses. By providing this extra information it becomes much easier to find customers who might buy women's summer dresses rather than searching through the individual skus that represent women's summer dresses.

Figure 3: item data in table

itemid	Itemdesc	group	size	season	category
103021	mens white dress shirt	men	large	All	shirts
103029	infant sun dress	infant-girl	small	summer	dresses
102041	women bathing suit	women	small	summer	sports
123033	boy youth jeans	youth boy	medium	All	pants
143322	mens winter coat	men	large	winter	coats

Figure 4: item data in file

itemid    itemdesc    group    size    season    category
103021    mens white dress shirt    men    large    all    shirts
103029    infant sun dress    infant-girl    small    summer    dresses
102041    women bathing suit    women    small    summer    sports
123033    boy youth jeans    youth boy    medium    all    pants
143322    mens winter coat    men    large    winter    coats