[dynamic_help_sidebar root="leads"]

SIERA Expression Builder

/20 Comments/in , /by

1. Feature Overview

SIERA Expression Builder tool lets Admins create their own expressions that can be used to easily retrieve and manipulate data on any report. This is an evolving tool and presently helps users write basic aggregate functions with multi-conditions.

Note: This article will only list the different expressions you can use. To learn how to write expressions, read this article – Writing Expressions using SIERA Expression Builder.

 

2. Prerequisites

SIERA is a paid feature. To enable it for your account, contact your account manager or support@leadsquared.com.

 

3. Expression Builder Rules

  • A field in the expression builder has to be enclosed with backticks. For example, `data_source_name.field_name`.
  • The field name has to be appended with the entity. For example, `Leads.Lead Stage`.
  • The values for a string have to be in single quotes. For example, 'string'.
  • An array of values must be enclosed in square brackets. For example, ['Value 1', 'Value 2'].
  • SIERA stores empty date values as 01-01-1925. If you want to write an expression with null date values excluded, use ToYear(<date field>) > 1925. Similarly, ToYear(<date field>) <= 1925 will filter rows with date values not defined. For example,
    • CountDistinctIf(`Leads.Prospect ID`, ToYear(`Leads.Date of Birth`) > 1925) will return leads who have a defined date of birth.
    • CountDistinctIf(`Leads.Prospect ID`, ToYear(`Leads.Date of Birth`) <= 1925)will return leads where the date of birth is not defined.
  • Clicking on a tab will enter the selected function from the expression builder.

 

4. Types of Expressions

Expressions in SIERA are of 3 types –

  • Aggregated Expressions – An aggregate expression uses an aggregate function to summarize a selected metric. These include expressions like Count, CountDistinct, Sum, Min, Max, and Avg.
    • For example, if we want to get a count of leads, we can use the expression Count(<field name>). 
  • Conditional Expressions – Conditional expressions use aggregate functions along with conditions.
    • For example, if you want to Count leads only if they are from Google and Facebook, using an expression of Count() will not work. In this case, we can use CountIf(<condition>).
  • Calculated Values – Calculated Values are a combination of 2 or more expressions (aggregated or conditional) on arithmetic functions like +, –, *, or /.
    • For example, if we want the percentage of leads from Google and Facebook over total leads, we can use (Countif(<condition>)/Count(<field name>))*100. Calculated Values do not support drill-down in the reports or dashboards. 

 

5. Navigate to Expression Builder

Follow the steps below to navigate to the expression builder:

  1. Click Create Report.
  2. Select a Data Source.
  3. Click the + Add Values button.
  4. Click + Custom Values.

navigating to expression builder on SIERA

This sections below will help you understand the syntax and usage of various functions available for building expressions.

 

6. Operators, Conditions, and Constants

Function & Example Expression
Equal​ – Used to compare if 2 values or fields are equal.

`Leads.Lead Stage` = 'Customer'
Not Equal​ – Used to compare if 2 values or fields are not equal.

`Leads.Lead Stage` != 'Customer'
Less Than​ – Used to compare if a value or field is less than compared to another value or field.

`Leads.Lead Score` < 50
Less Than or Equal​ – Used to compare if a value or field is less than or equal to another value or field.

`Leads.Lead Score` <= 50
Greater Than ​– Used to compare if a value or field is greater than compared to another value or field.

`Leads.Lead Score` > 50
Greater Than or Equal ​–Used to compare if a value or field is greater than or equal to another value or field. 

`Leads.Lead Score` >= 50
in – Used to determine whether the value exists in a list of values.

`Leads.Lead Stage` in ['INVALID', 'DISQUALIFIED']
notin – Used to determine whether the value does not exist in a list of values.

`Leads.Lead Stage` notin ['INVALID', 'DISQUALIFIED']
Text Constant – Text constant to be written as ‘Text’, with quotations.

`Leads.Lead Stage` notin ['INVALID', 'DISQUALIFIED']
Number Constant – Text constant to be written as 50, without quotations.

Leads.Lead Score >= 50
Addition – Used to add two values in your report. Fields (Number type) and constants can be used as operands

Sum(`Leads.Custom 2`) + Sum(`Leads.Custom 3`)
Subtraction – Used to subtract two values in your report. Fields (Number type) and constants can be used as operands. 

