Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    12

    Question loyalty points scheme help needed

    Hi, I'm stuck on how to add a loyalty points scheme to my database. I wondered what's the best way to add a number of points everytime users go through my website, these should ideally accumulate automatically in each Customer ID field, alternatively I wouldn't mind doing it manually, but I'm just struggling to visualize the right table structure. Anyone with better eyesight please?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    If it wa my issue then i'd go down the route of issuing loyalty points automatically with a reason code, of how/where they earned the loyalty reward. there is nothing then stopping you adding (or subtracting) rewards manually as you see fit. to get the customers currnet loyalty 'score' you then sum the points. that gives you transaparency of when they earned there points, how and so on. it provides more information to your site to understand how and when your customers interact with the site.

    the alterntive is to store a lump sum score, which is fine if all you need to know is cusoter xxxx has yyy points, but it gives no indication of when or where the points were awarded.

    eg

    rewardpointtypes
    id
    description


    customerrewardpoints
    customerid
    rewardpointtypeid
    quantity
    dateearned
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2009
    Posts
    12
    Automation issue with a reason code would be the ideal situation (of course I'd "reserve the right" to subtract or change those manually as you said...).

    My problem is that I don't know what a reason code is, does it require programming at all? Because I'm a sort of apprentice here and can't programm. I don't want to bother anyone here to explain to me how to set up a reason code, but any tutorial/video/website/etc... where I could get up to speed on how to do this would be very appreciated. Thanks!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    reasoncodes
    ID
    Description
    Value

    01
    Visit
    +1

    10
    offensive posting
    -1

    11
    obnoxious post
    -10

    99
    Bought Goods
    +5

    02
    recommended site
    +1
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2009
    Posts
    12
    Suppose I want to give 100 points to every reservation made through my website, that gives me the table I attached.

    Im sorry Im such a muppet but I just dont see it, a registered user books 4 people through my website and Im giving away 100 points PER PERSON, how do I automatically give the d^mn 400 points to John? I understand what you say about the fields of value, description and date but I think it's the multiplier that's blocking me.
    Attached Thumbnails Attached Thumbnails 100pointsID.jpg  

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    so either you write an entry for each person booked
    or you record 4 x's code xxx where xxx relates to the reservation award

    so
    that could be
    user 69
    reasoncode 123
    Qty 4
    date 13/12/09

    where reasoncode123 = "Booking in Site", Value 100

    or
    Code:
    ID  USER reasoncode   date
    201  69       123      13/12/09
    202  69       123      13/12/09
    203  69       123      13/12/09
    204  69       123      13/12/09
    ID is a unique ID in the awards table to make a record unique. you could instead sya use a composite key of USER date and itemID
    Last edited by healdem; 12-14-09 at 08:45.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •