Reward Point Mod beta now available

Great contribution Andrew, thanks and will test later
:clap:

Suggestion: you may want to make the points "unconfirmed" it payment is made by COD, check, etc. and wait until order status changes from Pending to "confirm" the points.

From a redemption perspective, suggest simply allowing redemption for gift certificates.

Good luck,
Scott

swguy:

Suggestion: you may want to make the points "unconfirmed" it payment is made by COD, check, etc. and wait until order status changes from Pending to "confirm" the points.

Thanks Scott, excellent suggestion. I put the change in the next update.

swguy:

From a redemption perspective, suggest simply allowing redemption for gift certificates.

Good luck,
Scott

Good idea. The two that I'm looking to code at the moment is to redeem against future sales as a straight points-to-cash swap (obviously with a different ratio) and another mod which set Redeem Points against products and then allow the customer to select which products they wish to redeem against at checkout.

More random thoughts:

- Delete orders in admin should remove points.
- Consider adding a log table to show when points are added, removed or redeemed.

Scott- I'm currently looking into how the mod could be used to defraud. i.e. Someone makes a large order, gets the points and then redeems them in a second order- Then cancels the first (say through doing a chargeback on a credit card).

So I think some form of audit trail needs to be made tracking each of the Reward Point transactions. Other ideas I have are:

1. A sunrise period between points being earned and the ability to redeem the points- i.e. Any points earned on an order placed today only become redeemable in 30 days time.
2. Only allow customers to accrue Reward Points once they've made a certain value of orders- Once this point is reached (and proved the customer to be trustworthy) then the Reward Points from the previous orders become available.
3. Disallow "redeem points only" orders.

What do you think?

The way I would effect a sunrise program is using the log; the log (rather than the reward_customer_points table) would be the medium for determining the current customer balance. The default would be simply to get all log records for that customer older than (say) 30 days. I wouldn't write a log record until the order had moved to (say) processing state, which could obviously be delayed for some payment methods; alternately, the log could store a state for each row, which would be something other than "available" for points accumulated by a transaction which hasn't settled.

The reverse charging you're talking about is really the most egregious form of fraud; in this case, the shopkeeper would have to plead his case to Visa not to allow the chargeback. I'm more concerned about script kiddie probes for vulnerability (submit a cod transaction for a backordered item, get the points, submit a real transaction for something in stock.)

I disagree with 3; I think this is the primary modality in which such programs are used. And I'm not crazy about 2. Allowing them to earn points from day 1 but forcing the points to age before use should be sufficient.

Good luck,
swguy

I can see the pro's and cons of the points maturity issue. One more thing to consider with the redemption is the fact that most retailers that use the rewards or point system only send the consumer the redemption once a quarter. This would deter some of the fraud activity with cod orders and such. My thought would be to let the point acrue for at least 30 days and then be released at least 2 weeks after that periods cut off date.

A common situation with chains like staples, office max and office depot is they let the earnings acrue for a quarter (3 months), then they mail out a card or coupon good for the reward point amount about 2 weeks after that period has ended.

The only other part of this is that in most places that have this program also have a place on the companies web site where the user can go see when they earned points and what their total was for that period and when they will be released.

code for thought......:frusty:

hi everyone,

is this contrib only for zc 1.3.8 or could i use in zc 1.3.7?

thx @all

greeting
Lissy

Lissy- while I haven't tested it with 1.3.7 there is no reason it should not work. None of the modification is "invasive" so it does not make any changes to any core modules.

swguy:

The way I would effect a sunrise program is using the log; the log (rather than the reward_customer_points table) would be the medium for determining the current customer balance. The default would be simply to get all log records for that customer older than (say) 30 days. I wouldn't write a log record until the order had moved to (say) processing state, which could obviously be delayed for some payment methods; alternately, the log could store a state for each row, which would be something other than "available" for points accumulated by a transaction which hasn't settled.

I've created a reward_points_audit table which tracks customer and order ID, reward points, date added and a status. The status can be 'pending', 'processed', 'cancelled' or 'redeemed'. A change to the status will update the customer reward points record which I am going to keep in order to keep the number of MySQL transactions/load down when just doing things like displaying the current total.

I've just got to write the Admin side of the audit trail and then I'll put it up for download.

Einstin:

I can see the pro's and cons of the points maturity issue. One more thing to consider with the redemption is the fact that most retailers that use the rewards or point system only send the consumer the redemption once a quarter. This would deter some of the fraud activity with cod orders and such. My thought would be to let the point acrue for at least 30 days and then be released at least 2 weeks after that periods cut off date.

This is why I'm thinking that the redeem side should be further mods with the parent mod including some basic redeem functionality.

I have got permission from hem and numinix to merge 2 current reward point modules together:
The new project can be found at:
http://code.google.com/p/zencart-reward-point/

PM me if you want to join the developer team.

I will start merging code as soon as I can.

yellow1912

Okie, after looking at both modules, here is some food for thought:

1. hem did a good job using observer class. Will use that instead of hacking into the checkout page
2. I think it's better to put the point into the log table first, with status 0 (not approved), then do a check and update the total reward point in the reward point table.
Reason:
Depending on payment methods etc, we may not want to approve the point right away. Also, point should be associated with order number, so that we can later approve/remove points etc when we change order status

More to come later.

Yellow- just a bit of an update on this:

With the feedback I've had here I've already started coding an audit trail. Points will now not automatically be added to a customers Reward Points pool. They will be added to a "Points Pending" until either the status of the order changes or a "sunrise period" passes- Both to be admin settable. Should have it finished by the end of the week.

Im trying to merge things together asap, so that we can work on the same code and dont have to worry about other things.

hem:

Yellow- just a bit of an update on this:

With the feedback I've had here I've already started coding an audit trail. Points will now not automatically be added to a customers Reward Points pool. They will be added to a "Points Pending" until either the status of the order changes or a "sunrise period" passes- Both to be admin settable. Should have it finished by the end of the week.

hem, more comments in your code would be appreciated, I'm having a hard time following the flow.

Anyhow, can you clarify the content of each field inside this table:

CREATE TABLE `reward_product_points` (
`rewards_products_id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`scope` INT( 1 ) NOT NULL DEFAULT '0',
`scope_id` INT( 11 ) NOT NULL DEFAULT '0',
`point_ratio` DOUBLE( 15, 4 ) NOT NULL DEFAULT '1',
`bonus_points` DOUBLE( 15, 4 ) NULL,
PRIMARY KEY ( `rewards_products_id` ) ,
UNIQUE `unique_id` ( `scope` , `scope_id` ));

Im thinking of 1 or 2 tables:
1 would hold category specific point ratio
1 would hold product specific point ratio
(can merge the 2, but make more sense to use 2 instead of 1)
and we also have a global ratio

The point bonus will be calculated using product specific ratio first, if not available then category specific, if not then global.

The log table will then store
products_id
orders_id
ratio_applied
points_applied
status
created_on
modified_on

stuffs like that. It will be easy to re-calculate the point when users return a specific products etc,


Regards.

Proposed table structure:

# use this table name since we want to use the code from store credit module
CREATE TABLE sc_customer (
  `customer_id` int(11) NOT NULL,
  `amount` float NOT NULL default '0',
  `created_on` int(11) NOT NULL DEFAULT '0',
  `modified_on` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY ( `customers_id` )
);

# holding category specific ratio
CREATE TABLE sc_categories_ratio (
  `categories_id` int(11) NOT NULL,
  `ratio` float NOT NULL DEFAULT '1',
   PRIMARY KEY ( `categories_id` )
);

# holding products specific ratio
CREATE TABLE sc_categories_ratio (
  `products_id` int(11) NOT NULL,
  `ratio` float NOT NULL DEFAULT '1',
   PRIMARY KEY ( `products_id` )
);

# log table
CREATE TABLE sc_logs (
  `products_id` int(11) NOT NULL,
  `orders_id` int(11) NOT NULL,
  `ratio` float NOT NULL,
  `amount` float NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0' ,
  `created_on` int(11) NOT NULL DEFAULT '0',
  `modified_on` int(11) NOT NULL DEFAULT '0',
)

hem, Im working my way thru yours and numinix's code, make progress bit by bit, for instances:
1. I think it's better to name the mod "store credit", since owners can just assign customers additional store credit for refund-etc.... (which is also a nice feature later, refund go to store credit instead of cash/cc/....)

2. Make changes to observer class, let it just simply do the call, it should not add credit or anything
http://code.google.com/p/zencart-reward-point/source/browse/trunk/includes/classes/observers/class.store_credit.php

3. Moving functions into class, breaking them down into smaller functions.
The idea is that this class can be re-used on admin side to retrieve and edit credit as well.
http://code.google.com/p/zencart-reward-point/source/browse/trunk/includes/classes/store_credit.php

Also, a product can belong to multiple categories, which cat will be used to calculate ratio? Master cat?