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:
- BigCommerce Admin Access
- Your Google Products settings already configured. (If you haven’t done this yet, start here.)
- Notepad ++ (Download free here.)
- Microsoft Excel (or a comparable spreadsheet program.)
- 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.
Choose the country (I chose United States) and click “Export my products to a Google feed file”
Click “Download my Google Shopping Feed” file and save it to your Desktop.
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:
You want to find one that looks like this:
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)
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:
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:
On the toolbar, select “Data” and “Text to Columns”:
Select “Delimited” and hit Next.
Select “Other” and add the Pipe character (its right above the Enter button, usually) and click “Finish”
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:
…now type CTRL + H and perform the following find and replace operations to take the HTML and unnecessary data out of the spreadsheet:
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.
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.