Tracking sales associates referrals

  • Posts: 332
  • Thank you received: 6
  • Hikaserial Subscription Hikashop Business
5 years 10 months ago #292799

Jerome,

I have a client that wants to provide coupons (or discounts) to different sale associates that they can give to prospects. If a prospects uses the coupon (or discount), they want to know how many that sales associate referred for each day.

As neither the hikashop_order_product_coupon table nor the hikashop_discount table have any ability to track this information, I was thinking that the best way accomplish this is to use the hikashop_order table as it has the order_discount_code field which is linked to the hikashop_discount table. As the client wants to assign a fixed unique 'code' for each sales associates, I could just use the discount_id field n the hikashop_discount table to store this unique 'code' for each sales associate and it would be in each hikashop_order record when an order is created. Then I could simply do a SQL command to retrieve order records from the hikashop_order table by the discount_id and the order_invoice_created fields to provide the client with the information via an export CSV format which I could run on a daily cron job and automatically email to the client's email address.

Is my logic sound?

Please Log in or Create an account to join the conversation.

  • Posts: 81361
  • Thank you received: 13037
  • MODERATOR
5 years 10 months ago #292801

hi,

When you create/edit a coupon and you have the affiliate system activated in HikaShop, you have that option available: Force affiliation to.
It allows you to force the order to a specific partner (and potentially calculate a commission for that with the affiliate system).
So you already have the order_partner_id column to track the orders origin.
And the partners can track "their" orders via the affiliates area on their user control panel on the frontend.
If that's enough for you, that would avoid coding, etc.

Please Log in or Create an account to join the conversation.

  • Posts: 332
  • Thank you received: 6
  • Hikaserial Subscription Hikashop Business
5 years 7 months ago #296306

Nicolas,

I do agree that all of the information my client needs is in the order table. Then I can just setup a cron job to generate a daily data dump of the table into a Excel CSV formatted file and email the file to my client. From that point on, she can import and view her sales with what type of discount was used (these are using discount code names to match up with individuals).

However, I have one hiccup in providing her the information. Date formatting.

When I view the order in Hikashop, it shows a date "2017-04-10 18:56". However, in the MySQL table it shows "1491872184". If I export the table to 'any' Excel importable format, that is the value that is imported into Excel. I have been unable to find a way to convert "1491872184" to "2017-04-10 18:56" in Excel. If you know how I can get this converted it would resolve a lot of issues.

Please Log in or Create an account to join the conversation.

  • Posts: 81361
  • Thank you received: 13037
  • MODERATOR
5 years 7 months ago #296315

Please Log in or Create an account to join the conversation.

  • Posts: 332
  • Thank you received: 6
  • Hikaserial Subscription Hikashop Business
5 years 7 months ago #296342

Nicolas,

Well, I assume the date is stored in that value because of the way MySQL works. I wish it was stored as "MM-DD-YYYY HH:MM:SS". :(

However, the formula to change it to a readable value is =(((A1/60)/60)/24)+DATE(1970,1,1).

1491872184 = Tuesday, April 11, 2017

Please Log in or Create an account to join the conversation.

  • Posts: 25994
  • Thank you received: 4004
  • MODERATOR
5 years 7 months ago #296347

Hello,

HikaShop works using Timestamps.
You can use Mysql functions to convert the timestamp into a MySQL date and manage in the same time, the timezone.

Regards,


Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.

Please Log in or Create an account to join the conversation.

Time to create page: 0.063 seconds
Powered by Kunena Forum