Chandoo writes a very interesting blog offering tips about how to make the best use of Microsoft Excel and to chart data in general. And he announced a data visualization contest yesterday. Contestants have to map the sales data of a fictional company using any software. The charts or dashboards created should help a senior manager understand how the sales people have done in the past 24 months. The winner gets an iPod Touch 8 GB. Cool, right?

Since Chandoo announced the contest is open for sponsors, we asked him if Zoho can be the sponsor. He immediately accepted. So, Zoho Reports is sponsoring this contest and we encourage all Zoho users and readers of this blog to participate in it.

Here's the details about the contest. If you are comfortable using spreadsheets, this should be easy for you. Try using Zoho Reports for the task and see how different/easy the charting experience is when compared to using Microsoft Excel. You can also try using our online spreadsheet tool, Zoho Sheet.

Your comments welcome on how you charted the data, the approaches you followed, the software you used etc.

One of the important things we have been focusing out this year has been integration between the various Zoho apps. An example of such an integration - Zoho Show has long allowed embedding of Zoho Sheet's spreadsheets and charts. The same is now possible with Zoho Reports too. You can embed your cool reports that you created in Zoho Reports into a Zoho Show presentation and impress your audience with live interactive reports.

Let's say as your company's Sales Manager, you are to present the month's sales figures. Use Zoho Reports to crunch all your sales data, make reports out of it and Zoho Show to make your presentation. Embedding the reports from Zoho Reports, you make the presentation much more interactive and powerful. You can mouseover the embedded charts and get relevant values displayed. You can click on the data points in the chart and drill down to the actual values too. See such a slide show in action below (embedding the presentation in a web page / another application is another plus when using Zoho Show)

How to embed reports in Zoho Show presentations

In Zoho Reports, click on Publish -> Embed in Website/Blog. Copy the html code snippet given. In Zoho Show, click on Insert -> HTML code. Paste the code from Zoho Reports and click on the Insert button. The chart/table will get embedded.

Go on. Create Zoho Show presentations with embedded cool & live reports from Zoho Reports. I'm sure your audience will love it :)

In the last post, we saw how Zoho Reports helps in providing a more comprehensive analysis of your Google Adwords campaigns than what's provided by Google Adwords in the form of reports. In this post, we'll see how to use Zoho Reports to analyze the performance of your various Ad Groups within a campaign. Our featured example is for a Mountain Bikes campaign which is having 4 Ad Groups - Adult Bikes, Kids Bikes, Athletic Bikes and a Common Keywords Ad Group.

The below report shows how the CTR has varied for the different Ad Groups over the months.

To get a more recent analysis, the time period can be changed to see how the CTR has varied over the last month alone.

Pivot tables can be made too. The below pivot lists the monthly conversions for all Ad Groups since Jan 2007.

You can also analyze how the various parameters within an Ad Group has performed. For example, you can compare the number of conversions vs the number of clicks.

A scatter plot can be plotted to see which Ad Group is more effective, say in terms of conversions for the ad impressions got.

The above are just a few report samples. The whole set of data and reports are available at http://reports.zoho.com/ZDBPublicDBView.cc?DBID=4000000176044. And this set of Ad Groups-level reports are readily available for your Google Adwords analysis too. All you have to do is to follow the easy steps as mentioned in this page and you can have the same reports as above for your Google Adwords account as well.

It is easy and powerful to analyze your Google Adwords Campaigns and Ad Groups (you can analyze for ads etc too) by importing the csv data files provided by Google Adwords into Zoho Reports. As said in our earlier post, we are working on a Google Adwords connector which will let you do this more readily. Stay tuned.

Google Adwords has become a very important advertising medium for all advertisers & marketing professionals. As one using Google Adwords, you should be analyzing your various campaigns, adgroups, ads etc constantly. But the readymade reports provided by Google Adwords are pretty rudimentary and have the below disadvantages.

  • There aren't actually many report options. The report consists of rows of data which you can see as HTML or export as a csv/xls files.
  • The only chart option given plots only one metric (CPM, Clicks, CTR etc) at a time.
  • The chart and the data are separate and this means you can't drill down a chart to see the underlying data.
  • The reports don't allow you to compare how two different campaigns are performing. Or how a campaign in July 2009 (current month) performed as against June 2009 (previous month) or July 2008 (same month, previous year).
  • The charts cannot be saved for referencing anytime later.
  • The charts cannot be shared or collaborated upon online.

