Apr 09, 2012

The Feed Doctor Unveils a New Business Rule Function: JOINNB

We’ve got quite a few business rule superstars here at ChannelAdvisor. I was talking to two of them, Alisyn Collins and Carol Scheible, about a rule they had written. They had used JOIN to splice together a bunch of fields with a delimiter between them. But some of the fields were blank sometimes, so they had extra delimiter characters they had to get rid of. To fix this, they were using a pretty scary regular expression. They suggested that we needed a new function:

“What we really need is a join that skips blanks, so you don't end up with duplicate delimiters in the first place.” 

This was definitely a facepalm moment on my part: of course we need a function for this! You might even argue that that’s the way JOIN ought to work by default. But since it’s kind of risky to change the behavior of things that aren’t, strictly speaking, broken, I decided to make this a new function. I let Carol and Alisyn pick the name: JOINNB. The “NB” is for “no blanks.” 

Here’s an example of how it works. Let’s say that $sku is “ABC,” $brand is blank, and $mpn is “123.”

If you wrote this rule:

 you’d get this output:

But if you use the new function:

you get this:

This new function will ship with the April release, just in time for Catalyst. Speaking of Catalyst: I’ll be there myself, so if you get a chance, come by and say hello!

Blog post by Anthony Alford, The Feed Doctor. 


Mar 09, 2012

The Feed Doctor's New Business Rule Functions

Have you ever wondered how we decide on the different functions that we build into the business rule system? Today I’ll tell you the story of two brand new ones.

I get asked to help write lots of complex business rules. Usually, I can figure out a solution, but sometimes it’s not pretty. If it’s a problem that comes up a lot, sometimes I like to create a brand new function to help make it simpler. Here's a recent example where I was asked for help on writing a rule. The input to the rule is a comma-separated list of values. The rule should output as many whole values in the list as possible so that the total length of the output is less than 50 characters. So for example, if the input is:


Then the output should be:


You can easily do the part about "less than 50 characters" using the LEFT function, but if you do that, you get this:


So, you have to do some REGEX magic to cut off that last comma and letter j, and it can get messy. It turns out, I've helped people with this very problem several times; I even wrote a blog post on it LONG ago: The Feed Doctor Makes a Long Story Short.

Finally, I decided it was time to build this function into the system. I created a new function, LEFTWORD. You give it the input text, the maximum length, and a "delimiter," which can be anything; in our example, it would be a comma. If you have a list of words and you want to “chop” it on word boundaries, you can use a space. If you have several sentences and you want to chop at sentence boundaries, use a period.

Another thing I like to do from time to time is take a look at some of the patterns of business rules that people write. If I see that numerous people are writing long, complex rules to solve the same problem, it’s obvious that there's an opportunity to create new functions. Here’s a pattern that we’ve had, basically forever: Write a rule that outputs value #1, unless it's blank, in which case it outputs value #2, unless that's blank, in which case it outputs value #3, unless...you get the picture. You can do this with the IFBLANK function, but since IFBLANK only allows two inputs, you have to "nest" the IFBLANK calls, like this:

IFBLANK(IFBLANK(IFBLANK("value #1","value #2"),"value #3"), "Value #4")

What a pain! Why can’t IFBLANK be more like CONCATENATE and JOIN, which can accept a variable number of inputs? Why indeed. So, I decided to fix that, too. Now IFBLANK will accept up to 8 inputs, and will output the first (i.e., "leftest") one that is not blank:

IFBLANK("value #1","value #2","value #3","Value #4")

These changes rolled out yesterday along with other updates that are included here in the complete Release Notes.

For more information on comparison shopping best practices, download 16 Common Comparison Shopping Mistakes to Avoid at All Costs.

Blog post by Anthony Alford, (a.k.a. The Feed Doctor), Technical Lead

Feb 22, 2012

E-Commerce superstars are coming to Vegas!

Vegas sign

This year’s Catalyst conference is stacking up to be the best ever.

This is largely due to the superstar line-up we’re bringing to Las Vegas in April. Marketplaces Newegg and Walmart.com are the most recent additions to the sponsorship list that also includes Amazon, eBay, Google, Buy.com, ShipWorks, PayPal, Terapeak and many more. We’ll have Sebastian Gunningham from Amazon, Michael Jones from eBay, Matt Eichener from Google, and now Gary Vaynerchuk and Zia Daniell Wigder have joined the list of keynote presenters as well.

If you haven’t heard of Vaynerchuk, this video will give you a little insight into what Catalyst attendees have to look forward to: Turning up the Tweet.

Gary V

