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!

The Cinderella Visitor

It’s surprising, every time I open Analytics, I learn something new about this product and I learn something new about my site … like the fact that I have to deal with Cinderella Visitors!

Here is a maths problem for you. In fact if you have pored through your Google Analytics account, I am sure even you would also have been stumped by the same problem.

Below is a screenshot of my site’s monthly traffic stats –

Monthly Stats

Now, yes … these are humble beginnings! That is why some of you should visit this site more often!! So if you look at the unique visitors (that’s the total number of visitors coming to the website), then it is 530. This is good, that means through the blog I am getting in touch with at least 530 people a month!! Holy cow!! That’s roughly 18 conversations in the day.

Out of those 530, a huge number are new visitors (511). So does that mean only 19 visitors are returning back to the blog for those 126 visits? Hmmm … that should not be that hard to find. One look at the Returning Visitors Advanced Segment should shed more light on this …

Returning Visitors

Well, this seems to the problem don’t it. Things don’t just add up here! 45 visitors are responsible for those 126 visits, which means that 485 visitors are responsible for those 511 New Visits. How can this be possible? These 485 people have come on the site ONCE ( … sigh!! aee jaanewaale ho sake to laut ke aajaa …), but the number of visits tell me otherwise.

Which should bring us to the real question of what does Google Analytics define as a visit? Here is an insightful article from Google about How Visits are Calculated in Analytics. There we have our answer –

A single visitor can open multiple visits. Those visits can occur on the same day, or over several days, weeks, or months. As soon as one visit ends, there is then an opportunity to start a new visit. There are two methods by which a visit ends:

  • Time-based expiry (including end of day):
    • After 30 minutes of inactivity by the visitor
    • At midnight

Ahhh! So if the visitor comes to the site, sticks around for more than 30 minutes without doing anything and then clicks on any other links, then its considered as ANOTHER visit. This should have been a sufficient reason IF the average visit duration was on the higher side, but since its not the case (and I write reasonable short posts too!), so the other method seems to be in action here!

At least 26 visitors have been around the site around midnight!! A quick check on my GA for hourly visits confirms my suspicions! I had 26 visitors coming to the website around midnight … and as the clock struck twelve, these Cinderella visitors fled (or at least Google Analytics made them fade away) and came back as New Visits!! By the way, in case if you are wondering why have you not heard about this term Cinderella Visitor before, then do not worry … I just coined it!

So where are the glass slippers? The glass slippers are the ubiquitous utmz cookies!!

Custom Reporting in Google Analytics

Google Analytics rolled out more than 5 years back, and it has been rocking ever since. The first free enterprise class analytics seemed to keep on adding more and more awesome features every year. Especially after the acquisition of the Urchin tracker system, GA has been the de facto analytics system for all websites.

Of course for the more seasoned people out there who cannot get their exact set of data from GA, there are other niche analytics products. In fact Avinash Kaushik has an entire chapter dedicated to this in his kickass of a book Web Analytics: An Hour a Day (I recommend that you do read this!!)

I have been seriously working on GA for about a year or so now and the more I use this tool, the more I learn about how little I know! That’s the thing with knowledge, by the time you know a lot, you think you know very little. I wish if the other way was true as well :-D

The thing with GA and what turns off most people is that the sheer volume of data it can show in those pretty little orange pages is a lot. So a cursory glance gives you loads of data, however to get an insight, you have to sift through this data. Create segments, look at those segments and search through various different reports to find that one insight which will help you drive more traffic, more leads, more sales to your site.

This is where Google has shone their brilliance, they have allowed web developers, analysts, webmasters and business intelligence guys to actually work together and create custom reports, custom segments which can be shared. Yes, so I can burn the midnight oil trying to find which content works best for my site … however if I had to do the same for another website, I would have to re-create all those steps all over again. What a colossal waste of time! But now, I can simply share that report/segment and voila!!

In the next few days, I will be sharing more custom segments as well as custom dashboards which you can simply import in your Google Analytics and start right off!

The Unusual activity

I clocked roughly 200 blog posts last year. That’s a post every alternate day. Impressive no? The feat was particularly possible because of my previous employment. I ruled the IT Infrastructure there (which was in addition to my actual role). The good things about being ‘God’ (read admin), are that the laws don’t apply to you. Power corrupts, and absolute power corrupts absolutely.

Start of the new year, I got an opportunity with a Mumbai based tech startup. Hours are long, pay is decent, I am home sweet home. Rosy picture aint it? Well for now it sure is. The catch is, no net access, especially Gmail, so no blogging L and no chatting. Thank heavens for Twitter!! Hopefully the email api of blogger still works.