Sum(`Leads.Custom 2`) - Sum(`Leads.Custom 3`)
Multiplication – Used to multiply two values in your report. Fields (Number type) and constants can be used as operands. 

(Sum(`Leads.Custom 2`) + Sum(`Leads.Custom 3`)) * 100
Division – Used to divide two values in your report. Fields (Number type) and constants can be used as operands. 

(Sum(`Leads.Custom 2`) + Sum(`Leads.Custom 3`)) / 100
Like – Matches string against SQL LIKE pattern (case-sensitive).

If(`Leads.Lead Stage` LIKE '%Pro%', 'This is Prospect','This is not Prospect')
ILike – Matches string against SQL LIKE pattern (case-insensitive).

If(`Leads.Lead Stage` ILIKE '%pro%', 'This is Prospect','This is not Prospect')

 

7. Logical Expressions

Function & Example Expression
and – An and expression returns true if all of its operands are true. The and expression will work if there is more than 1 expression inside it. 

For example, let’s say you want to return TRUE when a Lead has converted (Stage is Customer) and if the Source is either from Referral or Affiliate.

(<expression1>) and (<expression2>)

(`Leads.Lead Stage` = 'Customer') and (`Leads.Lead Source` in ['Referral','Affiliate'])
or – An or expression returns true if any of its operands are true. The or expression will work if there is more than 1 expression inside it. 

For example, let’s say you want to return TRUE when a Lead has converted (Stage is Customer) or if the Sub-stage is either Converted or Paid.

(<expression1>) or (<expression2>)

(`Leads.Lead Stage` = 'Customer') or (`Leads.Sub Stage` in ['Converted','Paid'])

 

8. Aggregate Expressions

Note:

  • Expression fields should be included in row grouping. Owner Auto ID should be preferred for partitioning.
  • Some of the expressions below support Partition. It divides a value or dataset into separate segments so each segment can be processed independently. This is useful when you want calculations or logic to run within specific groups rather than across the entire dataset.
Function & Example Expression

SUM

The Sum function allows you to calculate the sum of numeric values within a given field.

Example 1 – Create the calculated column which stores the sum of the field mx_Custom_2. We can use the Expression Builder to write the expression.

Sum(<fieldname>)

Sum(`Leads.Custom 2`)

Example 2 – Calculates a running or total sum of Lead Score counts within each Owner’s partition.

Sum(field, PartitionBy(...), OrderBy(...))

Sum(Count(`Leads.Lead Score`), PartitionBy(`Leads.Owner Auto ID`))
SumIf

The SumIf function allows you to calculate the sum of numeric values within a given field along with some conditions.

Example 1 – Create the calculated column which stores the sum of the field mx_Custom_2 when Stage is “Customer”. We can use the Expression Builder to write the expression as given.

SumIf(<fieldname>,<condition>)

SumIf(`Leads.Custom 2`, Stage = 'Customer')

Example 2 – Sums the count of Lead Score values only for rows where Lead Score is greater than 0, grouped by Owner.

SumIf(field, condition, PartitionBy(...))

SumIf(Count(`Leads.Lead Score`) , `Leads.Lead Score` > 0, PartitionBy(`Leads.Owner Auto ID`))
Count

The Count function lets you count the number of occurrences of a specific value or field within a given range.

​​​​​Example 1 – Create the calculated column which stores the count of the leads.

Count(`Leads.Prospect Id`)

Example 2 – Counts the number of rows in each Team partition after computing the summed Lead Score for each row.

Count(Sum(Leads.Lead Score), PartitionBy(Users.Team))
CountIf

The CountIf function allows you to count the number of occurrences that meet a specific condition.

Example 1 – Create a calculated column that stores the count of the leads when the lead stage is ‘Prospect’.

CountIf(`Leads.Lead Stage` = 'Prospect')

Example 2 – Counts how many leads have Status = Converted within each Owner partition.

CountIf( Lead.Status='Converted', PartitionBy(Lead.Owner Id))
CountDistinct

The CountDistinct function allows you to count the unique number of occurrences that meet a specific condition.

Example 1 – Create a calculated column that stores the count of unique leads.

CountDistinct(`Leads.Prospect Id`)

Example 2 – Calculates the percentage contribution of distinct Prospect IDs relative to the overall total across all partitions (Used in a % Grand Total scenario).

(Countdistinct(Leads.Prospect ID) / Sum(Countdistinct(Leads.Prospect ID), PartitionBy()))*100

