Best Practices for Analyzing Transactional Data

Best Practices for Analyzing Transactional Data

Information from business transactions can reveal a lot about an enterprise. Customer behavior, employee performance, effectiveness of promotions, and many other factors can be derived from this analysis.

By examining large amounts of different data types (or Big Data), hidden patterns, unknown correlations and other useful knowledge may be uncovered. This can open up competitive advantages, and result in business benefits like more effective marketing and increased revenue.

So, how can you best take advantage of this information?

“Customer-centric” Analysis

Ideally, this should be used to gain insight about everyone who visits your stores (or other outlets), why they came, whether they found what they wanted, what they chose to buy, and how they felt about the whole process.

Through loyalty schemes and other methods of tying the summary information about a transaction to a particular customer, try to determine the following:

  • Number of visits/purchases by each customer
  • Which of your stores or outlets they bought through
  • Item count, sales amount, etc., for each transaction
  • Whether they use a coupon or special promotion code
  • How demographics (about customers and locations) affects behavior
  • Characteristics of high-volume and low-volume customers over time

Customer-centric marketing practices will drive everything from how promotions are constructed to how stores are stocked, decorated, and sited. They will also suggest actions, which can be used to improve relationships with desirable customers. They can also be used for remarketing so that even data such as abandoned shopping carts from your ecommerce site can be put to good use.

“Visit-Centric” Analysis

This becomes necessary when it's not possible to identify individual customers and match them with transactions in a store. The relentless optimization of operations is essential to this strategy. In these situations, the effectiveness of marketing comes down to three things:

  • How conveniently a store is located
  • Whether it has products in stock at reasonable prices, and
  • How comfortable the shopping and purchasing experience is.

Marketing strategy here must be aggressive in ensuring the availability of items, competitive pricing, and a smooth checkout process.

Queries to inventory systems, or the inventory portions of a data warehouse, are essential to addressing stock issues. Out-of-stock situations can be a visit killer - especially where one of a highly "sticky" pair of products isn't available. The shopper may decide that because a particular item is out of stock, he or she would be better off in another store.

Through product affinity analysis, it's possible to determine which products sell well together. If one of a group of related products is out of stock, then all products in the group should be flagged - and measures taken. In turn, retailers can use the "stickiness" of products to each other to plan better promotions, and store layouts.

Competitive Shopping

Many retailers will regularly shop their competitors’ stores and obtain pricing information for a common basket of items. This will complement point-of-sale (POS) data in their own warehouse with competitive shopping information.

With this approach, it's possible to create a competitive index, by store. This shows how pricing policies compare against the competition, and can facilitate market-based price changes, in response.

At the Database Level...

1. Use Less Data

Limit access to the detail of each individual transaction, unless it's absolutely required. The transaction detail table often contains dozens of attributes about each item - like pricing information, discounts, package sizes, timestamps, and tax amounts.

Better to boil these down. Meaningful analysis can often be performed at the transaction summary level. Your goal is deriving value - not pitting your business intelligence tool against a large, poorly constructed database.

2. Carefully Examine Your Use of History

You don't necessarily need to analyze every transaction in your data warehouse over the last 10 years, to gain insight. Grocers, for example, have many transactions and repeat customers over short periods. And useful data might be found within six months of transaction detail.

3. Consider What's Specifically Relevant

All transactions are different, and shouldn't be compared with all others. Within a national retail chain, you might see dramatically different customer and purchasing behavior in different regions or seasons. Grocery, beer, and wine sales might see radical shifts, due to different state alcohol regulations, for example.

4. Simplify the Structure

Many companies will pull data directly off their POS systems, create an identical structure in their data warehouse then place a business intelligence tool on it. This simplifies the amount of data integration and Extract Transform Load (ETL) processes that must be built. But it doesn’t make analysis very easy.

Rather than copy the POS data model into the data warehouse, it's better to create ETL processes that actually transform the data into more usable models. Remember that a transaction is a dimension with its own attributes; things like products, items, and stores are just parts of it.

5. Maximize Your Batch Window

Push heavy calculations and repeatable processing into ETL and the batch process. Optimize the mundane data movement and loading processes, as much as possible. Ideally, do this during off hours - instead of in real-time, when it might impact on other users’ queries.

6. Target Your Analysis

Avoid analyzing the entire database at once. You shouldn't need to examine every transaction, to generate usable information.

7. Tune Your Data Warehouse

It's much better to spend time tuning your data warehouse and ensuring optimal performance, than asking your CFO for millions of dollars in hardware. Which you probably won't get.