E-COUPONS / E-CREDIT LINES

SendSafe fully supports e-coupons and e-credit lines. E-Coupons can be automatically issued when an event occurs such as a customer buying a certain product or periodically (for example) the 15th day of each of month; see Automatic Issuing of e-coupons for more information. E-coupons can also be manually created in any number of ways (including the use of a coupon import tool).

Coupons are defined in the {Coupon} database table. Review this Db table before attempting to issue coupons. E-Coupons definitions are entered into the system via the SendSafe E-Coupon WebAdmin page. The definitions can be created by the SendSafe Robot or they can be entered using a 3rd party program that inserts them into the database (this means that any program with SQL access can create e-coupons).

There are two fundamental kinds of coupons: (a) Robot Issued E-Coupons (b) Manual E-Coupons. The main difference is that Robot Issued e-coupons will have the coupon number created by the robot and the coupons will be e-mailed to the recipeint.

The customer enters a coupon number into the Billing Information Order Form or into the IHC credit card field (if the coupon is to be used for an In-house credit line).

Validation of coupons are performed twice, once by both the Web Page GUI and once by the Robot. If the coupon is for the full amount of the order, then the customer does not need to enter a credit card (see also User Entry Help).

Validation of IHC charges is performed once by the Robot after submission of the order.

When an e-coupons is used by a customer, the total cost displayed on the billing information page will be adjusted for the coupon (if the coupon can be validated). This discount is an estimated value and under some circumstances involving "shared coupons" this estimated value may not be correct. If two people are using the same coupon at the same time and there is only one use left, then the two orders will be in a race condition (where the 1st coupon processed by the Robot will win). If you do not issue shared-use coupons then the displayed value will alway be correct.

Coupon entry by customer:

The customer can enter coupons by typing them into the appropriate entry field OR by selecting from a dropdown list of available coupons. The coupon dropdown list appears directly below the coupon entry field. This list will contain all coupons which were specifically issued to this customer. The list will not contain generic coupons. If the customer has no specifically issued coupons then the list will be empty. Coupons are included in the list based on the following SQL:

select top 50 couponnumber from 
coupons where 
active = 1 and 
usesleft > 0 and 
RemainingDollarValue > 0 and 
CouponTemplate = 0 and 
BeginDate <= CAST( getdate() AS datetime) and 
EndDate >= CAST( getdate() AS datetime) and 
issuedto = 
(
select email from customers, addresses where 
customerid = 111111111 and 
customers.addressid = addresses.addresscodeid
)

Coupons can be configured to be three different types:

All coupons have several control characteristics specified in the database and the Web Admin Page:

E-Credit Line

Using E-Coupons for in-house credit lines (IHC) requires the creation of one coupon for each credit line. The coupon number is entered into the IHC account number field on the billing page.

E-Credit line account numbers MUST included 10 or more numeric digits. They can also include the letters A-Z, in addition to the minimum 10 numeric digits. Failure to include 10 numeric digits or more will result in a bogus credit card rejection event. This length requirement is set by Application("IHCCardNumLen" ).

In addition to the Account number a Security Code or PIN is also required (i.e. "back of the card" security code for Visa/MC). This code is setup in the system in the coupons.CodeDescriptor field. This field must only be numeric and leading zeros are illegal.

E-Credit lines require the following fields in an e-coupon:

E-Credit lines do not use the following fields in an e-coupon:

Creating E-Coupons

To create a coupon you must either use the Web based Coupon Admin page or make a direct entry in the {Coupon} table using a 3rd party tool or program. It is recommended that you use the Coupon Admin Page. If at all possible only issue coupons with a single use (i.e. UsesLeft = 1). This will result in more secure coupon operation since stolen or shared coupons will not be able to be used if the rightful recipient has already used it.

A coupon will not be enabled until the Active field is set to one (1) and the Uses Left is set to some positive value. 

Note: If manually entering coupons into the database... all fields in a coupon record must be set to a value... Null fields will be reported as an invalid coupon record during processing an order that uses the coupon.

You must set a maximum dollar amount for a coupon (see TotalDollarValue and RemainingDollarValue). The Remaining Dollar Value will be decreased with each use of the coupon until the value of the coupon is used up. This RemainingDollarValue field can be used to report and track dollar use of coupons. 

