Offers, Deals, and Discounts: A Product Pricing Data Model

Are you excited when you get an instant discount on an online purchase? Do you always look for a better deal before you place an order? This article explains how online stores manage product prices and discounts.

Everyone loves a deal, no matter how big or little it is. Would you rather pay full price for an item or apply a coupon code and save a few bucks? Offering price discounts is one major way retailers attract customers and increase sales. It is a game, one that’s based on psychology and that makes businesses more profitable.

Is there really psychology involved in saving money? Absolutely. Retailers appeal to the minds of shoppers by incorporating coupons and discounts into their overall marketing and pricing strategies. But it is also very important to realize that shoppers don’t like to do math. So companies make all their offers very easy to understand. Instead of crunching numbers, customers focus on particular figures in a promotion (i.e. “20% off your order”) and draw their conclusions based on that amount. The simpler the offer, the easier it is for customers to understand and to act on it.

What Kinds of Discounts Are Available?

As part of my research for this article, I looked at online retailers specializing in products like electronics, groceries, transport (bus/ metro tickets), and clothing. Here are some the classic examples of discounts I saw:

• 20% off on your first order (up to $60)
• $100 off your order of $1,500
• Prime customers get an extra 5% off their order
• Get 15% off baby clothes today from 5:00 PM till 5:15 PM
• Get 50% off any medium pizza on your birthday
• 40% off movie tickets every Tuesday
• Buy one denim shirt and get a second shirt half off
• Free shipping on your order of $300 or more
• 30% off your first 50 iPhone 6s handsets
• Get 70% cashback in your rewards wallet on your first computer and accessories purchase of $800 or more
• Refer a friend and get $40 in your rewards wallet when they place their first order with us
• Redeem reward points (up to $75) when you book your next flight

These offers can be put into various categories:

• Direct Trade Discount – This is the most common discount type. It simply offers a reduction based on the original price, such as $10 off or 10% off. This reduction can be applied to a particular product, a product category, or an entire order.
• Promotional Discounts – These discounts are given to promote a particular product or to promote the store itself. When you see “X discount on your first order with us” or “X rewards points for referring a friend”, you are looking at a promotional discount.
• Quantity-Based Discounts – Quantity discounts encourage shoppers to increase their order value to a specific threshold. They are easy to spot: they offer a dollar amount or percentage off your next order when you purchase a minimum amount of something or when your shopping total hits a certain amount.
• Premium Customers Discounts – Sites may have customers pay a small amount to get a premium membership to their store. This gives them certain benefits, like members-only deals or free shipping. Amazon Prime is a good example of this type of discount.
• Seasonal Discounts – This discount is given on a particular occasion or season, such as the Christmas season or on Cyber Monday (the Monday after Thanksgiving in the US).
• Personalized/Exclusive Discounts – These discounts are given to customers on a day unique to them, like their birthday or the anniversary of the day they signed up for a membership. Often, the offer details are sent to customers’ personal emails or phone numbers. These offers are only available to the specific customer for a defined period of time.
• Loyalty Discounts – This is a kind of buy back offer, such as when a customer exchanges a used product for a new product by the same manufacturer. A small part of the new product price is waived to “reward” customers’ loyalty to the company.

What Motivates Customers to Use Discounts?

When we think of the monetary benefits of these offers, the offers can be further categorized into three types:

1. Direct Reduction in Price – Online retailers issue certain coupon codes that customers apply to their orders during checkout. This reduces the price.
2. Reward Points Credit – Reward points are associated with a particular product or a set of products. Shopping portals credit the points to the customer’s account after they complete an order. These reward points can be redeemed by customers in subsequent orders. It’s common (but not universal) that one reward point equals a discount of one unit of currency (i.e. 1 EUR).
3. Cash Back – Retailers also have partnerships with financial institutions like banks or with wallet services. For example, if you pay for an order with a certain bank or credit card, a small percentage of the transaction is credited to your account. Usually, these cashback offers can be used with any other offer or discount offered by the store.

