Sales Lead Management - CRM

[red1]

I am now embarking on a new plugin for iDempiere that follows from what Adaxa tried to do here http://sourceforge.net/p/adempiere/disc ... bce9/#677b

sales dashboard screen.png (150.21 KiB) Viewed 30992 times

However after packing in their 2Pack into an old ADempiere361, i noticed that they encroach onto core models such as C_Order.

AdaxaSalesLeadManagement.png (51.14 KiB) Viewed 30992 times

Thus I like to propose an entirely separate plugin without touching core but rich with usability:
1. Dashboard Calendar on double-click can generate ticket for Sales Rep to follow up.
2. CRM Ticket goes through the Sales Funnel process.
3. At right stage, Business Partner record is created automatically without the Sales Rep need to do that manually.
4. The record will also pick up other actions such as credit value, visits, responses. They be stored in a new tab model under BPartner window
5. Sales Order from Proposal thru Quotation will also be automatically generated at the right CRM action.
6. Email reply can be used as direct to CRM model without need to manually return to Dashboard calendar.

[red1]

Done a window model (pack in 2Pack zip from SF), which updates the present Sales Management menu, adding Request linked to the Lead (AD_User) tab. When you click on the front dashboard calendar, it will open a Request ticket preset to Sales Lead type. You can enter your new business contact data as guided in the screen below. C=ClientName, K=SearchValue, T=603-178869-378, A=Address1, A2=Address2, E=mail@gmail.com

CalendarRequest.png (59.8 KiB) Viewed 30919 times

Upon save, as a new record, it will create for you a new Lead record and Request attached at the Sales Lead Dashboard window. This creation is still pending, in a day or two.
Next will be when you are handling the leads, anyone set to 'convert' will also converted to an Opportunity for you. The SQL logic at the Chart for Sales Pipeline (see chart image at the top of this thread) is as follows:

Datasource 1.. SQLFrom: C_Opportunity o
INNER JOIN C_SalesStage s ON (s.C_SalesStage_ID=o.C_SalesStage_ID)
.. SQLWhere: o.IsActive='Y' AND s.IsClosed='N 'and o.SalesRep_ID=101
.. CategoryColumn: s.Value || '. ' || s.Name
.. ValueColumn: sum(o.OpportunityAmt)
.. Table: C_Opportunity
.. KeyColumn: C_Opportunity_ID
>DataSource 2 .. ..ValueColumn sum(o.OpportunityAmt * o.Probability/100)

[red1]

Made an initial release of plugin with embedded 2Pack. Upon install, you can go to the Dashboard calendar and make a Sales Lead pop up and save as in previous post. Then the new lead or prospect AD_User account is created linked to the SalesRep. On zooming into the pop up again sends you to the Lead window with the Request as sub tab.

Zoom2Lead.png (70.29 KiB) Viewed 30909 times

The Sales Rep Dashboard with its Lead tab view also shows the created record.

DashBoardLeadView.png (37.51 KiB) Viewed 30908 times

The user can also login using its email and password is also same email name. I am now working on that BPartner is also created alongside and its credit information can be passed from the Request summary box.
The source is pushed to http://bitbucket.org/red1/org.crm.sales/

[red1]

After the Lead is Converted, it will auto-generate an Opportunity record with Initial Stage, Probability from it, ExpectedClostDate and ExpectedAmt set.

ConvertedSalesLead.png (76.67 KiB) Viewed 30839 times

Then the Dashboard has some magic to show.

DashboardAfterConvertLead.png (70.16 KiB) Viewed 30839 times

Next I will create an entirely new Campaign Process which sends Email blast to Leads, with Import Leads from Excel table, work on the Leads as Activities. This will greatly align with CRM marketing core function.

[red1]

Related items such as Interest Area, Marketing Campaign, MailTemplate and SendMail are moved into the same menu for closer use. So please take note if you been using them elsewhere. They can still be moved back to their original locations. I am following the principle of not touching or avoiding any change in core. So I will write a manual on how they be used. I will make an InfoWindow to connect the right data sets together from their data sets. More automated event triggers will be created later to reduce redundant work.

CRMMenu.png (27.58 KiB) Viewed 30825 times

Essentially, the user conducts campaigns by sending out emails to leads via the SendMail with pre-set MailTemplate to a list of registered Interest users or a chosen BP category. The sent out mails will be listed as a sub tab under MailTemplate. I will make a trigger for the event to also create activity records that are listed under the DashBoard and Lead windows.

The Campaign window keeps a list of opportunities (read-only). There seems no corelation of this window with the rest, so i have to think of a connection.

[red1]

