Connecting MySQL to Excel using ODBC Connector

We use vTiger CRM for capturing leads and general workflow management at Pristine. vTiger is by far one of the most feature rich and economical CRM solutions that are available in the market today. It lacks one thing though, a strong Reporting backbone, especially for the marketing department. So if you are running multiple campaigns across different mediums and capturing all these leads in vTiger using Webforms, then there are very few useful reports to find things such as Return on Investment (ROI) of all my marketing campaigns, or to find which campaign is working out on a day to day basis.

This is where we generally use Excel! I had written about one such report that we use to evaluate our daily and weekly campaign performance using Subtotals in Excel. You can of course pull data from vTiger in the form of a .CSV file and then run reports on it. However, if you have a local replica of the MySQL database of your CRM, then you can easily connect MySQL to Excel using Data Connections and MySQL ODBC Connector. Here are the steps on how you go about it –

  1. Obtain the MySQL ODBC Connector setup, select the appropriate version for you and install it
  2. Add an ODBC Driver – You can find this in Control Panel > Administrative Tools > Data Sources (ODBC). Here you will find all the existing ODBC Drivers listed, if you do not see any MySQL drivers listed, click on the Add button on the right hand side. In this list you will find the MySQL ODBC Driver that you had installed from Step 1

    Add ODBC Driver
    Add ODBC Driver
  3. On selecting the MySQL driver, you will be prompted to enter the database details that you wish to connect to. These are your MySQL Database details that you are maintaining on your local network (Pro Tip: Check for connectivity from your machine to the MySQL Server first! I have seen many a programmer waste endless hours trying to connect in vain.)

    Fill MySQL Details
    Fill MySQL Details
  4. Now you can import one single table from your target DB or you can choose to execute a query and fetch its results. I always prefer the later since then I can retrieve all the data I require in one Data Connection.

    Connecting to a table
    Connecting to a table
  5. Click on Save and Finish. Your ODBC Connection is ready to be used in Excel!

    Finish Connection
    Finish Connection
  6. Now open Microsoft Excel and switch to the Data Tab  and click on Connections to see all the data connections already present in the workbook. If it’s an empty workbook, then there will not be any data connections. Click on Add.

    Connection files on this Computer
    Connection files on this Computer
  7. Now your Connections are available in ODBC, so you need to click on Browse and click on the New Source to go to the ODBC Prompt.

    Connect to a new datasource
    Connect to a new datasource
  8. Choose your ODBC Connection and you are done!
  9. Now when you save the connection, Excel will pull the data and display it in your current worksheet! The only thing you need is mad ninja dashboarding skills to make your Excel look awesome!

Typically you would use this system if your dashboard or excel report is pretty much set and the data comes from a database which changes frequently (transaction based database). The only pitfall that I see of this method is that the size of the excel file can really shoot up if you are pulling in a lot of data. Stuff such as pivot tables also work a lot in increasing the excel file size. So try to be frugal in their use when you create a dashboard! The screenshots that I am including are from me connecting our Excel report to our CRM database.


PS – A word of advice, do not directly connect your live database to the Excel file. ALWAYS use a mirror!

Series on CRM

Today, I sat down and started writing a post on Customer Relationship Management (CRM) implementations and it’s failures in most organizations.

The idea came to me as I was reading one of Andrew McAfee’s posts on his blog, the business Impact of IT. In case if you do not know about Andrew McAfee, you can read up on his blog at HBR.

There have been many theories and reasons on how to start implementing a CRM and what are the typical pitfalls. If you search for this on Google there will be pages on pages of do’s and don’ts. Of these I have read a good number, however theory as always is so vastly different from practice that when you are on the ground, it becomes difficult to relate (and subsequently apply) theory to real life problems.

I consider the CRM implementation at Pristine a failure. It’s not fully implemented yet, and its not fully being used as well … but those are precise points why I consider it a failure. I was intending to write a piece on this on my blog.

As I kept on writing relating my experiences with the implementation, adoption and failures of CRM systems, I realized that one post won’t do justice to this (I had touched around 1000+ words and there was room left for more!) and decided to split this into a series of posts.

In the next few weeks, I will keep writing regular posts on the CRM system at Pristine and how it has failed … and how it can be revitalized.

Updating this post after 5 years, the CRM system we installed has been a resounding success and a continuous source of business insights for the organization.

CRM is the new website

Back in 2000, the dot com market in India was booming. Every company who wanted to have it’s presence online was scrambling to find domain names, hosting, email solutions, etc. Companies such as Net4India sprung up and came up with offerings with ridiculous prices (some of these offerings still exist to date!). Executives bought these websites thinking that somehow building a website would translate into revenues. In fact even today, the average “Indian IT company” continues to dole out development costs of websites propelled by content management systems (CMS) to be around 5-6 lakhs. If you are lucky, then that figure can go as high as 14-15 lakhs :-) !!

What was the mistake then?

The single minded assumption that building a website will translate into revenues.

I still see folks who nurture this piece of ticking bomb waiting to explode and set their businesses to ruins. As a result, all derived-demand business also went bustville. It took a fair amount of years to clear the damage, and come back on track.

So why bring this up Now?

These days, whomsoever that I am talking (and you can include me in this list as well!!), is thinking about having an online enterprise. A sweet setup, where the person can run a business irrespective of where this aforesaid individual is. There are more than a fair share of success stories around, and these people have become heralded role models.

Yes, the online dream can become true. Yes.

Feels good to hear these words, don’t they? These days I am seeing a lot more structured approach, newsletters, opt-in forms, CRM systems, mass emailing systems, affiliate management systems, advertising networks, etc … the systems are all there. People just need to start using them and start churning the proverbial money mill.

But …

Back then it was IT firms, now I am seeing a large growth of Digital Marketing firms. It’s the same plot, just the players are different now. Organizations are again back to setting up websites, now with complex marketing and sales systems. But how are you doing in terms of sales? Is the juice worth the squeeze?

If you do own a website, could you share with me what are the plans that you are thinking of to generate sales? Are you creating value to generate those sales?