Bigcommerce Tutorial – Create a Bing Merchant Product Listings Feed without Expensive 3rd Party Software

Featured Off 115

Product Listing Ads from Google and Bing are a low-cost, effective way to get targeted traffic to your products. In fact, many Protocol Three clients see the best ROI and conversions through Product Listing Ad campaigns.

Product Listing Ads (or PLAs) are produced by exporting your your site catalog into a feed file (XML or CSV) and then uploading the information into an advertiser’s site. The data is aggregated and inserted into search results for people shopping for that particular product.

Bigcommerce provides a tool to create a product feed for Google and submit it to them but lacks this feature for Bing. While Google is certainly much larger than Bing, in my experience both convert traffic into sales quite well. There is no reason to neglect this channel of highly targeted, low-cost advertising.

Bing product feeds have been a Bigcommerce feature request for many years now. Bing says they can handle a Google XML feed, I have never found that uploading one does anything but fail. So, sadly, there seems to be no intuitive simple way to do this and the alternatives suggested are 3rd party systems with a monthly payment that still require time to set up and use properly.

For those of us who want to use Bing Product Ads without the extra expense of marketplace feed software, I’ve outlined a process that you can follow to produce a Bing-ready product feed from the Bigcommerce Google XML file in a relatively short amount of time.

I’ll demonstrate this using our own picnic basket gifts site as an example.

To get started, you will need:

  1. Bigcommerce Admin Access
  2. Your Google Products settings already configured. (If you haven’t done this yet, start here.)
  3. Notepad ++ (Download free here.)
  4. Microsoft Excel (or a comparable spreadsheet program.)
  5. Your store verified in Bing Advertising.

First, download the Google XML feed.

In the Bigcommerce Admin, go to Marketing >> Google Shopping Feed.
Note: your admin panel may look different depending on the dashboard version.

Bigcommerce Bing Product Feed 1

Choose the country (I chose United States) and click “Export my products to a Google feed file”

Bigcommerce Bing Product Feed 2

Click “Download my Google Shopping Feed” file and save it to your Desktop.

Bigcommerce Bing Product Feed 3

You now have a file called GoogleBaseFeed.xml on your desktop.

Clean up the extra categories in Notepad ++

Bigcommerce will list each category as a “Product Type”. This is really unnecessary for us and will break the CSV file we’re going to need to create if we don’t remove these extra instances.

Note: This step only applies if you have products assigned to more than one category. If you do skip this step and open it into Excel to find that your product data is staggered across multiple rows, you need to follow this step.

1) Open GoogleBaseFeed.xml in Notepad ++

2) Click CTRL + F. In the “Find What” box, type in and click Find Next until you find an instance where are in two corresponding rows.

For example, most products in your store will probably look like this:

Bigcommerce Bing Product Feed 4

You want to find one that looks like this:

Bigcommerce Bing Product Feed 5

We’re going to remove that second entry with a simple Find & Replace operation.

Type CTRL + H and follow these steps:

Find What: Grab ONE INSTANCE of a repeated </g:product_type> <g:product_type> and paste it into the box.
Replace With: XXXXXXXX (or any pattern of unique characters)

Bigcommerce Bing Product Feed 6

Note: Make sure you put your cursor at the TOP of the document and click “Replace All”

Now we need to remove the “XXXXXXX” and the extra instance of </g:product_type>.

Choose the Regular Expression radio button at the bottom of the Find/Replace window and do the following:

Find What: XXXXXXXX.*?</g:product_type>
Replace With: </g:product_type>

So far so easy, right?

Let’s turn this Google Feed into a Bing Product List

Next, we’re going to get this data out of its XML format and into a more Bing-friendly product feed.

1) Open the revised Google XML Data Feed in Excel.
Click to “Open as an XML file” and close off any notices/warnings.

2) Use this in the furthest right column, usually column Y, Z, or AA, paste this formula into the formula box and copy it down across all rows:

=G2 & "|" & H2 & "|" & I2 & "|" & J2 & "|" & K2 & "|" & L2 & "|" & M2 & "|" & N2 & "|" & K2 & "|" & O2 & "|" & P2 & "|" & Q2 & "|" & R2 & "|" & S2 & "|" & T2 & "|" & U2 & "|" & V2 & "|" & W2 & "|" & X2