There is a customer window which has a Request sub-tab too. So I forgot to save the Request model to keep the C_BPartner_ID, and now that is solved. When a new CRM ticket is made, you can see the Request under the Customer window. So the Customer window will group all future request tickets. But remember they have the IsProspect flag set to true. When they make their first Order, then I will make a trigger to drop that flag.

Also I made the SalesLead RequestType to be color-coded as green.

RequestColorGreen.png (29.62 KiB) Viewed 30817 times

I made an interim movie here



I also removed the SalesRep_ID = 101 hard code that now works with #AD_User_ID@ in the ChartDataSource setting. Patch is in the 2Pack and also sent to JIRA. Then i tried assigning a Campaign to the Opportunity. Below is how it turns out. Working fine.

TotalExpectedCampaign.png (52.05 KiB) Viewed 30804 times

The Total is the OpportunityAmt. Expected is the Probability factor applied to it. Data test continues.

[red1]

While checking around, I found out that Paul Bowden of Adaxa has done a process to convert Leads. It is called from the process icon in overhead menu bar.

ConvertLeadProcess.png (73.49 KiB) Viewed 30803 times

When clicked, it pops up a dialog box.

ConvertDialog.png (61.55 KiB) Viewed 30803 times

It already has the needed settings, so I have to revert the creation of BPartner record and leave it to this process. Also no need for the Convert event. I tested this and it takes care of them.

ConvertedOK.png (25.5 KiB) Viewed 30803 times

Changes will be committed.

[red1]

Hiep LQ pointed me to http://wiki.idempiere.org/en/NF2.0_CRM which puts the background of how this module got into core. The PDF documentation links there are important reference. I be following them closely. The above latest changes are now uploaded into http://sourceforge.net/projects/red1/files/SalesCRM/

SourceForge.png (101.15 KiB) Viewed 30787 times

Made a new follow up movie to update the audience.


I also made the Sales Stage follows Adaxa reference PDF.

SalesStage.png (82.44 KiB) Viewed 30782 times

You can edit this table but login as SystemAdmin as it is used for all clients.

[red1]

When you made a new Lead, from the Request Calendar on the main Dashboard panel, and you click into it to the Lead Window, you can go to the Tab Activity to begin setting Meeting.

CreateActivity.png (96.23 KiB) Viewed 30757 times

Ensure that the Start and End DateTimes are set in order to let the generated Request appears in the Calendar. Your Description will be prefix with 'Meeting with '+UserName and written to Summary. You can edit further in the resulting Request window.

GeneratedRequest.png (50.24 KiB) Viewed 30757 times

You can click on that and it goes back to the Lead>Request tab showing it as your second request ticket. This tickets can be closed as normal requests will keep you and your supervisor in the loop. Note that i have changed the colors a bit for clearer viewing. You can change colors at the SystemAdmin > RequestType window.

Next I will look at the mail regime, where as emails been sent out during Mail Campaign can be replied and we want those emails tracked, and their canned responses analysed and help the sales team to plan follow ups.