With Zoho Reports, you can do a lot of analysis with the data provided by Google Adwords. Multiple metrics can be plotted and studied. Comparisons between campaigns can be done and a campaign can be compared to how it had performed across different time lines (daily, weekly, monthly, quarterly or even yearly). Based on such analysis, you can take actions to reduce costs and get higher returns for the money you spend on Google Adwords.

Let's see in this post how Zoho Reports can help you analyze various campaigns & allow you to get the maximum bang for your buck (lower costs, more conversions etc). Embedded below are charts with real data from a Google Adwords account that we have set up. The campaigns have been renamed generically as for mountain bikes. There are 3 campaigns being run, one in the US, one in Europe and one comprising of other countries in Australasia and Africa since Jan 2007.

The above chart shows the avg cost / conversion trends across the various campaigns.

This chart shows how the CTR has varied over the months for the three campaigns. The above two examples compare how various campaigns are performing with respect to each other. We can also compare how a campaign's various metrics have performed over time.

The above chart plots the impressions vs clicks for the Europe campaign over the past year. If pretty charts aren't your cup of tea, you can make pivot tables. Like this one for example, which plots the quarterly conversions for the campaigns.

The embedded reports above are just a few samples. The original database with a slew of reports for the three example campaigns are available here - http://reports.zoho.com/ZDBDataSheetView.cc?DBID=4000000163350. You can play around the data, create as many reports as you want and see for yourselves how flexible and powerful your Adwords Campaign data becomes, when using the right business intelligence tool.

Most importantly, all these set of campaign-level reports are readily available for your Google Adword analysis. All you have to do is to follow the easy steps as mentioned in this page and you can have the same reports as above for your Google Adwords campaigns as well. We are planning to make this easier further by providing a direct Zoho Reports connector to your Google Adwords account soon.

Next, we'll see with examples how to analyze your Google Adwords campaigns at the Adgroups level.

So much of your business data from many of your applications lie in a database like Oracle, SQL Server, MySQL or MS Access, behind a firewall. Getting meaningful reports out of such databases and taking corrective actions based on those reports is what business intelligence is all about. The databases themselves are typically costly and creating reports out of them isn't that easy. Buying additional reporting tools, which you need to install and maintain, adds up to your costs. But by making your data available in Zoho Reports, you get these major benefits :

  1. Create meaningful reports easily using just drag-and-drop
  2. The reports you create are accessible from anywhere
  3. Enable collaborative analysis and reporting
  4. Share the reports over the web to whoever you want
  5. Very affordable, pay-as-you-go monthly pricing plans (Zoho Reports is actually free, as of now)
Zoho Reports makes it easy to push data from behind-the-firewall databases (and CSV files too) by providing you with an Upload Tool (a database connector). Using the tool, you can easily connect and push data from your databases like Oracle, SQL Server, MySQL and MS Access  to Zoho Reports. You can schedule data uploads in regular intervals so that you continue to get near-real-time reports based on your dynamic data.


As the diagram above shows, the tool works in a simple way. It is a database connector that pulls data from your databases and uploads it to Zoho Reports. Data gets transfered securely over the Internet to Zoho Reports using a secured connection (HTTPS).  You can then create powerful reports  and analysis based on the data uploaded. Read more about how to move data from your in-house databases to Zoho Reports.

If you are interested in using Zoho Reports for your business intelligence and reporting needs, do contact us for a personalized demo.

In a typical organization, most databases are built with data keyed in by different people. For example, a CRM database will be updated by several sales reps and each of these reps will have his/her own way of entering data. It can be 'Road' for some, while others may prefer 'Rd'. Similarly, it can be Drive or Dr, and CA or California. Such variations slowly corrupt the sanity of the data and can make it unsuitable for reliable analysis & reporting.

The situation is made all the more worse if the data is aggregated from multiple data sources where each system follows its own conventions and processes. To make such data uniform and credible, periodic data cleansing becomes necessary. Only clean, sanitized data can provide you reliable inferences when analyzed.

