Excel x jestor – How to create a Conversion Rate Indicator

Conversion Rate is extremely important for you to know if your company’s efforts are paying off! To find that rate, you can use a Conversion Rate Indicator.

In this text, we’ll teach you how to create this indicator on Excel and jestor. Which one do you think is easier?

Walkthrough: How to create a Conversion Rate Indicator in Excel

1º) The first step is to create a table with sales information. In this table, you should have the following columns:

• Sale ID;
• Date;
• Product;
• Value;
• Status.

2º) Fill in the rows by placing the status of each sale, their values, etc.

3º) To define the conversion rate indicator, we’ll focus on two factors:

• Date: will be responsible for segmenting in which period the conversion took place;
• Product: so you can understand the conversion rate for each product.

To begin the analysis, you’ll have to organize your data, which consists of using Excel features and formulas to consolidate the information and generate the indicator.

4º) We’ll start by organizing the date, so create a column named “Today” or “Date X” and enter the formula “= today ()” so Excel has today’s date value and can compare it with the sales’ date.

5º) Create a “Month” column next to the “Date” column. Enter the month formula, which is “= month()” and relate it to the “Date” column.

NOTE: Don’t forget to format the column for the “number” condition (which can be done on the “Home Page”), otherwise the result may have a wrong format.

6º) Enter the same month formula to a cell next to “Today” (or “Date X”) and relate to it:

7º)  Now, we’ll define the conversion rate by some parameters, the first one is “Sales Won”.

To find this information, it’s necessary to use a data processing formula:

= COUNTIFS(month column; today’s month; product column; “desired product name”; status column; “Won”)

This formula will cross-reference information and show sales with those conditions.

8º) The second parameter to have the conversion rate is “Total Sales”, and its formula is:

=COUNTIFS(month column;today’s month;product column;“desired product name”; status column; “Lost”) + Won Sales

9º) Finally, with the “Won Sales” and “Total Sales” numbers, you can reach the Conversion Rate. To do this, go to a new cell and enter the formula:

=Won Sales / Total Sales

NOTE: Format the cell to the percentage(%) condition.

Walkthrough: How to create a Conversion Rate Indicator in jestor

Creating a Conversion Rate Indicator is much simpler on jestor than on Excel. In Excel, it takes longer to build and the chances of error are bigger if someone messes with the table, columns, etc. Also, Excel becomes visually tiring if it shows many formulas, auxiliary columns and other things that had to be created for the analysis to be done. This can make Excel heavy and harder to navigate.

On jestor, the results are more reliable, updates happen at the same time and all information is saved in the database jestor provides.

1º) The first step is to create a Dashboard. Go to the “+” icon on the left menu, choose the option “Dashboard”, name it “Conversion rate” and click on “create”. 

2º) Click on “Add Component”, choose “Complex Indicator” and name it “Conversion rate”. 

In item “1. Define the operation of the indicator”, select the option “Ratio % (x/y)”;

In item “2. Define ‘x’”, select the following options:

• In “Select the table it is in”: Choose the table that contains the information you want to pull;
• In “Select the operation for ‘x’”: Choose “Number of records in the interval”;
• In “Choose the period analyzed”: Choose the period you want to analyze, like “last 7 days” or “all time”.
• In “Select a filter for ‘x’ (optional)”, filter the results to the desired status (such as “Won” deals).

3º) Do the exact same process for “y” minus the filter, and click on “Create”.Did you see how easy it is? jestor ends up being a very simple BI and Analytics tool that allows deep analysis and always up-to-date data, without having to code.