How to build a referral with incentive (promo) tracking?

Question

Currently my site has an invite system that generate a unique code for each invite sent by a user. It's a unique code per invite (not per user).

I want to have an invite with incentive promo (similar to groupon, when one invites a friend and that friend completes a transaction, then the one who invites get a credit).

How would I modify my currently working invite system so it works with minimal modification? Currently in my invite system, there is no tracking system. It only tracks the number of invites sent by user, so overall I can see the ratio of # of sign up and # of invites sent from all users.

Thanks.

Answer 1

Since you have posted no code, and there's necessarily an upper limit to what I can do without you writing me a large check, this must be general advice. But if you generally know what you're doing, it should be enough:

You will need two database tables for this; one of them you should already have in some form. Performance optimizations such as indexes I won't deal with much here, but you will need them.

The one you should already have is an invites table, which maps users to their invite codes. It should contain at least the following fields:

• invite_id a unique auto incrementing primary key; this is only used internally in your application and is never exposed to the public
• user_id this is a foreign key into your users table
• invite_code this is the actual invite code that you issue to a user

The new table that you need to create, let's call it invites_friends maps invites to friends:

• invites_friends_id a unique auto incrementing primary key; this is only used internally in your application and is never exposed to the public
• invite_id this is a foreign key into your invites table
• friend_id this is a foreign key into your users table and identifies the newly signed up user
• goal_complete this is a boolean, default false, which you set to true when the new friend completes their transaction and you credit the referring user; later you should move this data into another table, with extra fields such as transaction IDs or dates and whether you already issued credit, especially if you end up having more than one goal for which the referring user must be credited.

This should get your data stored in a reasonable form; you're just a couple of SQL queries away from all the reporting you will ever need. (Learn joins; you'll need them here.)

Your homework is to design a table which stores multiple goals for which you might credit the referring users, and a relation table which maps completion of the goals to invites so that you can track which users get credit and whether you credited them already. They'll work much the same as the above tables. There are literally thousands of tutorials out on the Internet about this, so pick a few likely favorites and enjoy.