Why it is not a Happy Republic Day

Yesterday as the entire nation celebrated it’s 64th Republic day, as different brands and people were happily swinging the tri-colour in the air, I deliberately chose NOT to post a tri-coloured image on my blog wishing everyone a Happy Republic Day. It has been 64 years for the country and frankly speaking I am not celebrating. There is no reason left to celebrate.

Problems, problems

As a libertarian, it does not matter for me where I stay, as long as the establishment does not interfere with my personal life. I will continue paying taxes, and life will go on for me … but sometimes when I think about my children, I get scared. Not because of the hyper-competition in the Education sector or the difficulty a parent has to face to secure admission for his children, but because of the sheer rut in which this country is in and how it threatens to endanger their very lives.

Women in this country are mistreated and the media causes a furore … the case is discussed across the different media … and soon things go silent. A politician is caught red handed filling his pocket with the country’s money, a committee is set … media wags their tongues and soon afterwards, things go to normal. I also add to this problem, since I do not really do anything. What can I do? Vote?

India the four wheeled vehicle

A good friend described the country’s problem quite aptly, he said that India is like a four wheeled vehicle, the four wheels being media, bureaucracy, politicians and the corporates. The politician wheel does not want to go anywhere, it is happy with the quid pro quo status. It simply does the act of spinning, however its stuck firmly and refuses to budge. The media wheel is connected to the the politician wheel in some nightmarish mechanical method. The exact dynamics are not well documented, however the media wheel is dependant on the politician wheel, and is controlled by the politician wheel.

The bureaucracy wheel shows the largest potential … since its the largest. The problem is that it takes a lot to make it turn, and it is more or less stationary. When calamities shudder the country (the vehicle in our analogy), then this wheel comes into action. But until then, its fairly stationary and has a large inertia.

The corporate wheel (this is the wheel with which I identify the most) is a dynamo on its own. It’s running wild, its spinning faster and faster … in fact its currently the only reason why the vehicle is going anywhere. However, with constant lack of co-ordination from the other wheels, this wheel is beginning to wear down. The rubber has burned away and slowly the support it has given is depleting (the Tatas, Birlas are no longer Indian citizens … and many more in this list).

All in all, the entire nation is being taken for a ride and the automobile simply is not going fast enough.

How did we get here?

Arrogance, Ignorance and Incompetence. Not quite sure in which order, but definitely in epic proportions. However, there is no need to cry over spilt milk … what that done has been done.

The more pertinent question is how to get going? For the more patient there always is the Reform Thyself adage … which might come to fruition in a century of self control and discipline. The activists will always suggest change and overthrow and morchas, however that ain’t happening … we already know what happened with Mr. Hazare and his fast. The passivist will write a long blog (or article) on this issue and get on with their lives. That’s pretty much what I will be doing!

The only action that I might do which will drastically improve the nation in which me and my family live in is … to immigrate!

 

Scheming the Schema: An Introduction to Structured Data

One of the major traffic sources for personal websites and blogs is Organic Traffic, i.e traffic originating from search engines. People working on the web would quickly realize that search traffic can also be bought through Pay Per Click (PPC) Programs such as Google Adwords and Bing Search Advertising. However, for those of us who do not have Venture Capital funded blogs to run, we still do things the old fashioned way … we write good content.

 

Content is still King

The good thing with this idea is that search engines such as Google do give more weight to things such as meta descriptions, meta keywords, and other Search Engine Optimization (SEO) wizardry which was erstwhile known to a few. Web applications like Google Webmaster Tools (GWMT) and Bing Webmaster Tools (BWMT) are making it easier for website owners to recognize these things and highlight important data to search engines.

Content is KingThis move gives the power back to the people, instead of focusing on complex discussions such as Information Architecture, Content Taxonomy, Tagging and Keyword Optimization, you just have to worry about one thing, and one thing only. Good quality original content!

How do these search engines make sense out of this content? Well, they look for patterns within the page. In fact, if memory serves me correctly, one of my classmates (who went on to become an ethical hacker) had written a similar piece of code for his final year engineering project. It’s a decade since he wrote the code, we can assume without a shadow of doubt that the search engine algorithms would be a lot smarter now to extract the right content. Smart enough to discern between the signal and the noise.

So how do site owners help search engines find important data?

One method is to use the tools that they provide us – the WMTs. However, each search engine has it’s separate nuance, and hence there was a need felt for a common standard … a standard which we, the content creators can use to signal to the search engines … that hey … this piece right here … yes this bold sentence … is meant to be special. It might mean something special to the person searching for it … it’s a beacon … a beacon of data.

 

Defining Schemas for better results

This is where structured data comes into picture. In early January of 2011, the major search engines came together and helped define the format of this structured data via schemas. You can see these formats on schema.org. An excerpt from their home page –

