Apply coupon code and Track Affiliate while shopping

Use case:
There are resellers/other site which generates leads for your ERPNEXT e-commerce website products/items/. When the user comes from other site to erpnext site for purchase, you should have the ability
(a) to know from which affiliate /sales partner he is coming (may be to give them exact commission or to analyse their performance) i.e. referral code
(b)Give user discount (based on Pricing Rule) on overall purchase i.e. Coupon code

Design :

[A] Sales Partner with unique referral code
Each Sales Partner can have unique referral code as below

[B] Coupon Code Doctype
Coupon Code form will look as below ,Coupon Code will be unique . It will be linked with Pricing Rule for discount calculation based on defined conditions.
Coupon Used count will update , when user will place order on shopping portal.

[C] Shopping cart flow
Potential customer will land to the website with URL as below
http://sellcc.erpnext.com/products/chair?sp=speed&cc=SAVE30
system will save the URL parameters in browser cookie , which will expire in 30 mins ( which sounds a good amount of shopping engagement time on the site).

After adding items to the cart, the cart will look as below

The Coupon Code custom field will be added as below to capture & apply the relevant discount to the user, while the sales partner code will remain hidden (though visible below) .
System will fetch the sales partner , mapped to the (unique) referral code, and will set in the Quotation ( Sales Partner – custom field) & Sales Order ( Sales Partner- field).

Shopping cart view

[D] Impact on Quotation and Sales Order when order is placed
At present , whenever potential customer , clicks on “”Add to Cart” button, in background ERPNext prepares Quotation and updates its line items based on various item additions.

On click of the “Place Order” button , Sales Order gets generated with the relevant details.

So the quotation and sales order with sales partner ( Print Hide) and coupon code information will look as below.

Quotation form
Edit : Pricing Rule is made to overwrite Price List / define discount percentage, based on defined criteria.
Discount section is to provide “Additional Discount” - as the section heading suggest, the "Additional Discount Percentage/ Additional Discount Amount (USD) fields will not be updated

Quotation Line Item will carry the discount information based on pricing rule

Sales Order on Shopping Cart Portal will look as below

Sales Order Form will have the Coupon Code Information

Edit : Pricing Rule is made to overwrite Price List / define discount percentage, based on defined criteria. Hence the Discount Precentage & Discount Amount field will not get updated.

Sales Order Item will carry the discount information based on pricing rule

Sales Order form will have the sales partner information

Looking forward for co-operation on getting this feature merged asap.

Since there are no defined module leaders for shopping cart, I would request the reviewer team (, @rmehta,@nabin ) to review it and give us approval on the same.

At the same time we would request to community members to give us suggestion only by keeping in mind the scope defined in the use case,.
If there are features above the scope request you to raise a different PR with reference to this one.
We wish to merge this within 2 weeks!

Below are the links of earlier discussion on same topic
Issue/ PR : [ADD]shopping_cart_coupon_code: feature of applying price rule on eco… by Solufyin · Pull Request #12559 · frappe/erpnext · GitHub
Discuss Post : How to get affiliate link and URL

Edit : After further analysis, in Quotation, SO.SI form .
Pricing Rule is made to overwrite Price List / define discount percentage, based on defined criteria.
Discount section is to provide “Additional Discount” - as the section heading suggest, so "Additional Discount Percentage/ Additional Discount Amount (USD) fields will not get updated through shopping cart .

Quotation Item and Sales Order Item will carry the discount information based on pricing rule.

Hence changed the screenshots of_

  1. Quotation Form
  2. Sales Order Form
  3. Sales Order on Webportal

Added two new screenshots of

  1. Quotation Item
  2. Sales Order Item
3 Likes

Looks okay, but UI for “Apply Referral Code” looks bad.

Also this will have to be contributed to develop branch

2 Likes

Hello, this really is somewhat “obvious” idea, meaning this should really be in the system. :wink:

What comes into my mind is that the ERPNext shopping cart needs an overall drastic development and that means that this type of stuff should be thought in a way that it won’t need to be totally re-designed after the shopping cart module changes otherwise (of course some adjustments are needed in any case).

I would understand @rmehta comment in such way that first this PR must be contributed into the develop branch and then if all ok, tested, it can be brought to the master branch. Let me know Rushabh if this is what you meant.

What comes to the UI suggestion, I do agree that the above sample doesn’t look the best option yet, but it is no problem to make it follow the basic look and feel of ERPNext, so I assume that as a minor thing to fix.

1 Like

Develop to master can be a long route though! At this point there is no ETA

We are done with
[1] Code PR
[2] It has test case. find video of same
[3] You can do hands on. Test server
[4] Documentation PR for
Coupon Code
Sales Partner Referral Code

We are awaiting Reviewer’s approval on PR.

1 Like

We would like to invite if anyone would wish to give a hands-on to the feature.

Basic Steps for customer (end-user) testing

  1. Click on the link
  2. Check the price and add the item to the cart and use the customer login mentioned below
3. View Cart

ShoppinCartApplyCouponCode

  1. Apply Coupon Code and check the discounted price
  2. Click on “Place Order” button to view final order price details

Customer user :
donald@trump.com
hello@2020

Test Server Instance Details

To Test the feature in action
Test Instance URL

