Associates Program | Building an Associates Program
Building an Associates Program
Rolling your own associates program is actually remarkably simple, so we’re now going to see how we can build our own.
There are typically three parts to an associates program:
+ Letting new associates opt into the program
+ Capturing the relevant clicks and purchases that count towards associate purchases
+ Figuring out how much to send to each associate
To keep this example brief, we’re going to skip the bit that brings new associates on board and work under the assumption that anyone who has made a purchase on the site and is therefore a registered customer is able to participate in the scheme. In the real world, you’d most likely offer a sign up form of some sort, but still use the Customers table to keep track of the associates as well as the customers by adding an additional column indicating that they were a valid associate.
We can quickly get from here to the point where we can let our associate, Rony, create a link on his site to earningfair. We have to find some way to let Rony know what his associate ID is. As we mentioned before, in this example we’re going to assume that his customer ID will be his associate ID.
Obviously, we don’t want to send money to an associate before we’re confident that the sale has satisfactorily gone through the system, which is why we don’t just dump the commission values down to a separate table but instead make sure we can calculate the commission they should earn in retrospect. We’re not going to cover this topic in detail in this section, simply to keep the example brief, as there are implications as to what you do with the commissions once you’ve worked out who to pay them to.
For example, do you want to create a Microsoft Excel spreadsheet containing the commissions and then pass them over to your accounts department, or do something else? Once the data is in the database, it’s f up to you how you deal with it.
The best way to do this is to run a batch process once a month that trawls the orders looking for commissions that need to be paid. It’s a good idea to leave a month-long window in this process to make sure that you’re happy with the order. (For example, don’t pay October’s commission until December 1st.) Then, simply select out the associates from the successful orders in that month with a SQL ! statement like this:
SELECT DISTINCT AssociatelD
WHERE PartID IN
WHERE Status = 1 AND Order lD IN
WHERE Completed BETWEEN ’10/1/1999′ AND ’10/31/1999′))