Search engines including Bing, Google, Yahoo! and Yandex rely on this markup to improve the display of search results, making it easier for people to find the right web pages.

Many sites are generated from structured data, which is often stored in databases. When this data is formatted into HTML, it becomes very difficult to recover the original structured data. Many applications, especially search engines, can benefit greatly from direct access to this structured data. On-page markup enables search engines to understand the information on web pages and provide richer search results in order to make it easier for users to find relevant information on the web.

What this means for us, is that if we use the mark-up defined for each of the individual schemas in our content, then the search engines will display this data in interesting manners to the people searching for that very piece of information. Wan’t to see an example at work? Here is one

Schema Display

Last week, I instructed my developers at EduPristine to start making use of certain schemas in our mark-up. It’s less than a week, and we can already start seeing them pop-up all over our search engine results.

 

Why Should You Bother

It does not take a rocket scientist to understand that in the game of organic traffic acquisition, the more interesting and relevant your search engine listing, the higher the chances of getting a click on your listing. Thus, marking of your content is bound to increase your organic Click Through Rates (CTRs). You can get this data in Google Webmaster Tools, better to check your current set of keywords (and their CTRs along with average ranks) and then compare the change when you start including schemas in your content.

If you have not heard about structured data or implemented schema.org before then I can guarantee you an increase in your CTRs and in your organic traffic.

Understanding the __utmz Google Analytics Cookie

Google Analytics does tracking by using the urchin tracking cookies. In case if you do not know what are HTTP Cookies, then you need to start reading some of these articles that I am linking to!! They can give the crunch to your digital marketing initiatives.

Do bear with me through this post, I will get a bit technical. In case if you do understand a bit of PHP, you will enjoy this post. For now, just keep this information with you, it will come to your aid sometime later when you would be talking to your developers!

Some Cookie Basics first

So if you have any kind of server side script running on your server, then you can create your own cookies! You should know that on a PHP server environment, the entire set of cookies is available in the $_COOKIE reserved variable.

Isn’t this awesome? What this means is that with one line of PHP code, you can refer to all the cookies which are now on your site for each of your visitors.

If you take look under the hood, then you will see that there are many cookies used by Google Analytics.

kidakaka utmz cookie

Each of these cookies have a certain purpose. The way GA utilizes these cookies is well documented, and you can find the developer note here.

So why the __utmz Cookie?

The __utmz cookie contains the traffic source information in Urchin tracker format. It contains information about how and when an individual visitor hits your site for the FIRST time. That means whenever the visitor comes back to your site, this value remains unchanged.

Imagine a scenario wherein a visitor first sees a post of yours on Facebook, or on Google … or other traffic sources. The visitor comes to the site, checks out a few pages and goes away (… such a shame!!). After a few days, the same visitor comes back to the site via an ad. This time the visitor leaves his information on one of your contact forms (I do hope you are using these!).

How would you know that the user is a repeat visitor? The form will only contain data which is about the visitor’s current visit.

So, whom would you attribute this event to? Your advertisement (the later) or your social media sharing (the former)? Do you believe in first impressions or do you believe in the recency effect?

Without having information about the user’s previous visit to your site, you will always choose the later. Thus misattributing your visitor acquisition to the wrong medium! And that’s why the __utmz cookie is so important! In effect it represents a unique visitor for your website.

I treat the __utmz cookie as equal to a real person!!

Associated with it is a story, this story which rolls out in a set of visits. Google Analytics can tell you if you are willing to sift through the data that is available to you.

What can we do with this Cookie?

How do we bite into this Cookie? Well, there are more than one method of cutting this cookie!!

  • If you have conversions on your site (Downloads, Sales, Contact Forms, etc), then you can always use the value in this cookie to track the origin of the visitor. This will give you a definite number on which traffic source (and which marketing campaign) is more effective for you when it comes down to conversions.
  • Each __utmz has a unique value set, this unique value can be converted into a Custom Variable and you can then track a unique visitor across your site using this Custom Variable (the free version of Google Analytics allows 5 Custom Variables, so use them sparingly).
  • For the more technically sound platforms, you can use this and the other GA cookies to track the visitor ACROSS multiple visits. However, there are other tools which are far more easier to setup to do this viz., Mixpanel.
  • If you are betting a lot on acquisition of traffic via Search engines, then the __utmz cookie can be mined for keyword data … yes, the data which people are searching for and are coming to your site. However, as of 2013, GA only provides the keyword data of users who have not signed in into their Google Accounts (around 30% of the data is not available).

Hmmm … crunchy delight!! And nutritious as well!!

I would love to hear from you how else can we use this and the other Cookies. In the meantime, do start integrating your Cookies with your CRM for more visitor intelligence!

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!