In my last post, I wrote about using lookup lists to convert your values (e.g., for item condition or availability) to a shopping engine’s values; in more complicated language, this is called mapping from one set to another. This is Really Useful to make sure that your data feeds meet the engines’ specifications, but we all know we need to do that. Fundamentals are important, but nobody watches a basketball game for the precision dribbling. We want backboard-shattering slam dunks.

I may be overselling this blog post a bit.

Let’s get back to feed doctoring. This week, “M.” in Grinders Switch, TN, writes:

Dear Feed Doctor,

I have three items that I’m going to reduce the price on as a promotion. I don’t want to change their price in my inventory system, though; I just have a 2-column table here with the item’s skus and sale prices. Can I write a business rule that will send out the reduced price from this spreadsheet for these items?

First, let’s talk about what M. wants to do. She’s got a set of items in a 2-column table; the columns are sku and price. This sounds a lot like the “key” and “value” pairs of a lookup list, doesn’t it? Well, you probably guessed from the title of the post that we’d be working with a lookup list. It’s a little different from last week, though; instead of mapping her values to an engine’s values, M. wants, for certain items, to replace her inventory values with alternate values. The first thing M. should do is to use the data in her table to create a lookup list, where the keys are the skus and the values are the sale prices.

This might be a good place to point out that if M. wants to create a list with hundreds or thousands of items, she might want to consider adding the information to her inventory data instead. M. didn’t say where her inventory is coming from; if she’s already using ChannelAdvisor to manage her inventory (a.k.a. “internal” inventory), the best way to do this is create an attribute for each of her items and populate that attribute with the sale price. If her inventory is coming from an external data feed (”external” inventory), then she should use one of the custom fields. However, since changing her inventory data is probably a big undertaking, and she only has a handful of affected items, then the lookup list is definitely the way to go.

Business Rules and the LOOKUP Function
So far, so good, but the next step is a little different from last week. Instead of using the list directly in the feed template, M. needs to write a business rule. If she’s ever written a formula in a certain “excellent” spreadsheet program, she shouldn’t have any trouble with writing a ShoppingAdvisor business rule. The core concept of business rules is the function. If you’ve done any kind of programming, or if you remember your high school math, you’re probably familiar with a function. In simplest terms, a function is a thing that you give one or more pieces of information to (these are the inputs) and it gives you back a single thing (called the output).

Let’s revisit the phone book example from last week, only instead of a phone book, think of a web page that searches the phone book for you. You type in a person’s name and town (the inputs), click the button (this is sometimes called “invoking” or “calling” the function), and it gives you back (”returns”) a phone number (the output). Of course, there are exceptional cases; for example, if the person isn’t in the phone book, what should the output be?

As you might have guessed, ShoppingAdvisor has a function to find values in a lookup list; it’s called LOOKUP. In fact, we actually used it last week; when you setup a template to use a lookup list to populate a field, you’re actually creating a “hidden” rule that calls this LOOKUP function. LOOKUP has the following inputs:

• The name of a list
• The key to find in the list

The output of the function is the value that is associated with the key, in the given list. Wait, we forgot to talk about what can be used as inputs. Well, it’s a lot like the template setup page: you can use text, or an inventory field, or the output of another function (we’ll see how that works in a bit). So what are the inputs that M. needs for her LOOKUP function? Well, the name of the list you can see in the image up there: Sale Items. That’s considered text, so in the business rule, we’ll have to surround it with quotation marks, like this: "Sale Items"

The next input, the key to find in the list, is the item’s sku. That, of course, is an inventory field. If M. has internal inventory, then the field is called ITEMSKU. If her inventory is external, then it’s probably called model. Let’s assume the former. In a business rule, you have to put a dollar sign in front of field names, like this: $ITEMSKU

To put it all together, to write the rule, you just put the function name, followed by a left-parenthesis, followed by the inputs (separated by commas), and then a right-paren. Like this: LOOKUP("Sale Items",$ITEMSKU)

Hopefully there’s somebody in the audience laughing at me at this point. What would happen if M. used this rule in a feed? Well, none of the items would have prices EXCEPT the sale items. Why? Because if LOOKUP can’t find the key in the list, then it returns a blank value. Since price is usually required in the outgoing feed, that means that ONLY the sale items will be sent to the shopping engine, and I’m pretty sure that’s not what M. has in mind.

Default Values in IFBLANK
Last week, we were able to specify a default value for situations where the lookup list gave back a blank value. The only problem, and the whole reason why we have to write a business rule for this situation and can’t do the lookup directly on the template, is that that default value would be the same for every item. In other words, all the non-sale items would have the same price. Again, not what M. has in mind. Instead, M. really wants the default value to be the item’s “real” price. That means the business rule needs to do this:

1. Use the value from the lookup list, unless it’s blank
2. If it’s blank, use the item’s real price

Again, you’ve probably figured out that there’s a function for that. It’s called IFBLANK, and it has two inputs:

• The “real” text, which the function returns, unless it’s blank
• The “alternate” text, which the function returns if the “real” text is blank

Remember how we said that the output of a function can be the input to a function? That’s what we do here: the first input to IFBLANK, the “real” text”, is the output of the LOOKUP function. The “alternate” text is the item’s price, which again is an inventory field (possibly ITEMPRICE for internal inventory, currentprice for external). So here’s the final rule:

IFBLANK(LOOKUP("Sale Items",$ITEMSKU),$ITEMPRICE))

Now all M. needs to do is create the rule:

and then setup her template to use that rule:

That’s it for this edition of “Ask the Feed Doctor.” I have a feeling we’ll have a few more questions about lookup lists, and then we’ll move on to something even more exciting. What could be more exciting than phone book analogies? Well, you’ll just have to wait and see!

Share This