Practical Ecommerce

Formatting a Product Spreadsheet for Google Merchant Center

Google has created a service that helps online retailers list and submit products to Google’s Product Search, Product Ads, and Commerce Search offerings. This service, called “Google Merchant Center,” can accept a data feed of product information.

In this article, you’ll find a description of how to format a text data feed from a spreadsheet, including an explanation of many of the most important or required columns necessary to get your products listed with Google.

What Google Merchant Center Feeds Support

There is a key term and a few site descriptions that will help to explain the process of submitting product details to Google Merchant Center and why submitting those product details can be good for an ecommerce business.

First Google Merchant Center accepts “data feeds.” A data feed in this context “is a file made up of a list of items which use groupings of attributes that define each one of your items in a unique way. Attributes, such as ‘condition’ and ‘availability’, can have standardized values, or accepted ‘answers,’ or attributes can been open to fill with your own choice of value, such as ‘id’ or ‘title’. Accurately describing your items using these attributes allows users to search and find your items more easily,” according to the official Google definition of the term from the Merchant Center site.

Perhaps more simply, the Merchant Center data feed may be thought of as a spreadsheet — like Microsoft Excel or Google Spreadsheet — wherein each column represents some product attribute like price, name, SKU, UPC, or product description. Each row in the spreadsheet is an individual product.

Product information passed to Google in the Merchant Center data feed is used to support three distinct Google services.

Google Product Search is an online shopping search and deal site that Google maintains. Results from the Google Product Search also appear on standard Google results pages. Essentially, this service will help you put more of your products in front of potential buyers.

The Merchant Center data feed can also be used to work in conjunction with pay-per-click advertising campaigns on Google, allowing AdWords advertisers to see their product listings. Called Product Ads in the context of the Merchant Center, this is the second service to which the data feed provides product information.

Finally, the data feed is also used to power Google’s Commerce Search, which is a hosted search solution that merchants can use to provide a Google-powered search on their own sites.

The Data Feed Specification

Google has a very detailed data feed specification. This specification is divided into eight sections that apply to online sellers in North American and Europe. Each section has several required and accepted product attributes — columns in the spreadsheet.

Basic Product Information

The first data feed section is for communicating standard product information. Theses attribute names should appear as column titles in your spreadsheet. For column headers that contain more than one word, you should use an underscore character rather than a space, since the data feed will be passed in a text format.

  • id is a unique product identification. It can be any combination of numbers and letters, but once it has been set and submitted it cannot change. Rather it will also be used to refer to a particular product as long as the product is listed.
  • title is the name of the product. For example, “Converse All-Star Shoe.” Google recommends including identifying attributes like color or size in this title, so “Converse All-Star Shoe – Navy Blue – Size 10.”
  • description is up to 1,000 characters of text explaining or describing the product. Google recommends using at least 500 characters.
  • google_product_category is only required for products that are being submitted to the “Apparel and Accessories,” “Media,” and “Software” categories in the Google product taxonomy, but Google does recommend this attribute for all products. It is also important to note that you may only specify a single product category in this field, so choose the most applicable. The data you insert will take the form of “Apparel & Accessories > Shoes > Sneakers.” You will need to spend some time with the Google product taxonomy to discover what you should submit here.
  • product_type is a somewhat redundant column that accepts a Google product category just like the google_product_category attribute mentioned above. Here, however, you may submit more than one category.
  • link is a URL aimed directly at the product on your site. This field must begin with “http://” or “https://.” It must not link to a landing page or any page requiring registration. If symbols are used in the URL, those symbols must be replaced with URL encoded entities — see the explanation from W3Schools. For example a “?” is replaced with “%3F” and a “=” is replaced with “%3D.” Finally, the link must be to a domain you have already validated with the Google Merchant Center.
  • image_link is not a required field, but it just makes sense to include a link to a product image. The image is stored on your servers, and must be at least 250 pixels by 250 pixels, although a larger image would be better, say 660 pixels by 660 pixels. Remember this is a link to the image, not a file name only, so “http://some-domain.com/some-img.jpg.”
  • additional_image_link is specifically for providing additional product views, so from different angles as an example. You may include up to ten additional images, separating each image with a comma in your spreadsheet field.
  • condition is a required field that may contain one of three options, “new,” “used,” or “refurbished.”