% Grand Total Usecase
CountDistinctIf

The CountDistinctIf function allows you to count the distinct number of occurrences that meet a specific condition.

Example 1 – Create a calculated column that stores the count of distinct leads when the lead stage is ‘Prospect’.

CountDistinctIf(`Leads.Prospect Id`, `Leads.Lead Stage` = 'Prospect')

Example 2 – Counts distinct Prospect IDs where the Status is Converted, grouped by Owner.

CountDistinctIf(Lead.Prospect ID, Lead.Status='Converted', PartitionBy(Lead.Owner Id))
Max

The Max function allows you to calculate the maximum of numeric values within a given field. 

Example 1 – Create the calculated column which stores the maximum of the field mx_Custom_2.

Max(<fieldname>)

Max(`Leads.Custom 2`)

Example 2 – Returns the maximum summed Lead Score within each Owner’s partition.

Max(field, PartitionBy(...))

Max(Sum(`Leads.Lead Score`), PartitionBy(`Leads.Owner Auto ID`))
Min

The Min function allows you to calculate the minimum of numeric values within a given field. 

Example 1 – Create the calculated column which stores the minimum of the field mx_Custom_2.

Min(<fieldname>)

Min(`Leads.Custom 2`)

Example 2 – Returns the minimum summed Lead Score within each Owner’s partition.

Min(field, PartitionBy(...))

Min(Sum(`Leads.Lead Score`), PartitionBy(`Leads.Owner Auto ID`))
Avg

The Avg function allows you to calculate the average of numeric values within a given field. 

Example 1 – create the calculated column which stores the average of the field mx_Custom_2. 

Avg(<fieldname>)

Avg(`Leads.Custom 2`)

Example 2 – Returns the average summed Lead Score within each Owner’s partition.

Avg(field, PartitionBy(...))

Avg(Sum(`Leads.Lead Score`), PartitionBy(`Leads.Owner Auto ID`))
MaxIf

The MaxIf function allows you to calculate the maximum of numeric values within a given field along with some conditions.

Example 1 – Create the calculated column which stores the maximum of the field mx_Custom_2 when Stage is ‘Prospect’.

MaxIf(<fieldname>,<condition>)

MaxIf(`Leads.Custom 2`, `Leads.Lead Stage` = 'Prospect')

Example 2 – Returns the maximum summed Lead Score but only for rows where Lead Score is greater than 0, grouped by Owner.

MaxIf(field, condition, PartitionBy(...))

