Top 3 Data Analytics and Reporting tools

#1 — Google Sheets: Compared to Microsoft excel, I find google sheets to be much more powerful and versatile

What makes google sheets powerful?

• Real time updates and easy collaboration: No needs to exchange emails or files
• All excel formulas and features available (including pivot tables and conditional formatting and more) — see entire list here. Few examples, import range is one very powerful function using which you can link multiple sheets or collate them into one summary sheet. Also, if you’re familiar with sql, you can directly manipulate data using query function.
• Comprehensive charting and plotting options made more powerful by the explore option (check bottom right button on your google sheet).
• Unlock powerful add ons (menu >> add ons >> get add ons) and automate routine stuff with app script (like building custom functions and automating email notifications/reports). Some of my most used add ons are — remove duplicates, yet another mail merge, and google analytics.

Top use cases:

• Project plan/checklists: Use existing templates or create your own project plan which can be shared across team. Email alerts can also be created when a task is marked as complete.
• Mini database for master data: For managing master data which needs to be updated frequently, e.g. which marketing campaigns are running currently, or which product promotions are active, use a sheet which can be viewed across teams and the data can be linked to various other sheets. Again, import range function can come in handy to use master data across various sheets.
• Financial calculations: The simpler analysis can easily be done similar to that in excel. There are specialised add ons also available for risk analysis and statistical computations. For small businesses like that of ours, we’ve linked revenue calculations for our partners based on the raw data which is simply uploaded in one sheet for calculating monthly payout. For our web partners, we can even see their performance from google analytics data (using a connector) against their payouts.
• Consumer feedback/form responses: Whether you are using typeform or google forms, it’s easy to view and summarise responses in a google sheet. Also, you can add triggers based on form submit which will make your custom functions to run whenever a new form is submitted. For example, we have a script which runs when a form is submitted to send auto response to submitter based on the values submitted by them.

Power features:

• App script: Learn more here. Sample use case: I heavily use 1) custom functions e.g. I have a script to check if a particular value of an id variable has been used anywhere else across multiple sheets and 2) trigger based custom script for automating emails to people who are submitting the form.
• Connecting sheets to data-studio: Sheets can be easily connected to data studio using a built in connector (more on data studio in the final section of this post). This makes it easier to automate the dashboards in datastudio without manually uploading any file.
• Embedding sheets on websites: Though I personally haven’t used it much — but realising the power of sharing your work, this gives sheets an edge. To embed your sheets — from menu visit File >> Publish to web >>Link entire document or embed to get the iframe snippet for a particular sheet.

#2 — R: it gives you an edge (over using spreadsheet based tools) on tasks which require deep analysis on large datasets. I recommend R Studio which is an IDE for R.

What makes R powerful?

• Open source: R is open source with very active community supporting regular development
• Easy to learn: R as a language is easy to learn, considering it was originally designed keeping interactive data analysis in mind, with rich documentation available. There are many good courses and books available on introduction to R.
• Access to packages to help you connect to various tools using APIs, to access sophisticated plotting abilities, and to deploy powerful statistical techniques without implementing them from scratch.

Top use cases:

• Connecting to tools through API access: For example, we use freshdesk for tracking our tickets and clevertap for user engagement. In order to automate routine analysis tasks, instead of regularly downloading reports from freshdesk/clevertap and then spending time in cleaning and manipulating data, we use simple R scripts which perform these tasks and plot the output/save the data to be shared with everyone in the team. You can even send important alerts on your respective Slack channel. This daily saves us important hours that would have otherwise been spent in these routine activities.
• Deploying web apps for routine analysis: When we can’t access the data directly from within R, we import the data into R and create a script for performing the analysis steps. The same script can be deployed as a web app (using Shiny package) which can then be used by other members of the team.

Power features:

• Tidyverse: really powerful collection of packages for data exploration, manipulation, and plotting
• Shiny: You can build dashboard or interactive web apps using shiny.
• httr and jsonlite packages for http requests

#3 — Datastudio by google: Build interactive dashboards by directly connecting your data

What makes google datastudio powerful?

• Super easy to use and share: Create new charts using drag and drop. Share your dashboards with your team.
• Wide range of connectors: As a lot of the data we use is either in Google analytics, Google Bigquery, or in Google sheets — it’s easy to connect it to datastudio using built in connectors. For entire list of datastudio connectors — visit this link.

Top use cases:

• As one stop dashboarding solution for the team: We use datastudio to track important metrics and trends. This makes datastudio an ideal starting point to start your day where you get an overall picture and then you can either use datastudio itself or respective tools to dig deeper into the problem.
• For sharing information with teams outside the organization: For projects where we are jointly working with teams outside our organization. We typically set up a datastudio dashboard where all of us can regularly track the progress and movement of metrics. This increases transparency in the project and reduces the need for exchanging emails.

Check out these sample reports on datastudio for inspiration on how to powerfully use datastudio.

Please comment and share your knowledge, specially if you want to recommend any powerful analytics/reporting tool that I’ve missed.