You are essentially building a spreadsheet with the attributes as column heads.

You are essentially building a spreadsheet with the attributes as column heads.

Price and Availability Information

The next section of the spreadsheet is required to provide Google with your product prices and how many of each product that you have in inventory. Google encourages merchants to keep this data up to date.

  • availability is a field that may contain one of four values: “in stock,” “available for order,” “out of stock,” or “preorder.”
  • price is the item’s price before tax in the United States and with the value added tax in other nations. The field’s value will look something like “100.00 USD,” where “USD” stands for United States Dollars.
  • sale_price is, as its name implies, a special price for a product. The field format is identical to the price field. When this option field is used, the product detail page associated with the product must display both the regular price and the sale price.
  • sale_price_effective_date is a date range describing when a product should be considered to be on sale. This field takes the format of four-digit year, two-digit month, two-digit day, and time based on a 24-hour clock. For example, if an item was to go on sale on December 21, 2011 at 1:00 p.m., it would be “2011-12-21T13:00.” Notice that the “T” prefaces the time. A slash may be used to separate a starting and ending sale date. For example, if the sale above was to end on December 25, 2011 at 2:00 p.m., it would be “2011-12-21T13:00/2011-12-25T14:00.”

Unique Product Identification

Google also asks for some specific and unique information about each product.

  • brand is the brand name of the product. It is required for all items except books, media, and custom made goods.
  • gtin is the field for capturing a Global Trade Item Number like the UPC or ISBN code. It is not required if brand and manufacturer part number are used, but is still recommended if it is available.
  • mpn is the manufacturer’s part number.

For Apparel

For U.S. merchants, Google requires some additional information for apparel products. Merchants in other nations are not required to submit this information, but Google has indicated that it may become required in the future.

  • gender is answered with one of three options, “male,” “female,” and “unisex.”
  • age_group is used to distinguish between “adult” and “kids.”
  • color is used to describe the item’s dominant color.
  • size is used for size information like XL, 30/36, or similar.

Tax and Shipping

There are three fields related to tax and shipping. You can also establish tax and shipping rules in the Merchant Center settings, so that these are essentially optional.

  • tax is a relatively complicated grouped field that contains tax rate information for each jurisdiction for which the merchant is responsible to collect. Each jurisdiction is identified by an ISO 3166 country code, like “US” for the United States, followed by a region. In the U.S., the region can be a two-letter state abbreviation or zip code range where the “*” is used as a wildcard (i.e. 836*), representing any combination of numbers. The tax rate is included as a percentage. Finally, you must tell Google whether or not tax is charged on shipping. If you wanted to specify state tax for the U.S. State of Idaho at 6 percent and indicate not to charge tax on shipping, you would use “US:ID:6.00:n.” So that “US” stands for the United States, “ID” stands for Idaho, “6.00” percent is the tax rate, and the “n” means not to charge tax on shipping. If more than one jurisdiction should be specified, separate the values with a comma. Note that the colon must be present even if some value is omitted. So imagine a national U.S. sales tax of 9 percent in addition to the state sales tax., “US::9.00:n,US:ID:6.00:n .”
  • shipping is used to specify estimated shipping charges for each product. This is optional, since general shipping information can be set up when you create a Google Merchant Center account. If you do use this field, it takes a format similar to the tax field in that there are groups that specify a country, region, service level, and price. So if ground shipping to New Mexico were $9.99, you would use, “US:NM:Ground:9.99.” Additional values would be separated with commas.
  • shipping_weight is used to describe the weight of the item in pounds, ounces, grams, or kilograms. All told there are eight accepted units specifications, lb, pound, oz, ounce, g, gram, kg, kilogram. An example value would be “5 pounds” or “16 oz.”

For Multi-Channel Merchants

If your business sells both online and from a physical store and if that physical store is listed with Google Places, you can tell Google whether or not a particular item is available in that physical store.

  • online_only is used to tell Google whether or not the item is available online only. Simply use a “y” to indicate that it may only be purchased via ecommerce or “n” to indicate that it is also available in a physical store.

