Building an Ed-Tech Sales CRM using ToolJet 📈

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 and ToolJet Database.

Here is a quick preview of the CRM that we’ll be building in ToolJet.

Prerequisites:

• ToolJet (https://github.com/ToolJet/ToolJet): An open-source, low-code business application builder. Sign up for a free ToolJet cloud account or run ToolJet on your local machine 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.

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 component on the canvas from the components library 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 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.

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 component and change the tab names to Overview, Sales Executives and Revenue.

• In Revenue tab, add a Table component which will contain the data from the sales_revenue table in the database.

• Now add a 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 ).

• 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 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.

• 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 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}}

• 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.

• In the Overview tab, add three 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 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.

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 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 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.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.

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 or connect on Slack for queries and doubt-solving.