Report on sales by lead source

[websitedesigner]

Hi guys, I am trying to work out a way to do something that I think is fundamental to managing customers which is running a report on how much money we are making from the different sources of leads. i.e. what is our revenue from clients referred by word or mouth, vs from Google etc.

We use MYOB for accounting like a lot of Australian small businesses and I have no intention of entering my sales into 2 systems. I have put a few comments on the posts relating to MYOB integration but are there any other options? Can I somehow import sales into Sugar once every few months or something?

There must be ways people are managing this? I would appreciate any advice.

Thanks

Dan

[heta]

hi,

you can create import script and set it in cron job to run the script after regular interval.

[datasponge]

We had a very long evaluation / testing cycle for SugarCRM. So I wrote sql scripts to extract data from our old CRM system and import it into Sugar every night. It ran on its own overnight, then we could play with current data while we evaluated Sugar and developed customizations for the features we needed.

Sugar also has good import functionality, so your best option is to try to use Sugar import first. That will save a lot of work mapping fields and figuring out relationships tables and let Sugar handle all the internal actions for you.

You can do it either way.

But you first have to get the data out of MYOB. I know nothing about this application. Does it save data in the same database? Is it MySQL? Do you want to save opportunities only or do you need to save new accounts as well?

It also depends on how you plan to use each system and what data you will routinely enter into each and what data has to be common across the two systems (like account names, addresses, phone numbers, etc). This can quickly become a big mess because of data that is common to both systems.

Phil

[websitedesigner]

Well the thing is MYOB is a Windows application. It doesn't use a web-based database system. If it did, I would be able to do as you say however the other issue is where do I actually import the data into Sugar? The data from MYOB is actual sales - its not opportunities. Does Sugar have the capacity to store actual sales data for each customer? As far as I can work out, Sugar doesn't even have the ability to store actual customers right? It has accounts, however accounts are created when you convert the lead to an opportunity, there are lots of accounts in there but they don't all become paying customers so there is no actual list of paying customers right?

Thanks for your replies.

[datasponge]

Hi websitedesigner,

We track customers in several different ways:

1. As an 'Account Type'
2. As an opportunity with 'Sales Stage' of 'Closed Won'
3. In a custom Products module

1 lets us select all Accounts that are customers with a query
2 lets us see exactly what opportunities were closed as a Sale and relate lead source to sales income including the ability to do the reporting you are looking for. There are charts defined in the dashboard already to do basically what you are looking for, so it is a logical place to start.
3 lets us track exactly what was sold and delivered for fulfillment and support purposes

So for your purpose, you could import each sale from MYOB as an opportunity with status 'Closed Won'. If you're not already using the Opportunities module, this is fairly easy because you can create an opportunity for each sale from MYOB when you import. If you ARE using opportunities, you'll have to figure out how to relate the MYOB entry to an existing Opportunity. You can add a custom field to Opportunities to hold the MYOB id field and import it as well and use it along with dates to synchronize/relate entries between the two in case you want to do other reporting/relating later.

You'll just need to figure out how to export from MYOB for the date range you want to import. If you only need it monthly, then at the end of the month export all the sales from MYOB to a CSV file, then import from the CSV to SugarCRM. If you want it daily you can do that as well, but if you're doing it manually, that involves more effort, so your call how often to import.

If MYOB has features that can automate the export, then you can automate the entire process by automatically exporting using Windows scheduler, then automatically importing using a cron job.

Phil

[websitedesigner]

Hi Phil, thankyou very much for your detailed response. I certainly have a few things to think about. However I can see 1 problem with what you are suggesting. My basic problem is we have quite a few leads that come through our websites (which are tracked as leads, and converted to opportunities and accounts etc). However we also have a big part of our business that doesn't go through this sales cycle (hosting for example - every quarter we invoice a stack of clients - none of this goes into sugar, it would be over the top to enter over 100 sales each quarter - of course unless we could do it by an import or automate it).

The issue I can see with what you are suggesting is some of the people we invoice in MYOB will already be in sugar - because they have come through as leads from the website??

I'm also aware there is the ability to add lead source directly into MYOB using a custom list which would mean we could get MYOB to do the reporting and not Sugar however this will involve manually re-entering the lead source for a few hundred clients and any new ones.

[websitedesigner]

Thinking about this a bit more. I guess one option would be not to use the opportunities module as we are currently using it and just user Sugar to track leads and once they become opportunities just manage them offline but I think I would like to use as much of sugar as we could.

[datasponge]

It's very possible to fully automate. As I said, we imported data from our old CRM, which is a Windows app with its own database. It has export tools that can be run in batch mode, so I have a batch file that extracts the data and creates a dbf file, then launches a Microsoft Access database file that writes to MySQL tables through ODBC.

Not trivial, but not especially hard either.

The only real difficulty you have is matching your MYOB records to existing opportunities and accounts or leads. But if you do a good job standardizing the company names, that will catch 95% of the matches.

You could also create a custom module patterned after opportunities specifically to hold MYOB "opportunities". Or you could create a special opportunity type for MYOB "opporrtunities" and just treat them differently.

If you pre-process the import file in Access, you can match in a series of steps against the account name, against an MYOB ID already imported to Sugar or whatever you like and get the error rate down to a very reasonable level.

Phil

[websitedesigner]

Cool ok I think I'm getting somewhere so I have the lead source (how the customer heard about us) captured in the leads form, it comes across into the opportunity and the account when we convert it. I've got a status in the opportunity called Closed - Won so I can retain the ability to report on opportunities that are won or lost from different campaigns then I have another status in the opportunity called 'Actual Sale - Imported from MYOB' so all actual sales from MYOB will be captured as opportunities with the status 'Actual Sale - Imported from MYOB' and they will be linked to companies so I can then write a report that will report on actual sales by lead source. Ok cool this gives me something to go on, I'll keep people posted with progress. Thanks again

[websitedesigner]

Hi guys, I thought I would update this post quickly to mention that Ken Wood from IT On Tap has been very friendly helping me out with this. He has written an eBook on this very issue and he has a hosted solution that he provides to clients that integrates Sugar with MYOB, Joomla, Magento, Wordpress and his own eMarketing platform. His solution works with The professional version of Sugar and requires that you have your systems hosted with them.

For us I am still going to look at doing this import because I want a solution that works with the Community version and probably don't need anything as sophisticated as what Ken is offering but its worth a look and the eBook is worth reading.