A few months ago I went on a mission to find a solution for importing our sales data from MYOB into the community version of SugarCRM. Unfortunately we weren’t able to find one so we went ahead and built one. This post explains how you can use it to keep your sales data in your CRM and get up to date reports on your revenue, numbers of clients and where your customers are coming from all via SugarCRM.
*Note because we have just built this internally and only tested it on our own version of MYOB I would suggest you don’t install this extension unless you are a web developer or you have one handy!**
Download the Extension
The first thing to do is visit the MYOB SugarCRM Sales Import project page and download the extension. There is also quite a bit you need to do to configure SugarCRM to use the extension so what follows is a detailed description.
Configure SugarCRM
I thought I’d also include a basic description here of how we have configured Sugar to make this work.
The way we use Sugar is slightly different to some others. We are using it for solo and small businesses and believe it needs to be configured in a way that makes it simple to follow and easy to administer. The main thing we do is we skip the opportunities module entirely in the sales process – i.e people start out as leads and are managed in the leads area until they become an existing customer or are no longer a legitimate prospect.
When they become a customer we convert them to a contact and an account so at no stage do we use the Opportunities module. (note you can still use this extension and also use the opportunities module).
Instead of using the Opportunities module to house Opportunities we re-name it to “Sales” and use it to house actual sales imported from MYOB. That way its possible to determine who is an actual paid customer (according to MYOB where your real financial data is kept). It also means it is easy to show actual purchases for each of our accounts and draw certain real financial data from Sugar that you wouldn’t have been able to previously draw.
If you still want to use the opportunities module read further down.
There are also a few other limitations with Sugar that need to be worked around for this module to work (unless we just can’t work it out) – one is the fact that the inbuilt lead_source field in the leads module doens’t convert across to accounts and contacts when you convert a lead. This means you can tell where your leads have come from but you can’t tell where your actual customers have come from so we need to create another lead_source field for this purpose. This is really really annoying and hopefully this will be fixed in the future.
Here is the full list of steps you need to do before installing this module.
- Click on Admin / Rename tabs and edit the Opportunities tab to be called ‘Sales’. We will use this to enter all of your MYOB Sales.
- Click on Admin / Studio then expand the leads folder out and click Fields. Create a field called source – it will re-name it to source_c – this needs to be a drop down field and make sure you put the appropriate items in your list that reflect how people may have heard about you (you can use the inbuilt source_dom if you want or create a new one). Then repeat this for the contacts modules and the accounts module so you have the exact same field in all 3.
- Under the leads module click on layouts then in all of the layouts replace the in-built lead_source field with the new one – do the same for the contacts and the accounts module. Make sure you put the source field there not the lead_source field which is the default sugar field (which won’t convert properly when you convert the lead). If there are any other fields that you want to carry across between leads / contacts and accounts you have to do the same thing here.
- Under the Sales (or Opportunities) folder click fields and text field called myob_invoice_number (it will re-name it to myob_invoice_number_c) and another one called payment_status which needs to be a drop down with the display labels “Closed” and “Open”. Then make sure both fields appear in all layouts for sales.
- While in the sales fields click on the sales_stage field and edit the sales_stage_dom drop down so that there is a item in there called Sale Made MYOB Import – you can keep other stages in there as well if you like. This stage will be used to update Sales to when they come in from the Sales import.
- Under the accounts folder click on fields and click the “Account_Type” field then click to Edit the drop down. Make sure it only contains the options of blank, Customer, Prospect and Other. We will update this to Customer if an invoice is imported for them.
I would also turn off the default lead_source field from all views then run through adding a lead (and setting the source) and then converting the lead. On the convert form if you have set up all the fields correctly it should display the fields on there but sometimes it doesn’t. You will also notice the default lead_source will be displayed there for the contact you will have to ignore it (they really need to work on something that can configure what is displayed on this page). Whether they display there or not if they are set up correctly the values should port across successfully when you convert the contact and account.
Install The Extension
Go to Admin / Module loader and browse for the installation files and click upload then once it is displayed down the bottom click Install. Click on the home tab to refresh teh site and you should see a Sales Import tab on your top list of tabs.
To display the dashlet on the home screen click Add Dashlet and click the charts tab then select My Revenues – it won’t display any data until you import a MYOB file.
Use The Extension
After installation you should see a Sales Import tab, click on there and then click Create Sales Import name it after the financial year it is for (i.e. FY 09 10) then browse for the CSV File exported from MYOB and import the file. Once this is done, look in the Accounts area and any business that you have dealt with that was in the file should be in there as an account and they should have their type set to Customer. So you now have the ability to search by actual paid customer vs others.
Click on the Sales tab and you should see every invoice in there. All of the detail from MYOB should have come in including the invoice number field and the payment status field.
Go into your accounts area and make sure all of your accounts have a lead source defined and then back on your home screen on the Dashlet you should see a revenue break down by lead source – if you click on the pencil to edit the Dashlet you can change the date range for this. The Dashlet will also give you a total count of clients based on how many different businesses have had invoices created for them in the period selected.
*Note this system assumes you are accounting on an accrual basis so it will consider invoices regardless of whether or not they are paid**
**Still want to use the opportunities module**
If you do still want to use the Opportunities module for its intended purpose you are free to do so – just don’t rename it to “sales” and make sure you set up the ‘type’ field to have something other than ‘Paid Customer – MYOB Import” as the type so you can differentiate between ‘opportunities’ and ‘sales’.
Further Discussion
We have put a post up on the SugarCRM forums to discuss this extension which you can find here.
