How does the affiliate program work?
The affiliate program is a type of cooperation between the client and provider, in which the client receives a referral link or a promotional code. Such a client is considered a partner or referrer, and new clients who have registered under that link (promotional code) are referrals. The partner receives a percentage of the expenses of his referrals.
Note:
Only a new client, who has not yet purchased any services, can be added using a promotional code.
Referral program results:
- partner makes a profit from the attracted clients;
- provider gets new clients and advertising by the partner.
General operating principle
On the provider's side
The panel's administrator:
- Creates a referral program in menu Marketing → Affiliate programs → Add.
- Specifies the amount of reward and URL of the provider's website.
Places the following code on at least one page of the provider's website:
<script language="javascript"> var counter_url = "https://billmgr.domain/mancgi/counter?rnd="+Math.random()+"&project=1&r="+escape(document.referrer)+"&p="+escape(window.location.href); document.write("<img src='" + counter_url + "' border=0 width=0 height=0 style='display: none;'>"); </script>
CODECommentsbillmgr.domain — the domain name of the provider's billing system;
rnd — parameter to prevent caching;
project — identifier (id) of the referral program provider. In "BILLmanager Corporate", it can assume values other than "1". In the version "BILLmanager” it always assumes the value "1";
r — parameter for registering the page or domain from which the referral came;
p — full URL of the current page. The ID of the partner who brought the referral is extracted from it.
If you want BILLmanager to be the referral program website, place the specified code in the brand settings. Enter Settings → Branding settings → HTML → Heading.
This code is used to track clicks and registrations under referral links.
Note:
One client can be connected to only one affiliate program.
On the client's side
The client goes to Client → Affiliate program and copies the link or promotional code there. Users who clicked this link or entered the copied promotional code when ordering the first service become referrals of the client.
To view statistics of clicks on the referral link, the client (referrer) goes to menu Client → Affiliate program → Statistics. The menu shows how many times visitors clicked the link and how many of those who clicked the link registered in BILLmanager and made the payment.
DB tables
The affiliate table contains general information about the affiliate program:
id | name | project | integration | billurl | url | promocode | reward | period | account_group | account_group_restrict | name_lt | name_ru | name_ua | url_lt | url_ru | url_ua |
1 | NULL | 1 | NULL | NULL | NULL | LULULU@ID@ | 1000 | NULL | NULL | 8 | NULL | Ref | NULL | NULL | http://172.30.20.103:8080/?p= | NULL |
id — program code;
name — referral program name;
project — provider code on which the referral program runs;
integration — application method. Not used;
billurl — BILLmanager URL. Not used;
url — referral link template;
promocode — referral promotional code template;
reward — referral reward percentage;
account_group — code of the client group to which the referral program is available;
account_group_restrict — code of the group which is not allowed to use the referral program;
name_ru, name_ua — name of the referral program in different languages;
url_ru, url_ua — URL of the provider's website.
The affiliateitemtype table contains information on reward for specific customized product types:
id | affiliate | itemtype | pricelist | reward |
1 | 1 | 103 | 1 | 50 |
id — record code;
affiliate — referral program code;
itemtype — product type code;
pricelist — tariff code;
reward — referral reward percentage.
The account2project table contains information about which referral program the client is connected to, whose referral it is and under which referral program it was attracted:
account | project | affiliate | affiliatereferer | affiliateprogram | assigning_date |
2 3 5 6 7 | 1 1 2 1 1 | 1 1 NULL NULL NULL | NULL NULL NULL 3 3 | NULL NULL NULL 1 1 | 2020-01-08 2020-01-08 2020-01-10 2020-01-10 2020-01-12 |
account — client code;
project — provider code;
affiliate — affiliate program that the client uses with the provider;
affiliatereferer — the partner under whose link the client was attracted;
affiliateprogram — the referral program under which the client was attracted;
assigning_date — the date of client's connection to provider.
The affiliateclick table contains the list of clicks on the referral link:
site | account | referal | ip | project | sesid | cdate |
2 2 2 3 | NULL 6 NULL 7 | 172.30.20.1 172.30.20.1 172.30.20.1 172.30.20.1 | 1 1 1 1 | 1578689949.b7XKmd 1578689962.GLQNPy 1579183750.OC4uK2 1578833311.M0DVRk | 2020-01-10 20:59:09 2020-01-10 20:59:22 2020-01-16 14:09:10 2020-01-12 12:48:43 |
site — the address of the page from which the click was made;
account — affiliate client code, whose link was clicked;
referal — the code of the client, who has registered under the link;
ip — the IP address from which the transition was made;
project — code of the provider, under whose referral program the transition was registered;
sesid — unique key;
cdate — transition date and time.
The affiliatereward table contains the list of rewards under the referral program:
id | affiliate | account | referal | amount | currency | payment | cdate | itemtype | pricelist | reward |
1 | 1 | 3 | 6 | 1000.0000 | 126 | 3 | 2020-02-01 | NULL | NULL | 1000 |
id — record code;
affiliate — the code of the referral program, under which the reward was accrued;
account — code of the client to which the reward was accrued;
referal — code of the client, regarding whose costs the reward was accrued;
amount — reward amount;
currency — accrual currency code;
payment — code of the payment by means of which the reward was accrued;
cdate — accrual date;
itemtype — code of the product type for which the reward was accrued;
pricelist — code of the tariff, for which the reward was accrued;
reward — percentage of the reward accrued.
Creation of a referral program
When creating a referral program, BILLmanager makes a record in the database:
An example of a DB table record
INSERT INTO affiliate (id, name_ru, project, reward, url_ru) VALUES('1', 'Invite friend', '1', '15', 'http://billmanager.docker:8080/?from=')
id — referral program id;
name_ru — referral program name;
project — provider id;
reward — percentage of accrual on referral's expenses;
url_ru — referral link.
When adding special referral program rules in the menu Marketing → Affiliate programs → Rules → Add, BILLmanager makes a record in the database:
An example of a DB table record
INSERT INTO affiliateitemtype (affiliate, id, itemtype, pricelist, reward) VALUES('1', '1', '103', '1', '50')
affiliate — referral program id;
id — rule id;
itemtype — product type;
pricelist — tariff plan;
reward — amount of reward in percent.
Client clicking on the referral link
Tracking of clicks
When the referral link is clicked, BILLmanager makes a record in the database:
DB table record
INSERT INTO affiliateclick (site, account, ip, project, sesid, cdate) VALUES ('', '2', '172.30.20.1', '1', '1579183750.OC4uK2', NOW())
site — the address of the page from which the click was made;
account — affiliate client code, whose link was clicked;
ip — the IP address from which the transition was made;
project — code of the provider, under whose referral program the transition was registered;
sesid — unique key;
cdate — transition date and time.
The referral is linked in the following cases:
- in case of transition to the showcase page;
- manually in the client's personal account settings (BILLmanager Corporate);
- manually in the client settings (BILLmanager);
- at the time of registration.
If the client did not have a session in the browser, the referral will be linked, but the table entry will not be created. This can occur, for example, in incognito mode or when using a proxy.
Referral link click log is kept in /usr/local/mgr5/var/counter.log.
Registration of a new client by the referral link
Linking the client to the provider:
DB table record
INSERT INTO affiliate (id, name_ru, project, reward, url_ru) VALUES('1', 'Invite friend', '1', '15', 'http://billmanager.docker:8080/?from=')
CODEComments to a DB recordid — program code;
name_ru — name of the referral program in the Russian language;
project — provider code on which the referral program runs;
reward — referral reward percentage;
url_ru — URL of the provider's website.
Connecting a new client to the referral program:
DB table record
UPDATE account2project SET affiliate='1' WHERE account='3' AND project='1'
CODEComments to a DB recordaffiliate — affiliate program that the client uses with the provider;
account — client code;
project — provider code.
Linking a new client to the referrer as the referral:
DB table record
UPDATE account2project SET affiliateprogram='1', affiliatereferer='3' WHERE account='5' AND project='1'
CODE
Reward accrual
By default, rewards for the referral program are accrued by a cron job, on the first day of each month at 3:10 server time:
Cron job
10 3 1 * * /usr/local/mgr5/sbin/billmaintain --command affiliatemonthly >/dev/null 2>&1
When calculating reward for the referral program, BILLmanager perfoms the following:
Sends queries regarding each referral:
An example of query
Jan 16 12:40:16 [12:151] core_module INFO Request [billmaintain][root] 'affiliate=1&func=affiliate.process&month=2020%2D01%2D20&out=xml&referal=6&referer=2'
CODEChecks whether referrer is permitted to take part in the referral program:
An example of query
SELECT Count(*) FROM affiliate a LEFT JOIN account2group a2g ON a2g.account = 2 AND a2g.accountgroup = a.account_group LEFT JOIN account2group rest ON rest.account = 2 AND rest.accountgroup = a.account_group_restrict WHERE a.id = '1' AND ( a.account_group IS NULL OR a2g.account IS NOT NULL ) AND rest.account IS NULL
CODEIn case of negative response, the below type of errors will be displayed in the log:
An example of a log error
Jan 10 20:11:20 [2724:1586] libmgr ERROR Error: Type: 'access' Object: 'affiliate' Value: '1'
CODEChecks whether rewards for the referral have already been accrued in the reported month:
An example of query
select count(*) from affiliatereward where account=2 and referal=6 and affiliate=1 and cdate='2020-02-01'
CODECalculates the referral's costs by means of the query:
An example of query
SELECT e.amount, s.currency, ai.reward, e.operationparams, ai.id AS rewardid FROM subaccount s join expense e ON e.subaccount = s.id left join item i ON i.id = e.item left join pricelist p ON p.id = i.pricelist left join pricelist pparent ON pparent.id = p.parent left join affiliateitemtype ai ON ai.affiliate = 1 AND ( ai.pricelist IS NULL AND ( ai.itemtype = p.itemtype OR ai.itemtype = pparent.itemtype ) OR ( ai.pricelist = p.id OR ai.pricelist = p.pricelist_group OR ai.pricelist = pparent.id OR ai.pricelist = pparent.pricelist_group ) AND ( ai.itemtype = p.itemtype OR ai.itemtype = pparent.itemtype ) ) left join pricelist aip ON aip.id = ai.pricelist WHERE s.account = 6 AND s.project = 1 AND e.realdate >= '2020-01-01' AND e.realdate < '2020-02-01' ORDER BY IF(ai.pricelist IS NULL, 0, 1) DESC, IF(aip.isgroup = 'on', 0, 1) DESC
CODEIf the conditions are met and the referral has expenses for the reported period, BILLmanager will calculate the reward for the referrer:
An example of a DB table record
INSERT INTO affiliatereward (account, affiliate, amount, cdate, currency, id, referal, reward) VALUES('2', '1', '10.00', '2020-02-01', '126', '1', '6', '10')
CODEFor each referrer, who was accrued a fee. BILLmanager sends a query:
An example of query
Jan 16 12:40:16 [12:153] core_module INFO Request [billmaintain][root] 'cdate=2020%2D02%2D01&func=affiliate.createpayment&out=xml&referer=2'
CODECreates and credits advance payments with a PartnerPayment/ID number:
Example of DB table records
INSERT INTO payment (createdate, currency, id, paymethodamount, randomnumber, status, subaccount, subaccountamount, usedamount) VALUES('2020-01-16 12:40:16', '126', '2', '10.00', 'oRBId9qgDJOr', '1', '1', '10.00', '0.0000') UPDATE payment SET number='PartnerPayment/2' WHERE id='2' UPDATE payment SET description='Advance payment #2' WHERE id='2' UPDATE payment SET status='4' WHERE id='2' UPDATE payment SET description='Partner Payment - Gym' WHERE id='2' UPDATE affiliatereward SET payment='2' WHERE id='1'
CODEComments to a DB recordcreatedate — creation date;
id — payment code;
paymethodamount — payment amount in the payment method currency;
randomnumber — unique instant payment number;
status — current payment status:
- 1 — new,
- 2 — payment in progress,
- 3 — pledged payment,
- 4 — credited,
- 5 — pending return,
- 6 — returned to client in full,
- 7 — fraudulent,
- 8 — new payment with quick credit,
- 100 — deletion in progress
subaccount — personal account code to which the payment is bound;
subaccountamount — payment amount in the currency of the personal account;
usedamount — payment amount used;
number — payment number;
description — advance payment designation;
payment — code of the payment by means of which the reward was accrued.
Note:
Payments for awarding according to the referral program are created without indication of the payer and acts on such payments will not be made.