Online marketing expert and author of The Thank You Economy Gary Vaynerchuk

Zia Wigder of Forrester will join us in Vegas as well to share her thoughts on the hot topic of international expansion, with a focus on the fastest growing global e-commerce markets: Latin America and China. 


Forrester Research, Inc. Vice President and Research Director Zia Wigder

Stay tuned—we’ve got more exciting announcements about Catalyst coming in the next few weeks.  Our event space is limited, so you should probably go ahead and secure your ticket now before Catalyst is sold out!

Catalyst is sure to be a valuable opportunity for anyone in online retail. Don’t miss out, register for Catalyst NOW!

For more details and a full list of speakers and sponsors, read Best and Brightest Of E-Commerce To Participate In ChannelAdvisor Catalyst Conference.

Drawing a record number of participants and attendees, the Catalyst theme, NewCommerce, is proving to be top of mind for the leading retailers and companies in the e-commerce industry.

NewCommerce defines trends that span social media to cross-border trade, tablet computing and “couch commerce,” new smartphone capabilities and more, all of which create an ‘anytime, anywhere’ level of accessibility for shoppers and represents the accelerating complexity for retailers and manufacturers trying to reach consumers.




Dec 14, 2011

A Dose of Tips from The Feed Doctor

Much of the wisdom of software development is expressed as wry---nay, even cynical---aphorisms. My favorite (attributed to Hamming) is: "Mathematicians stand on each other's shoulders while computer scientists stand on each other's toes." That one is merely descriptive, but the most important ones are prescriptive, like this one from Knuth:

"Premature optimization is the root of all evil"

Programmers obsess over "optimization." Mostly they mean they want their programs to run faster, because everybody hates waiting. In case you haven't noticed, business rules ARE little programs, and as my gift to you, I'm going to give you 3 tips that could make your business rules run faster.


Let's say you've got a rule that has three possible outputs:

1. If brand is "Acme," output "Free Overnight Shipping"
2. If brand is "X," output "Free Shipping"
3. Otherwise, output "See Site for Shipping Cost" 

I usually advise people to use SELECTCASE when dealing with more than one condition, like this:

SELECTCASE($brand="Acme","Free Overnight Shipping",$brand="X","Free Shipping","See Site for Shipping Cost")

I think this looks cleaner than the alternative, using nested IFs:

IF($brand="Acme","Free Overnight Shipping",IF($brand="X","Free Shipping","See Site for Shipping Cost"))

These two rules have exactly the same output, and I assumed they took about the same time to run. I was surprised to find out that the 2nd rule is TEN TIMES faster! Here's the fundamental truth of "optimization:" it's always a trade-off. In this case you are trading clarity (first rule) for speed (2nd rule). Choose wisely!


Here's a pattern I've seen a few times:

IF(ISINLIST("Some list",$brand),LOOKUP("Some list",$brand),"Default text")

This rule checks to see if brand (it could be any attribute, of course) is the left-hand or "name" column of a list. If so, the rule outputs the corresponding data from the right-hand or "value" column. Otherwise, it outputs some default text. This is perfectly fine. However, if ALL the entries in the list have a non-blank "value" column, the rule could be this:

IFBLANK(LOOKUP("Some list",$brand),"Default text")

The second rule is twice as fast! The reason is that the first rule ALWAYS looks up the value in the list TWICE, whereas the second rule only does it once. Now, again, there's a trade-off: if your list has blank entries in the "value" column, the rules are NOT the same.

Regular Expressions

Ah, who doesn't love a regex? (By the way, here's another aphorism “Some people, when confronted with a problem, think ‘I know, I'll use regular expressions.’   Now they have two problems.’) However many problems you wind up with, you'll have this one at least: regexes can be SLOW. And given two regexes that do more-or-less the same thing, one could be MUCH slower than the other. For example, here are two rules that remove html tags from a description:



I won't go into the details of these; instead I will note that the second one is faster by about 50%. Be very careful with your regexes. Most of the tradeoff is in the time it takes you to write the thing; sometimes you have to be pretty clever.

There you go friends: 3 tips for faster rules. With all the time you save, you might be able to watch all 35 bowl games this year.

Blog post by Anthony Alford, (a.k.a. The Feed Doctor), Technical Lead

Nov 18, 2011

Countdown to Cyber Monday: Daily Tip 4

In exactly one week, thousands will rise at ungodly hours, camp out and form store-wrapping lines to kick off the Cyber 5! And for online shoppers the dedication to deals is just as intense!

Make the most of the rush by enhancing your visibility on Comparison Shopping Engines.

Tip #4: Comply with Google Product Search Changes

We’ve got a secret…Google Product Search is going to be HUGE this Holiday Season. Aside from its ever-increasing popularity as a shopping starting point, a number of apps, like RedLaser (found to be the most widely used barcode scanner by our Global Consumer Shopping Habits Survey), now use GPS data to compare prices and locate products. 

GPS iphone

Get on board with the most recent GPS specifications and check the requirement list—twice—to optimize on traffic and sales. Apparel retailers will want to pay special attention to include product variation data (color, material, pattern and size) as well as unique images for items that vary (by color, pattern or material). For more details, check out Google’s Feed Specification Help Page.

GPS boots

Bonus Tip: Retailers should heed to Google’s wishes by listing shipping and tax costs, too. Incorrect or excluded cost data can lead to suspension and, more importantly, can deter shoppers who expect total price points at a glimpse. Still think it’s not a big deal? Check out this Search Engine Land article warning retailers against these violations, as well as the guest blog post from Google's Mayuresh Saoji on this blog. 

If you've missed our past tips here they are: 

Tip #1: Add Gift-Themed Categories

Tip #2: Offer and Highlight Free and Expedited Shipping

Tip #3: Bid Up Early and Often

Blog post by Natalie Sink, ChannelAdvisor Communications Assistant


Sep 30, 2011

The Feed Doctor Returns: Percentage Savings Rule

25OFF Recently I've been asked how to create a rule to generate a "percent
savings" message. Let's say your sale price is in an attribute called
$itemstoreprice, and the regular price is $itemretailprice. The formula
for percent savings is:

100 * (1 - $itemstoreprice/$itemretailprice)

Here's the result for an item that's marked down to 14.99 from 19.99:

Well, that's kind of ugly. Maybe I only want whole numbers. That's easy to
do with the FORMATNUMBER function:

FORMATNUMBER (100 * (1 - $itemstoreprice/$itemretailprice), "F0")

That "F0" means to output a Fixed number of digits to the right of the
decimal; in this case, zero digits.

And here's the result: 25

If you wanted two digits to the right of the decimal instead of "F0" you'd
have "F2" and the result would be 25.01.

Let's stay with "F0," and some text:

$itemstoreprice/$itemretailprice), "F0"),"% off retail!")

Here's the result:

Save 25% off retail!

Blogpost by Anthony Alford: The Feed Doctor

Sep 19, 2011

Guest Post: Google's Mayuresh Saoji Shares Product Search Feed Changes

Guest Post from Mayuresh Saoji, Senior Product Manager, Google

Mayuresh Saoji  As of September 22, 2011 Google will begin gradually enforcing the new Product Search feed spec requirements announced earlier (link). Beginning Sept. 22, Google will being dropping items that do not meet the new spec requirements. In some cases, Google may even completely suspend accounts that don't meet the new requirements from Google Product Search. We’re guest-blogging here to make sure that ChannelAdvisor’s retailers are well aware of the changes, and can act in time to avoid items from being dropped or accounts from being suspended from Google Product Search, as well as to provide you with tools available from Google to help you prepare. 

New tools to help you prepare
  • First, use the new Test Data Feed feature in Google Merchant Center (under “Data Feeds”) to test whether your updated feed complies with the new requirements. You will be able to download a full error report and resolve any issues prior to submitting your actual live feed. Rest assured, your test feed won’t appear on Google Product Search.
  • For additional guidance, you can now view our new video tutorials:
  • We are revamping the "Data Quality" tab in Google Merchant Center to enable you to quickly view the most critical data quality errors, and learn how to fix them (coming soon).
Understanding the new requirements

We’ve received a few questions about specific attributes and have addressed these below. It’s a lot to read, but should save you time in the long run. Also, before you cancel vacation for your IT staff, please note that many of the requirements only apply to certain countries and categories.
  • Google Product Category (aka GPC): This attribute ensures that your products appear in the right category (you wouldn’t want that Harry Potter DVD showing up under “Books”) and that we apply the correct set of enforcement rules for a given category.
    • GPC is only required for feeds targeting the US, UK, Germany, France or Japan
    • You don’t necessarily have to send us this attribute. It is only required for items that belong to one of the following seven categories:

1. Apparel & Accessories > Clothing

2. Apparel & Accessories > Shoes

3. Apparel & Accessories

4. Media > Books

5. Media > DVDs & Movies

6. Media > Music

