Web Analytics India

 - Contributed by Gunjan Aggarwal

A layman who would look at the above graph, at his first glance would tell that the Paid Search had not performed well in the initial months, or HE/SHE would think that there must be some changes happened in the Paid Search Section in the month of June which in turn shows hike from July onwards
Through this trend in first glance you will say that Paid Search didn’t yield any visitor in the month of April, May and June. But the fact is Paid Search Campaign started in July.
Now how to show trend line starting from July onwards is a trick.
Goto your Data Source

In the Paid Search Section Replace the “0” for which the data is not available with the formula “=NA()”.

By making a minor change in the data source the Graph can be depicted in more meaningful manner as shown below:

A person looking at the above graph would understand that the Paid Search has started from July onwards.

If you need a sample report on any of these dashboard tricks, please leave your email id in a comment. And we will mail it over to you.

- contributed by Gunjan Aggarwal

Dashboard designs are one of today’s most discussed topics. The first dashboard created for a client is always a challenge. Limited time and best results.

One of the things that can be done to simplify a dashboard of a web analytical report, containing data of a lot of verticals, is using the checkboxes to make the dashboard neat and near-perfect!

Let’s assume, If we are asked to create a graph for the eight different sources of visitors, we would end up creating 8 different graphs and hyper linking it to different cells numbers or creating a single graph that would show the trend lines simultaneously in a messy manner as shown below:

Demerits of such a Graph:

• Its difficult to analyse the trend for a particular source of Visitor which is more important from an analytical point of view
• The chance of lines graph over lapping with each other is more
• The Source with less traffic may not be visible

A definite idea to showcase such a graph with different source of data is a CHECK BOX. Check Box is one among the Excel functions that would allow the user to select the required data source and view the trend for that particular source. To top it, it gives various options for the user to select a particular source of the data and view its trend, select two different sources and compare it with the other. Now, the best thing about it is that, it needs no VBA code or macros! Here, for instance, if you select Paid Search Check Box you can see a sharp drop in visitors in August which was not visible in cluttered chart. It works at the user’s option were in you can customize the graph and view only the trend that is required for the moment!

For instance: you can notice in the graph here, that when there is a drop in traffic of Paid Search in August, Direct Traffic is showing hike.

Now here’s how we can make Interactive Multiple Line Chart:

1. First make simple chart and checkboxes
2. Now we have to allocate one cell to control checkbox. On selection of Checkbox the cell value will be “True” otherwise False. Right Click on the checkbox, click on “Format Control”. Click last tab “Control” and under cell link type cell number where you want to give control of checkbox.



3. Now below the table make another table having “If” formulas.
   Formula = If (Checkbox control cell, cell which contains actual value, NA ())
4. Link the chart to the table having formulas
5. Finally the Graph with the Check box idea will be ready

Well, I have been researching on some of the simple dashboard design techniques for graphs. We can still make enhancements to the current post, about checkboxes. This would include using drop-down menu for the same metrics. So hope to get back with more useful stuff soon!

If you need a sample report on using checkboxes, please leave your email id in a comment. And we will mail it over to you.