Building an Ed-Tech Sales CRM using ToolJet 📈
Tags: javascript, tooljet, lowcode, beginners
Published: 2024-06-11 12:33:52

## Introduction

An effective Customer Relationship Management (CRM) system can help Ed-tech companies streamline their sales processes, maintain customer information, and enhance customer interactions.  
  
In this tutorial, we will guide you through the process of building an Ed-tech sales CRM using [ToolJet](https://www.tooljet.com) and [ToolJet Database](https://docs.tooljet.com/docs/tooljet-db/tooljet-database).  
  
Here is a quick preview of the CRM that we’ll be building in ToolJet.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r253djk8qvb5proe7wwi.png)

**Prerequisites:**
*   ToolJet [(https://github.com/ToolJet/ToolJet)](https://github.com/ToolJet/ToolJet): An **open-source**, low-code business application builder. [Sign up](https://www.tooljet.com/signup) for a free ToolJet cloud account or [run ToolJet on your local machine](https://docs.tooljet.com/docs/setup/try-tooljet/) using Docker.
*   Basics of JavaScript: Basic knowledge of JavaScript can be handy for implementing dynamic functionality in your ToolJet applications.

## Setting up the ToolJet Database

This section will teach you how to set up the database for our CRM app. We will be using **ToolJet Database** for this application.  
  
Log in to your ToolJet account and click on the ToolJet Database icon in the left sidebar. We will be creating two tables, one will be for the _**sales\_revenue**_ and other for _**sales\_executives**_. Let's start by creating the database tables.  
  
Create a new table with the following columns and rename it to _**sales\_revenue**_:

- id (primary key/auto-generated)
- course(varchar)
- number_of_courses_sold (int)
- discount (varchar)
- customer_name (int)
- customer_email (varchar)
- customer_country (varchar)
- customer_age_group (int)
- revenue  (int)
- se_name (varchar)
- profession (varchar)
- sale_date (varchar)

Create a new table with the following columns and rename it to **_sales_executives_**:

- id (primary key/auto-generated)
- name(varchar)
- email (varchar)
- phone (varchar)

We recommend adding some dummy data to the table so that we have something to work with when we begin the app development process.

## UI Development

Once you are done setting up the database, click on the **Apps** in the sidebar and create a new app with the name **Ed-Tech Sales CRM**. After the successful creation of the App, you will land on the App-Bulider page.

Now we can drag and drop ToolJet's pre-built components to build the app's UI quickly.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c0a51v03j5zu67jda3t4.png)

Here is the structure of the app that we will be building:

* Header
* Tabs
  * Overview
  * Sales Executives
  * Revenue

Let us start will building the **Header**:

*   Drag and drop a [Container](https://docs.tooljet.com/docs/widgets/container/) component on the canvas from the [components library](https://docs.tooljet.com/docs/tooljet-concepts/what-are-components) on the right and rename it to _header_. Containers are used to group related components in the App-Builder.
*  Resize it and change its color to #2E425A and border radius to 8.
*   Inside the Container component, add two [Text](https://docs.tooljet.com/docs/widgets/text) component for and rename it to _brandName_ and _CRMTitle_.
*  In the _brandName_ component change the Data to **TOOLJET** and change the color to white, font size to 20, font weight to bolder and letter spacing to 6.
*  In _CRMTitle_ component, change the Data to **Ed-Tech Sales CRM** and change the color to white, font size to 18. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mlt6zglchrt0v0mgx95x.png)

_Renaming components can be useful as the application grows, allowing easy reference to component-related values throughout various parts of the application._


*   Drag and Drop the [Tabs](https://docs.tooljet.com/docs/widgets/tabs/) component and change the tab names to **Overview**, **Sales Executives** and **Revenue**.

*   In **Revenue** tab, add a [Table](https://docs.tooljet.com/docs/widgets/table/table-properties) component which will contain the data from the _**sales_revenue**_ table in the database.

*   Now add a [Modal](https://docs.tooljet.com/docs/widgets/modal/) component, place it above the table, and rename it to _saleDetails_. In properties change the title to **Sale Details**, Trigger button label to **Add sale** and change the color of button to primary color ( `#2E425A` ).


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rvre20m2z5q6b70ufoy2.png)

*   Now we need to add input fields in this modal. Here we will be dividing the modal into two parts, first is **Customer Details** and **Revenue Details**.

*   In **Customer Details**, add [Text Input](https://docs.tooljet.com/docs/widgets/text-input/) component for **Name** and **Email**, Dropdown component for **Country**, **Course** and **Sales Executive name**, Radio button component for the **Age group** and **profession** and a Date component for the **Sale date**.

*   For the **Country** dropdown you can add the following data in Option values and labels:

```
{{ ["Argentina", "Australia", "Brazil", "Canada", "China", "Egypt", "France", "Germany", "Greece", "India", "Indonesia", "Italy", "Japan", "Mexico", "Netherlands", "New Zealand", "Russia", "Saudi Arabia", "South Africa", "South Korea", "Spain", "Switzerland", "Thailand", "United Kingdom", "United States"]
}}
```

*  For the Courses dropdown you can add the following data in Option values and labels:

```
{{ ["Marketing Management", "Data Science Fundamentals", "Financial Accounting", "Introduction to Psychology", "Business Ethics", "Digital Marketing Strategy", "Creative Writing Workshop", "Computer Programming Basics", "Public Speaking Mastery", "Introduction to Economics", "Full Stack Web Development"]
}}
```

*   For the **Sales Executive** dropdown, we will be fetching data from the _**sales\_executives**_ table in the database.

*   Here is the quick preview of _salesDetails_ modal UI, you can change the design at your convenience.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ytip4je8zxdrk9exv7ta.png)

*   In _salesDetails_ modal, as you can see we have the **dynamic UI** elements that will show the values of **Revenue without discount** and **Total revenue generated** based on what they choose in **Courses** _sold_ and **Discounts** offered. The HEX color codes for both of these Text components are `#4A90E2` and `#9013FE` respectively.

*   To build these dynamic text elements we need to add **on change** event handlers on **Courses sold** and **Discounts offered**.

*   We will run a script that will calculate both of these revenues and then will populate those details in the Text components.

*   Open the Query Manager and add a Javascript query. Name it **updateRevenue**.

*   Add the following code to this query. This will return the values that we can use in the **Revenue without discount** and **Total revenue generated** in UI respectively. Make sure you rename the number input component to _courses_ and the radio button component to _discount_.

```
const revenue = components.courses.value * 2000 * (100 - components.discount.value) * 0.01
const revenueWithoutDiscount = components.courses.value * 2000

return {revenue, revenueWithoutDiscount}
```

_[Events](https://docs.tooljet.com/docs/tooljet-concepts/what-are-events) in ToolJet are used to run queries, show alerts, and other functionalities based on triggers such as button clicks or query completion_.

*   Update the Data property of the **Revenue without discount** text component: `{{queries.updateRevenue.data.revenueWithoutDiscount ? queries.updateRevenue.data.revenueWithoutDiscount : 0}}`

*   Update the Data property of the Total revenue generated text component: `{{queries.updateRevenue.data.revenue ? queries.updateRevenue.data.revenue : 0}}`


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sy5hjuyxp8h1xfzu9lqe.png)


*   Once you do these updates, just add **on change** and **on select** event handlers in **Course sold** number input and **Discount offered** radio button input respectively and add **updateRevenue** query.

*   In **Sales executive** tab also, we will be adding a modal and a table, so drag and drop both of these components in the tab.

*   Change the modal size to small and height to 300px. Also change the color of trigger button to `#2E425A`.

*   Now add three text components for **Name**, **Email** and **Phone** and a submit button in the modal. Change the color of submit button to `#2E425A`.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k942srsx87ut0xdnr0mh.png)

*   In the **Overview** tab, add three [Statistics](https://docs.tooljet.com/docs/widgets/statistics/) components. Hide the secondary value and change the primary color to `#F28585` for all three components.

*   Set the primary value labels to **Total revenue**, **Total courses**, and **Total customers**. Keep the primary values as default for now. We will be update it later once we add the data.

*   Now add seven [Chart](https://docs.tooljet.com/docs/widgets/chart/) components from the component library. Set the chart type to pie for five of them and to bar for the remaining two.

*   Change the background color of all the charts to `#F1F4FC`. For the two bar charts, set the marker color to `#2A77B4`.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z0bmxvi5idneixhj7yn5.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hbel39oic5t6nqfk78ah.png)

We have completed building UI, now let's quickly create queries and add functionality to our app.

## Data Fetching

With ToolJet, we can easily connect the UI elements to the data sources and fetch data by [creating queries](https://docs.tooljet.com/docs/tooljet-concepts/what-are-queries/) in the Query Manager.

We will be adding queries for **Revenue**, **Sales Executive** and **Overview** tabs. We will start with **Sales Executive** tab, as we will need that data in the modal for adding sales in the revenue tab.

### 1. Sales Executive Tab

Here, we will need two queries, one will be to add the data to the database and one will be to show the data in the table.

*   Open the **Query Manager** at the bottom, click on **Add**, and then click on the **ToolJet database**. Rename the query to **getSalesExecutives**.

*   Select the table name as **_sales\_executives_** and operation as **List rows**. In settings, turn on **Run this query on application load?** toggle to run this query every time the app reloads.

*   Once you are done with this, you can click on **Preview** to check the data.

*   Let's link this query to the table. Click on table and add `{{queries.getSalesExecutives.data}}` in the data. You will see your data from the database in the UI.

Now we need to add another query to add the data in sales_executives from the modal component.

*   Click on **Add** in the Query Manger and then click on the **ToolJet database**. Rename the query to **addSalesExecutive**.

*   Select the table name as **_sales\_executives_** and operation as **Create row**. Then select **name**, **email** and **phone** in the column.

*   Now we need to add the data from UI to these columns in the keys input. Add `{{components.seName.value}}`, `{{components.seEmail.value}}`, `{{components.sePhone.value}}` in the respective columns in the keys input.

*   Here _seName_, _seEmail_ and _sePhone_ are the component names. Make sure you rename them before adding them to the query.

*   Open the modal and select the submit button. In properties, click on **New event handler** and add **on click** event, select action as **Run query** and select the **addSalesExecutive** query.

*   The last thing we need to add is the event handlers in the query. You can add these three events - close modal, success prompt and run **getSalesExecutives** query.

*   Try adding the data in the inputs components and submit it. You will see the that data populating in the table.


### 2. Revenue Tab

In this tab also, we will be needing two queries, one will be to add the data to the database and one will be to show the data in the table.

*   Open the **Query Manager** at the bottom, click on **Add**, and then click on **Run Javascript code**. Rename the query to **getRevenueDetails**.

*   Select the table name as **_sale\_revenue_** and operation as **List rows**. In settings, turn on _**R**_**un this query on application load?** toggle to run this query every time the app reloads.

*   Once you are done with this, you can click on **Preview** to check the data.

*   Let's link this query to the table. Click on table and add `{{queries.getRevenueDetails.data}}` in the data. You will see your data from the database in the UI.

Now we need to add another query to add the data in sale_revenue from the Modal component.

*   Click on **Add** in the Query Manger and then click on the **ToolJet database**. Rename the query to **addRevenueDetails**.

*   Select the table name as **_sale\_revenue_** and operation as **Create row**.

*   Add and the columns and then add the data from UI to these columns in the keys input. Add `{{components.name.value}}`, `{{components.email.value}}` and all other items from the modal based on names you have added for all the input components.

*   Open the modal and select the submit button. In properties, click on **New event handler** and add **on click** event, select action as **Run query** and select the **addRevenueDetails** query.

*   You can add these three events - close modal, success prompt, and run **getRevenueDetails** to your query.

*   Try adding the data in the inputs and submit it. You will see that data populating in the table.


### 3. Overview Tab

In this tab, we will be calculating all the data to be added in all the Chart and Statistics components using Javascript.

*   Open on the **Query Manger** at the bottom, click on **Add** and then click on **Run Javascript code**. Rename it to **analytics**.

*   You can paste the following code which calculates all the data that you can put inside of all the charts and statistics components.

```
await queries.getRevenueDetails.run(); 

let data = queries.getRevenueDetails.getData(); 

const totalRevenue = data.reduce((acc, obj) => acc + obj.revenue, 0);
const totalCourseSold = data.reduce((acc, obj) => acc + obj.number_of_courses_sold, 0);
const totalCustomers = Array.from(new Set(data.map(obj => obj.customer_email))).length


const countryCounts = data.reduce((counts, obj) => {
  counts[obj.customer_country] = (counts[obj.customer_country] || 0) + 1;
  return counts;
}, {});

const customerCountryData = Object.keys(countryCounts).map(state => ({ x: state, y: countryCounts[state] }));

const ageRangeCounts = data.reduce((counts, obj) => {
  counts[obj.customer_age_group] = (counts[obj.customer_age_group] || 0) + 1;
  return counts;
}, {});

const ageRangeData = Object.keys(ageRangeCounts).map(state => ({ x: state, y: ageRangeCounts[state] }));

const discountCounts = data.reduce((counts, obj) => {
  counts[obj.discount] = (counts[obj.discount] || 0) + 1;
  return counts;
}, {});

const discountData = Object.keys(discountCounts).map(state => ({ x: state, y: discountCounts[state] }));

const professionCounts = data.reduce((counts, obj) => {
  counts[obj.profession] = (counts[obj.profession] || 0) + 1;
  return counts;
}, {});

const professionData = Object.keys(professionCounts).map(state => ({ x: state, y: professionCounts[state] }));


const courseCounts = data.reduce((counts, obj) => {
  if (obj.course) {
    counts[obj.course] = (counts[obj.course] || 0) + 1;
  }
  return counts;
}, {});


const courseData = Object.keys(courseCounts).map(course => ({ x: course, y: courseCounts[course] }));

const revenueByExecutive = data.reduce((acc, obj) => {
  if (obj.se_name) {
    acc[obj.se_name] = (acc[obj.se_name] || 0) + (obj.revenue || 0);
  }
  return acc;
}, {});


const teamData = Object.keys(revenueByExecutive).map(executive => ({ x: executive, y: revenueByExecutive[executive] }));

// Aggregate revenue by year
const revenueByYear = data.reduce((acc, entry) => {
    const year = parseInt(entry.sale_date.split('/')[2]);
    if (!acc[year]) {
        acc[year] = 0;
    }
    acc[year] += entry.revenue;
    return acc;
}, {});

// Format the data
const yearRevenueData = Object.entries(revenueByYear).map(([year, revenue]) => {
    return {
        y: revenue.toString(),
        x: parseInt(year)
    };
});

// Sort the data by year (y) in increasing order
yearRevenueData.sort((a, b) => a.y - b.y);



return {totalRevenue, totalCourseSold, totalCustomers, courseData, teamData, customerCountryData, ageRangeData, discountData, professionData, yearRevenueData};
```


You can preview and check the data. Now we will be connecting the query to all the components in this tab.

*   Select the **Total Revenue** component and add `{{queries.analytics.data.totalRevenue}}` in the Primary value.

*   Select the **Total Course** component and add `{{queries.analytics.data.totalCourseSold}}` in the Primary value.

*   Select the **Total Customers** component and add `{{queries.analytics.data.totalCustomers}}` in the Primary value.

*   Select the **Customer location** chart component and add `{{queries.analytics.data.customerCountryData}}` in the chart data.

*   Select the **Courses** chart component and add `{{queries.analytics.data.courseData}}` in the chart data.

*   Select the **Age range** chart component and add `{{queries.analytics.data.ageRangeData}}` in the chart data.

*   Select the **Team revenue** chart component and add `{{queries.analytics.data.teamData}}` in the chart data.

*   Select the **Profession** chart component and add `{{queries.analytics.data.professionData}}` in the chart data.

*   Select the **Discounts** chart component and add `{{queries.analytics.data.discountData}}` in the chart data.

*   Select the **Year Vs Revenue** chart component and add `{{queries.analytics.data.yearRevenueData}}` in the chart data.

Once you are done connecting all queries to your components, turn on **Run this query on application load?** toggle in the settings, to run this query every time the app reloads.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ijwk1uvmym4wk9mqbl21.gif)

## Conclusion

Congratulations, your Ed-tech sales CRM application is now fully functional! By leveraging Tooljet and the ToolJet Database, you've created a powerful and efficient CRM tailored to the unique needs of the Ed-tech industry. Your application can now effectively manage leads, track sales activities, and analyze performance metrics, all within a user-friendly interface.

We hope this guide has been helpful and inspires you to explore further capabilities and integrations with ToolJet. To continue exploring, check out the official [ToolJet docs](https://docs.tooljet.com/docs/) or connect on [Slack](https://tooljet.com/slack) for queries and doubt-solving.