This week, “J.” in Winslow, AZ, writes:

Dear Feed Doctor,
I sell model cars, and one of the attributes I want to put in my feed is the year of the car. Unfortunately, I don’t have any separate data field for the year; however, the title DOES have the year in it. Can I write a business rule that will pull the year out of the title? My titles are usually something like “Red 1978 Mercury Cougar.”

Well, at first glance, that doesn’t look very challenging, does it? I mean, if the pattern is just “Color Year etc.,” then we could use a function like GETPART to split the title into single words, and take the second one. But that would be too easy! Besides, it’s dangerous to generalize too much; what if J. has a “Metallic Mint Green 1963 Pontiac Tempest?” Nope, I’m afraid we’re going to have to deploy something a bit more powerful: a regular expression.

I’ve mentioned regular expressions (or “regexes”) before, calling them the “Swiss Army knife of text processing.” Well, in this post, I’m going to show how a regex can be a Deus ex Machina that will save J. from a poorly-plotted, yet certain, doom.
(more…)

Share This

This week, “M.” in Fond du Lac, WI, writes:

Dear Feed Doctor,
Just as gas stations do, I calculate my items’ prices to 3 decimal places. However, most shopping engines only want 2 decimal places. What should I do?

M. is right; most shopping engines want prices with exactly two digits to the right of the decimal point—no more, no less. They also usually don’t want a currency symbol. The FORMATCURRENCY function will (surprise) remove the currency symbol and fix the number of digits to the right of the decimal point. Here’s how you might write the rule: FORMATCURRENCY($ITEMBINPRICE), and here are some example results (for a US customer; for a European customer, the currency symbol and separator characters would of course be different):

A couple of things to note:

• As examples #1 and #2 show, the function rounds to the nearest penny (0.009 becomes 0.01).
• As example #3 shows, the function will add trailing zeros to “fill out” to exactly 2 decimal places.
• As examples #4 and #5 show, the function will remove any commas. In fact, the commas are completely ignored.
• As example #6 shows, the function will remove the currency symbol.

Now here’s the really cool part: just as I mentioned in last week’s post, for most default templates, we already have a rule in place that will format the prices properly. So, once again, M., the answer is: we already did it for you.

Share This

This week, “P.” in Meridian, MS, writes:

Dear Feed Doctor,
Most of the time the shipping price data in my inventory contains an actual price, but sometimes it has text, such as “See Site.” However, some shopping providers only accept numeric values for shipping. Can I write a rule that turn these non-numeric values into blanks?

Good news for P: there’s a boolean function called ISNUMERIC, that returns “true” if its input is a number, and “false” otherwise. That means he could create the following rule:

IF(ISNUMERIC($shippingprice),$shippingprice,"")

and then assign that rule to the shipping field in the appropriate templates. Of course, most of our default templates already have a shipping-price formatting rule that does this where appropriate, so the real answer to P’s question is: you don’t have to; we already did it for you! But that wouldn’t be as much fun for you guys to read about, would it?

Share This

This week, L. in “Woostah,” MA, writes:

Dear Feed Doctor,
Can I write a business rule that does one thing when used in a template for one shopping provider, but does something completely different for all the rest?

You sure can. It’s unlikely you’d ever need to, since you can create different rules for each provider, but it’s good to know you could if you had to.

GETCONTEXTVALUE
There’s a special function called GETCONTEXTVALUE that will return different output depending on how your account is setup, which provider your feed is for, or even the number of items currently processed. For convenience, let’s call these, oh, “context values.” GETCONTEXTVALUE has only one parameter: the name of the context value you want. Here are some particularly useful ones:

• CurrencyCode. This value depends on your account setup. For US accounts, the return value is “USD.”
• CurrentExportCount. This is the number of items that have been successfully added to the output feed. The return value is 0 for the first item (because there aren’t any in the feed yet, see?), 1 for the second, and so on.
• SiteCode. This is a text value that identifies the shopping provider that the output feed is destined for. Usually it’s just the name of the provider as we show it in our UI, but with certain characters, such as spaces, removed

So how would you use these? Well, as it turns out, some of our default templates use the first two. Some providers require the currency code in their feeds, and some want the feed’s total item count in either the header or footer; calling GETCONTEXTVALUE("CurrentExportCount") at the end of processing will return this, so we create these headers and footers last. You could also use GETCONTEXTVALUE("CurrentExportCount") in the item part of the template if you needed to do something like give each item a unique number, or you could write a filter that limited your feed to the first 100 items, etc. And finally, here’s the answer to L’s question:
IF(GETCONTEXTVALUE("SiteCode")="SomeProvider","Do One Thing","Do Another")

Share This

Surprisingly few people ask me for relationship advice, and this week is no exception. Instead, this week “B.” in Springfield writes:

Dear Feed Doctor,
My items’ categories look like this: Top Level>Mid Level>Bottom Level. How would I write a rule to append the top-level of the category to my keywords?

Hopefully now my post title makes more sense; B. needs to break up her category hierarchy into its pieces, using the “>” character as a guide. If you think about it, this sounds like the opposite of what last week’s JOIN function does, and of course we have a function that does just what we need.

GETPART
Remember that JOIN will take a bunch of pieces of text and join them, using another piece of text that we call the delimiter as the “glue” between each piece. The GETPART function, which I said does the opposite, has three inputs:

• The text to break into parts
• The delimiter, which determines the breaking points
• The index of the part that you want

Wait…what’s that last one? Well, really you can only do something with one of the pieces at a given time, so GETPART only gives you one of them. The index tells GETPART which one. So, taking B’s example, GETPART("Top Level>Mid Level>Bottom Level",">",1) would output Top Level, and so her rule would be:
CONCATENATE($keywords," ",GETPART($merchantcategory,">",1))

Now suppose that B. doesn’t want the top level category; she wants the bottom level. No sweat, you say; just change that 1 to a 3. Ok, well suppose that not all categories have exactly 3 levels; suppose there are categories like this:

• General>Misc
• Home>Appliances>Kitchen>Countertop
• Toys>Video Games>Console>Games>Sports>Football>Former Head Coach, Commentator, and Hardware Store Spokesman 2006

Fortunately, we’ve thought of that. If you make the index a negative number, it counts from the other end. So if you want the bottom level category, use a -1. If you want the next-to-bottom level, use -2, and so on.

That’s all for this week. Be sure and send me your questions, about feeds, or relationships, or whatever.

Share This