Zoho Reports now supports a Find & Replace function which makes data cleansing easy and which results in keeping your data clean. For using this function, right-click on a column name and choose the Find and Replace option. (you can also invoke the Find and Replace option under the More Options menu too).


Enter the relevant data for Find what and Replace with fields.


All the matching data in the column will get replaced.


Other than with text values as shown above, the Find and Replace also works with date & numerical values. Say a sales rep mistakenly entered data with the year as 2007 instead of 2008 (a pretty common data entry error, especially when a new year arrives), you can easily cleanse such data too.


Hope you like the Find and Replace function useful for your data cleansing job. Try it now in Zoho Reports & let us know your feedback.

 
Microsoft Access is basically a database application on the desktop. Where as, Zoho Creator is a powerful, scalable, and secure cloud computing platform that lets you build and run database applications online. Some of our users call it as 'Microsoft Access + Visual Basic for the web' (Sorry, PDF). So, what happens when you migrate data from Microsoft Access to Zoho Creator? Well, it can become a powerful web application. This is basically what we are doing with our announcement today. We are excited to announce the latest addition to our family of Microsoft plug-ins - Zoho Plug-in for Microsoft Access (EXE link).


Two weeks back, we integrated our collaborative productivity applications into Microsoft SharePoint. Last week, our project management application added the capability to import your existing projects from MS Project to Zoho Projects 2.0. This week, we have a new add-on that imports your data effortlessly from Microsoft Access to Zoho Creator and Zoho Reports and does much more.


So, what happens when you migrate your database (MDB file) from Microsoft Access to Zoho Creator? Your entire database structure and data are migrated to Zoho Creator and an application is automatically created with Forms (Tables) and Views etc. This is a quick way of creating a web-based collaborative database app from your non-collaborative Microsoft Access DB. Once the Microsoft Access DB is migrated to Zoho Creator, the collaborative aspects kick in where the entire application (or parts of it) can be shared with multiple users.

To summarize the core functionality of this plug-in in three short points, this is what it does...
  • Migrates your Database from Microsoft Access (MDB File) to Zoho Creator & Zoho Reports
  • Automatically creates a web-based database app from your Microsoft Access database &
  • Allows you to share your applications with others
This video will give you a good idea on how this works...





Also check out: Why you should migrate from MS Access to Zoho Creator

Zoho Creator platform offers more flexibility for your applications. Once you have your app migrated from your MDB file, you can edit your existing application online and can also add additional business logic using Deluge Script, our scripting language. The power of Deluge Script makes it easy to design business workflows and perform a variety of automated actions based on the criteria specified. If you have your app created and if you are not a Creator expert, but want to take advantage of our advanced functionality, we have many developers in our community who are willing to help you with your app. If you think your migrated application can be valuable for other users, you can also sell your application in Zoho Marketplace.

Once you have your app created in Zoho Creator, it can also be deployed onto Google AppEngine.

Migrate Microsoft Access DB to Zoho Reports:

This migration tool not only imports your data to Zoho Creator, but it can also import the data to Zoho Reports, our Online Business Intelligence tool that lets you analyze your data and create powerful reports including charts, pivot tables, dashboards etc. Once you have your data imported from Access, similar tables are automatically created in Zoho Reports and are available for analysis.

This Migration tool is available for free. So whether you want to create a web-based database app from your existing data, or analyze your data or share it with others, this migration tool makes it extremely easy.


We certainly hope you'll find this tool useful. As usual, we can't wait to see the amazing apps you build on our Zoho Creator platform.

Helena Schwenk of Ovum Research has a nice post analyzing LucidEra's failure and what it means for the other SAAS BI players. Helena concludes,


 The spotlight is now likely to shine very brightly on other SaaS BI companies (...) While LucidEra's collapse doesn't necessarily mark the end of SaaS BI as we know it, it could mark the start of a SaaS shift towards larger BI players whose brand and broad portfolios continue to dominate the market.