MaxIf(Sum(`Leads.Lead Score`) , `Leads.Lead Score` > 0, PartitionBy(`Leads.Owner Auto ID`)
MinIf

The MinIf function allows you to calculate the minimum of numeric values within a given field along with some conditions.

Example 1 – Create the calculated column which stores the minimum of the field mx_Custom_2 when Stage is ‘Prospect’.

MinIf(<fieldname>,<condition>)

MinIf(`Leads.Custom 2`, `Leads.Lead Stage` = 'Prospect')

Example 2 – Returns the minimum count of Lead Score only for rows where Lead Score is greater than 0, grouped by Owner.

MinIf(field, condition, PartitionBy(...))

MinIf(Count(`Leads.Lead Score`) , `Leads.Lead Score` > 0, PartitionBy(`Leads.Owner Auto ID`))
AvgIf

The AVGIF function allows you to calculate the average of numeric values within a given field along with some conditions.

Example 1 – Create the calculated column which stores the average of the field mx_Custom_2 when Stage is ‘Prospect’. 

AvgIf(<fieldname>,<condition>)

AvgIf(`Leads.Custom 2`, `Leads.Lead Stage` = 'Prospect')

Example 2 – Returns the average count of Lead Score only for rows where Lead Score is greater than 0, grouped by Owner.

AvgIf(field, condition, PartitionBy(...))

AvgIf(Count(`Leads.Lead Score`) , `Leads.Lead Score` > 0, PartitionBy(`Leads.Owner Auto ID`))
ArgMax

Returns the value of target_field for which selector_field is maximum.

For example, the expression below returns the Lead Source of the lead that has the most recent Updated On date.

ArgMax(target_field, selector_field)

ArgMax(Lead.Source, Lead.Updated On)
ArgMin

Returns the value of ‘target_field’ for which ‘selector_field’ is minimum.

For example, the expression below returns the Lead Source of the lead that has the earliest Updated On date.

ArgMin(target_field, selector_field)

ArgMin(Lead.Source, Lead.Updated On)

ArgMaxIf

Returns the value of ‘target_field’ for which ‘selector_field’ is maximum where condition holds.

For example, the expression below returns the Lead Source of the most recently updated lead, but only among leads where Status = Converted.

ArgMaxIf(target_field, selector_field, condition)

ArgMaxIf(Lead.Source, Lead.Updated On, Lead.Status = 'Converted')

ArgMinIf

Returns the value of ‘target_field’ for which ‘selector_field’ is minimum where condition holds.

For example, the expression below returns the Lead Source of the earliest created lead, but only among leads where Status = New.

ArgMinIf(target_field, selector_field, condition)

ArgMinIf(Lead.Source, Lead.Updated On, Lead.Status = 'New')

Any

Returns any arbitrary value of the field from a group.

For example, the expression below returns any one City value from the group.

Any(field)

Any(Lead.City)

AnyHeavy

Returns the most frequent value (>50%) using heavy-hitters algorithm.

For example, the expression below returns the most frequently occurring City, but only if it appears in more than 50% of the records.

AnyHeavy(field)

AnyHeavy(Lead.City)

AnyLast

Returns the last-seen value based on processing order.

For example, the expression below returns the last-seen Stage value based on the order in which records were processed.

AnyLast(field)

AnyLast(Lead.Stage)

 

9. Date Time Expressions

Function & Example Expressions
DurationAdd

The DurationAdd function allows you to add any duration to a particular date field.

Allowed Units – second, minute, hour, day, week, month, quarter, year

Returns – Added Duration (DateTime)

For example, you can create the calculated expression which counts the lead which came into the system 4 months ago and the current stage is not ‘Customer’.

DurationAdd(<date>,<value>,<Unit>)

CountIf(`Leads.Lead Stage` != 'Customer' and DurationAdd(`Leads.Created On`,4, 'MONTH') < CurrentDateTime)
DurationSubtract

The DurationSubtract function allows you to subtract any duration from a particular date field.

Allowed Units – second, minute, hour, day, week, month, quarter, year

Returned Value – Subtracted Duration (DateTime)

For example, you can create the calculated expression which counts the lead which came into the system 4 months ago and the current stage is not ‘Customer’.

DurationSubtract(<date>,<value>,<Unit>)

CountIf(`Leads.Lead Stage` != 'Customer' and `Lead.Created On` < DurationSubtract(CURRENTDATETIME, 4, 'MONTH'))
CurrentDateTime

The CurrentDateTime function returns the current date and time.

For example, you can create a calculated column that stores -4 months from date field mx_Custom_2.

CurrentDateTime

DurationSubtract(CurrentDateTime, 4, 'MONTH'))
ToHour

The ToHour function allows you to extract the hour component of any datetime(timestamp) field.

Returned Value: Hour Component (Number) eg: 23

For example, you can write a condition where you want to count the leads which came into the system at 11 PM.

ToHour(<date>)

CountIf(ToHour(`Leads.Created On`)='17:00')
ToYear

The ToYear function allows you to extract the year component of any datetime(timestamp) field.

Returned Value: Year Component (Number) eg: 2023

For example, you can write a condition where you want to check if the year component of the datetime field is equal to 2023.

ToYear(<date>)

ToYear(`Leads.Created On`)= 2023
ToYearWeek

The ToYearWeek function allows you to extract the week and year components of any datetime(timestamp) field.

Returned Value: Week and Year Component (1-53 Number) Eg: 202345 (45th Week of 2023)

For example, you can write a condition where you want to check if the week and year component of the datetime field is equal to the 45th Week of 2023.

ToYearWeek(<date>)

ToYearWeek(`Leads.Created On`)=47
ToMonthOfYear

The ToMonthOfYear function allows you to extract the month component of any datetime(timestamp) field.

Returned Value: Month of the Year (1-12 Number) eg: 1 (for January)

For example, you can write a condition where you want to check if the month component of the datetime field is equal to January.

ToMonthOfYear(<date>)

ToMonthOfYear(`Leads.Created On`)='0002-02-01'
ToDateOfYear

The ToDateOfYear function allows you to extract the date component of any datetime(timestamp) field.

Returned Value: Date of the Year (1-366 Number) eg: 32

