Data, as much as it is a lifeblood of our business, can also be Achilles Heel for various reasons, sometimes reasons best known to none! If you are in the Business Data Integration stream, the complexity only multiplies exponentially. When you are selling an integration product, you are by default understood as flawless integrator of data regardless of any factors. But we are not living in an ideal world are we?

More often than not the data received and described, is never as accurate, never as complete or never as consistent as it is required to be. The result of this would be data flow not being as smooth as intended. When the result isn’t the best the actions and conclusions based on it will not be the best as well, eventually leading to a dissatisfied customer. Some of the common issues encountered as a result of poor quality of data,

• Data duplication – The key reason for this to happen is that systems do not have proper keys (primary or external ids)  i.e. the keys that tie applications.
• Application Crashes – This could happen for many reasons ranging from machine or servers to amount of data transfered.
• Half data being moved – reasons include limitation of data transfered or system governance limits to errors in mapping or data quality or validations
• Performance lag – reasons could be in-adequate resources applied to the integration software to network bandwidth allocated to the integration.
• No synchronization – reasons include in-correct credentials, data mapping errors or data validation issues.

As with any product, with evolving times, comes in more automation targeting performance enhancements and more sophistication, meaning lesser room to ensure data quality. Integration process without accurate, consistent data is as good as integration without the data and eventually business without customers. So it is absolutely necessary to strike the right balance between the two.

Over a period of time we have followed best practices to ensure a smooth integration as any amount of data profiling, data management, data cleansing can ensure at best what can be described as a half solution and not a complete one. Hence we believe in incorporating Customer intelligence into the product and this is being done by gathering key facts about the customer, their nature of business and their data usage and management, a step that not only gives an edge over competition but more importantly also ensures a step ahead in having a satisfied customer.

Do you know how your application is used by your Customers?
Can you predict if a prospect will make a purchase?
Can you identify if your Customer will renew your subscription?

These are some of the common challenges facing every organization. So what is Customer Intelligence?

Customer Intelligence (CI) is the process of gathering and analyzing information regarding customers; their details and their activities, in order to build deeper and more effective customer relationships and improve strategic decision making.

Customer Intelligence is a key component of effective Customer Relationship Management (CRM) such as salesforce.com or Microsoft CRM, and when effectively implemented it is a rich source of insight into the behavior and experience of a company’s customer base.

Customer Intelligence begins with reference data – basic key facts about the customer and their interaction with your company and / or applications. By mining this data, and placing it in context with wider information about competitors, conditions in the industry, and general trends, information can be obtained about customers’ existing and future needs, how they reach decisions, and predictions made about their future behavior.

This data is then supplemented with transactional data – reports of customer activity. This can be commercial information (for example purchase history from sales and order processing), interactions from service contacts over the phone, e-mail, web visits to tracking use of your application. A further subjective dimension can be added, in the form of customer satisfaction surveys or agent data.

This and in my coming series of articles we will address some of the ways to develop strategies and architect solutions and analytics to help you develop a better intelligence set.

Some of the ways to build your customer intelligence

• Tracking Application usage
• Tracking web site activity
• Tracking shopping cart activity and understanding lost baskets
• Integrating Accounting with CRM to track payment patterns and Account Receivables
• Social Media integration and mining
• Predicting Renewals for subscriptions

Developing a strategy for building a Customer Intelligence platform –

Building Customer Intelligence using DBSync

Any Customer Intelligence platform should have the following

• Define: What is that you want from the information and what you plan to do with it
• Model: Define the algorithm that would best define your measures. Measures are the input parameters that you need to have to effectively score and segment your customer. A way to think about it is
  y = f(x1,x2,x3)
  Many times while defining the model we introduce measures which could enhancement and improve your model, but would be difficult to capture given the existing technology and capabilities of the organization. As part of designing CI, efforts should be made to check feasibility of implementing the model.