What Helena points out will certainly happen. The "M" in SMBs, will now look at the smaller, one-product SaaS BI vendors with more caution - and rightly so. But they don't have that many places to look at. The solutions offered by larger BI vendors are typically more expensive and complex and that puts businesses in a bind. They may have to stick with using the good old spreadsheets as their only BI tool (read why spreadsheets aren't that good a choice).

Zoho Reports is bridging the gap by providing a more powerful solution for analyzing data than a spreadsheet or simple pivot table, but not something as complex as a Business Objects implementation. An enterprise opting for Zoho CRM, Mail, Projects, Meeting and other Zoho services can easily take advantage of Zoho Reports for analyzing its data. Its simple, spreadsheet-like interface makes it easy for the users to build, view and share reports. And its various import options allow companies to upload as well as push data from applications or databases behind the firewall easily. Zoho Reports is free as of now and the pricing to be announced soon will be in the lines of other Zoho products - an ample free version accompanied by very affordable pricing plans.

Helena also makes another good point. Customers always want to know that their investments will be safe in the long term. Zoho Corporation serves the technology needs of more than 40,000 customers worldwide. We have been profitable in every year since our inception in 1996. Customers who are looking for a stable BI partner who is investing in an easy-to-use, reliable BI platform for the long-term will find an excellent tool in Zoho Reports.

Standard & Poor's publishes the Case-Shiller® Home Price Indices on the last Tuesday of every month. The indices are calculated from data on repeat sales of single family homes. The indices are normalized to have a value of 100 in the first quarter of 2000. It is calculated monthly using a three-month moving average and published with a two month lag. We imported the data released today into Zoho Reports and created a few charts to see how the house prices have performed over the years.





There's also a separate chart that plots the Composite 10 and Composite 20 (an aggregate of 10 and 20 cities respectively) index values.


Inferences from the above charts
  • West
    • Los Angeles, followed by San Diego are the cities where the prices peaked much (indices of around 275 and 250 respectively)
    • In Seattle and Portland, the prices peaked almost a year late when compared to other cities in the West (around Sep 2007 as against Sep 2006)
    • There was a mini bubble in Los Angeles in 1989-1990
  • Midwest
    • Chicago and Minneapolis have seen the maximum rises
    • In Detroit, prices have fallen the most (given the state of the auto industry there)
    • Prices in Cleveland have almost remained level (just went up by 20 points)
  • Northeast
    • During the bubble years, New York's prices rose at a faster rate than Boston's (from 1999 to 2004, prices rose almost at the same rate in both the cities)
    • In the 1987-1996 time period, prices haven't varied much in the two cities
  • South
    • Prices rose the most in Miami, followed by Washington DC and Tampa
    • Like with Cleveland in the Northeast, prices in Charlotte, Dallas & Atlanta didn't rise by much
    • There was a brief bubble in 1989-90 in Washington, DC
  • General
    • The indices for many of the cities are showing a small uptick for April 2009
    • Almost every region in the US has got uniformly affected by the current housing bubble
Do let us know your own inferences from the above. The data is available here. Play with it and create as many charts as you like. For example, the below is an interactive chart of how the indices in the Midwest cities have been since Jan 2001.


We will be updating the data on the last Tuesday of every month. The charts embedded above being dynamic will change automatically.

Related Links

We posted about enhancements done to the pivot table recently in Zoho Reports. Here's more. Pivot tables (and charts, filters) now support quarterly and weekly intervals. This adds to yearly, monthly intervals before. This can be best explained in the sales context. What if you want to know how you did in 1Q 2009 Vs 4Q 2008 (previous quarter) or 1Q 2008 (previous year, same quarter)? Or what if you want to know how you did this week compared to last week or see the trends of the earlier weeks? It is easy now to have reports for such scenarios.



The screenshot above is for the Actual Values - Quarter & Year, sales in various quarters of each year. Seasonal/Cyclic - Quarter option differs from this as it will give the sum of Quarterly Sales summed across years.



Similarly, the Weekly option is available too. Sales figures of different regions for the various weeks in 2008 shown as a Pivot table.



The same quarter & week concepts are available in Charts and Summary Views.




You can also Filter the data using Quarter and Week timelines.



Try the new Quarterly & Weekly options in Zoho Reports and let us know your feedback in the comments.

Related Links