Analyze-Interpret-Solve

Hello readers👋 Today we will learn about data and its components in product management. 

1. Cohort Analysis📈

We use it to calculate customer retention and it is the most basic tool to check on our product advancement. We can calculate this using MS Excel or Tableau or any other application that you like.

Link for Tableau understanding: Youtube

I will explain how to do cohort analysis using MS Excel in this blog →

• The tables that are already given to us are Customer Cohort and Net Revenue by Cohort
  
• The yellow and light red blocks are the total customers that the company has in that particular month
  
• To calculate percentage we use the formula cell value/the initial value of the month for customer retention by cohort table. For example = (=D11/$D11),(=E11/$D11) and so on
  
• The net revenue by cohort shows the revenue generated by that particular cohort i.e. $1,750 denotes the revenue generated by January cohort = 35 customers in 0th month.
  
• To calculate the NDR (Net Dollar Retention) we use the same formula as point 3. For example = (=D41/$D41), (=E41/$D41), and so on
  
• Expansion Revenue can be seen when the NDR is seen above 100% due to increase in revenue spent by the cohort due to certain factors like discounts, freebies, trials, etc.
  
• To calculate Cumulative Lifetime Revenue we use the formula  (=D41+E41), (E71+F41),(F71+G41) and so on
  
• To calculate customer lifetime revenue we use the formula = cumulative lifetime revenue/ customer cohort. for example= (=D71/$D11), (=E71/$D11)
  
• If you want to know the exact formula for each cell just click on the cell and put = sign in the fx row below the menu bar.
  
• Customer lifetime value = Customer lifetime revenue * gross margin;
  in the sheet it can be calculated as (=D86*$Q$101), (=E86*$Q$101), and so on

The major component that we need to learn from cohort analysis is the Customer Lifetime Value which tells us our profit margins and are we spending correctly in CAC( Customer Acquisition Cost) to boost our revenue. 

Here, D86= $50 is spent by the customer and D101= $33 is seen as the profit by the company and overtime the profit is rising, so we can say that the CAC that we are spending is normal as it is not leading to a loss.

Link for the sheet: GSheet

Other resources that you can refer for Cohort Analysis:
Link-I  ||  Link-II  || Link-III || Link-IV || Link-V

2. Funnel Analysis📲

Funnel Analysis or funnel charts are basically used to increase the conversion rate. They tell us where we are going wrong or the features that we need to improve to attract more customers for our company.
   It can also be seen as a customer journey as to how the customer uses the product. There are different customers and they follow different paths. Thus, there is dropping off customers and the funnel gets tapered towards the end.

Here, I will tell you about Funnel Exploration using Google Analytics. You must go through the following steps to get the desired result:

1. Create a Google Analytics account. You can go to your google account and find the google analytics tab, click on it and create your account.
   
2. Use the demo account for ease of analysis if you are a beginner. Link - Demo account
3. Go to the stream setup and check the explore tab present on the left of the screen. Click on it.
   
4. Select Funnel Exploration option.
   
5. Remove the all the steps in the settings option
6. Click on the edit icon to add new steps like- when a product is viewed (view_item), when added to cart (add_to_cart) and so on. 
7. To make the steps more detailed, time limits, parameters and other conditions can be added.
8. Click on Apply at the top to create the funnel

Other resources that you can refer for Funnel Analysis:
Link-I  ||  Link-II  || Link-III

3. Databases📲

A database is a tool that helps us store and manage data. There are two main types of databases: relational and non-relational.

SQL - 

• Stands for structured query language
• Relational database management system (RDBMS)
• Suitable for structured data with predefined schema
• Data is stored in tables with columns and rows
• Follows ACID (Atomicity, Consistency, Isolation, Durability) properties for transaction management
  
• Supports JOIN and complex queries
• Example: MySQL, PostgreSQL, Oracle, SQL Server, Microsoft SQL Server.
  

NoSQL- 

• Stands for not only structured query language
• Non-Relational database management system
• Suitable for unstructured and semi-structured data
• Data is stored in collections or documents
• Does not necessarily follow ACID properties
  
• Does not support JOIN and complex queries
• Example: MongoDB, Cassandra, Couchbase, AmazonDynamoDB, Redis.

Various teams such as business development, marketing, sales, product, analytics, finance, data science, technology use SQL and it's scope is quite large. It has various use-cases across industries and functions.

Here, we will learn about SQL and it is very important to practice problems regrading it. The best website to refer is W3Schools
You will find all the significant concepts in this website. 

I will share some problems of SQL in my next blog!