• Capture: Capture information from your data sources. These could be either from transactional databases or your data warehouse. In general you would have the following setup –
  • Data extraction from applications and datasources into a data ware house
    Example: Extracting web activity from your web application into a data warehouse which could have online customer tracking cookie, IP, referring source, visiting pages, time of visit, length of visit.
  • Aggregating and Summarizing information in data warehouse
    Example: Summarizing data extracted from accounting to track payment patterns and frequency, Credit status or from CRM to look at number of opened cases in last six months.
  • Validate: Run your data captured and summarized to the model developed. Check if your model does provide adequate scoring and segmentation that is required to effectively make decision. A good practice is to segment your score into color codes to define levels of Customer Intelligence Level that could help you quickly say if for example a new Lead is a qualified prospect or an existing customer relationship is going sour.
  • Integrate with CRM: Now that you have valuable information with your model to represent your customer intelligence, you need to integrate and make it easily accessible to your Sales and Marketing team. The best way is to seamlessly integrate with your CRM application so that your marketing team can use your CRM customer or prospect database and Customer Intelligence data along with CRM inbuilt analytics to continue to track and nurture the relationship with your customer.

• • Value— clearly identify information of value.
  • Context— clearly identify the context in which the data was gathered or processed. For instance, an increase in umbrella sales may be due to an increase in local precipitation rather than a fashion trend.
  • Granularity of identity— clearly distinguish and associate between data instances. For example, information surrounding the attributes of customer A may not apply to customer B.
  • Action—The results of analytics should point to a course of action.

Use Case

At DBSync we have a comprehensive customer tracking system built in to track customer usage and satisfaction to build our Customer Intelligence.

We use salesforce.com as our CRM application. Amongst the various models that we track, one is on predicting customer renewals based on usage of our application.

Define: Track and monitor Customer use of DBSync to predict renewals and assist customers continue increase use of the application. Our goal is the have each customer at 7 or higher.

Model: Our model y = f(x1,x2,x3,x4) can be best described as

y = A value between 1 (low usage) to 10 (high usage)
x1 =  Records processed in last 1 week
x2 =  Records processed in last 1 months
x3 =  Records processed in last 6 months
x4 = Date of Customer Acquisition
f(…) = A mathematical weighted model to score the customer.

Capture: Our Extract, Transform and Load for executing this model is as follows –

1. We use DBSync application itself to extract data from our tracking database into our data warehours
2. Use DBSync to execute data warehouse processes to build the data mart for summarization and aggregation.
3. Once our data warehouse is ready we use DBSync to run our Model y =f(x1,..) and populate Salesforce.com customer records.

Integration with CRM: Once information is populated in Salesforce.com, we have reports and dashboards for customer usage analytics. These reports are scheduled to be emailed out every week to the Sales and Management team to track and assist our customers.

Articles and links that would be helpful

Learn More about DBSync

Tutorial on how to use DBSync with database

Register for Free Trial

Need to perform exact schema and data replication to your database? DBSync for Data Synchronizer is the right tool for you.

Why do you need it?

• To build a data warehouse or create complex reports using BI tools
• To perform integration with other inhouse applications like Customer Master databases, ERP or other databases
• To perform offline data scrubbing and migration before final migration to Salesforce.com.

How does it work?

DBSync is a simple application which understands data structures in Salesforce.com and your database (any JDBC complaint database like MySQL, SQLServer, Oracle, DB2 or PostGresSQL) and can auto-create both schema and data in your database.

DBSync provides 3 ways for driving data to and from a database -

1. Using a web interface to define your integration “Profiles” end points, and select the Salesforce.com objects to synch-up.
2. Batch interface – once you have setup your profiles, you can use pre-configured batch scripts (for Windows or Linux) to initiate data and schema transfer from Salesforce.com to database or data transfer from database to Salesforce.com.
3. Real-time integration from Salesforce.com to database – DBSync leverages Salesforce.com outbound messages to intercept and interpret inbound message and then route it to the target database.

The initial setup and install time is pretty quick. A person familiar with databases should be able to install and setup in less than an hours time.

More information can be found at DBSync Synchronizer page

Sales and Inventory forecasting is a limitation for QuickBooks … Lokad to the rescue…

DBSync for Lokad - Sales forcasting made easy

DBSync has partnered with Lokad to provide an amazing sales forecasting solution. In about 20 miniutes, you can extract years of sales data from your QuickBooks directly into Lokad’s Salescast applicaiton and get your forecast.