Specifying Where Products Are Used

Google has two more fields that may be added to your spreadsheet data feed. These fields control where products appear and whether or not products listed in the data feed should ever expire.

  • excluded_destination is used to tell Google not to submit the product information to one or more of its services. Accepted values are “Product Search,” “Product Ads,” or “Commerce Search.” Remember, specifying a service excludes it.
  • expiration_date tells Google that a particular item should not be listed after the date indicated. The format for the date should be a four-digit year, two-digit month, and two-digit day, so that December 25, 2011 is “2011-12-25.”

In the Spreadsheet

Each of the descriptions above represents a column head in your product spreadsheet. Each row in the spreadsheet should represent a single product. Once the spreadsheet is completely filled out, you will need to save it as a tab-delimited text data file.

If you were working in Microsoft’s Excel, you may choose “Save as” from the File menu. In the Save as box, choose “Text (tab delimited). Then save. You may be asked to confirm.

Other spreadsheets — like Google Spreadsheets or LibreOffice Calc — have similar options for saving as a tab-delimited text file.

You Will Need an Account

Before you can actually submit your products to Google Merchant Center, you will need to create an account and verify your domain. But once your account is created and your data feed is ready, you can begin submitting products to Google.

Summing Up

This article has described in some detail how to format a data feed for Google Merchant Center. This data feed will help you get your products in front of many potential buyers searching for products on Google. Preparing and updating your product data feed can be time consuming, but hopefully this post will help you.

Armando Roggio

Armando Roggio

Bio   •   RSS Feed


email-news-env

Sign up for our email newsletter

Comments ( 9 )

  1. allstarjd74 December 20, 2011 Reply

    Great post- a lot of good information here.

  2. Andrew December 21, 2011 Reply

    Great post Armando. Users will also want to make sure they add accurate tracking parameters to each product URL they insert into their data feed. Google has a cool tool which helps merchants figure out how to tag them:

    [http://support.google.com/googleanalytics/bin/answer.py?hl=en&answer=55578](http://support.google.com/googleanalytics/bin/answer.py?hl=en&answer=55578)

  3. Armando Roggio December 21, 2011 Reply

    Andrew, yes, of course. Good point.

  4. ELaine November 24, 2013 Reply

    Good Post here!
    But I have some Q to clarify hope anyone can help. My data feed have items with in sale price but not all out of 1787 items there are only 87 in sale price and some are not. Can anyone advise me what text should i put to the column of the items were not in sale.
    Thanks

  5. Greg Rovithis March 24, 2014 Reply

    THANK-YOU, THANK-YOU , THNAK-YOU!!
    I have been fiddling with submitting my business’ products for a couple weeks and getting warmer and warmer but there has just been no resource to explain what some of the acceptable, or even standard formats and information to put into these fields (including Google’s own “Help”, ahem, section) especially for tax and shipping, which are the last two problematic colums and errors that I have been struggling (for weeks)to resolve.
    Your article was clear, concise and beyond helpful with ACTUAL ANSWERS on what to use in some of these fields. I cannot express enough how helpful you have been and I am finally ready to submit my feed and see “NO errors” !
    You rock ! You’re the friggin’ rockstar-iest Rockstar in all Rockstardom and we thank-you very, very much!
    Greg – Hot Mess T-Shirts

  6. Mark October 10, 2014 Reply

    I would love to see on example spreadsheet for a custom product that does not have a UPIC,brand, etc.

    • Sharon March 21, 2015 Reply

      Me too Mark. I am not familiar with spreadsheets but I have all my data in Excel and added a column for “identifier_exists2 but I’m unsure if I put “FALSE” in every column down :/ A sample spreadsheet would be a great help for custom products.

  7. paulina johnson May 23, 2015 Reply

    THANK YOU!!! finally someone that explains in simple English for those of us that are not in the profession.

  8. rizwan June 22, 2017 Reply

    Well, but i am facing 2 errors item group Id & Multiple values for variant attribute, could anyone to elloberate how can i resolve this error