These three types of offers can be often be combined, with a customer using one from each category. However, sometimes stores will restrict the number of discounts that can be used on a single order.

Now we have a good idea of the requirements that our data model needs to meet. Let’s look at the complete model.

The Complete Data Model

Based on the three types of offers, we’ll divide the data model into three subject areas:

1. Product Pricing and Discounts
2. Loyalty Reward Scheme
3. Tie-Ups with Banks and Wallets

Obviously, each serves one type of offer. Let’s start with the first subject area.

1. Product Pricing and Discounts

Usually every online retailer’s database has a product table that contains basic details like name, description, when the product was listed, the number of units in stock, and so on. They do not keep pricing details in the same table, as pricing changes over time.

In this product table, I’ve added two new columns:

• reward_points_credit – Each product comes with a set number of reward points that are credited to the customer’s account when they buy the product.
• product_category_id – This is the category to which the product belongs, such as electronics, clothing, food, or beverage.

The product_category table holds details about product categories and subcategories. You might be surprised to see the max_reward_points_encash column in this table. Usually, retailers impose some restrictions on the maximum reward points that can be redeemed with a single order. This depends primarily on the order’s product category. For example, a site selling vacation packages allows customers to pay up to 30% of their purchase with reward points when the customer is buying an international tour package, but only 10% of a domestic tour package can be redeemed. This is considering the fact that the profit margin is smaller with domestic tour packages than with international packages.

The product_pricing table stores pricing details for an individual product at any given time. The create_date, expiry_date and in_active columns are the most important, and they are also self-explanatory. I’ve added them to this table to capture pricing data, since price is a slowly changing dimension. There is one and only one active record for any product at any time.

E-commerce portals usually apply a discount to an entire set of products (a product category) rather than applying it to individual products. The product_category_discount table holds all discounts and offers currently valid for a product category. To understand the columns in this table, let’s look at the terms and conditions of a sample offer:

30% OFF All Soaps

Coupon Code – SOAP30OFF

• Offer is valid until 30th April 2017.
• Customer will receive 30% off our entire range of soaps.
• Maximum discount is $80.
• Coupon code can be used with reward points.
• Coupon code can be used only once and is not applicable with any other coupon code.
• Coupon code is applied to products’ MRP (maximum retail price).
• Taxes applicable on the discounted price.

Now, let’s take a look this table’s columns:

• id – The primary key of the table.
• product_category_id – The product category related to the discount. This is the “SOAP” subcategory in our example.
• discount_value – The amount of discount offered. In this case, the value is ”30”.
• discount_unit – The type of discount, i.e. a percentage (30%) or a flat currency amount ($30). We would expect values like “PERCENTAGE” or “CURRENCY”; in this example, it would be a percentage.
• create_date – The date when the offer was entered into the system.
• valid_from – When the offer starts. E-commerce portals may advertise an offer that only becomes available in the future.

• valid_until – The date when the offer is no longer valid. In the above example, this would be “30 April 2017”.

  
  Note: There are cases when an offer is valid for just a few minutes. Therefore, I’ve used the TIMESTAMP data type for the create_date, valid_from, and valid_until columns.
• coupon_code – A preset code that users enter during the checkout process to receive a discount. It is “SOAP30OFF” in this example.
• minimum_order_value – The minimum amount needed for an order to be eligible for a particular offer. In our example, since there is no minimum order value given, this column would be “0”.
• maximum_discount_amount – The maximum discount that a customer can get with this offer code.
• is_redeem_allowed – If customers can redeem reward points while using this coupon code.

The product_discount table is an exact replica of product_category_discount except that it holds the details of discounts and offers for individual products rather than entire product categories. Retail sites sometimes offer discounts on a particular product to boost its sales or to clear out their inventory.

Loyalty Reward Scheme

Let’s assume that every e-commerce site has a user table that stores basic details about their customers. I’ll add a few more columns to this standard table:

• promotional_reward_points – The individual user’s current balance of promotional reward points. These reward points are usually associated with an offer and can expire. In addition, there are restrictions on how these points can be used. They may be redeemed as partial payment for an order, but not as full payment. At this point, the max_reward_points_encash column I added to the product_category table should make more sense.
• non_promotional_reward_points – The user’s current balance of non-promotional reward points (such as a refund on a returned product). Many online retailers credit non-promotional reward points to users’ reward accounts instead of refunding the purchase amount to the payment source. These points have no expiry date, and customers can use them in subsequent orders with no major restrictions.
• membership_type_id – The customer’s current membership type – for example, an Amazon Prime customer. Customers with premium memberships often pay a certain amount each year in return for perks like free shipping or special members-only prices.
  Since subscribing to a premium membership is optional, this column is NULLABLE.

The membership_type table stores details about the types of memberships available. A customer can subscribe to any one of them. The columns in this table are:

• id – A unique ID number assigned to each membership type.
• membership_type – The name of a membership type – Prime, Silver, Gold, Platinum, etc.
• discount_value – The membership discount amount associated with the relevant membership type.
• discount_unit – If the discount is a percentage or a flat currency amount.
• date_created – When the membership was introduced.
• valid_until – When the membership type is no longer valid. Usually, membership types are valid indefinitely.
• is_free_shipping_active – Whether free shipping is offered to customers of a particular membership type.

There are portals that have a predefined set of memberships and that track customers’ order history. They can set membership type for individual customers based on how active the customers are. This is also a good way of rewarding customers’ loyalty.

The user_reward_point_log table records the reward point activity of individual customers. The columns in this table are:

• id – The primary key of this table.
• user_id – The related user ID.
• reward_points – The number of points to be credited to or debited from the customer’s account.
• reward_type – Whether points are promotional or non-promotional.
• operation_type – Whether the operation is a credit or debit.
• create_date – The date when the transaction occurred.
• expiry_date – The date when promotional reward points expire. Usually these points are valid for some months or years.

Tie-ups with Banks and Payment Wallets

Many shopping sites leverage various third-party payment gateways (like PayPal). In addition, they may tie up with financial institutes like banks or wallet services like Oxigen; this provides customers with supplementary offers and incentives if they use that service. For example: “Take 30% off a bus ticket of $150 or more and get $15 cash back when you use your Discover credit card”.

This subject area is concerned with the latter part of that offer: the perk that comes with using your Discover card. There is only one table for this section, payment_offer. The columns in this table are:

• id – The primary key of the table.
• institute_type – Whether the institute is a bank or a wallet service.
• institute_name – The name of the bank or wallet.
• card_type – Whether the offer is valid for credit cards, debit cards, or both.
• coupon_code – The related coupon code.
• discount_value – The value of the offer. In the Discover card example, it would be “15”.
• discount_unit – Whether the discount is a percentage or flat currency amount. Above, it is a flat amount.
• create_date – The date that the record was added to the table.
• valid_from – When the offer starts. These offers can also be made for future dates.
• valid_until – When the offer ends.
• maximum_discount_amount – The maximum discount amount that can be offered with an order.
• product_id – The relevant product ID, when the offer is related to a specific product.
• product_category_id – The relevant product category ID, when the offer is related to a specific category.

If the offer is applicable for any order, irrespective of product or product category, both of these columns (product_id and product_category_id) would be NULL. Thus, I have kept both of these columns NULLABLE.

What Else Could We Add?

Another aspect of product pricing is dynamic pricing. This is a very fresh concept in which base prices and discounts continuously adjust in response to real-time supply and demand. There are enormous benefits associated with dynamic pricing, including greater control over pricing strategy, better flexibility, and better profits.

Do you think our existing data model could support dynamic pricing? What additional changes do we need to build in dynamic pricing capabilities?

Let us know your views in the comments section.