Note: To make a non-expiring coupon: UsesLeft should be set to a very large number to make sure that a coupon never runs out of uses and the begin and end dates should be set for a long enough period of time.

E-Coupons Limiting Flags

In addition to flagging coupons as good only for specific products, the entire option of entering coupons can be turned on/off by the presence of an e-coupon limiting flag. This flag is located in the OrderLineItem.OrderFlags column. If the flag "LIMEC" is found in the orderlineitem then the coupon entry fields are turned off for this order. The "LIMEC" flag can be placed in an lineitem in a number of ways. The most common way is to insert it into the order by using an ORDERFLAGS entry field in the addToBasket FORM POST operation: i.e. <INPUT TYPE=hidden name="ORDERFLAGS" value="LIMEC">

E-Coupons Issued by Robot

Approximately every ten minutes, the robot issues e-coupons that have been created using the Coupon Admin Page. These coupons are sent by the robot to the recipient of the coupon.

*Note: If the robot is operating in backup mode then it will not issue e-coupons.

Coupon Price Discount Calculation

The discount value of a coupon is applied to the full cost of an item including any shipping charges. This means that a coupon valid for one free item would include shipping costs. A coupon good for 10% off would have 10% taken off the total cost of the item including shipping costs. The result of this logic is that orders which include coupons are calculated in a way that yields results as if the steps listed below were used:

  1. Total All items
  2. Calc shipping
  3. Apply coupon
  4. subtotal order
  5. Calc sales tax
  6. Total order

**The actual software logic that totals orders and applies coupons does not follow the discrete accounting steps listed above (but instead uses different methods to arrive at a like result). This departure in methods is due to how coupons are applied and how shipping charges and sales tax must be calculated for split orders which ship to different states with different sales tax ratios and different shipping costs. The logic that implements the (above) accounting steps uses a ratio-metric calculation of sales tax by applying to the sales tax the same discount-ratio for which a coupon is applied to line items. i.e. if a coupon results in an 80% discount on an order’s line items THEN total sales tax for that order is also reduced by 80%. The ramifications of this logic is that some coupons when applied to split orders can result is an average reduction of all sales taxes (and does not take into account the specific sales tax that was applied to any one specific item).

The order of precedence for coupon discounts is:

  1. BuyX - GetN free
  2. Percentage Discount
  3. Dollar Amount

The precedence comes into play in controlling how a discount will be applied. If you set both "BuyX-GetX free" and a Percentage, then the "BuyX-GetX freee" will be applied and the Percentage will be ignored (with the total dollar amount limiting the upper value of the coupon). If you do not set "BuyX-GetX freee" or a Percentage, then the full dollar amount of the coupon will be used as a discount. It is very important to understand this order of precedence when creating coupons.

Use the following values to disable a discount mode:

  1. BuyX - GetN free: Set BuyX = 0
  2. Percentage Discount: Set Percentage = 0
  3. Dollar Amount: Set dollar amount = -1 (warning: setting dollar amount = -1 will create a coupon with no upper limit to its dollar value. Set this value to -1 only if you want the coupon to be good for any amount; otherwise it is good practice to set the Dollar Amount to some upper limit, unless the coupon is a percentage coupon and good for a percentage of the order regardless the total order amount.) < /li >

Coupons for Specific Products

For example: Stereo Equipment or Soft Drinks or a 4oz Jar of Strawberry Fields Jam.

Selecting products restrictions by type: The ValidateProductTypes field in the database contains the product types for which this coupon is valid. The ProductType field in the {Items} table establishes a product's type. This ProductType is compared to the list of ValidateProductTypes of a coupon to ascertain if a specific product is valid with this coupon. 

Selecting product restrictions for specific products: The ValidateProductTypes field in the database lists ItemCodeIDs (in <brackets>) for which this coupon is valid.

For all coupons except BuyX GetN free, you can have a mixed selection of both "By Type" and "Specific Products". See the ValidateProductTypes database information for specific examples of constructing these lists.

Note: You must set the ValidateProductTypes to the string "<any>" (no quotes) if you want all products to be covered by a coupon.