Test Data :

Items [URL] Rate Coupon Code Discount Grand Total
1)Golden Ring 750 SAVE5 (5%) 37.50 712.50
2)Chair 500 0 500

Desk User :
desk@user.com
hello@2020

Other Important Data Links

Coupon Code
Pricing Rule
Shopping Cart Settings
Sales Partner

Steps to configure Coupon Code Feature

Ensure Shopping Cart Settings > Apply Coupon Code is enabled.

CCShoppingCartSettings

  1. Create Item
2) Create Pricing Rule for Item in step 1 - (Coupon Code Based = true

PriceRuleCC

3) Create Coupon Code , link it with Pricing Rule of Step 2

CouponCodeDoctype

4) Add Item in Shopping Cart and view cart

ShoppinCartApplyCouponCode

  1. Apply Coupon Code and check the price

This is a critical feature for online shopping. Will this be merged to v12 or V11 branch? Will appreciate a clarification.

2 Likes

@zerodiscount, we have submitted the PR for review. It is for v12 develop branch.
PR - feat: affiliate sales partner referral code and coupon code by ashish-greycube · Pull Request #17117 · frappe/erpnext · GitHub

This critical feature for online shopping is requested and sponsored by @MichaelPinkowski
& @Steve_Simonson
Big thanks! to them for giving back to the community

4 Likes

Hey Jignesh. Thanks for your efforts to introduce this feature. We are in the process of evaluating its use for a marketing campaign and have the following questions:

If a consumer on website enters a unique coupon code that was given to them by a sales partner - how does that code tie back to the sales partner in ERPnext? It seems we have 2 codes - a referral code and a coupon code? I see the option to show on website for sales partner - but it is also asking for a route and sales partner website.

Have not tested the functionality fully yet - but is it not possible that a coupon code be linked directly to a sales partner? For example under each coupon code have the option to select a sales partner associated with it? This would be more efficient - and not require consumers to input 2 separate codes…

Will appreciate your thoughts on the functionality as you envision it. Thanks.

Tracking Sales Partner and Providing discount are 2 independent functionality.

Referral code in Sales Partner is to track sales_partner (URL paramter= sp) and coupon_code(URL paramter=cc) is to provide discount based on coupon code

Help Manual
https://erpnext.com/docs/user/manual/en/selling/sales-partner
https://erpnext.com/docs/user/manual/en/website/coupon-code

Thanks for the clarification. Yes - I agree that it is different. But the way it is setup - the discount code would apply to all consumers online - and independent of whether they enter the sales partner referral code. So a consumer could use the coupon and the sales partner would not get credit for the sales? This would defeat the purpose. Should we not make the coupon code conditional to entering the corresponding sales partner code? Thats what I was trying to link a coupon code to a referral code (in the backend). Or have some logic in the cart to automatically populate the sales partner code based on parsing the coupon code entered? Price list should also be tied to a coupon / referral code - as sales partner may offer separate promotion on their pricing. Appreciate your thoughts.

Sales partner based on referral code and coupon code both are stored in sales order, hence you would be able to track both.

Would request you to do few transaction to understand how shopping cart + this feature works.

@jignesh_shah - has this feature launched fully? I am getting a server exception error when I visit the page for the Sales Partner. The default route is /partners/test-partner (say). I get the error below. In addition, in the shopping cart, I only see the coupon code box, not the referral code box. On a side note - even the coupon code is not working independent of sales partner in my install (v12-develop) - was not working for version-12 either. Will appreciate your thoughts / comments. Is this working for you?

## Uncaught Server Exception

```
Traceback (most recent call last):
  File "/home/frappe/frappe-bench/apps/frappe/frappe/website/render.py", line 48, in render
    data = render_page_by_language(path)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/website/render.py", line 152, in render_page_by_language
    return render_page(path)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/website/render.py", line 168, in render_page
    return build(path)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/website/render.py", line 175, in build
    return build_page(path)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/website/render.py", line 188, in build_page
    context = get_context(path)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/website/context.py", line 28, in get_context
    context = build_context(context)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/website/context.py", line 87, in build_context
    ret = context.doc.get_context(context)
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/setup/doctype/sales_partner/sales_partner.py", line 34, in get_context
    "*", as_dict=True)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py", line 401, in get_value
    order_by, cache=cache)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py", line 445, in get_values
    out = self._get_values_from_table(fields, filters, doctype, as_dict, debug, order_by, update)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py", line 592, in _get_values_from_table
    as_dict=as_dict, debug=debug, update=update)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py", line 156, in sql
    self._cursor.execute(query, values)
  File "/home/frappe/frappe-bench/env/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/home/frappe/frappe-bench/env/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/home/frappe/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/frappe/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/home/frappe/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/home/frappe/frappe-bench/env/lib/python3.6/site-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/home/frappe/frappe-bench/env/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/home/frappe/frappe-bench/env/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1054, "Unknown column 'sales_partner' in 'where clause'")
```
  1. Yes working for me.
  2. route is not developed by us, so don’t know about the error.
  3. referal code is to be passed in URL as parameter e.g. sp=“referralcode”, check documentation.
  4. coupon code works independent of sales partner on version-12 (production).

for any issue raise it on github