Scenario
A funnel report in sales is a visual representation of the various stages that potential customers go through in the process of becoming paying customers. It is called a “funnel” report because, much like a traditional funnel, it illustrates how a larger number of leads or prospects at the top gradually narrow down as they progress through different stages, ultimately resulting in a smaller number of converted customers at the bottom of the funnel. This article is just an illustration of how we can achieve this in NetSuite since NetSuite does not give a standard report for this.
Note
The searches used in the script are not tested and verified.
This report includes leads, prospects converted from leads, and customers converted from both leads and prospects.
jj_funnel_chart.html
<!DOCTYPE html>
<html>
<head>
<script src="https://cdn.plot.ly/plotly-2.25.2.min.js"></script>
</head>
<body>
<div id="myDiv"><!-- Plotly chart will be drawn inside this DIV --></div>
<script>
var reportData = [0, 0, 0];
var gd = document.getElementById("myDiv");
var data = [
{
type: "funnel",
y: [
"Leads",
"Prospects",
"Customers",
],
x: reportData,
hoverinfo: "x+percent previous+percent initial",
},
];
var layout = { margin: { l: 150 }, width: 600, height: 500 };
Plotly.newPlot("myDiv", data, layout);
</script>
</body>
</html>
jj_sl_funnel_report.js
/**
* @NApiVersion 2.1
* @NScriptType Suitelet
*/
define(['N/file', 'N/search', 'N/ui/serverWidget'],
/**
* @param{file} file
* @param{search} search
*/
(file, search, serverWidget) => {
const DATA = {
getLeads(){
let count = 0;
let leadSearchObj = search.create({
type: "lead",
filters:
[
["stage","anyof","LEAD"]
],
columns:
[
search.createColumn({
name: "internalid",
summary: "COUNT",
label: "Internal ID"
})
]
});
leadSearchObj.run().each(function(result){
count = result.getValue({
name: "internalid",
summary: "COUNT",
label: "Internal ID"
});
});
return count;
},
getProspects(){
let count = 0;
let prospectSearchObj = search.create({
type: "prospect",
filters:
[
["stage","anyof","PROSPECT"],
"AND",
["systemnotes.oldvalue","startswith","LEAD"],
"AND",
["systemnotes.field","anyof","CUSTJOB.KENTITYSTATUS"]
],
columns:
[
search.createColumn({
name: "internalid",
summary: "COUNT",
label: "Internal ID"
})
]
});
prospectSearchObj.run().each(function(result){
count = result.getValue({
name: "internalid",
summary: "COUNT",
label: "Internal ID"
});
});
return count;
},
getCustomers(){
let count = 0;
let customerSearchObj = search.create({
type: "customer",
filters:
[
["systemnotes.field","anyof","CUSTJOB.KENTITYSTATUS"],
"AND",
["systemnotes.oldvalue","startswith","LEAD"],
"AND",
["systemnotes.newvalue","startswith","PROSPECT"],
"AND",
["stage","anyof","CUSTOMER"]
],
columns:
[
search.createColumn({
name: "internalid",
summary: "COUNT",
label: "Internal ID"
})
]
});
customerSearchObj.run().each(function(result){
count = result.getValue({
name: "internalid",
summary: "COUNT",
label: "Internal ID"
});
});
return count;
}
}
/**
* Defines the Suitelet script trigger point.
* @param {Object} scriptContext
* @param {ServerRequest} scriptContext.request - Incoming request
* @param {ServerResponse} scriptContext.response - Suitelet response
* @since 2015.2
*/
const onRequest = (scriptContext) => {
try {
if (scriptContext.request.method === 'GET') {
let form = serverWidget.createForm({
title: 'Funnel Chart'
});
let htmlField = form.addField({
id: 'titlefield',
label: 'chart',
type: serverWidget.FieldType.INLINEHTML,
});
let htmlFile = file.load({
id: './jj_funnel_chart.html'
});
let customers = Number(DATA.getCustomers());
let prospects = Number(DATA.getProspects()) + Number(customers);
let leads = Number(DATA.getLeads()) + Number(customers) + Number(prospects);
log.debug("customers", prospects);
log.debug("prospects", prospects);
log.debug("leads", leads);
let htmlCode = htmlFile.getContents();
htmlCode = htmlCode.replace('var reportData = [0, 0, 0];', `var reportData = [${leads}, ${265}, ${156}];`);
// htmlCode = htmlCode.replace('{ y: 4, label: "Prospect" }', `{ y: ${prospects}, label: "Prospect" }`);
//htmlCode = htmlCode.replace('{ y: 3, label: "Customer" }', `{ y: ${customers}, label: "Customer" }`);
log.debug("htmlCode", htmlCode);
htmlField.defaultValue = htmlCode;
scriptContext.response.writePage(form);
}
} catch (error) {
log.error("Error @ onRequest", error);
}
}
return { onRequest }
});
