Just FYI, by “hack” I mean “a clever or quick fix to a computer program problem,” not a security exploit. When programmers talk about a “hack,” they often mean a solution that gets the job done, even though seems like it “breaks the rules,” because it uses a piece of technology in a way the maker probably didn’t intend. Sometimes this happens because the right tool for the job either doesn’t exist, or we’re too lazy to go get it—ever open a paint can with a screwdriver?

One of ChannelAdvisor’s own CSE experts, Mark Vandegrift, sent me the following hack he used to create a clever business rule for filtering. He wanted a rule that would filter out items that did NOT have certain words in their titles. Another way to put it is he wants a filter that only keeps items if their titles contain certain “keeper” words:

But the list [of keeper words] was so long that writing the filter as NOT(CONTAINS OR CONTAINS OR CONTAINS….) was not an option. So instead I created a lookup list with the words/phrases I was looking for as the “Name” and something arbitrary as the “Value” (I used “vandegrift”). I then used as part of my filter an expression that looks like this:

NOT(CONTAINS(
REPLACEWORDLIST(”topseller-title-search”,
TOLOWER($offername)),”vandegrift”)))

So the system is going through my list of “include” terms, replacing that term in the title with the arbitrary word, then looking for the arbitrary word. Thought this was worth sharing as it could have many applications. Next time you find your self writing a long string of CONTAINS OR CONTAINS… give this a try.

Thanks, Mark! Of course, what this is telling me is that Mark really needs a new function that is to REPLACEWORDLIST as CONTAINS is to REPLACE: that is, it checks a piece of text to see if it contains words from a given list. Call it CONTAINSWORDLIST.

Guess I know what I’ll be working on for my next fun project…

Share This

The topic for today’s post comes from Brian Wulfe, Online Marketing Manager for Richlund Ventures, Inc. Brian and I have been having forum discussions at our Strategy and Support Center, and he’s come up with several scenarios for business rules to calculate discounted prices. One quick note: All the rules I’m showing below use the FORMATCURRENCY function to make sure the final value is…well, formatted correctly; that is, it’s a number with no currency symbol, and exactly two digits to the right of the decimal point.

Excluding Products from the Discount
Here’s Brian’s first scenario:

We currently apply the business rule FORMATCURRENCY(0.95 * $currentprice) to the product price field to compute a 5% discount. How could we exclude a selection of products from that rule?

Assuming the items you want to exclude were in a list called “No Discount”, I would try the following:

FORMATCURRENCY(IF(ISINLIST("No Discount",$model), 1.0, 0.95) * $currentprice)

This is the same as what you have, except instead of always multiplying the price by 0.95, we calculate a multiplier using the following logic:

• if the item’s model (a.k.a., “sku”) is in the list called “No Discount”, the multiplier is 1.0 (i.e., no discount)
• otherwise, the multiplier is 0.95

Discounting Only Items from a Certain Manufacturer
Here’s the next scenario:

I am trying to use the IF function to apply discount only to a single set of products and leaving everything else the same. I would like to apply this discount by looking in the Manufacturer column and finding “XYZ” and then reducing the corresponding prices by 10%.

This one is just a variation of the first; instead of applying the discount to all items except certain ones, the discount is only for certain items. So:

FORMATCURRENCY(IF($manufacturer = “XYZ”,0.90,1.00) * $currentprice)

Multiple Discount Rates
Brian’s last scenario:

I want to apply a blanket discount of 7% to all skus except for a particular look up list called “No Discount.” In addition, I would like to specify unique discounts to certain categories such as “Outdoor Living” or “Appliances > Ice Makers,” but I need a “wild card” as there are several different subcategories within each.

Here’s what I cam up with:

FORMATCURRENCY(
SELECTCASE(
ISINLIST("No Discount",$model),1.0,
CONTAINS($merchantcategory,"Outdoor Living"),0.90,
CONTAINS($merchantcategory,"Ice Makers"),0.95,
0.93)
* $currentprice)

This is still the same pattern: we have a logic block that computes a multiplier, which is then multiplied by the price. However, since there are several different cases we need to consider, we’re not using the IF function. We’re using SELECTCASE. Inside the SELECTCASE we’re using the CONTAINS function to match the category values.

Another possibility, if you have lots of categories with different discounts, is to use a lookup list for that as well. You would have each discounted category in the “name” boxes, and the respective discounts in the “value” boxes. If you name the list “Category Discounts,” then your rule could look like this:

FORMATCURRENCY(
IF(ISINLIST("No Discount",$model),1.0,
IFBLANK(LOOKUP("Category Discounts",$merchantcategory),"0.93"))
* $currentprice)

That’s all for now. Thanks, Brian, for sharing your ideas with us! And you readers out there, let me hear from you.

Share This

I’m happy to announce that this past Saturday, ChannelAdvisor shipped its April release. One of the major items released was Shop.com CPC Support in the US and the UK!

This new CPC model allows customers to take advantage of Shop.com ’s traffic and treat it like a traditional comparison shopping engine rather than a marketplace.

To my knowledge (and if you can’t trust a Google/News/Blog search, who can you trust?), we’re the first third-party to announce support for this. We’re seeing substantial interest in this new, easy to use channel from Shop.com and believe this immediately vaults their site into the “top tier” of paid comparison shopping engines in the US. Did I mentioned we added support for our UK customers as well?

The same feed supports either the existing Shop.com marketplace (CPO) or the cost-per-click offering. The customer’s relationship with Shop.com determines which solution they are a part of. Here’s some more information from Shop.com directly about their different programs.

With ChannelAdvisor? Shop.com is enabled as a channel for all customers on the ChannelAdvisor Complete platform, both MarketplaceAdvisor Premium and full ShoppingAdvisor customers. Contact your Corporate Account Manager for more details.

Not with ChannelAdvisor? Get more information on how to sign up.

Share This

I’ve been up since 5am because of the rollout today, so this post will be short and simple. Today’s letter comes from “R.” in Winterset, IA. He writes:

Dear Feed Doctor,
Some of my item titles are in ALL CAPS, but in my feeds I’d like to have “proper” or “title” casing, so that only the first letter of each word is capitalized. Let me guess: I need a regex, right?

That’s a good guess, R., but we actually have a function that does just what you want. It’s called PROPER. It takes in a text input, and outputs that text just as you said: all the letters are lower-case, except the first letter of each word, which is upper-case. Here’s what the R’s rule looks like:

PROPER($offername)

And here are some examples:

Now, nitpickers might point out that this isn’t technically title casing, because PROPER will capitalize conjunctions, articles, and prepositions (”and,” “the,” “of,” etc.). Well, they’re right. As a nitpicker myself, I sympathize. If it really bothers you, write to me and we’ll come up with a regular expression or something to fix it.

Share This

I almost forgot to mention that I will be at Catalyst tomorrow—that’s Tuesday, April 1 (and this is not a joke, either). I’ll be at one of the roundtables, so drop by and say hello. I’ll only be there tomorrow, though.

See you!

Share This