Category: InOrder Tips and Tricks
Standing Orders and Continuity Order Types Explained
Do your customers ever need to reorder supplies on a regular basis? Or have you ever had customers who want to buy one of each new product you release for a particular category of products? Do you need to supply customers with a featured product each month that ships automatically?
We often get questions such as “What is the difference between a standing order and a perpetual continuity order?” This article attempts to define the similarities and differences between several types of orders you can process through InOrder.
Standing Orders – Allow customers to sign up for an offering they will perpetually get until they cancel their standing order. This type of order is normally used when a customer will get the same product at regular intervals. For example, a customer who signs up to receive regular shipments of beauty cream, motor oil, or coffee cake, forever. In this case, the quantity on the order indicates how many each customer receives when his or her interval comes up.
Another typical use for standing orders is a “Something of the Month Club.” A customer signs up to receive a different product every month, for which they pay or are billed each month as products ship to them. In this case, all customers who sign up will receive the same product for the month. This is accomplished by adding standing orders for a phantom product that is never in stock. Then, each month define a different substitution for the phantom product, and fill the monthly standing orders to ship that month’s product to everyone who signed up.
Another feature of substitutions is called “standing order trigger items,” which allows you to order one product that substitutes into shippable products, plus a “trigger item” that is used just to automatically generate the standing order for that customer.
Continuity Orders – Similar to standing orders, in that customers sign up and receive shipments until they cancel. The difference is that the items they will receive over time are from a pre-scheduled “series,” so each customer will receive the first issue in the series at the first interval, and the second issue of the series at the second interval, and so on.
So, if you have 100 customers signed up for standing order A101, then this month they will all receive A101, or else the “item of the month” that you are sending out through a substitution for A101. But for 1000 customers subscribing to a continuity item C101, each customer will receive the next item in that “series” they did not yet receive, and this will depend on how long they have been signed up for this series. In the same month, some of these customers will be new and will get the first item in the series, and other customers who have already received that item last month will get the next item in that series, and so on.
When you are setting up the schedule for the series of products in a continuity series, your goal is usually to find the sequence of products that keeps customers subscribing to the series for as long as possible. You can define varying versions of the series, and then define rules to assign these versions of the series to different customers, allowing tests to be conducted, or to vary the series sequence based on the needs of the customer.
Additionally, the features of InOrder allow for three different types of Continuity series to be defined: Regular, Perpetual, and Standing Continuity Series. A Regular Continuity Series will send a list of items to a customer at regular intervals until the customer cancels or the end of the series is reached. A Perpetual Series will send the customer all items in the series until the customer cancels. In this case, if the customer reaches the end of the list, it will start again from the beginning, and repeat the shipments again and again, forever. A simple example of this is a series that sends customers motor oil every 3 months, but includes an oil filter every other time, and fuel injector cleaner with every fourth shipment.
The Standing Continuity Series behaves similar to a Regular series (expiring at the end), but rather than starting with the first item in the list, all subscribers to the series receive the same item in any set of generated orders. (Example: In January, all subscribers get item A1, in February, all subscribers get item A2, etc.) Subscribers can join at any time, and any issues they missed will be sent when the series restarts. (If a subscriber joined in June and started with A6, then they will receive A1 – A5 the following January – May.) Subscribers are able to opt-out of any of the scheduled shipments, and an order will not be generated for them in that order fill. The InOrder default web cart even supports this series schedule listing with an opt-out feature.
In these cases for standing orders and continuity series, the filling interval can vary, each shipment to a customer is done by generating an actual order, the inventory is tracked for the items that ship, and “adjust to invoice” credit card processing can be used based on the original order.
Now Your Customers Can Personalize Multiple Items
InOrder now features an Item Personalization enhancement. With this new feature, you can give your customers the option to personalize any item in inventory.
What we like about this enhancement is that it’s so flexible. Let’s say you’re a fulfillment solutions provider and one of your corporate clients has a company store. Employees can go to the online store and place an order for company branded polo shirts — and indicate that each shirt needs to have individual names.
What this means is that the Item Personalization enhancement lets customers order the same personalization across multiple items and at the same time personalize individual items in the same order. Very cool!
With the Item Personalization enhancement, you decide if you want your personalization fields to run horizontally or vertically or to appear via a pop-up. And, you can let customers edit personalization to an item even if the item is already in the cart.
Setting up the enhancement is easy. Click the “Misc” tab in the Inventory module to add personalization fields to any inventory item, identify display preferences, and enable/disable web.config settings.
Have you put this enhancement to use? Let us know what you think of it.
Increase Sales with InOrder’s In Stock Notification
You know what it’s like to anticipate purchasing an item, only to learn, when you get to the store or Website, that it’s sold out. You feel disappointed! Of course, you can go to another store or another company’s Website, but sometimes, the item you want is available through one company only.
Now you can alleviate your customers’ disappointment — and increase sales — by notifying people when items become available with InOrder’s new Email Marketing Enhancement. Used with InOrder’s List Management module, the Email Marketing Enhancement allows you to send emails to people who requested to be notified when an item is back in stock.
With the Email Marketing Enhancement, you can also promote related items as alternatives.
Setting up emails is fairly straightforward. You can send text or HTML-based messages (or both!), and you can use email marketing messages with InOrder Web cart templates. The enhancement lets you use timers to control the burst size and timing of your outbound emails. You can also test messages via the “test mode” feature.
Have you started using the Email Marketing Enhancement? Have questions? Want to share your tips? Leave your comments below.
Run InOrder Desktop from any Windows Browser
How many times have you been at another PC in your company (or at home, or on the road) and you need to get into InOrder quickly to check something?
Because InOrder is a client program, a familiar way to use it is to run the setup.exe on the PC to install it locally. But this doesn’t help if you’re using an unfamiliar PC or a PC outside of your corporate firewall.
One approach is to setup your PC to allow “Remote Desktop” connections. This is as easy as right-clicking “Computer,” selecting “Properties,” and then enabling “Allow connections” for the Users who should be allowed remote access.
The trouble with Remote Desktop is that:
(a) If someone is using your PC, you can’t remotely connect at the same time (except for advanced versions of Windows that allow several logins at the same time)
(b) You see the entire desktop and then you need to run InOrder, which can be confusing, and (c) Your system administrator needs to open port 3389 on your firewall, but this isn’t a secure internet connection unless you’re also using a virtual private network (VPN, a remote connection that often leads to frustration).
When many people need to connect remotely and run the same program, such as InOrder Desktop, they will often set up a server dedicated to these connections. This server is a Windows Server that has “Terminal Services” running.
Often this is incorrectly referred to as a “Terminal Server,” which actually means a computer that allows many green screen terminal connections. When Microsoft released Windows Server 2008, they renamed “Terminal Services” to “Remote Desktop Protocol” (RDP). RDP Servers allow you to install InOrder (and other programs) one time, to be shared by many people at the same time. This capability is built into Windows Server, but you need an extra RDP access license for the number of connections you will have. (Also, if there will many connections, then the server will need extra memory installed.)
Using another Windows Component called “RD Gateway” and an SSL security certificate, you can enable secure connections over the Internet, without a VPN. RD Gateway also allows you to use Internet Explorer as the RDP Client (except you still need additional third party software if you are connecting from Mac OS X).
When you have hundreds of users connecting to InOrder this way, you will typically want to have several RDP Servers in a “farm” with “Load Balancing.” This means that you connect to Remote Desktop Connection Broker (another component that comes with Windows Server), and it decides which RDP Server you connect to. When you’re using this type of environment, it’s vital that you install, configure, and upgrade everything identically on each of these servers in the farm.
Typically problems arise with print drivers and print queues, and eventually you’ll find that one of the RDP Servers had an out of date print driver. The symptoms will be sporadic because users who print to that printer will randomly connect to the RDP server with the problem, based on the number of other connections on each RDP Server at the time they log in that day.
The best part is that, InOrder 9.x supports “Remote App,” which allows you to set up InOrder so that you can run it from any Window PC without installing it first, even over the Internet. When you’re using InOrder (Desktop or Point-Of-Sale), it looks and acts like it is installed locally on your PC. Instead of installing InOrder on each PC, you give each user an RDP file to click from their desktop as a shortcut, or, you can even give them a browser URL that will start InOrder from any browser. The nicest thing about this is that users don’t have to see the RDP Server’s “desktop” anymore.
IT Administrators: For easier upgrade deployment, we recommend setting up your RDP Servers so they point to a generically-named InOrder instance. Then, whenever you install an InOrder upgrade, you can test it against the Play database using a full RDP login session, and deploy it by copying the InOrder program directory over the folder with the generally named instance (but don’t copy the InOrder.ini file). A .bat file works best for this, as long as it checks to make sure that the Copy only proceeds if the old InOrder.exe file can be safely deleted first.
Stock Allocation and Your E-Commerce Shopping Cart
Many shopping carts now include the ability to see the status of an item; some even show number of units available and number en route to the warehouse. However, these details are often updated in timed batches, so they’re always slightly out of date. This means when inventory is low, shoppers can experience a big letdown when they find items are out of stock during check out or after.
With InOrder’s tight integration between web carts and inventory, e-commerce sites connected through an API can allocate units of stock as customers place items in shopping carts. Service reps can also help customers add items to carts and check them out over the phone if they desire. The upside is that customers know items in their shopping cart will be held for them, just like in a brick and mortar retail store. The downside is the risk of temporary denial of service (intentional or otherwise) until abandoned carts are put away. (Brick and mortar stores can also experience this problem, but in the online world, it’s on a much larger scale.)
So, is it better to configure shopping carts to allocate stock while customers shop? Or is it better to set carts as “price quote orders” that don’t allocate stock until customers check out?
The answer really depends on the nature of your business. However, if you do go with the “allocate as they shop” approach, there are ways to minimize potential problems.
One way is to set reasonable limits on the number of units of each SKU customers can add to their carts, based on common ordering quantities. This helps alleviate the impact of abandoned carts.
Another is to trigger alarms when stock depletes too quickly and reduce delays before putting away abandoned carts. Using InOrder version 9.2’s Email Communications Enhancement, you can set up a series of abandoned cart emails to remind customers to complete abandoned orders before releasing stock to other customers. Here’s an example:
Subject: Did you forget something? eLectronics Shopping Cart
Reminder #191281
eLectronics Shopping Cart Reminder
Dear Matthew:
Thank you for shopping at eLectronics.
During a recent visit, you left one (or more) items in your shopping cart.
This is a reminder that we clear inactive shopping carts periodically.
There’s still time to complete your order.
Visit our website (http://connect.morsedata.com/), select your shopping cart and continue to checkout.
You added the following item(s) to your shopping cart:
Item #: HL61A750
Description: Samsung HL61A750 =E2=80=93 61″ Widescreen LED DLP HDTV2
Qty Ordered: 1
Net Price: $1799.99
Item Total: $1799.99
———————————————
Subtotal: $1799.99
Our Customer Service Department is happy to answer your questions.
Email: test@test.com
Phone: (800) 860-9515
You may also view your orders online by visiting: http://connect.morsedata.com/
eLectronics
10 Second St.
Dover, NH 03820
USA
Another helpful tip involves reserving stock for other channels. For example, when sending stock to other e-commerce channels such as Amazon or eBay, it’s common to reserve stock so you can guarantee its availability as advertised. Typically, you set up InOrder EDI to send inventory to these channels based on quantities transferred to a designated hold access code. This helps keep customers happy by eliminating “no longer available” acknowledgements.
With a tightly integrated price quote-based shopping cart, you can warn customers of fulfillment delays when items are temporarily out of stock. Some sites avoid using the word “backorder” and instead simply frame delays in terms of number of days to ship (i.e. “ships in x days”). They can provide customers with a real-time prediction of arrival dates because they know how many of reorders are already committed to backorders.
What are your experiences or thoughts on stock allocation through your e-commerce web store?
Getting the Most Out of Your InOrder E-Commerce Module
With InOrder’s tightly integrated e-commerce site and add-on modules, there’s almost no limit to what you can do. Here we’ve listed 11 1/2 InOrder e-commerce functions to improve internal efficiencies while delivering better customer service:
1. The E-Commerce module allows customers to get up-to-date order and backorder status, as well as shipment tracking. They won’t have to call customer service to find out where their order is, regardless of whether they placed it by phone or retail location.
2. The E-Commerce module allows web customers to set up re-orders. Re-orders are especially helpful for customers who place annual gift orders with many ship-to recipients and customers who order the same supplies repeatedly, regardless of how they were previously purchased (e.g. phone, in person, web, etc.)
3. With the List Management Module and Email Marketing Enhancement, web customers can sign up for notifications when out-of-stock items come back in. You can even link the email back to your e-commerce site and the item’s order page.
4. With Email Marketing Enhancement, you can set up a series of abandoned shopping cart emails to entice customers back to your e-commerce site.
5. With Loyalty Program Enhancement, you can synchronize point balances, allowing customers to see point earnings and balances, and redeem them online. This is especially beneficial when points are earned from phone or in-person purchases: loyalty program information on packing slips and receipts can prompt customers to visit your website.
6. With the Gift Card Program, you can sell plastic as well as e-gift cards. Customers can check balances online and redeem across all sales channels.
7. Personalization Enhancement enables customers to purchase personalized products online, including customized team uniforms (where items have both team names and individual player names).
8. Digital Distribution Enhancement enables digital file downloads from your e-commerce site (such as music, e-books and apps), for website purchases made from iPads or other devices.
9. Standing Order Enhancement enables customers to place and view standing orders for products they order regularly or that trigger “product of the month” clubs.
10. With Continuity Series Enhancement, customers can view and manage their regular, revolving and standing continuity series orders.
11. With the Subscriptions module, web customers can review past and future subscription shipments. This is especially useful for today’s web customers who purchase subscriptions and immediately want adjust them (such as placing subscription vacation holds).
11 1/2. Oh, and the Subscriptions module also allows online management of membership benefits, expirations and renewals and computes membership discounts immediately after membership purchase.
Which modules do you use? Let us know in the comments section.
Keeping Your ERP Server Files Fit — Part II
When someone asks us, “How do you stay so fit?” we might talk about our diet and exercise regimen. The news is full of what to eat, what to avoid, and how much exercise we need. We thrive on these snippets, but the moiling reality is that to make a difference, we have to be consistent versus doing “yo-yo” dieting to maintain health.
In part one of this article, I talked about how to use server redundancy to keep your company healthy, wealthy and wise. To help your servers respond instantly, some consistent “nutrition and exercise” is also required. In part two of this article, we’ll explore “nutrition and exercise” tips for keeping a database server “healthy”: Organizing, Balancing, Archiving, and Participating.
Organizing
Have you ever emptied a full closet, re-organized everything, and then put it all back, to happily discover that your things are easier to find, and the closet now seems half-empty? Windows Server and SQL Server both have utilities to do this, and the more frequently these utilities are used, the more organized the data is on a regular basis.
Windows includes a utility called “Disk Defragmenter.” This utility finds pieces of data stored separately but go together, and moves them so they are stored together. This is a typical problem in a supermarket when you don’t have many big open places to put a new SKU. First you might need to slide other SKUs down to consolidate free space, or move some items to a new aisle all together. By doing this, it will be easier for everyone to find this SKU than if it were stored in three or four separate places just because it did not all fit in one place.
The good news is that just about any Windows system lets you schedule the Defragmenter to re-organize your data (a typical frequency for this is weekly). The bad news is that on a SQL Server, this job will not re-organize the files that matter most: the SQL Data files and log files that are locked for update whenever SQL Server is up and running.
Therefore, on SQL Servers, we need to make sure the system is organized before we install and build the SQL database. We need to pre-grow the database to its expected size. Lastly, we set the database to grow in bigger leaps. All of this keeps the database from growing too frequently in tiny pieces, since these tend to scatter all around, making it take longer for the computer to find things quickly.
Balancing
When a tree needs to get water from the roots up to every leaf, it’s faster if the tree has main branches, smaller branches and then twigs reaching the leaves. On the other hand, a very long “vine” has to carry the water an awful long way to water the furthest leaves down at the end. This is the problem with SQL databases too.
As you add more and more customers, inventory, orders, or other data to the database, it tends to build “vines” rather than a “balanced tree,” and so you’ll start to get delays when you are doing lookups. SQL Server comes with several utilities that should be used on a regular basis. These utilities re-organize these long vines into balanced trees. This is called “Index Rebuilding.” If you never ran this utility, your database server would still function, but responses might get very slow over time.
SQL Server helps you set up and schedule these utilities by providing a tool called the “Maintenance Plan Wizard” that can be used to do your balancing. This can also cause problems when your database is too out of balance, because the Wizard tries to process the entire database. There may not be time to do that overnight, causing extra locking delays the following morning. When you’re signed up for Morse Data’s SQL Server Health Monitoring Service, we take this a step further by monitoring which parts of the database need the most attention and tune these areas most frequently — allowing the maximum amount of balancing you can get.
Archiving
If you continuously add new inventory, customers, orders, and other data to your database every day, your database will grow every day. This means you are slowly increasing the work that the computer needs to do to find something and the amount of extraneous data that might be listed when you are looking for something. Additionally, it increases the work the computer needs to do when you are re-organizing or balancing your database.
To keep your system fit, it makes sense to regularly archive any old data that you don’t need anymore. I use the term “archiving” to mean that you might run a report, or make a full database backup, and then purge off the old data. This way you can save that report or backup copy for a fixed period of time in case you ever need to refer to it. The assumption is that this data is old enough that you probably won’t need it anymore.
For credit card-related data, the PCI requirement 3.1 doesn’t provide specific lengths of time that this data should or should not be stored, but it does state you must establish some sort of retention policy and then follow it. It makes sense to set this policy for all types of data you’re storing, and then set up this schedule on the server to follow this policy.
Participating
Microsoft and Morse Data are both constantly finding new ways to improve their software, and developing fixes as problems are reported. By participating and applying these service packs and updated versions regularly, we’re able to take advantage of the best possible operating environment for our organizations when it matters the most.
Is it better to skip or delay these updates and keep going happy just the way you are? I agree that upgrades can sometimes be painful, but delaying them for too long only builds them up and ultimately causes greater pain.
Did you like these two posts on how to keep your SQL server in tip top shape? If so
Server Redundancy Keeps Your Company Healthy, Wealthy and Wise
Benjamin Franklin coined the phrase, “Healthy and Wealthy and Wise.” We all want this, especially health because without health, the other two don’t matter as much.
When we’re healthy, we tend to forget how miserable we felt the last time we had a problem. It feels so great to be healthy, that once we overcome a problem, we eagerly shift focus to “Wealthy and Wise.”
This same human nature applies to how we take care of our computers.
It feels great when all systems are humming and the computers are responding instantly. In today’s world, this is a given. How frustrating is it, as a customer, to enter a web order with long delays between pages, or hear a CSR say, “Our computers are slow today?” Delays should be unacceptable, “downtime” is catastrophic, and just the thought of losing any data is unthinkable.
How do we keep our systems as consistently “Healthy” as possible, so that our business can focus on its goals?
First, servers need to stay humming
I.T. started making servers more reliable by doubling up the weakest point of failure. Disk drives are like incandescent light bulbs; they eventually burn out. These days,we set them up in groups called “RAID arrays.”
On a SQL Server, the data should be stored on a RAID 10 array, similar to the groups of tires on an eighteen wheeler. This distributes the weight, and the truck can keep going if one of the tires blows (to be replaced it as soon as possible).
Another way to increase server reliability is to mix disks from different manufacturers or different lot numbers. If one disk has a design flaw, they don’t all burn out at once. The “claimed” average life of a disk drive used to be 12 years, and now they are claiming as much as 57 years for consumer drives and 171 years for server grade drives. In real life, drives can last 15 minutes to 15 years.
In every 10 servers that are several years old, you may typically find one or two servers that have a failed drive in a group that needs to be replaced. The server is still functioning so often nobody is even monitoring for this.
In an important server with many drives, it’s a good idea to add an extra drive as a true “hot spare” that sits in waiting 24x7x365, and then automatically joins whatever group has a failure.
Think about it: your system is replacing the flat tire without even stopping the truck! Most servers can do this today, if you plan ahead and set it up this way. If they’re big enough, you can even use retired drives for this.
Second: Double up the entire server
If any other components on the server fail, another server can take over. Obviously now, instead of doubling the cost of your disk drives, you need to also double the cost of the entire server, and do additional set up so that the second server always has a fresh copy of what is on the first server.
For Web Servers and Terminal Servers, Windows comes with the ability to set up Server groups that can work together to “share the load.” If one fails, the others in the group need to work harder but they can keep things going.
Amazingly, SQL Server 2008 comes with this capability, and SQL Server 2012 improves it further to allow what they call “SQL Server Always-On Availability Groups.”
Picture a convoy of trucks. If one breaks down, they cast it aside and the next one instantly takes the load and continues the journey.
With some advance setup work from Morse Data, your InOrder application can enjoy this protection — that is, to jump to another server without anyone realizing that the main server just went up in smoke and triggered the sprinkler system.
Speaking of sprinkler systems, someone recently asked, “Why do I need still need to do backups and pay extra for disk drive groups, and now also extra servers!?”
The answer is clear if you think about it: Why do we have fire extinguishers when we have a sprinkler system? And why do we need sprinkler systems when we have fire departments and fire insurance?
Whether you’re hosting servers or running servers in house, you still need to ask your I.T. people what the plan is to keep your servers humming.
To keep our companies — and customers happy — we need all systems humming. However, we also need them to instantly respond — which will be Part II of this series.
Using Prospect User-Defined Fields to Make List Creation More Powerful
Many InOrder users are familiar with User-Defined Fields (UDFs) on Prospect records. These can be used to collect demographic information about your potential customers. They can be exposed on your web interface for your visitors to enter information such as age, gender, interests, where they first heard about you, or any other type of question that helps you target specific prospects and provide them with a more personalized experience.
Because these UDFs are all available as Selection Criteria in [List Creation], you can use these fields to pull very specific mailing lists.
But what if you want to make [List Creation] more powerful and include more prospect-based criteria than exists as built-in choices? These Prospect UDFs don’t have to be merely the Q&A fields your prospects answer themselves. You can also have UDFs as “concept” fields; updated regularly using a scheduled “SQL statement” job that categorizes customers based on buying behaviors or patterns that you are looking for.
InOrder client Rod’s Western Palace wanted to do just this. They needed lists of people who purchased any product from a specific department. But they also wanted to be able to pull a list of people who had purchased any product that was NOT in that same department. We worked with them to achieve this by adding two “concept” fields, both simple Yes/No fields that are recomputed by a nightly job that reviews each customer’s order history.
Here is how you can implement this type of Prospect UDF at your company.
First, create one or more Prospect UDFs in the [Client Setup] window. Make sure to set the ‘Data Type’ appropriately (YesNo, Text, Number, etc) for the type of information you will be populating into the field.
Rod’s used two UDFs set to the YesNo data type. The first (UDF Field 1) was called ‘Tack Buyers’ (customers who ordered items from the Tack department) and the other (UDF Field 2) ‘No Tack Buyers’ (customers who placed an order but never ordered any items from the Tack department).
In the screenshot, we show you how to do this using our test database with “Chocolate Buyers” as the example:
Next, determine the criteria to calculate the computed value for this field on each prospect record. If the criteria is based on items ordered, what distinguishes the items? SKU? Inventory Type? Inventory Status? One of the 21 Inventory User-Defined Fields? Do the items need to be part of a posted shipment in order to be used in determining the calculation? This criteria will be used to build your SQL statement.
Next, if you know SQL then you can build the SQL statement, otherwise contact Morse Data Technical Support for assistance. If you build this statement in-house, remember to always have Morse Data Technical Support approve your statement before executing it against your live database, to avoid unexpected results, and to stay within the terms of your software agreement. (We recommend having Morse Data help you the first time, and then consider trying it yourself if you ever need to make adjustments or add new fields.)
Here is an example update statement that uses inventory types, with a description of what each step does.
1) Insert the two Prospect UDFs into prospect records where they currently do not exist, and set the values to ‘N.’:
Insert tblProspectUserField (lab_KEY, lab_FIELD_NUMBER, lab_USER_DEFINED)
Select lab_key,’3′,’N’
from tblProspect pro
where not exists
(Select * from tblProspectUserField prev
where prev.lab_KEY = pro.lab_KEY
and prev.lab_FIELD_NUMBER = ‘3’)–Chocolate Buyer Prospect Field
Go
Insert tblProspectUserField (lab_KEY, lab_FIELD_NUMBER, lab_USER_DEFINED)
Select lab_key,’5′,’N’
from tblProspect pro
where not exists
(Select * from tblProspectUserField prev
where prev.lab_KEY = pro.lab_KEY
and prev.lab_FIELD_NUMBER = ‘5’) –Non-Chocolate Buyer Prospect Field
Go
2) Update the value on the first UDF to ‘Y’ if: there exists a posted order associated with this customer, and that order contains a posted line item of an inventory item belonging to one of three inventory types:
Update puf
Set lab_user_defined = Case
When Exists (Select * from tblPostedOrder por
INNER JOIN tblPostedLine pli on por.por_ORD_KEY = pli.por_ORD_KEY
INNER JOIN tblInventoryPrimary inp on inp.own_OWNER_CODE = pli.own_OWNER_CODE
and inp.inp_SHORT_ITEM_NUMBER = pli.inp_SHORT_ITEM_NUMBER
Where por.cus_key = puf.lab_KEY
and inp.ity_INVENTORY_TYPE_CODE IN (‘DK’, ‘MK’, ‘WT’))
THEN ‘Y’
ELSE ‘N’
END
From tblProspectUserField puf
Where puf.lab_FIELD_NUMBER = ‘3’ –Chocolate Buyer
Go
3) Update the value of the second UDF to ‘Y’ if: there exists a posted order associated with this customer and there exists no posted orders associated with the customer containing a posted line item where the inventory item belongs to one of three inventory types:
Update puf
Set lab_user_defined = Case
When Exists (Select * from tblPostedOrder por
INNER JOIN tblPostedLine pli on por.por_ORD_KEY = pli.por_ORD_KEY
INNER JOIN tblInventoryPrimary inp on inp.own_OWNER_CODE = pli.own_OWNER_CODE
and inp.inp_SHORT_ITEM_NUMBER = pli.inp_SHORT_ITEM_NUMBER
Where por.cus_key = puf.lab_KEY)
AND NOT Exists (Select * from tblPostedOrder por
INNER JOIN tblPostedLine pli on por.por_ORD_KEY = pli.por_ORD_KEY
INNER JOIN tblInventoryPrimary inp on inp.own_OWNER_CODE = pli.own_OWNER_CODE
and inp.inp_SHORT_ITEM_NUMBER = pli.inp_SHORT_ITEM_NUMBER
Where por.cus_key = puf.lab_KEY
and inp.ity_INVENTORY_TYPE_CODE IN (‘DK’, ‘MK’, ‘WT’)
THEN ‘Y’
ELSE ‘N’
END
From tblProspectUserField puf
Where puf.lab_FIELD_NUMBER = ‘5’ –Non-Chocolate Buyer
A regularly-scheduled SQL job similar to this one allows Rod’s Western Palace to perform targeted mailings to their buyers of specific items. Once you are back in the [List Creation] window, these fields are available for use as Selection Criteria.
The nicest part about these SQL statements is that they can consider more complex logic that you might want to build into a UDF, like only considering sales that occurred in the last 120 days, sales that were not returned, or sales of specific items that were placed where the order was responding to an abandoned cart email campaign. If you can think of it, there is probably a way to compute it. And amazingly, these SQL statements can evaluate hundreds of thousands of customers and their respective order history, usually in a matter of minutes each night!
Keep Customers Coming Back, Increase Sales with a Loyalty Awards Program
Quick – look in your wallet. How many rewards cards do you have? According to data from Forrester Research, if you’re like one of the 2 billion Americans (or 120 million Canadians), you’re enrolled in 8.4 loyalty awards programs. That’s a lot of loyalty!
Companies from grocery stores to your local day spa use loyalty awards programs to help keep customers coming back, to increase sales and to improve customer engagement. Data from Forrester Research states that loyalty program members spend up to 13% more than non-members. Store or web repeat visits also increase – by up to 20%.
Every time your customer interacts with your business, you have a golden opportunity to make a great impression – and help encourage word-of-mouth (online and off) – with a loyalty awards program.
With a loyalty awards program, you can provide points for each dollar spent. Points can also be rewarded for other marketing-related activities such as:
- Joining an online mailing list (such as special promos or a monthly e-newsletter)
- Creating an online account
- Requesting a catalog
- Referring a friend
Points can then be redeemed through online purchases, your call center or your retail store. A loyalty award program can offer many redemption options such as one-time discounts, free shipping, or free designated merchandise per order.
“But what about all those cards?” you may be thinking. I know, we don’t like carrying around a bunch of cards either, which is why the InOrder Loyalty Award Program (part of the Inventory and Order Management module) doesn’t require cards.
Incorporating the features cited above, the InOrder add-on (see screenshot below) allows you to look up the customer (whether on the website or through the call center or retail store) by just using their name, street, phone number, or email. It’s as simple as that.
Screenshot from InOrder's Loyalty Award Program
(Click the screenshot to view a larger version of it.)
If you would like to learn more about the Loyalty Award Program and all that InOrder has to offer, please contact sales@morsedata.com.
And, if you’re using InOrder’s Loyalty Award Program, let us know how it’s going — and feel free to share your tips for success in the comments section.