For example, you can write a condition where you want to check if the date component of the datetime field is equal to 2nd Feb.

ToDateOfYear(<date>)

ToDateOfYear(`Leads.Created On`)='2023-02-02'
ToQuarterOfYear

The ToQuarterOfYear function allows you to extract the quarter component of any datetime(timestamp) field.

Returned Value: Quarter of the Year (1-4 Number) eg: 2

For example, you can write a condition where you want to check if the hour component of the datetime field is equal to 2nd Quarter.

ToQuarterOfYear(<date>)

ToQuarterOfYear(`Leads.Created On`)='Q2'
ToMonth

The ToMonth function allows you to extract the month and year components of any datetime(timestamp) field.

Returned Value: yyyy-mm-01 (String) eg: ‘2023-02-01’

For example, you can write a condition where you want to count the leads created in the month of February 2023.

ToMonth(<date>)

CountIf( ToMonth(`Leads.Created On`) = '2023-02-01' )
ToQuarter

The ToQuarter function allows you to extract the quarter and year components of any datetime(timestamp) field.

Returned Value: YYYY QX (String) eg: ‘2023 Q2’

For example, you can write a condition where you want to count the leads created in the 2nd Quarter of 2023.

​​​​​ToQuarter(<date>)

CountIf( ToQuarter('Leads.Created On') = '2023 Q2' )
ToDayOfWeek

The ToDayOfWeek function allows you to extract the day of the week component of any datetime(timestamp) field.

Returned Value: 1-7 (Number) Range: (1-7: Monday = 1, Tuesday = 2, …, Sunday = 7) eg: ‘6’ for Saturday

For example, you can write a condition where you want to count the leads created on Saturdays.

ToDayOfWeek(<date>)

CountIf( ToDayOfWeek(`Leads.Created On`) = 6 )
ToWeek

The ToWeek function allows you to extract the week and year components of any datetime(timestamp) field.

Returned Value: YYYYWW (Number) eg: 202347

For example, you can write a condition where you want to count the leads created on the 47th Week of 2023.

ToWeek(<date>)

CountIf( ToWeek(`Leads.Created On`) = 202347 )
DateUnitDiff

Returns the count of the specified time unit boundaries crossed between the start and end dates. This can be used to create TAT between 2 date fields.

Returned Value: (Number) eg: 43

DateUnitDiff(<start date>, <end date>, <interval>)

Accepted Intervals – 

  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR


For example, consider your data source has 2 fields – CreatedOn and PaymentDate which capture the lead created and the payment date of the lead. To create a TAT report (in days) on the PaymentDate from the lead created, we can use the expression below –
Avg(DateUnitDiff(`Leads.Created On`, `Leads.Payment Date`, 'DAY'))

The above expression will calculate the TAT for each grouping on average. For cases when there is no value in the date field, the expression builder will take the value of 01-01-1925. To exclude cases where there is no payment done, use the syntax below-

AvgIf(DateUnitDiff(`Leads.Created On`, `Leads.Payment Date`, 'DAY'),ToYear(`Leads.Payment Date`) > 1925)
ToDate

Returns the date component from a datetime field.

Returned Value: (String) eg: 2023-10-03

For example, you can write a condition where you want to count the leads created on 2023-10-03

ToDate(<date>)

CountIf(ToDate(`Leads.Created on`) = '2023-10-03')

ToDayOfTheMonth

Extracts day of month (1–31).

For example, the expression below returns the day of the month (1–31) from the Created On date of the lead.

ToDayOfTheMonth(date)

ToDayOfTheMonth(Lead.Created On)

ToDayOfYear

Extracts day number in year (1–366).

For example, the expression below returns the day number in the year (1–366) extracted from the Audit Event Time field.

ToDayOfYear(date)

ToDayOfYear(Audit.Event Time)

 

10. String and Column Functions

Position

Returns 1-based index of substring; 0 if not found.

For example, the expression below Checks whether the substring “Pro” exists in the Lead Stage field. If found (i.e., position > 0), it returns “This is Prospect”; otherwise, it returns “This is not Prospect”.

Position(string_field, substring)

If(Position(`Leads.Lead Stage`,'Pro')>0  , 'This is Prospect','This is not Prospect')

Concat

Concatenates multiple fields into a single string.

For example, the expression below combines the First Name and Last Name fields into a single full name string, inserting a space between them.

Concat(field1, field2, ...)