7. Software > Video Game Software

    • For items falling under these categories, you must include one of these seven values appropriately for each item.
    • More granular categorization is always preferred, but don’t stress out about this. If you can follow the level of categorization above, then you’ve satisfied our requirements.
  • Images: We made image_link required for all products. Images are especially important for product in the “Apparel & Accessories” category, where shoppers love to see the different variations of a product.
    • Required worldwide (except Japan)
    • For products that fall under “Apparel & Accessories” (and all corresponding sub-categories), we require unique images for products that differ by the variant attribute ‘color’, or ‘pattern’, or ‘material.’ No one likes seeing a black sweater when they click on the red one.
    • We recommend sending separate images for variant products in other categories as well, but these are only required for “Apparel & Accessories.”
  • Apparel/ Variants: Variety is the spice of life, and we’re working to create a richer shopping experience for apparel & accessories that come in multiple colors, patterns, sizes, etc.
    • Apparel variants are only required for feeds targeting the US. For feeds targeting other countries, the attributes are recommended and may be required in the future.
    • Variant-level information is required only for products in the “'Apparel & Accessories' category, and all related subcategories
    • You only need to send us data for variant attributes if your product varies by that specific attribute. So, if your shirts are all made of cotton, there’s no need to send the “Material” attribute. However, if your shirts were available in three colors and three sizes, you would send us nine separate line items, varying by color and size.
    • There is no penalty for not sending variant level data for other categories
    • If you include variants, you also need to ensure that you send an “item_group_id” to connect those variants and that these group of variants share the same common title. More on this below.
  • Item_Group_ID: We use this attribute to cluster together all the variants you send us for a given item. Sort of like Crazy Glue for variants.
    • This attribute is required only for variant Apparel products in the US.
    • If you have a “Parent SKU” shared by all variants of a product, you can provide that as the value for 'item group id'.
    • If you send us an item_group_id attribute, we will automatically look for variant attributes. Conversely, if you did send us Item_group_id, you should ensure you send us at least one variant attribute.
  • Size: This is an important Variant attribute for “Apparel & Accessories.”
    • Size is only required for feeds targeting the US.
    • Separate your products into different line items in the feed (each line will have a different “size” attribute, and maybe even vary by other attributes)
    • There’s no need to send separate images for separate sizes (unless the appearance of the item changes because of the size)
Please refer back to our detailed Product Feed Specification and Help Center for more information. We hope these tips will help you be more fully prepared to make the most of Google Product Search as we head into the most important selling season of the year for online retailers. 

Shop On! 

Blog Post by Mayuresh Saoji, Senior Product Manager, Google Product Search


Jun 24, 2011

Google Changes Webinar Q+A follow-up...

Before Internet Retailer, on June 9th, we held a webinar about google changes including Panda, Google Product Search (GPS) changes and the impact on CSEs.   After 70mins we still had ~10 questions in queue and promised to answer those.  Here are the answers to those questions.  

