RQW: Getting Down with the Data

Aug 01, 2004 9:30 PM  By

Few catalog companies can use their order management system as an effective database marketing platform. The way data are managed in entering and processing transactions is designed to streamline the entry and fulfillment of your customers’ orders, not the analysis of your customers’ behavior. So to undertake any kind of database marketing program, you must engage in a multistep process that first converts your data into a usable format. This is usually done with an extraction and transformation utility that feeds transaction data into a data warehouse.

From the data warehouse, your data can be subdivided or reconfigured into a series of data marts that serve as focused repositories for subsets of your entire database. You can define your data marts in whatever way makes the most sense for the type of analysis you want to do: by time period, by record type, by customer type, by order type, and so on.

With the data warehouse and the data marts as a foundation, you can then use the data to perform a variety of analytical chores, ranging from structured online analytical processing (OLAP) queries to statistical analysis and modeling to sophisticated data mining.

Every one of these steps can be managed by a separate application or by one or more service providers. The system or service provider that manages your data warehouse will often handle the extraction and transformation process (or the transformation, in any case, if you do your own data extraction), and one or more solutions or service bureaus might handle the OLAP and the statistical analysis.

Getting RIAL

Like many other services and solutions in the database marketing world, RetailQuadWare (RQW) from Cupertino, CA-based RIAL Solutions, Inc. (RSI) can provide solutions to handle all database marketing processing chores. Its own contribution is the data warehouse/data mart application, with a powerful extraction and transformation component. It relies on third-party solutions for analysis.

Originally devoted to the airline and hospitality sector, RSI has made a successful and impressive transition to the catalog world, initially fine-tuning RQW for users of the Ecometry order management system (with the ability, of course, to have the system interface to any order management system).

The key to how RQW is structured, according to RSI president/CEO Richard Irwin, is “one fact, one field,” which accomplishes a number of objectives:

  • There is no ambiguity about what the data really represent. “A place for everything, and everything in its place” means that when data are loaded into RQW, their origin and purpose are unambiguous. Of course, this means that the data mapping and system set-up process must be painstaking and exhaustive, which is exactly how RSI approaches it.

  • You can put appropriate and meaningful labels on data fields that are intuitively obvious, instead of the cryptic field labels often used by programmers.

  • The level at which data analysis can take place is as granular as you can get, not just in terms of the level of trackable detail but in the ways that sets of data can be compared among and between each other.You can also create derived fields (from combining values in multiple fields) to support specified types of analysis, or you can group multiple fields in categories that make sense from an analytical perspective.

  • The structure of the “metadata,” which describes how pieces of data relate to each other, is eminently flexible; since the metadata tools in RQW support user-based queries, without programming intervention, the system imposes no restraints on analysis that aren’t imposed by the source system itself.

The data that feed the data warehouse can come from multiple sources, including an accounting package, a stand-alone database, or a Website. One Ecometry user, for instance, has been able to use RQW to analyze the relationship between keyword searches and resulting purchases on its Website. Another has linked the system to a FileMaker database used as a data mart for all catalog product information.

RQW functions in real-time; although Ecometry itself has a set of “barometers” that reflect current operations activity, RQW can be used to construct as many additional barometers as you want (or to feed “ticker-tape” information to your desktop).

Another key aspect of RQW is that it archives all data on a daily “snapshot” basis, letting you go back and reconstruct what your business looked like at any point in time. For instance, RQW can accurately determine an exact calculation of gross margin return on investment on all inventory at any point in time on any day, resulting in more-informed purchasing decisions.

Analytical tools

RQW can be installed with any query and reporting tools, such as Brio, Essbase, Crystal, Oracle Reports, SQL/Server, and even Excel and Access. The more-sophisticated OLAP-style query tools let you define data tables and join them for structured query language (SQL) analysis.

Currently RQW is set up to support a “marketing data cube” and a “merchandising data cube.” With these you can create RFM scores and customer segmentation by any time period, channel, division, or level of product purchase behavior. You can also determine contributions by product for any period, offer, list, channel, or division, and perform square-inch analyses. The system supports ad hoc queries to let you drill down or roll up to whatever level of granularity or aggregation you choose.

The ease of use of the system will depend in part on the choice of the query tool and in part on the skills of the person using it. RQW does not require the expertise of a trained statistician to use, but some experience with OLAP and query tools is helpful.

With each of its clients RSI employs a rigorous implementation and training process developed by Ernst & Young and Hewlett Packard, which ensures that there is adequate commitment from all relevant departments — marketing, merchandising, circulation, sales — for the system to be a success. A formal “project charter” identifies all team participants and spells out the project scope, the anticipated return on investment, the project milestones, the goals and objectives, the assumptions and constraints, and the measures of success.

After several days of implementation meetings with the team, RSI needs about six weeks for data mapping, data conversion and importation. During implementation, the vendor is on-site for several days of training and initiation every two weeks, followed by periodic visits until the user is fully up to speed. There may also be special training from the vendor of the chosen query and OLAP tools.

The bottom line

RQW is a “managed solution,” meaning that while the user manages a separate application server in his own data center, RSI takes responsibility for all database management and administration on the system on an ongoing basis.

The cost of the system is $120,000 a year, paid monthly, for companies with fewer than 1 million orders a year; RSI provides a tiered pricing model that adds $30,000 to the annual fee for every additional 500,000 orders. For businesses with multiple companies, the price multiples per company are reduced beyond three installations. For all installations there is a one-time implementation charge equal to the yearly subscription fee. The cost of developing an entirely new “data cube” for analyzing something other than marketing or merchandising data averages $5,000-$10,000.

Return on investment on RQW can be substantial. The system is enabling one major marketer to track sales accurately by channel/offer/use and product category, allowing it to track the mix of channels in the life cycle of an offer. This has resulted in new sales and increased revenue and net margin. Previously the company could only guess at the mix of sales by channel using the form of payment type. The marketer is also using the system to postpone remailings of catalogs in season to customers where it is or expects to be out of stock on their primary product preferences.

In sum, RQW functions at a wide range of tactical and strategic levels to support true database marketing. RSI expects to integrate its solution with other order management systems going forward.


Ernie Schell is president of Marketing Systems Analysis, a database marketing solutions consultancy based in Southampton, PA.