Concat(Lead.FirstName, ' ', Lead.LastName)

JSONExtractString

Extracts string value from a JSON object by key.

For example, the expression below extracts the “city” value from the JSON object stored in the AdditionalInfo field.

JSONExtractString(json, key)

JSONExtractString(Lead.AdditionalInfo, 'city')

RegexpExtract

Extracts regex capture group by index.

For example, the expression below extracts the portion of the email before the ‘@’ symbol using a regular expression, and returns the first captured group (e.g., the username part of the email).

RegexpExtract(str, pattern, index)

RegexpExtract(Lead.Email, '([^@]+)@', 1)

 

11. Window Functions

Note: Expression fields should be included in row grouping. Owner Auto ID should be preferred for partitioning.

Lag

Returns value from a previous row in partition.

For example, the following expression returns the Lead Stage from the previous row within each Owner’s partition, based on the stage’s ascending order.

Lag(field, offset, PartitionBy(...), OrderBy(...))

Lag(Leads.Lead Stage , 1 , PartitionBy(Leads.Owner Auto ID), OrderBy(Leads.Lead Stage ASC)) 
Lead

Returns value from a following row in partition.

For example, the following expression returns the Prospect Creation Date from the next row within each Owner’s partition, ordered by creation date in ascending order.

Lead(field, offset, PartitionBy(...), OrderBy(...))

Lead(Leads.Prospect Creation Date , 1 , PartitionBy(Leads.Owner Auto ID), OrderBy(Leads.Prospect Creation Date ASC))

RowNumber

Assigns sequential row number within partition.

For example, the follwing expression assigns a sequential row number to each record within the Owner partition, ordered by Lead Stage.

RowNumber(PartitionBy(...), OrderBy(...))

RowNumber(PartitionBy(`Leads.Owner Auto ID`), OrderBy(`Leads.Lead Stage` ASC))

FirstValue

Returns first value in ordered partition.

For example, the follwing expression returns the first Lead Stage value in each Owner’s partition, based on ascending stage order.

FirstValue(field, PartitionBy(...), OrderBy(...))

FirstValue(Leads.Lead Stage , PartitionBy(Leads.Owner Auto ID), OrderBy(Leads.Lead Stage ASC)) 

LastValue

Returns last value in ordered partition.

For example, the following expression returns the last Lead Stage value in each Owner’s partition, based on ascending stage order.

LastValue(field, PartitionBy(...), OrderBy(...))

LastValue(Leads.Lead Stage , PartitionBy(Leads.Owner Auto ID), OrderBy(Leads.Lead Stage ASC)) 

 

12. Custom Dimensions

Function & Example Expressions
If

Returns a string based on a condition. The condition can be nested.

Returned Value: (String) e.g., ‘Hot’, ‘Medium’, ‘Warm’, ‘Cold’

For example, let’s assume we want to classify leads based on their ProspectStage into ‘Hot’, ‘Medium’, ‘Warm’, or ‘Cold’ categories.

If(<condition>, <value_if_true>, <value_if_false>)

If (`Leads.Lead Stage`= 'Customer', 'Hot', If (`Leads.Lead Stage`= 'Prospect', 'Medium', 'Cold' ) )

 

Any Questions?

If you have any questions related to Expression Builder that weren’t answered here, please leave comments below. We’ll be happy to help!

WhatsApp Reports

/4 Comments/in , , , /by

1. Feature Overview

These reports offer insights into your business’s engagement level with leads you’ve communicated with through WhatsApp. They include various data points, such as the total number of messages sent, messages sent through automations, and both incoming and outgoing messages. The following reports are available –

  • Usage Reports – This report shows logs of outgoing messages sent and incoming messages received, categorized by source (bulk, automation, one-to-one, and Converse). It also includes template-wise logs, based on the templates available in your account.
  • Delivery Reports – In this report, you can access individual message logs for both incoming and outgoing messages. For outgoing messages, it displays message status (Awaited, Sent, Delivered, Read, Failed, and Others) along with general information like transaction ID and lead ID.

To access these reports, navigate to Apps>WhatsApp Reports.

LeadSquared WhatsApp Reports

 

2. Usage Reports