If you missed the original webinar, you can listen to a recorded version of it here from our webinars page (http://www.channeladvisor.com/webinars/) or follow this link to go directly to the webinar.

Q1: On Google for the term 'clearvite apex',  we show up in organic search, page 1 in the top 5 results.  Yet we do not show up in google shopping until page 2.  How do the results from shopping differ from the organic search.  What can be done so that shopping results are more consistent with the “everything” organic search? 

A1: The two algorithms are not the same as you've seen.  Pagerank looks at the content (URL, page, etc.) and the inbound links.  GPS uses the content and then factors such as sales rank (CTR as proxy), etc.  Without knowing more, it's hard to say, but usually our GPS experts can analyze a feed file and find a whole host of things that can be improved on the content side to improve your rankings which drives more sales which increases your sales rank, etc. 


Q2: Can you please tell me if I have this link right. I am having a problem accessing it. http://www.google.com/products/seller?zmi=cdrackem.com 

A2: Weird, usually that shortcut works.  We tried using the longer form and got there here: 


The longer form instructions:

  1. Go to a google SERP and click on 'rated' for those that have merchant ratings.
  2. Edit the URL to be your URL instead of the one you clicked on. 

Q3: Also have you ever heard of "influencing keywords". I am using an SEO company called XXXX that specializes in 'influencing keywords'. I fear at this time they be doing me harm rather than good. How can I find that out? 

A: As we said on the webinar, any SEO firm that claims to have a silver bullet that 'guarantees' you some kind of SEO results, should probably be avoided.  If they are out there buying links for you, that is very very dangerous and has resulted in overstock and jcp being in google's high profile penalty box.  Panda/Farmer was specifically targeted at content farmers and this 'influencing keywords' sounds like someone maybe going out there and writing bogus content to try and fool google to increase your rank.  It's our belief that the risk is not worth the reward on these types of schemes, but of course it's up to each retailer to go through that calculation. 

Q4: I was wondering if there was any way to get more information on how Product Display Ads / Auto Targets work within Google.We are trying to figure out what criteria Google uses to display the products.  Sometimes the products are not even relevant to the search term. For instance, if you search for “Raw Protein” the product Wobenzym will show in the Product Ads section on the top right, but our competitor’s Raw protein product shows. 

A: Google does not publish or discuss their matching algorithm for Google Product Listing Ads. It is supposed to automatically match user intention with products, but we have seen, as you have, that is not always accurate. We suspect that this is part of the reason that we are currently seeing fewer product listing ads in Google search results as Google works to refine their matching algorithms.

Q5: I was suspended from Amazon Marketplace (I was 100% not guilty-it was an Amazon internal issue).  Am I still able to sell on Amazon using Amazon Product Ads?  I ask because this was suggested as a way to sell on Amazon (without having a Sellers account)?

A5: Amazon is extremely efficient at never letting suspended sellers back on the site. While I've never seen this exact situation, my guess is they would not allow it.  Your best bet would be to partner with a reseller or a completely different entity with completely different information (everything - IPs, bank accounts, names, addresses, EIN, SSN, etc.) and work with them to run product ads.

Q6: Have you found that Google adwords ads are more or less effective this year (as op to last year)?  Is Bing or economy doing any damage to Google adwords?

A6: Over on eBay strategies we release SSS and search details. Here's a link to the May results.  As you can see y/y search has slowed, and conversion rates are up, costs are up, etc.  Between google and bing, we don't see a change in that google has > 66% market share, so the whole space is a little soft right now, most likely due to the economy.

Q7:  I tried the Google link you posted at the webinar (to ck Web Site reviews) but link did not work?  Is this link correct: google.com/products/seller?zmi=xxx.com A: See Q2 for more directions.

Q8: If selling via ebay, amazon and your own web site would be the top 3 venues to sell on - what would you consider to be no 4? 

A8: We have an unusual view of the world in that we view the web site as an enabler for even more channels.  So I would say once you have your web site active, you should consider search.  We publish an annual report on the influence across all of e-commerce for the major channels.  For 2010: 

  • Paid Search - 44%
  • Marketplaces (largely eBay and Amazon) - 27%
  • CSE - 10%
  • Direct - 9%
  • Mobile - 6%
  • Social - 4% 

Search is the largest and in your question you don't mention it which is why we suggest search and CSE as the next channels because they represent 53% of e-commerce.  Of course this is a rule of thumb and some situations don't work well in those channels and we'd recommend something else.  That being said, if eBay and Amazon are working well, then usually retailers can make the jump quickly to search and CSE - they are just different and require a different set of knowledge.


That's it - if you have any other questions, sound off in comments.


Nov 10, 2010

CSE Holiday Rates for 2010

Tis the season… For ecommerce retailers, this means huge boosts to conversions and revenue. For advertisers on CSEs, this typically means rate increases. In Holiday Seasons past, these rate increases have generally been 25% across all categories starting at the beginning of November and ending as last mid-January. However, last year, we saw a different trend. For some CSEs, the rate increases did not apply to all categories and for the categories that did have increases, they varied from 5 – 25%. There were some CSEs in 2009 who chose not to raise rates all!

For 2010, we find NexTag and PriceGrabber raising rates across all categories by 25%. This applies to all traffic you have received since November 1. Their rate increase will last into January of 2011. Shopping.com and Shopzilla rate increases will vary between 0 and 30% based on category. This is the first I remember any category rate increase being more than 25%. Amazon Product Ads and Become.com will not be increasing rates for the holidays.

For a more complete list of which CSEs are making rate changes, what the increases will be, when the rate increases will begin and end, and rate cards for category-by-category rate increases, see our 2010 Comparison Shopping Holiday Rate Changes page in the ChannelAdvisor Strategy and Support Center.

Sep 25, 2010

ChannelAdvisor @ Shop.org - booth 507

A bunch of ChannelAdvisors are heading down to Dallas for this year's shop.org annual summit.  We'll be in booth 507 if you want to see our search, cse, marketplaces and rich media in action.  Also, I'm speaking at the "40+ Things You Can Do to Make More Money Next Week" session taking place Tuesday, September 28 from 3:15 to 4:15.  This is going to be a fast-fire session with some great strategies that you can implement before Holiday 2010. I look forward to meeting everyone in the Great State of Texas!