• Manage inventory by understanding your safety stock levels and trends
• Identify trends in sales, inventory and more
• Reduce cost by reducing capital stuck up in your inventory

Our solution now can un-lock valuable QuickBooks data and provide actionable business intelligence.

To learn more, check out our Lokad and QuickBooks integration offerings. Call us to see all the amazing things we can get out of your accounting data.

Read more at Lokad Blog to see how to setup and get started.

The key problem one of the Radiology – healthcare providers was facing was visibility to their referral information. They were using Net.Orange, and was proving to be quite expensive.

To run their day to day operations, they were using Amicas a leading EMR/Radiology Information System, which managed patient scheduling, physician information and all details other than the billing information, which was managed by another system. While it contained all key data from referral standpoint, they were unable to understand and related referral activities to that of physician liaison and outreach activities.

We used Healthcare CRM: Physician Relationship and Referral Management module built on force.com and were able to quickly setup the sales and marketing end of it. The key still remained to extract information out of the EMR and scheduling system. We had couple of options

Options:

1. Leverage HL7 – HL7 is an event driven protocol and two transactions namely ADT (Admit – discharge) and SIU (Scheduling) was relevant in this case. The issue was that not elements were passed through with the transaction – like complete referring physician information or diagnosis codes and we were not able to extract historical data as it was needed for forecasting future referral.
2. Perform an export in flat-files and manually import it. While it sounded like was in-expensive way to do it, it was quite expensive in the long run. Also, we had seen a drop in usage as folks would forget to perform import.
3. Automate data extract and import of data using DBSync. We were able to tap into the SQL Server and construct views for each of the data sets required for our analysis. We then scheduled data extracts every 15 minutes with the built in scheduler and push to force.com.

Analytics:

While Force.com has built in reports, dashboards and workflows, it does lack some statistical calculations like variance and drop offs. We added real time calculations on a number of statistical calculations required for analysis using APEX – an on-demand programming language for force.com. These routines would run at either real-time to process referral statistics as it came through the integration or run every day at a scheduled interval.

The end result was an on-demand data warehousing and analytical tool to provide clear real-time reports and dashboards, along with workflows to automate notifications of referral drop offs by physicians or new physician referrals.

For more detail check out http://www.avankia.com/healthcarecrm_prm

There are an ever growing number of companies offering EDI + API that stirred up some thought:

Having worked with GXS, one of the leading EDI VAN services, I felt I should provide my readers with some background – EDI or Electronic Data Interchange is a B2B protocol used by major business to automate their supply chain. These standards pre-date the WebServices days and were designed to be compact messages designed to save bandwidth and are governed by standard committees (ANSI, EDIFACT or TRADACOM).  These  B2B documents represented an invoice, acceptance notification and many other pre-defined forms.

EDI offers major savings over large corporations like Walmart or JC Penney who wanted to streamline their businesses and normally have large partner networks.  Standardizing and integrating businesses between Hub (the Company) and Spokes (all the partner suppliers) is a win-win for everyone and ends up being a cost to Spokes to choose to do business with the main Hub.

The Spokes are smaller firms. They choose EDI because the Hub asked to do so. Will they internally adopt EDI?  Maybe / maybe not.  As most firms, internal applications are integrated or can be connected by building bridges between applications.  In today’s world, they will very likely invest in integration tools which connect directly to each other.  To integrate with the Hub, they would go with an EDI VAN service like GXS, buy a tool to map and transform their internal representation of Invoice or other documents and send it through to them as EDI, using a prebuilt transfer protocol like FTP/S, EDI*Express or others.

Now let’s look at an Integration provider building an offering in API+EDI. It would make sense to me if the Integration Provider -

• Had a trading partner hub so that it made sense for Spokes to join in – or if they don’t have an offering of a hub,
• Offered an integration platform for Spokes to transform their documents in EDI and push to the network – seamlessly.
• Sold to the Hub an offering to replace EDI VAN services, and move to become a front-end to them for receiving EDI.  In this case, the Integration Provider should have a good strategy to upgrade the connector software for all Spokes.

So what I am getting to is that a firm will have difficulty making headway by only providing a way to transport EDI over an API without providing a value proposition to either the Hub or the spoke.  In this case, perhaps I am missing something.