Note: SendSafe can be run in a mode where it will process all coupon orders, but reject all credit card charges. This mode (meant primarily for site development testing) can also be used to create a site that accepts only coupons. The robot is placed in this mode with a -D command line switch.

E-Coupon Grace Period

There is a 48 hour grace period applied to all coupon end dates. This grace period exists partially to provide time for customers in different date zones to use what is still a valid coupon in their date zone (while the web server at its date zone would otherwise see a coupon that has expired).

This grace period of 48 hours is added to the End date of the coupon. The grace period is not displayed in the e-coupon that is e-mailed to the customer. In a future release of SendSafe the grace period will be configurable in 1 day units of time.

Automatic issuing of e-coupons

SendSafe can automatically issue coupons using two different mechanisiums:

Job issuing is used to run a SendSafe job that sends out e-coupons to a selected (SQL) group of existing customers base. Buy Event Issuing is used to send out e-coupons to specific customers when they purchase specific items or types of items.

Buy Event E-Coupon Issuing

This is an exteemely powerful feature that can be used to generate return customer traffic.

Buy Event issuing is configured by creating auto-coupon templates. An auto-coupon template is a coupon record with the AutoCoupon and Coupon Template check boxes checked. The E-Coupon WebAdmin page is used to create auto-coupon templates as well as individual e-coupons.

An E-Coupon is issued when a product is purchased which is listed in either the AutoCouponProductId or AutoCouponProductType fields of an auto-coupon template. The e-coupon will be immediately issued to the customer that made the purchase once their credit card has been authorized. A single order can result in multiple e-coupons if several matching items are purchased. Additionally, a single item purchased can result in multiple e-coupons if the item is listed in more than one auto-coupon template.

Rules:

  1. If the customer ordered more than one of a matching item then the system will issue a single coupon worth "One Coupon Unit Value" per Item ordered, instead of issuing multiple individual coupons. For example: If the autocoupon template had a TotalDollarValue of $10 and the customer ordered 5 matching items then coupon issued to the customer would have the autocoupon template value of TotalDollarValue increased to $10 x 5 = $50.00.
  2. The RequiredDiscountPlan for the coupon is set to a value other than null then the customer must be a member of that discount plan to recieve this auto-coupon.
  3. The Beginning date for the coupon is set to the date that the order was placed.
  4. The ending date for the coupon is set to the difference in days between the beginning and ending dates as set in the autocoupon template.
  5. The contents of the CodeDescriptor  from the template is inserted into the codedescriptor field for each autocoupon that is created. This is very useful for reporting and tracking on autocoupons. 
  6. Auto-Coupons will not be issued for items which have a zero dollars value.
  7. Auto-Coupons will not be issued on orders where the entire coupon and sale adjusted cost of the order is zero.

Job for Issuing E-Coupons

The SendSafe job scheduler has built in jobs for many different activities. One of the built in jobs is for issuing coupons. You can use the job scheduler to run a job once or to run the same job on a periodic basis.

The one time jobs are often used to issue coupons to a group of customers. This group is specified by an SQL Select statement that you provide (so virtually any selection criteria is possible).

The periodic jobs are useful (for example) if you issue coupons to customers on specific days of the month. One application might be setting up a job that uses an SQL Select statement that will send coupons to anyone that made a purchase in the last sixty days.