The Usage Report contains logs of outgoing messages sent and incoming messages received. You can view and filter this report using the following data points –

  • Templates – View and filter the report based on the templates used for sending outbound messages.
  • Message Source/User Wise – Filter the reports based on the following message sources –
    • Automation – When WhatsApp message campaigns are triggered to your leads through an automation.
    • Bulk – When WhatsApp messages are sent to multiple leads either by selecting leads from the Manage Leads page or by sending messages to all leads in a list.
    • One-to-one – For individual WhatsApp messages sent to specific leads.
    • Converse – When WhatsApp messages are sent to leads through Converse.
  • Select Date – Choose the time interval for which you want to view the sent messages. The date range can be up to 60 days in the past.
  • Cumulative – This option renders the report as a pie chart.
  • Timeline – This option renders the report as a bar graph. When you select this option, the 10 most used templates will automatically be displayed in the graph.

In addition to these filters, you can also perform the following actions –

  • Refresh – Updates the report with the latest data when you refresh it.
  • Export – Provides two options for exporting the report –
    • Summary Report – Contains a brief summary of all the details present on your dashboard.
    • Detailed Report – Contains a detailed breakdown of all the details available on your dashboard.
Note: When a user exports a report, they’ll receive a download link option via email, sent to the email ID of the logged-in user.

LeadSquared WhatsApp Reports

 

3. Delivery Reports

The Delivery Report contains individual message logs for all Incoming and Outgoing Messages.

Under Outgoing Messages, you can filter the report by the following data points –

Message Source – Filter the report based on the following message sources –

  • Automation
  • Bulk
  • One-to-one
  • Converse

3.1 Sent Successfully

Messages appear under this category when they have been successfully sent from LeadSquared to the WhatsApp provider.

WhatsApp Delivery Report

  • Message Status – Filter each message source by the following message status –
    • Awaited – When the message is pushed successfully to the WhatsApp provider, but we are yet to receive the delivery report. In this case, the WhatsApp provider may have delivered the message to the lead but LeadSquared has not received any delivery update/report from the provider.
    • Sent – When WhatsApp servers receive the outbound message, and it is successfully sent to the lead. However, due to certain factors (like the lead’s mobile not being connected to the internet), the message is yet to be delivered to the lead.
    • Delivered – When the outbound message is successfully delivered to the lead.
    • Read – When the lead reads the received message.
    • Failed – If the outbound message remains in WhatsApp’s servers for more than 30 days without being delivered to your lead, it is considered as failed.
    • Other – Certain service providers send statuses (like error, etc.) outside of the ones listed above. You’ll be able to view those statuses under this column.
  • Once you filter by message status, click on the template name to view the following details –
    • General Info – Displays the “From” number, transaction, lead, and sender ID, the template used for the message, and the date on which the message was sent. It also contains a masked version of the lead’s phone number.

WhatsApp Delivery Reports

    • Delivery – Shows the message delivery status and time for messages with Awaited, Sent, Delivered, and Read statuses.

LeadSquared WhatsApp Delivery Reports

    • Integration – Displays the following details for messages with Awaited, Sent, Delivered, and Read statuses –
      • Activity Status – When a WhatsApp message is sent, a LeadSquared activity is created. This section contains the activity created and updated status, status description, and the message sent activity’s ID.
      • Converse Status – If Converse is enabled on your LeadSquared account, and it’s integrated with the WhatsApp connector, you can view the Converse activity created and updated status, status description, and the message’s Converse ID.
      • Automation Details – If your message was sent using an automation, you can view the Automation ID and Automation Action ID.
    • Provider APIs – Your WhatsApp service provider integrates their APIs with LeadSquared’s WhatsApp connectors to send and receive messages. Click to view the API request sent from LeadSquared and the response received from the WhatsApp provider. You can also copy the cURL data to your clipboard.

WhatsApp Delivery Report

Here’s an example of navigating the report –

LeadSquared WhatsApp Reports

Under Incoming Messages, you can view the following details –

  • By default, you can view the –
    • Total messages received
    • Messages received successfully
    • Messages that weren’t received due to various errors
  • When you click on a message, you can view the following details –
    • General Info – Displays the “From” and “To” numbers, transaction, lead, and provider message ID, the lead who sent the message, and the date on which the message was sent. It also contains a masked version of the lead’s phone number.
    • Reception – Displays the message receipt status, and a status description.
    • Integration – Displays the following details for messages with Awaited, Sent, Delivered, and Read statuses –
      • Activity Status – Contains the activity created and updated status, status description, and the message sent activity’s ID.
      • Converse Status – If Converse is enabled on your account and integrated with the WhatsApp connector, you can view the Converse activity created and updated status, status description, and the message’s Converse ID.

LeadSquared WhatsApp Connector

3.2 Errors

Messages under this category threw errors either at the LeadSquared end or at the WhatsApp provider’s end.

WhatsApp Delivery Reports

Click the message instance under the Template/Message column or click Error under the Sent Status column to view more details –

WhatsApp Delivery Reports

 

FAQs

In the downloaded WhatsApp report, what’s the difference between the “Sent Status” and “Sent” columns?

LeadSquared WhatsApp Reports

When “Sent Status” = Yes, it means that the message has been successfully pushed from LeadSquared’s end to the WhatsApp Provider (for example, GupShup, Kaleyra, etc.) When “Sent” = Yes, it means that WhatsApp’s servers received the outbound message, and it is successfully sent to the lead. However, the message is yet to be delivered to the lead’s phone (e.g., the lead’s mobile might not be connected to the internet). It’s equivalent to a single tick when we send a WhatsApp message.

 

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!

Location History Tracker Report

/0 Comments/in , , , /by

1. Feature Overview

Monitor and track the daily activities performed by your field sales users using the Location History Tracker Report. In this report, you can also view a comprehensive trail of your users’ location coordinates received via the LeadSquared Mobile App throughout the day.

Based on the location coordinates received and the activities performed during the day, you can

  • View the entire route taken by the user from the moment they check-in till check-out. 
  • View the total distance travelled by the field sales user for any given date.
  • View the user’s last recorded location. This allows you to determine where the user was located and at what time they were last observed to be moving or engaged in activities before they eventually checked out for the day. This data is only visible for the present day i.e., Today. 

 

2. Prerequisites

  • You must have the LeadSquared Mobile app installed on your device, and location permission access must be enabled. To know more, please refer to Location Tracking on LeadSquared Mobile.
  • Enable Location Insights on your account. To do so, contact your account manager or write to us at support@leadsquared.com.
  •  Admins must enable location tracking permissions for their users. To do so, navigate to Settings>Mobile App>Permissions. Under Location Tracking, enable all the permissions.
  •  Enable user check-in in your account for location tracking and distance calculation. To do so, navigate to Settings>Users and Permissions>User Check-in and enable the Show check-in widget to web users setting.
  •  All mobile users must Check-In/Check-Out daily for the system to effectively track their location and calculate the distance travelled during the day. Distance calculation will not be initiated for the day if the mobile user has not checked in.
  • To get data with highest location accuracy, refer to Measures to Ensure Higher Distance Computation Accuracy in Mobile App.

 

3. Location History Tracker Report

To view the Location History Tracker Report, navigate to Reports>Reports Home>Field Sales Tracking>Location History Tracker. On this page, based on your user role and organisational hierarchy, you can view the location history of your users/teams/groups for the selected date.

  • To select a user, team or sales group, click the Filter By dropdown and select the relevant option.
  • To choose a user, team or sales group from the available options, click the Choose dropdown and select the relevant option.
  • To view a user’s detailed location history on the selected date, click on the user card.
  • To filter out the locations where the user checked-in, checked-out, or recorded activities, click filter icon.
  • The time the user was last observed to be moving or engaged in activities before they eventually checked out for the day is displayed below the date range filter. This data is only visible for the present day i.e. Today.

Note: 

  • No location data or distance will be captured for the days the user has not checked in on mobile.
  • The system will store and display only up to 90 days’ location data for a single tenant while accessing the report.

LeadSquared - Location history tracker

 

4. Settings

To view the settings, on the bottom left corner of the page, click mobile settings icon. On this page, you can configure the following advanced setting –

  • Metric for distance – You can set the metric for distance as Kilometres and metres (km, m) or Miles and yards (mi, yd). Your selection will be applied to all distance related metric across journeys and reports.

Note: 

  • LeadSquared’s Proprietary Distance Computation Algorithm (Recommended) utilizes location coordinates from the mobile device of a checked-in user to estimate the distance travelled fairly accurately for any given day.
  • The Google Direction Maps API records the latitude and longitude coordinates and returns the distance travelled by your users, by assuming a specific path of travel.

Leadsquared - distance travelled report advanced settings

 

Any Questions?

We’d love to answer your questions or hear your unique use cases. Feel free to share your experiences in the comments section below.