But i also hit on a new idea this morning:
1. What if when you click on the Dashboard Calendar you are able to generate a Sales Order for that Lead on the fly?!
2. This is how it can be done: You are able to choose a RequestType = Sales Order and put in C= or K= to get the Lead record, P=<Product>, Q=<Qty>,$=<negotiatedprice>
and it will find the Lead record, generate a Sales Order (convert the Lead to Opportunity if isn't so!) under the Opportunity window > Orders tab!
The Request record also has an Order link so that will be used to associate with it. Later an InfoWindow can be created, using Aladdin!:
Name:R_Request,AD_User,C_Order,M_InOut,C_Invoice
ColumnSet:d.IsPaid* (* to mean criteria column in Info Window!)
This Info Window will sieve through tickets to see their orders status whether shipped, invoiced or paid at one glance.
Not only that:
3. When a shipment is done and/or an Invoice is finally settled and paid, the Sales Order ticket will then be closed! In this way the company can keep track of leads, meetings and orders until paid for.

[red1]

The Request Sales Order is done! This time, after creating a Sales Lead Request, you can right away issue a Sales Order on the fly, or even later when the Lead is converted into an Opportunity. Below is how you do it. Make sure you select the Type to be Sales Order.

RequestSalesOrder.png (44.47 KiB) Viewed 30752 times

You can even do it with just C=BPName,P=ProductName without Qty which will be defaulted to 1. $=price if included will force a price usually discounted from the actual price. But first make sure your BP Group has a pricelist or else you will hit an error and nothing is saved (which is good as you do not want rubbish saved.)

GroupBPPriceList.png (67.59 KiB) Viewed 30752 times

When you go to your Sales Order window you can see it there.

RequestCreatedOrder.png (132.95 KiB) Viewed 30752 times

I have committed the code and binary for now for anyone to test it. I also made the Request to save the Order ID so you can trace easily. What is special with my work here, is that i avoid touching any core model.

[red1]

Now we can create Sales Order on the Fly! I am now refactoring the code a bit and writing a tutorial. Will commit the code in a while.

Tutorial.png (223.35 KiB) Viewed 30695 times

Tutorial guide and plugin at http://sourceforge.net/projects/red1/files/SalesCRM/
It is so fast that the movie is over in few minutes:

[red1]

Been troubleshooting the wonderful Charts done by Paul Bowden (ADAXA) and having a bit of trouble with the Sales YTD Chart. Referring to http://www.tutorialspoint.com/jfreechar ... _chart.htm
helped me to use as test case and it works in his code. I realised that you need many series of data before it can draw a graph. So I made more completed SOs as the formula for the chart to register is:

Code: Select all

SELECT sum(o.GrandTotal), (C_Order_ID IN (SELECT o.C_Order_ID FROM C_Order o WHERE o.DocStatus IN ('CO','CL') AND o.AD_Client_ID=11 AND o.IsSOTrx='Y' AND 'Current' = 'Current' AND  TRUNC(o.DateOrdered, 'MM')  = TO_DATE('2016-04-01','YYYY-MM-DD') ))

And something showed up.

SalesYTDSeries.png (47.83 KiB) Viewed 30628 times

This also gives me an idea to make the Sales Order's Date Ordered and Date Promised to follow the Request Start and Complete planned dates.

RequestOrderDates.png (34.78 KiB) Viewed 30628 times

Note the dates are copied over. In this way, the user can date the SO according to the Calendar directly.

SalesOrderDates.png (58.39 KiB) Viewed 30628 times

[red1]

You may wonder how to make the BarChart nicer. Well, it is supposed to be StackedBarChart as chosen in the Chart model. So i went into the code and solved it and submitted a patch for acceptance into core: https://idempiere.atlassian.net/browse/IDEMPIERE-3069

CorrectedStackedBarChart.png (53.46 KiB) Viewed 30617 times

The correct one is on the right. It certainly looks sexier. Also note that you can double-click on any bar and it will open up the window showing the exact data window that the bar represents!

[red1]

I have improved the use of Chart via a ChartMaker that automatically generates 3DStackedBar Charts for any model particularly the Sales YTD to show details according to Product or Customer breakdown and even by country, region, UOM or just any criteria. Go read here for more viewtopic.php?f=45&t=1826

Now I be showing how to setup the email communication. Request CRM since Compiere days already has an email processor to send out notifications of Request status. For example here is one that i did with the Sales Order on the fly:

SalesOrderRequestEmail.png (57.28 KiB) Viewed 30253 times

In the following post i will show from the start how to set this up properly step by step. You have to create a test email so in my case i created an email amazingerp@gmail.com. It is advisable not to use your own personal email as the security has to be lowered for gmail to be accessed from a third party such as our ERP. Of course an expert can go in and ensure secured email access, but for now, off the shelf, this is the base setting.

[red1]

After creating your gmail account, you have to enable IMAP in it.

EnableIMAP.png (118.66 KiB) Viewed 30208 times

You should also turn the conversation off so that each email reply does not bunch with the previous and get processing entangled up.

GmailConversationOff.png (82.3 KiB) Viewed 29923 times

That is all for now on gmail, and go to your iDempiere client to setup Client email:

ClientEmailSetup.png (66.12 KiB) Viewed 30208 times

If you get it all correct, pressing the Test button will give:

ClientEmailTest.png (33.86 KiB) Viewed 30208 times

Next we enable the Request Processor engine:

RequestProcessorTurnOn.png (47.96 KiB) Viewed 30208 times

You will see that it is inactive, so just check the Active box. Now you can go and make a Sales Order on the fly or any other Request Type. You will get an email instantly:

SalesOrderRequestEmail.png (84.04 KiB) Viewed 30208 times

You can receive updates in your email as set in the Request Processor to every 15 minutes. You can change the timing. However the Request Processor engine only kicks in after a restart. So do that and go to the root host page of iDempiere http://localhost:8080/:

RootHostPage.png (191.04 KiB) Viewed 30208 times

Click on the toolbox icon on the top right to open up the Server Monitor:

ServerMonitor.png (84.39 KiB) Viewed 30208 times

You can see among the processors running is the Request processor. Note that this depends on your tickets settings under Request Type rules. That is it! Next I will show how to setup the email from the Setup panel and System side so that you receive notification each time someones steals your server for a spin! In the end I be tackling how to import emails and process them. That is part of the new enhancement to the SalesLead CRM work I am doing under sponsorship by SYSNOVA.

[red1]

Now to setup server-side email service, is done during /setup.sh or install.app from Eclipse. You can run this many times and does not affect present system nor database. It will do a test and save the email credentials in properties file. But first there is a bug, which i found and asking Hiep LQ who then solved it here https://idempiere.atlassian.net/browse/IDEMPIERE-3100. So if this patch is not applied by Carlos Ruiz in core, you have to apply it manually in Eclipse first.

SetupEmailProperties.png (54.33 KiB) Viewed 30163 times

This will store your email password too in the idempiereEnv.properties. So take note and hide it in a secured location. You will get this email in your mail box:

ServerTestMailNotice.png (145.43 KiB) Viewed 30160 times

. I am not sure if this setup is needed as the client setup before this seems sufficient to move the Request Processor and the following step is to setup the same on the System Client:

SystemClientEmailSetup.png (92.19 KiB) Viewed 30160 times

After this is done, when you restart your server you should get a notification. Here is my mail box with the 2 notices - 1 for the System Client email setup test and the idempiere-server startup:

2EmailsSystem.png (24.09 KiB) Viewed 30160 times

This is kinda groovy as it gives you proper time-stamp official notice that your server was started.

[red1]

Now we run the Request Email Processor which reads from a mailbox to import into the Request table. In my amazingerp@gmail.com i filter email that contains a key word, 'SalesLeadCRM' to be sent to a folder labelled 'request'. I also created a 'processed' folder for this purpose.

RequestEmailProcessorSetting.png (33.8 KiB) Viewed 30103 times

The EmailSvr hard code will set imap.gmail.com to port 993, so you can use just without the port. However if you are using another email server with different port then the :<port> will not be set until this patch is applied https://idempiere.atlassian.net/browse/IDEMPIERE-3106.

RequestEmailProcessSuccess.png (19.66 KiB) Viewed 30103 times

SucessRequestEmailLog.png (73.33 KiB) Viewed 30103 times

NewRequestFromGmail.png (78.93 KiB) Viewed 30103 times

GmailUpdated.png (71.33 KiB) Viewed 30103 times

This is the raw off the shelf handling as done in the code by Carlos Ruiz and Hiep LQ before this. I am now studying the code to see how to improve it further. At the moment the code checks for same Message-Id to cluster together as a same request. I want to make the Requests to synch via its DocumentNo/Message-ID so that they are all gathered as updates to the same customer ticket. That will be convenient for the Sales Rep to check the email conversation sent by the marketing campaign and the reply from the prospect/customers.

[red1]

Now that is solved! When an incoming email is processed, it will take its LastResult as a new RequestUpdate so that you can see all updates in a single Request instead of ignored. Not only that, I have made the anonymous Request to take the email address's User details, so that under the Lead (User) window you can see the new Request added to it. In this way, the SalesRep can really keep track of all communications with the prospect in a single window. This basically concludes our marvelous improvement to Adaxa's Sales Lead CRM.

EmailUpdatedRequest.png (80.25 KiB) Viewed 29960 times

Here we see the latest record of the 4 updates received under the same email Request.

EmailRequest2Lead.png (64 KiB) Viewed 29960 times

The new Request attached to the Lead window. However the display is not helpful. In hindsight, I should include a DocumentNo and LastResult fields also. Will adjust that and update the 2Pack later. For now, i only pushed the latest code changes so that the above can be replicated.

[red1]

The CRM experience should be more exciting now with the fuller email integration. LastResult with Document IDs displayed.

LeadRequestEmailChanged.png (88.05 KiB) Viewed 29939 times

All source committed to http://bitbucket.org/red1/org.crm.sales, binary with 2Pack and updated tutorial to http://sourceforge.net/projects/red1/files/SalesCRM/

[red1]

I made a POC of another Killer Concept of E2E or Email to ERP, where a user can rely entirely on Email and that means been mobile, sending an email to the ERP and it responds obediently!

Here i sent a coded message (starts with #) without going into a desktop and login directly into iDempiere. The email is fetched by iDempiere remotely too with asynchronous advantage. Meaning iDempiere need not be on the cloud or hooked via any web services. Just plain old but killer email.

EmailSent.png (20.31 KiB) Viewed 25498 times

With the Request EMail Processor (setup to run on Scheduler) and GardenWorld Request Processor running in the background, where i setup to 10 minutes timer, i got a reply back on my mobile email, within 10 minutes to mean mission accomplished!

EmailReceived.png (51.97 KiB) Viewed 25498 times

When you check on your iDempiere desktop, you will also see a confirmation in a highly visible manner.

OrderCreated.png (40.06 KiB) Viewed 25498 times

The possiblities are endless. A user can send coded messages to the ERP such as requesting for shipment status. The sender email when registered as a user in iDempiere will be tagged to receive replies.