This will take all contents of each row, starting with the Product Name, and apply the pipe symbol (|) a a delimiter. The end result will look something like this:

Bigcommerce Bing Product Feed 7

COPY the entire contents of this column from top to bottom row.

Open a NEW excel document. In Cell A1, right-click and click Paste Special. In the pop-up, click “Values” and click “OK”.

**It is IMPORTANT that you click “Paste Special” and not just “Paste” or you will only see #REF! in every column instead of the data.

We now have a list of all products with a pipe | delimiter to easily separate into columns.

Now, click the “A” column in Excel:

Bigcommerce Bing Product Feed 8

On the toolbar, select “Data” and “Text to Columns”:

Bigcommerce Bing Product Feed 9

Select “Delimited” and hit Next.

Bigcommerce Bing Product Feed 10

Select “Other” and add the Pipe character (its right above the Enter button, usually) and click “Finish”

Bigcommerce Bing Product Feed 11

We now have a list of data with each piece divided into individual rows.

Naming the Title Columns for Bing’s Feed Requirements

Google’s Feed structure of each column is:

Title   Link   Description   Expiration   Date   ID   Condition   Product Type   Brand   ????????   Image Link   Model Number   Shipping Weight   Height   Length   Width   Google Product Category   MPN   Availability   Price

You can safety remove “Expiration”, “ID”, “Shipping Weight”, “Height”, “Length”, “Width”.

These should be columns D, E, I, L, M, N, O. Hold the CTRL key, highlight each column with the mouse, and delete them.

Add a row at the top of the spreadsheet and add these column headers:

title   link   description   condition   product_type   brand   image_link   id   product_category   mpn   availability   price

Bing does accept these additional columns but they aren’t required. In case you want to use them here they are with data examples:

  • bingads_grouping – cookware
  • bingads_label – premium,cookware,set
  • custom_label_0 – premium
  • custom_label_1 – cooking
  • custom_label_2 – set
  • custom_label_3 – black
  • custom_label_4 – cookware
  • bingads_redirect – http://r.merchant.com/1/
  • sale_price – 17.99
  • sale_price_effective_date – 2014-11-05T08:15-05:00/2014-11-10T09:30-05:00

A Few more Clean-Up Steps

In my experience, Bing’s feed submission really seems to hate any form of HTML, so let’s fix a few pieces of the spreadsheet’s data before we submit anything to them.

Highlight the entire spreadsheet by clicking the arrow symbol at the very top left corner of the spreadsheet:

Bigcommerce Bing Product Feed 12

…now type CTRL + H and perform the following find and replace operations to take the HTML and unnecessary data out of the spreadsheet:

Bigcommerce Bing Product Feed - Find and Replace 1

Bigcommerce Bing Product Feed - Find and Replace 2

Bigcommerce Bing Product Feed - Find and Replace 3

Depending on your configuration, you may have more HTML symbols in your data that Bing won’t accept. You will have to submit a “Test Catalog” in their Shopping interface and see if it produces errors. You will be able to tell from there if you’re OK or not to submit your catalog for review.

Save the file as a Text (Tab Delimited) file to your desktop for easy access.

Granted you’ve already verified your store in Bing Merchant, you should be ready to upload this catalog into Bing Advertising to get your products approved.

FINAL WORDS

Once you’ve submitted your feed, Bing can take up to three (3) days to publish your product catalog and allow you to set up a Product Ad campaign.

Granted it’s a little messy and imperfect, but I think if you follow these steps once your twice you’ll find you can do all of these steps in about 5 minutes from start to end.

Bing requires you submit an updated product feed every 30 days, so 5 minutes of work is relatively little work that can save you tens of dollars investing in marketplace/template data feed software and applications when you’re not even sure if that marketing channel is going to work for you.

Good luck!

About the author / 

Adam

Adam is an enigma, folded gently into dough, wrapped inside of cellophane, and hidden on the top shelf where he peers at passersby. He lives in Portland, OR with his wife and two dogs.

Rate This Commerical

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Categories