To create a job that issues coupons using jobs follow the steps below (see also jobqueue). An admin page for modifying the job queue does not exist at this time so these jobs must be entered into the database manually or via a 3rd party program.

  1. Add a job to the job queue (using SQL Query tool of you choice).
  2. Set the JobCode = 1
  3. Set the store = your store
  4. Set the scheduling = when you want the job to run (typically this is every XX day the month).
  5. Set enabled = 0 (set to 1 when you are doing setting up this job and want this job to be avaiable for execution).
  6. Set runnable = 1 (typically the backup robot will not be running this kind of job).
  7. Set Param1 = the SQL Selection logic that will produce a list of customer who will receive coupons.
  8. Set Param2 = the Coupon number (not indexkey) for the Coupon Database Record that will be a template for the coupons being issued.
  9. Set Param3 = Optionally set this field to the string "{SIMRUN}" which will cause the job scheduler to run the job without creating or issuing coupons (check the audit file for a list of customerId's that would have received a coupon).
  10. Set NextRun = the date and time when you want the 1st run to occur.
  11. All other fields set to NULL

Param2 is a template for the coupon that will be created. All fields in the Template Coupon Database Record will be used to create the new record except as listed below.

  1. The CouponNumber will be a newly generated number.
  2. The Active flag will be set active once the coupon is issued.
  3. The BeginDate will be set to the date of issue
  4. The EndDate will be set to the difference in days between the beginning and ending dates as set in the coupon template.
  5. The IssuedTo field will be set to the e-mail address of the customer that the coupon was issued to.
  6. The Issued flag will be set true once the coupon is issued.
  7. The Coupon Template checkbox must be checked.
  8. The AutoCoupon checkbox does not needed to be checked, but can be if this template has multiple uses.
  9. The contents of the CodeDescriptor  in the template is inserted into the codedescriptor field for each e-coupon that is created. This is very useful for reporting and tracking on e-coupons. 

Param1 contains an SQL clause that selects a list of customers who will be e-mailed coupons. This SQL clause can be as simple as "select customerid from customers" or as complex as the example SQL below which will select all customers who have belonged to a club prior to last month and purchased a specific product type last month. If you are going to use long SQL clauses like the one below then it is recommended that you use stored procedures instead of inserting very long SQL clauses into param1. 

Select Distinct Customers.CustomerId From orders, OrderLineItem, Customers, Addresses, Items
where
OrderLineItem.TransactionId = Orders.TransactionId AND
Orders.CustomerId = Customers.CustomerId AND
Items.ItemCodeId = OrderLineItem.ItemCodeId AND
Addresses.AddressCodeId = Customers.AddressID AND
Items.ProductType = 'CLUBMEMBERSHIP' AND
Customers.DiscountPlan = 'ClubLevel1' AND
Orders.Date < CAST( CAST ( DATEPART(MONTH, DATEADD(month, -1, getdate() ) ) AS char ) + '/1/' + CAST ( DATEPART(YEAR, DATEADD(month, -1, getdate() ) ) AS char ) AS datetime) AND
Customers.CustomerId IN

    (Select Distinct Customers.CustomerId From orders, OrderLineItem, Customers, Items, Addresses
    where
    OrderLineItem.TransactionId = Orders.TransactionId AND
    Orders.CustomerId = Customers.CustomerId AND
    Addresses.AddressCodeId = Customers.AddressID AND
    tems.ItemCodeId = OrderLineItem.ItemCodeId AND
    Items.ProductType = 'BOOKS' AND
    Customers.DiscountPlan = 'ClubLevel1' AND
    Orders.Date between CAST( CAST( DATEPART(MONTH, DATEADD(month, -1, getdate() ) ) AS char ) + '/15/' + CAST( DATEPART(YEAR, DATEADD(month, -1, getdate() ) ) AS char ) AS datetime) AND
    CAST( CAST( DATEPART(MONTH, CURRENT_TIMESTAMP ) AS char ) + '/15/' + CAST( DATEPART(YEAR, CURRENT_TIMESTAMP ) AS char ) AS datetime) )

E-Coupon Configuration

E-Coupons must be enabled in the storefront admin for the e-coupon entry fields to appear on the shopping cart pages. In the storefront set "Coupons Enabled" = 1. The system configuration cookie "EnableCoupons" holds the state for the storefront configuration. After you enable coupons, you must reload the storefront. Coupons can also be enabled/disable on an individual order by the E-Coupon limiting flags. Since e-coupons are enabled/disabled on a session basis, you must shutdown your browser and reopen it after you enable coupons (to enable coupons for your session).

You can relabel the coupon field with this setting: Application("LabelECouponField" ) = "E-Coupon"

The following entries in the robot configuration file for the storefront can be used to personalize or customize the text and appearance of the e-coupon, the help URL included in the e-coupon, and URL Links to e-store web site that appear in the e-coupon.

Things to Check when Coupons are not Working

The coupon entry fields will only be displayed when two conditions are met: