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.

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:

1. Creates a referral program in menu Marketing → Affiliate programs → Add.
2. Specifies the amount of reward and URL of the provider's website.

3. 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>

   Comments

   billmgr.domain — the domain name of the provider's billing system;

   rnd — parameter to prevent caching;

   project — identifier (id) of the referral program provider. In the version BILLmanager Startup, Advanced 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.

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:

The affiliateitemtype table contains information on reward for specific customized product types:

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:

The affiliateclick table contains the list of clicks on the referral link:

The affiliatereward table contains the list of rewards under the referral program:

Creation of a referral program

When creating a referral program, BILLmanager makes a record in the database:

INSERT INTO affiliate (id, name_ru, project, reward, url_ru) VALUES('1', 'Invite friend', '1', '15', 'http://billmanager.docker:8080/?from=')

When adding special referral program rules in the menu Marketing → Affiliate programs → Rules → Add, BILLmanager makes a record in the database:

INSERT INTO affiliateitemtype (affiliate, id, itemtype, pricelist, reward) VALUES('1', '1', '103', '1', '50')

Client clicking on the referral link

Tracking of clicks

When the referral link is clicked, BILLmanager makes a record in the database:

INSERT INTO affiliateclick (site, account, ip, project, sesid, cdate) VALUES ('', '2', '172.30.20.1', '1', '1579183750.OC4uK2', NOW())

The referral is linked in the following cases:

• in case of transition to the showcase page;
• 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

1. 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=')

   Comments to a DB record

   id — 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.

2.  Connecting a new client to the referral program:

   DB table record

   UPDATE account2project SET affiliate='1' WHERE account='3' AND project='1'

   Comments to a DB record

   affiliate — affiliate program that the client uses with the provider;

   account — client code;

   project — provider code.

3. 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'

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:

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:

1. 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'

2. Checks 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

   In 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'

3. Checks 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'

4. Calculates 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

5. If 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')

6. For 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'

7. Creates 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'

   Comments to a DB record

   createdate — 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.

Related topics

• Affiliate program. General information
• Client groups
• Client segments