Published on December 6, 2008
Merchant Product Datafeeds/Data Feeds for Affiliates – 101 by Carsten MERCHANT PRODUCT DATA FEEDS FOR AFFILIATES – 101 Article by Carsten Cumbrowski, January 2006, August 26, 2006, September 9 and November 15 2006.This is an updated and greatly extended version of my original Scrap. Carsten, 11/15/2006 TABLE OF CONTENTS Page 1 - Introduction, Datafeedformat and Delivery Options o Introduction o 1. Datafeed Format o 2. Delivery Method Page 2 - The Content o 3. Information to Include in the Datafeed / Content o 3.1 Comments on specific Content Page 3 - Comments, XML and Resources o 4. General Notes and Comments o 5. XML vs. Text Feed o 6. About this Article o 7. Resources, Third Party Datafeed Integration & Publishing Tools and Services Note: Datafeeds or Data Feeds are the same thing. Both spellings are being used in the industry (including the the grammatically wrong version). If you search for additional resources to the topic at forums or search engines, make sure that you use the singular and plural version (feed/feeds) in addition to the two different spellings. Search for quot;Datafeedsquot;, quot;Datafeedquot;, quot;Data Feedsquot; and quot;Data Feedquot; plus your additional keywords to get the best results. INTRODUCTION I have dealt with feeds from hundreds of merchants, including every U.S. affiliate network: Commission Junction, Linkshare, BeFree, Performics, ShareASale, Kolimbo, Dark Blue, you name it! The feeds come in all kinds of formats and via all types of delivery methods. I have also, as an affiliate manager, implemented datafeeds for merchants. With this in mind, you can believe me when I say that I know what I am talking about. If you are an affiliate manager and want to provide a datafeed to your affiliates, you might want to print this article and show it to your IT department. You can also contact me directly to ask any questions to affiliate datafeeds or give feedback on this document. Page 1 of 11
Merchant Product Datafeeds/Data Feeds for Affiliates – 101 by Carsten 1. DATAFEED FORMAT Delimited Text File. Please note: this is not CSV, which stands for comma separated values, already a very specific delimited text file format which I do not recommend for automated affiliate product datafeeds due to possible issues it can cause. Using the tab character (chr(9)) as column delimiter makes a lot of sense for affiliate datafeeds, since it can't be used on the web and should be replaced in the content as multiple spaces anyway. Tilde ( ~ ) and pipe ( | ) will do fine as well. I would avoid using commas or semicolons. I will provide a clarification in a moment. Row delimiter should be either (A) a standard windows line-break: carriage return/cr (chr(13)) + linefeed/lf (chr(10)) OR (B) a standard Unix linebreak: linefeed/lf (chr(10)) only without a carriage return. If a quotation mark (quot;) is not specified as a text qualifier, don't enclose strings with it. Simply avoid using a text qualifier such as a quotation mark or double quotes because people often misuse them and there are no real benefits for using them in affiliate datafeeds. Quotes and double quotes are often part of the feed content itself (product names like 19quot; monitor or Joe 'Jay' Doe Doll). If you use a text qualifier and the text qualifier itself is part of the content, the character in the content has to be replaced by 2x the character - ' becomes '' (2 single quotes) and quot; becomes quot;quot; to prevent the characters from being mistaken as the beginning or end of a column's content. The only case in which the text delimiter occurs doubly is when the column has no value. There is no fixed rule; if text qualifiers can be used for empty text values, but it is a special case that must be taken care of manually to prevent problems. Column and row delimiters must be removed from the feed content itself or be replaced. They have a meaning for the feed structure, and will be mistaken as errors in the feed structure if they appear in the content. You should provide column headers in the first row. Do NOT use SPECIAL CHARACTERS. You should only use 0-9, a-z, A-Z and, if you have to, the characters quot;-quot; or quot;_quot; to separate individual words. Do NOT use: !,?,&,%,#,',quot;,(,),,/ etc. Those are usually reserved characters in applications or programming languages. Also, avoid blank spaces. Use only ASCII code 32 (space) to 126 (tilde ~) for the content (called 7 bit ASCII, US-Char set, ISO 636 or ANSI [American National Standards Institute] Standard x3.4). Delimited files MUST have the same number of columns for every single record! If information is not available for a product, send the initial value of the column instead (0 for numbers, 0.00 or 0 for currency, nothing for text and character values and 1/1/1900 for dates). Page 2 of 11
Merchant Product Datafeeds/Data Feeds for Affiliates – 101 by Carsten Yes, this comment is directed straight at Linkshare. To begin with, Linkshare uses no column headers. Further, using a first row with four columns (quot;HDR,quot; MID, merchant name, date last updated) and last row with two columns (quot;TRL,quot; number of products) is against the definition of quot;delimited.quot; (By the way, thanks for letting me know at the end how many products I just read in sequential order. My computer cannot count.) XML format is fine, too, but only if you provide the data via a web services (SOAP/WSDL, REST etc.) or if you can generate correct XML (most people can't). Do not use only XML if you provide the feed as a file to download. Use GZip to compress the datafeed file if necessary (for large feeds), don't use Winzip (not all affiliates are using Microsoft Windows). 2. DELIVERY METHOD Offer FTP download, make the datafeed available via HTTP on the site (if the feed is < 5 MB) or set up a web service (SOAP/XML). If you can, do all three. Don't provide FTP upload as the only delivery option (it's very kind if a merchant offers that, but most of the time it's impractical for affiliates). It's fine if it is an option, but it should not be the only option. If you make it available as a file, don't make the name dynamic (make the creation date part of the file name for example). You might do that if the file is provided via FTP (NOT HTTP) for which affiliates can write a script that checks what file(s) are available. The file is scheduled to be generated automatically on A RELIABLE SCHEDULE: daily, weekly, monthly, whatever. For files generated daily, make sure to consistently generate the file during the same time window - which should be as small as possible and during the night. Do it as early as possible so that affiliates can also pick it up (and process it) during the night. When I say quot;night,quot; I do not necessarily mean quot;at night where you live,quot; but quot;at nightquot; for your targeted customers, also known as quot;low business hoursquot;. Remember to consider that your customers may be in different time zones. If you can, provide affiliates the information about a change of an HTTP datafeed that affiliates can check first if they need to update their content, before they waste time and resources downloading and processing the whole and unchanged datafeed for no reason. Have a second file with a fixed filename available via HTTP that contains information like: most recent date/time the feed was updated, filename of current feed, size, number of records, etc. Affiliates have different levels of sophistication and available tools. Some build links to selected products manually, some use simple scripts or software, and others have a high-end enterprise server at their disposal. Some run their stuff under windows, others use Linux. Page 3 of 11
Merchant Product Datafeeds/Data Feeds for Affiliates – 101 by Carsten The more formats and delivery methods you provide, the more affiliates can and will use your datafeed. Documentation is always king. A description of the datafeed structure and format takes the guesswork out of working with feeds. 3. INFORMATION TO INCLUDE IN THE DATAFEED / CONTENT Bold columns/attributes are considered quot;must havequot; or quot;requiredquot; by most affiliates. This does not make the others unimportant. If you have the information and can provide it, do it. SKU/Product ID (unique) Name of the Product Description of the Product Price of the Product (retail: price the customer has to pay) Product URL (Affiliate Link) URL to Product Detail Image URL to Product Thumbnail Image Regular Price or Retail Price/MSRP Product Category ISBN Number (books) - ISBN-13 (transition to ISBN13 will be in January 2007) UPC (US Barcode) - UPC-A or UPC-E International EAN / EAN-13 Manufacturer / Brand Name (separate columns if different) Manufacturer Part Number Color Properties delimited Size Properties delimited Keyword Suggestions Artist (Music, Art) Author (Books) Publisher / Label / Studio (Books / Music / DVD) Shipping Information (available options like ground/next day, restrictions) Shipping Cost for a single unit (if flat across the country) Availability Related Items (this could be a list of SKUs in delimited format like quot;123|321|456.quot;) Flags like Best Seller, High Rating, Clearance, On Sale, etc. Link to Product Category Buy URL/Add to Basket URL Brand/Manufacturer Logo (URL to image) 3.1 COMMENTS ON SPECIFIC CONTENT SKU/PRODUCT ID A unique ID for a product in the merchant catalog: either the internal product ID or SKU or UPC/EAN/ISBN number of the item (which is also unique). The manufacturer's part number can work as well in some cases. Most merchants already use some form of unique identifier for their products internally. Use it and don't hide it from your affiliates. Ask yourself why you have unique IDs in the first place and what you are using them for. Your answer is the same reason affiliates will use them. Page 4 of 11
Merchant Product Datafeeds/Data Feeds for Affiliates – 101 by Carsten If you have to generate unique product IDs for the affiliate datafeed only, consider the following: numeric IDs work the best, because they leverage the power of database software. Avoid letters if you can. Only use hyphens, dots or underscores if there is a practical benefit for doing so. Don't add them just quot;for looks.quot; MAKE SURE that there are no duplicate records with the same product ID in the datafeed. If alternative categories are available for a product, use additional columns for the alternative categories. Additional categories are often special promotional categories like Clearance and On Sale. Or, for example, a category that can be a top category by itself can also be a subcategory. For example: Sport Shoes can be top category and you also can have Sport Shoes as a subcategory for Women's Shoes, Men's Shoes and Kids' Shoes. I will come back to categories and category hierarchies and alternatives to static hierarchies a bit later (Faceted Classification). If a product has multiple size and color options, don't send a record for each possible combination unless you have a separate product detail page for each of them. Add the main SKU or Master SKU to the datafeed and provide available options (attributes) in separate columns. I will come back to the scenario where you might not have certain combinations available for the product, or you might have different prices for different combinations. DESCRIPTION OF THE PRODUCT Use a short description or a long description (or both in separate columns). Additional information and features may be included as separate fields, depending on how you have the data available yourself. Don't use HTML code unless it makes sense, and remember to keep it simple and don't use formatting such as font tags or colors, etc. Use tables only where absolutely necessary and assign a class to them (CSS) to give affiliates the ability to change the layout of the table without jumping through hoops. Div tags might be a better alternative to tables if you know how to use them. For everything else use bold, underlined, unsorted or sorted lists, and that's it. PRODUCT URL Affiliate link (URL) to the product detail page includes the affiliate ID (or a placeholder for the affiliate ID), which either you replace with the affiliate ID (on the fly when an affiliate requests the feed) or the affiliate replaces. In the latter case make sure to provide easy to understand (not too technical) documentation for your affiliates or you will end up with affiliates that do not replace the placehholder with their affiliate ID and contact you with a message that quot;your tracking is not working.quot; You might want to provide the direct URL without affiliate ID as separate column. This especially makes sense if the affiliate link is a completely different URL than the direct URL and not only the direct URL with the affiliate ID as additional parameter. REGULAR PRICE OR RETAIL PRICE/MSRP This column might have the same value as quot;pricequot; for some or all products, if the product is not on sale and/or you don't have products on sale at all and/or no MSRP is available. MSRP (manufacturer's suggested retail price) might be useful to provide as a separate column, if you have the MSRP for most Page 5 of 11
Merchant Product Datafeeds/Data Feeds for Affiliates – 101 by Carsten of your products - a regular price and a sales price. I know several merchants who do this. If you have products that go on sale or clearance with a reduced price, provide this information to your affiliates via the datafeed. It is a promotional sales tool not only for you, but also for your affiliates -- unless you don't want to sell your price reduced items :). Don't use the argument that the price changes frequently. If you are concerned about affiliates showing items on sale after a sale ends, you have multiple ways to prevent and combat it. Prevention: Add a column for the sale's end date. If you can, because there are no competitive reasons not to, provide the sale's start date if you know that a product will be on sale on a specific date and know the sale price. Some affiliates can work this into their marketing planning and increase exposure and traffic to your offers. If possible get datafeed access information, such as: which affiliate accesses the datafeed when (how often), how (if you offer multiple delivery options) and in which format (if you provide the datafeed in more than one format). Combat issues: Check affiliate sites that use your feed (this is where knowing where to look for datafeed access information comes in handy). Separate tracking and statistics for affiliate product detail links might help or referral data in the Website log of your Webserver. If you see something you don't like, contact the affiliate. For repeat offenders block access to the feed if you can. In severe cases terminate the relationship. Adding guidelines to your affiliate resource pages or terms and conditions might be a good idea, too. PRODUCT CATEGORY For example, quot;Jewelryquot; could be the product category; if an item is in a subcategory send it delimited by quot;>quot; or any other unique delimiter that will never be part of a category name, for example: quot;Jewelry>Rings>Engagement Rings.quot; If you utilize matrix-like, faceted or dynamic drill-down navigation (Faceted Classification) without having pre-defined paths or hierarchy offered by products like Endeca Guided Navigation, Dieselpoint Faceted Navigation or Fast ESP Dynamic Drill-down, great! I love the navigation based on Faceted Classification. The resulting dramatic increase in conversion proves it is the right approach, and the way to do it. You should provide all relevant attributes in separate columns via your datafeed. But not everybody is that advanced yet. Also, the price tag for a professional solution that leverages this method of content delivery, filtering and refinement is beyond the means of the average affiliate - and many merchants as well. Page 6 of 11
Merchant Product Datafeeds/Data Feeds for Affiliates – 101 by Carsten If you have the money to afford the solutions mentioned above, you should have the money and resources needed to generate for your affiliate product datafeed an artificial hierarchy based on the common paths taken by users on your site. Navigation based on Faceted Classification and XML Feeds excel and show their real power in cases where you have products with multiple possible values for a single attribute. In this case the delimited format shows one of its biggest weaknesses, which can only be worked around to a limited extent by having multiple values for an attribute in one quot;Fieldquot; separated by a third delimiter (not the same as used for columns and rows). To provide information about the availability or the lack of specific combinations of values for two or more attributes, delimited values for each attribute involved must be ordered, and nonexistent attribute values for another attribute value must be specified via a value that indicates that it does not quot;exist.quot; Example: Product A is available in three sizes: S, M and L and the colors: white and black. All but one of the six combinations is available; L/black. quot;-quot; indicates that the combination does not exist and a comma is used to separate the values. The values for Size and Color in the datafeed Size: S,S,M,M,L,L Color: white,black,white,black,white,- If you never have combinations of more than two attributes to worry about, the missing value pair for the two attributes could be dropped. I used it in the example because you would need it for combinations of three attributes. No problem if you have a different price for each value pair. Simply add another column for the prices, structured the same as the other attributes. Like this (0.00 indicates quot;not availablequot;, you could use something like -1 to make it more obvious): Price: 5.00,5.00,7.00,7.00,10.00,0.00 As you can see, this method has its limitations and becomes impractical for large number of attributes and/or values. AVAILABILITY Available, ships within 24 hours, back order, temporarily out of stock, etc. Don't add products to the feed that are permanently out of stock; add only what you have or what you can get. BUY URL Add to Basket URL that automatically adds the item to the shopping cart. This eliminates one more step for the user to purchase the product and at the same time eliminates one more step where the user might decide to quot;bail outquot; and leave the sales funnel. Page 7 of 11
Merchant Product Datafeeds/Data Feeds for Affiliates – 101 by Carsten 4. GENERAL NOTES AND COMMENTS CONTENT IS KING The more, the better. Not all affiliates use datafeeds the same way. The more information provided by the merchant, the more affiliates can and will benefit from it - it's as simple as that. Some use only selected products while others use simple showcase creators, price drops, quot;on salequot; features, price comparisons, or niche sites where people look for very specific things. Some use datafeeds for multiple purposes and a few (often bigger) affiliates can use and leverage ALL information you provide. The more information you provide, the more affiliates can and will use your datafeed. DOCUMENTATION IS KING TOO Documentation is always king when it is about technical things like product datafeeds, which are essentially a data interface between merchant and affiliate. You can also call it API standing for Application Programming Interface if data are delivered in real-time in response to affiliate data requests. The best examples of APIs are web services. If you have interfaces to your vendors for other purposes already, ask your IT department about the importance of good documentation. They should actually write the first documentation for you (technical specifications). You should provide additional documentation for non-technical affiliates. Practical examples are always good, use screen shots for illustration of more complicated examples. Providing sample source codes (in as many programming languages as you can get your hands on) to automate datafeed downloads will help you to have more and more affiliates automate the download. Affiliates also appreciate links to resources on the web. Provide information about the content, including what affiliates can expect to find in the columns/attributes. This becomes especially important for network feeds where the feed structure is fixed and you have to work with the given structure without the ability to add custom columns to it. Examples of this are datafeeds provided by Commission Junction, ShareASale and Darkblue. BeFree provides the option for custom columns; Linkshare allows up to 10 custom columns. Linkshare feeds actually have either no custom columns or 10 custom columns with no other option in between. Feeds that use one or all of the custom columns are the ones where quot;Attribute Filequot; is specified as quot;yesquot; in the Linkshare Merchandiser participants list provided by Linkshare in Excel format. The Performics datafeed allows custom columns, too. Network datafeeds without descriptive columns require even more documentation, because some networks completely neglect to mention what data, in what format, might be found in the feeds. Network feeds without descriptive columns include datafeeds provided by ShareASale, Linkshare and Kolimbo. Page 8 of 11
Merchant Product Datafeeds/Data Feeds for Affiliates – 101 by Carsten MISUSE OF STANDARD COLUMNS IN NETWORK FEEDS If you have to use standard columns of a network feed for something else, fine, but you have to document it (that's true for all network feeds including CJ, BeFree, Linkshare, ShareASale, Performics, and others). You will probably become subject to angry emails written by affiliates using language that becomes worse depending on the time the email was sent, indicating how much time the affiliate wasted before figuring out what the problem with your feed is. Pick columns where the type of data the column was originally intended for by the network have the same format as the data you put there. For example, don't dump a character string in a column that was meant to hold dates or numbers or currency (some networks sadly allow that). INITIALIZE VALUES If you use a column/attribute of a standard network feed, always send data and do not leave it empty for products where you don't have the information. For example, if you provide a quot;sale pricequot; send at least quot;0quot; or the quot;regular pricequot; for products that are not on sale; don't leave the column empty. The technical term for this is to quot;initializequot; the columns/attributes used. SUMMARY I can live with feeds that don't have enough information. I simply don't use them. I can fill some gaps myself if I believe it's worthwhile to do so. What I cannot live with are errors in the file structure or garbage in the content. Maybe a scraper site can use that junk, but affiliates who design sites for users with the goal of providing value to their website visitors can't make gold out of it. They risk driving off customers who never come back. No thanks. I waste a lot of my time doing the following: Fixing datafeed structures (pre-processing); and Technically and often even quot;logicallyquot; validating the content of each column. We use provided category structures or hierarchies. I have to spend a lot of time with messed up category structures such as: thousands of products assigned to a single subcategory Category with a subcategory where both have the same name Subcategory with another single subcategory only, but no products Categories with hundreds of subcategories The list goes on and on! It includes all things that make the quot;catalogquot; not quot;browseablequot; by a user. Page 9 of 11
Merchant Product Datafeeds/Data Feeds for Affiliates – 101 by Carsten 5. XML VS. TEXT FEED XML files are much bigger and still not always as easy to process (especially if the XML format is proprietary and you don't get the XML schema sent with the XML data file). A benefit of XML is the amount of data per quot;recordquot; or product you can send. XML has something like a 2 GB limit. This is different with delimited files. The maximum number of characters per quot;linequot; is usually limited by the software that is using the files. 4 KB, 8 KB (kilo bytes, 1 KB = 1024 bytes or characters which usually exclude the column separators), etc., but we rarely have an issue with that when we deal with product datafeeds. A merchant can never send too much information! If a merchant sends so much information that I have a problem handling it, that's a good problem to have. :) XML makes perfect sense for a web service (XML/SOAP/WSDL). Tools that support the creation and/or use of web services are getting easier to use every day. You don't have to deal with the actual XML most of the time anymore. The conversion to XML typically happens in the background automatically, without you, the developer, having to worry about it. XML as an underlying format for the communication between sender and receiver is as good as any, because the interface provides the data in a useful structure and format that you are familiar with in your particular programming environment. A good analogy would be TCP/IP. You don't have to understand the structure of the traffic via TCP/IP if you do an FTP transfer. It's used by server and client in the background, automatically, without you having to worry about it. I dedicate an entire article to XML and web services as data delivery format and platform for affiliate marketing, where and when it makes sense and beats classic delimited datafeeds. The Article is called: quot;XML Datafeeds and Webservices for Affiliates.quot; 6. ABOUT THIS ARTICLE This article is a summary of various emails I have sent to merchants and networks. Some of the emails were to complain about issues with the feed, while others were to make recommendations. I made considerable additions and changes months later, after receiving a lot of positive feedback from merchants, smaller affiliate networks and affiliates alike when I put the first version of this article online. I have also sent emails to merchants who started an affiliate program and are thankful for every bit of information that helped them to get their program going. I like those the most since they are usually the ones who really listen and try as hard as they can to help you out. It works out to be a two-way street, since I can consequently do a better job promoting them and generate sales for them and commissions for me. Page 10 of 11
Merchant Product Datafeeds/Data Feeds for Affiliates – 101 by Carsten 7. RESOURCES, THIRD PARTY DATAFEED INTEGRATION & PUBLISHING TOOLS AND SERVICES I am always on the lookout for useful third party services and developer tools that provide products or services to make it easier and faster to utilize affiliate datafeeds. To see the results of my research and years of spending time on this topic, check out my compiled list of Affiliate Product Datafeeds Tools and Services. Also visit the Affiliate Datafeeds Resource Page of my Affiliate Marketing Resource Site, my Web Development, APIs and Webservices Resource Page. Powerful Database Servers are the key for the work with large affiliate product datafeeds. If you are a developer, you might find my SQL Server and MySQL Server Resources useful to expand your understanding and knowledge of SQL and make you more effective in leveraging affiliate datafeeds, APIs and web services. Cheers! Carsten Cumbrowski Page 11 of 11
Datafeeds/Data feeds provided by merchants to their affiliates. A comprehensive primer for merchants and affiliates to avoid mistakes and get the most out ...
Documentation is King too Documentation is always king when it is about technical things like product datafeeds, which are essentially a data interface ...
What is and Affiliate Datafeed? ... with lots of information about a merchant’s products. ... Affiliate data feeds are provided by merchants to ...
Data Feeds 101 (?) Thu Aug 07, 2008 1 ... I see sparrpw has a tool to change data feeds into RSS, ... for affiliates and affiliate managers/merchants ...
As an affiliate manager I am always looking for tools to help our affiliates promote our merchant products. Datafeedr not only makes importing datafeeds ...
6 Ways to Find Product Datafeeds in your Affiliate ... Merchants have datafeeds and how ... for Merchants and Affiliates to sync up product ...
Product Data Feeds Affiliates are able to download product feeds similar to the one that you supply us. ...
Affiliate datafeed sites ... I have also had a keen interest in promoting the responsible use of merchant data by affiliates. ... Datafeeds 101 – A ...
... best for your needs as a merchant when creating a new data feed. We support data feeds in two ... one way to submit product data to Merchant ...