I feel a better strategy we be to provide a built-in integration with Accounting or ERP packages to EDI.  This will certainly address the major concerns for the Spokes and increase traffic to the EDI Trading Grid / Network or connect with the Hub directly.  This is why a solution like DBSync makes the solution complete, as it connects to the accounting systems, provides mapping capability, and transfers to Hub in the form they need.

Please do jump in with your views…

I am often asked as to what are the relevant reports or metrics we need to track while integrating between Order-to-Cash process. Here is a list of reports that you will find useful while thinking through your business integration needs – mainly between CRM or eCommerce applications like Salesforce.com and Accounting systems like QuickBooks, Intacct or others.

Performance measures for assessment the overall order-to-cash process, order entry-billing, accounts receivable, credit and collections, and inventory accounting.

Order-to-cash

• Total order-to-cash process cost as a percentage of revenue
• Total order-to-cash process cost per order-to-cash FTE
• Days sales outstanding (DSO)
• Operating cycle
• Percentage of order-to-cash key controls that are automated

Order entry – billing

• Total order entry – billing cost as a percentage of revenue
• Total order entry – billing cost per order entry – billing FTE
• Total order entry – billing cost per customer invoice
• Order processing cycle time
• Order-to-fulfillment cycle time
• Order-to-bill cycle time
• Number of days between shipment or service and billing
• Order-to-cash cycle time
• Number of customer invoices per order entry – billing FTE
• Order entry – billing span of control
• Percentage of order entry – billing FTEs in shared services
• Number of separate order entry locations
• Number of separate billing locations
• Number of separate instances of order entry systems
• Number of separate instances of billing systems
• Number of separate customer master files
• Percentage of customers on standard terms

Accounts receivable

• Total accounts receivable subprocess cost as a percentage of revenue
• Total accounts receivable subprocess cost per accounts receivable FTE
• Total accounts receivable subprocess cost per receipt processed
• Total accounts receivable subprocess cost per customer invoice processed
• Average days unapplied cash
• Percentage of customer receipts received electronically
• Methods used for collecting receipts
• Number of receipts per accounts receivable FTE
• Number of customer invoices per receipt
• Percentage of errors in posting receipts
• Average number of days from customer-initiated deduction to resolution
• Accounts receivable span of control
• Percentage of accounts receivable FTEs in shared services
• Number of separate accounts receivable locations
• Number of separate instances of accounts receivable applications

Credit and collections

• Total credit and collections subprocess cost as a percentage of revenue
• Total credit and collections subprocess cost per credit and collections FTE
• Total credit and collections subprocess cost per past-due account
• Total credit and collections subprocess cost per account placed for third-party collection
• Bad-debt expense as a percentage of revenue
• Average days of past-due accounts
• Percentage of invoices paid within bill period- value
• Percentage of invoices paid within bill period- volume
• Average number of days until an invoice would be considered past due
• Average number of days until an invoice would be placed for third-party collection
• Average number of days until an invoice would be written off
• Credit and collections span of control
• Percentage of credit and collections FTEs in shared services
• Number of separate credit and collections locations
• Number of separate instances of credit and collections applications

Inventory accounting

• Total inventory accounting cost as a percentage of revenue
• Obsolescence write-offs as a percentage of revenue
• Annual inventory turnover
• Days inventory on hand (DIO)
• Days to compile year-end inventory
• Number of separate inventory accounting locations

While Quickbooks is a boon to small and medium businesses, you hit a wall as you grow to what all you can do with it. This is a list of few points that companies face and require either integration, data extracts or even a reason to upgrade to a more sophisticated accounting package.

1. Requirement for segregation of duties and stronger financial controls
2. Need for improved visibility, actionable data, robust reporting and analysis
3. Limited Chart of Accounts hinders necessary reporting formats
4. Seeking stronger scalability, availability, security and auditability
5. Need to improve business efficiency via automation
6. Need to systematize business processes
7. Requirement to manage multiple currencies and/or multiple business units, consolidated financials
8. Requirement to integrate with other key systems
9. Concurrent / distributed use by more users
10. Concern for performance, extensibility, scalability and reliability
11. Extensive reliance on Excel spreadsheets to prepare financial statements & overcome other shortcomings of QuickBooks