We are an agency that offers a marketing automation platform called SharpSpring to our clients. SharpSpring acts as a hub to match marketing campaigns to sales activities.
Leads are created 4 ways:
1) phone calls (from a tracking number on the website) (using SharpSpring's tracking number system)
2) phone calls (from AdWords click-to-call) (using Twilio twml applet)
3) form submissions from the website and
4) LiveChat sessions.
In a normal function, leads are converted to opportunities, then converted to sales (with $ amount of the sales transactions). This enables us to match marketing expenditures to sales. The client must do this manually, but often does not (forgets).
Our main problem is the client maintains a separate system (legacy CRM) that controls booking of appointments, dispatch of service delivery, and customer contain information + job price. Converting deals in the marketing automation/CRM system is double entry in a system that is not mission critical. So it often does not get done.
There is NO LINKAGE from the marketing automation (CRM) and the legacy CRM.
We would like to find a way to try and get more opportunities converted with minimal input from the client:
1) Opportunities are created from leads that were originated by our marketing programs.
2) Opportunities that resulted in booked business would automatically have their status changed to "Closed; won".
At the end of the month, the client will prepare an export from the legacy CRM to a Google Doc spreadsheet containing about 200 rows of information. Each row represents one sale during the month. Each row has about 8 cells that may include a phone number and one cell that has the price paid.
We want to programmatically search for ANY phone number that appears in that row among any leads/opportunities in SharpSpring.
If there is a positive match on a lead, we want to create a new opportunity, and append the campaign source and the price paid. The opportunity would then be marked "closed; won". Other mandatory fields could be synthesized by a concatenation of available or null fields.
If there is already a lead or contact associated with an existing opportunity, we want to mark the opportunity as "closed; won" and append the $ paid.