1. Feature Overview
To create a report using the SIERA Report Builder, admins must first select a data source. SIERA stores data separately in multiple tables, which offer the following features –
- Designed for superior performance during report generation.
- Simplify reporting by joining multiple tables in LeadSquared to create a mega-table, streamlining the report creation process.
- Eliminate the need for users to concern themselves with joins, schemas, and table details, allowing for a simple drag-and-drop approach to report creation.
It’s crucial to understand the different data sources as they determine the fields that will be present in the Report Builder.
2. Prerequisites
SIERA is a paid feature. To enable it for your account, contact your account manager or support@leadsquared.com.
3. SIERA Data Sources
SIERA Reports has 6 major Data Sources –
- Lead Data Source
- Lead (with Lead Owner)
- Lead (with Lead Created By)
- Activity Data Source
- All Activities (with Lead Owner)
- All Activities (with Activity Creator)
- Opportunity Data Source
- All Opportunities (with Lead Owner)
- All Opportunities (with Opportunity Owner)
- Task Data Source
- All Tasks (with Lead Owner)
- All Tasks (with Task Owner)
- Phone Call Data Source
- Phone Calls (with Activity Creator)
- Revenue Data Source
- Revenue (with Sales Owner)
These data sources contain lead fields (custom and standard) and fields from Activities, Opportunities, and Tasks (only the standard fields) respectively. Additionally, custom data sources can be requested for specific Activities, Opportunities, and Tasks.
3.1 User Types
The user type is a metafield (Learn more on how SIERA handles metafield).
To help users map the user IDs in the report, SIERA provides ready-made templates mapped to different user types. For example, to enable users to create reports on Lead Data Sources, SIERA has 2 templates – Leads with Lead Owners and Leads with Lead Created By.
3.2 Predefined Metrics
Each template also comes with its own predefined metric. For example, lead templates come with metrics like the Number of Leads, Number of Converted Leads, etc., While users have access to create their own metrics, pre-defined metrics are a quick way of creating reports.
3.3 Example flow
Here’s an example workflow that shows how data flows in the data sources –
4. Lead Data Source
This data source contains all the lead data in your LeadSquared system. The lead along with all standard and custom lead fields (dropdown, text, date, and number type) are stored in the data source, pre-joined, and ready to be processed.
Lead Data Source Predefined Metrics
| Metric Name | Logic | Expression |
| # Leads | This gives a count of leads | |
| # Converted Leads | This gives a count of leads where the stage is the final stage as per LSQ settings | |
| Converted Leads % | This is the ratio of # Converted Leads and # Leads expressed in % | (CountIf(`Lead.ProspectStage` in Config('TenantSettings', 'LeadStages.Final'))/Count(`Lead.ProspectID`))*100 |
5. Opportunity Data Source
This data source contains all Opportunities data in your LeadSquared system. All Opportunities created in your system, along with all standard Opportunity fields, enriched with their corresponding lead fields (from the Lead Data Source) will be stored in this data source.
Opportunity Data Source Predefined Metrics
| Metric Name | Logic | Expression |
| Total Opportunities | This gives a count of opportunities | |
| # Leads with Opportunities | This gives a count of leads in the opportunity data source. This will be total leads that have opportunities created on them. This will not be equal to the total leads in the system as leads with no opportunity will be missing. | |
| Lost Opportunities | This gives a count of opportunities where the opportunity status is Lost | |
| Lost Opportunities % | This is the ratio of Lost Opportunities and Total Opportunities expressed in % | |
| Open Opportunities | This gives a count of opportunities where the opportunity status is Open | |
| Open Opportunities % | This is the ratio of Open Opportunities and Total Opportunities expressed in % | |
| Won Opportunities | This gives a count of opportunities where the opportunity status is Won | |
| Won Opportunities % | This is the ratio of Won Opportunities and Total Opportunities expressed in % | (CountIf(`Opportunity.Status` = 'Won')/Count(`Opportunity.ProspectActivityId`))*100 |
6. Activity Data Source
This data source contains all Activity data in your LeadSquared system. All activities performed in your system, along with all standard activity fields, enriched with their corresponding Lead fields (from the Lead Data Source) and Opportunity Fields (from the Lead Opportunity Source) will be stored in this data source.
Activity Data Source Predefined Metrics
| Metric Name | Logic | Expression |
| # Activities | This gives a count of activities | |
| # Leads with Activities | This gives a count of leads in the activity data source. This will be total leads that have activities created on them. This will not be equal to the total leads in the system as leads with no activity will be missing | CountDistinct(`Lead.ProspectID`) |
7. Task Data Source
This data source contains all Tasks data in your LSQ system. All activities performed in your system, along with all standard tasks fields, enriched with their corresponding Lead fields (from the Lead Data Source) and Opportunity Fields (from the Lead Opportunity Source) will be stored in this data source.
Task Data Source Predefined Metrics
| Metric Name | Logic | Expression |
| Total Tasks Count | This gives a total count of tasks | Count(`Task.UserTaskId`) |
| Pending Tasks Count | Pending tasks are tasks that are yet to be completed and have not crossed their due date. StatusCode = 0 means the task is still incomplete. Along with this, we need to check the due date of the task is after today’s date | CountDistinctIf(`Task.UserTaskId`, `Task.StatusCode` = 0 and (`Task.EndDate` ≥ CurrentDateTime or `Task.DueDate` ≥ CurrentDateTime)) |
| Overdue Tasks Count | Overdue tasks are tasks that are yet to be completed and have crossed their due date. StatusCode = 0 means the task is still incomplete. Along with this, we need to check the due date of the task is before today’s date | CountDistinctIf(`Task.UserTaskId`, `Task.StatusCode` = 0 and (`Task.EndDate` < CurrentDateTime or `Task.DueDate`< CurrentDateTime)) |
| Completed Tasks Count | Completed tasks are tasks that have been marked as complete. StatusCode = 1 means the task is still marked as complete. | CountDistinctIf(`Task.UserTaskId`, `Task.StatusCode` = 1 ) |
| Completed Tasks Today Count | Completed tasks are tasks that have been marked as complete. StatusCode = 1 means the task is still marked as complete. To get Tasks completed today, we need to check if the completed date of a task is equal to today’s date | CountDistinctIf(`Task.UserTaskId`, `Task.StatusCode` = 1 and ToDate(`Task.CompletedOn`) = ToDate(CurrentDateTime)) |
| Due Tasks Today Count | Due tasks today count is overdue tasks whose due date is today’s date | CountDistinctIf(`Task.UserTaskId`, `Task.StatusCode` = 0 and ToDate(`Task.DueDate`) = ToDate(CurrentDateTime)) |
| Due Tasks Tomorrow Count | Due tasks tomorrow count is overdue tasks whose due date is tomorrow’s date | CountDistinctIf(`Task.UserTaskId`, `Task.StatusCode` = 0 and ToDate(`Task.DueDate`) = ToDate(DurationAdd(CurrentDateTime,1,'DAY'))) |
| Due Tasks This Week Count | Due tasks tomorrow count is overdue tasks whose due date falls under the current week | CountDistinctIf(`Task.UserTaskId`, `Task.StatusCode` = 0 and ToWeek(`Task.DueDate`) = ToWeek(CurrentDateTime)) |
| # Tasks Completed On Time | Tasks completed on time are completed tasks where the completed date is before the due date | CountDistinctIf(`Task.UserTaskId`, `Task.StatusCode` = 1 and (`Task.EndDate` ≤ `Task.CompletedOn` or `Task.DueDate` ≤ `Task.CompletedOn` )) |
| # Tasks Not Completed On Time | Tasks not completed on time are completed tasks where the completed date is after the due date | CountDistinctIf(`Task.UserTaskId`, `Task.StatusCode` = 1 and (`Task.EndDate` > `Task.CompletedOn` or `Task.DueDate` > `Task.CompletedOn` )) |
8. Revenue Data Source
This data source contains all your Revenue data which is captured in the standard Sales Activity on LeadSquared. All custom and standard Sales Activity fields, enriched with their corresponding Lead fields (from the Lead Data Source) and Opportunity Fields (from the Lead Opportunity Source) will be stored in this data source.
Revenue Data Source Predefined Metrics
| Metric Name | Logic | Expression |
| # Deals | This gives a count of sales activities | |
| Revenue | This gives a sum of the order value | Sum(`Activity.mx_Custom_2`) |
9. Phone Call Data Source
This data source contains all your Inbound and Outbound Phone Call Activity data in your LeadSquared system. All phone calls performed in your system, along with all system phone call fields, enriched with their corresponding Lead fields (from the Lead Data Source) and Opportunity Fields (from the Lead Opportunity Source) will be stored in this data source.
Phone Call Data Source Predefined Metrics
| Metric Name | Logic | Expression |
| # Calls | This gives a count of calls (Inbound + Outbound) | |
| # Calls Connected | This gives a count of calls with status = Answered (Inbound + Outbound) | |
| # Unique Leads | This gives total lead counts on which calls were attempted (Inbound + Outbound) | |
| # Unique Leads Connected | This gives a total lead count where call status = Answered (Inbound + Outbound) | |
| # Inbound Calls | This gives a count of inbound calls | |
| # Inbound Answered Calls | This gives a count of inbound calls with the status = Answered | |
| # Inbound Missed Calls | This gives a count of inbound calls with the status = Missed | |
| # Outbound Calls | This gives a count of outbound calls | |
| # Outbound Answered Calls | This gives a count of outbound calls with the status = Answered | |
| # Outbound Unanswered Calls | This gives a count of outbound calls with the status = Not Answered | |
| Total Call Duration | This is a sum of call duration | |
| Average Call Duration | This is an average of call duration |
|
| # Voice Messages | These are voice messages count if received | |
| # Call Duration less than 2mins | This gives a count of calls where call duration was < 2 minutes | |
| # Call Duration >=2mins & less than 5mins | This gives a count of calls where call duration was ≥ 2 minutes and < 5 minutes | |
| # Call Duration >=5mins & less than 10mins | This gives a count of calls where call duration was ≥ 5 minutes and < 10 minutes | |
| # Call Duration >=10mins | This gives a count of calls where call duration was ≥ 10 | CountIf(`Activity.mx_Custom_3` ≥ 600) |
10. Report Templates in SIERA Report Builder
To make it easy to build reports, SIERA comes with predefined templates to help users create their custom reports. The templates help users create reports across entities like Leads, Tasks, Activities, Phone Calls, and Activities.
To select the correct report template, you need the following information –
- Scope of the data – The data source and the report template primarily depend on the scope of the data. It refers to whether the report requires solely lead data or if it also requires activity or task data.
- Primary user type – The report you need can also be affected by the user type. Depending on your data source, you may have one or more user types.
- For example, in the Activity Data Source, you will find Lead and Activity Data. The user types available in this data source are –
- Activity Owner
- Activity Creator
- Lead Owner
- For example, in the Activity Data Source, you will find Lead and Activity Data. The user types available in this data source are –
Example Use Case
Consider the following report requirement: “I want to view connected calls and unique leads dialed per agent“
To generate the above report, we need the following information –
- Data scope – The Scope of the data here is the Calling Data which is present in the Phone Call Activities. Thus, the data scope will be Activity Data Source and Phone Call Activity Data Source. As there is a specific Phone Call Activity report, we can use this Data Source.
- Primary user type – Because the reporting requirement is by agents who have made the call, we need the template by Activity Creator (agents).
Any Questions?
Did you find this article helpful? Please let us know any feedback you may have in the comments section below. We’d love to hear from you and help you out!
