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. Note 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), for example prodclass in the Products file, must use the same field name as described in the table below.
3. Non required fields that do not have a specified field name in the tables below 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 (required)

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 (required)

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 (not required)

Although all Longbow uploads are encrypted and secure, some clients prefer not to upload a customer file.  It's no problem as we only need your transaction and products files to perform our analysis.  You can match your customer files to the customer IDs after you download your target lists. The benefit to uploading your customer file is that is saves you this step.

If you do send a customer file, the customer ID field is required. (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 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