Writing Expressions using SIERA Expression Builder

1. Feature Overview

This article helps you understand the process behind writing Expression through the SIERA Expression Builder. Admins can create custom expressions that can be used to easily retrieve and manipulate data on any report.

Note: This article will only explain the expression-building process. To find a list of expressions you can use, read this article – 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. Filter Hierarchy in SIERA

Before you start writing expressions in SIERA, it is important to understand how filters work in SIERA and their hierarchy.

SIERA supports filters at the data level. Sometimes, you might expect SIERA to execute filters in one order, but the order of operations dictates that they be executed in a different order – which gives you unexpected results.

SIERA follows the current filter hierarchy from top to bottom –

4. Example Use Case

We’ll use a couple of use cases to showcase the process –

4.1 Example 1

Build a report that gives total leads and conversions from all Sources in the last month.

Data Source – We’ll use the Lead Data Source to build our report. The Lead Data Source has all the lead information but you can’t filter the Data Source here.

Report Filter – We’ll need to filter for last month’s leads. We can use the filter of Created On (this field stores the lead created date) and set the value to Last Month. This is our 2nd level of filter and will be applied first (as there is no Data Source filter). Using this filter will bring only leads created in the last month in the query and a lead not matching the criteria will be ignored in the report.

Setting Dimensions – Now add Lead Source as a Row Grouping. This step adds each Lead Source as a row. This is our 3rd level of filter. This filter will be applied after the Data Source Filter and Report Filter. And, will be applied in each row. For instance, in the below screenshot, the first row is ‘Trade Show’. Any measure added will be filtered for ‘Trade Show’ in the corresponding row.

 

Adding Measures – Now, in the values section let’s add our measures. We will add the following measures –

1. Total Leads – CountDistinct(`Lead.ProspectID`)
2. Leads Converted – CountDistinctIf(`Lead.ProspectID`, `Lead.ProspectStage` in ['Paid', 'Customer'])

The first expression has a count function and no condition (this means no filter in this expression). While the second expression has a condition of Lead.ProspectStagein ['Paid', 'Customer'](this has a filter of Lead Stage either Paid or Customer).

All of the filters applied in the data source are in the order of –

1. Data Source Filter – No filters (all lead data)
2. Report Filter – Leads created in the last month
3. Dimension Filter – Each row is filtered by a Lead Source
4. Measure Filter – The first measure has no filter while the second measure has a filter of the lead stage. This is applied at the end of each row.

4.2 Example 2

Let’s consider the above example, but focus on leads from India.

Let’s say this data is stored in a lead field mx_Country – to get the report we need to apply this filter. This can be applied as a Report Filter, Dimension Filter, or Measure Filter (currently custom data sources cannot be created, hence we cannot add a filter at the Data Source level).

5. Writing an Expression

Now that we understand the order of operation of filters, we can begin to create 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. 

For more details on the aggregated functions supported and their syntax, you can read the Expression Builder – functions and syntax page.

6. Count vs CountDistinct

Consider this table as an example –

The table has data on Phone calls on leads. Phone calls can be of 2 types – Outbound and Inbound, and multiple calls can be done on a lead. Since each row represents one call, the values in the Activity Id column are unique – while ProspectId can be repeated (as multiple activities can be done on a lead).

Now, if we have to count the total calls made, we can use either of the following expressions –

Count(`Activity Id`) or CountDistinct(`Activity Id`)

If we want to count unique leads where the calls have been made, we will have to use Count Distinct –

CountDistinct(`ProspectId`)

7. Counting Leads, Activities, Tasks and Opportunities in SIERA

• To count leads, we use Prospectid field
  • Display name – Prospect Id
  • Schema name – ProspectId
  • Entity – Lead
  • Example – Count(Lead.ProspectId)
• To count tasks, we use ProspectActivityId field
  • Display name – TaskId
  • Schema name – UserTaskId
  • Entity – Task
  • Example – Count(Task.UserTaskId)
• To count activities, we use ProspectActivityId field
  • Display name – Activity Id
  • Schema name – ProspectActivityId
  • Entity – Activity
  • Example – Count(Activity.ProspectActivityId)
• To count opportunities, we use ProspectActivityId field
  • Display name – Opportunity Id
  • Schema name – ProspectActivityId
  • Entity – Opportunity
  • Example – Count(Opportunity.ProspectActivityId)

Here’s why metafields are different –

Take an example of a field like City. Let’s say we change a dropdown option from Madras to Chennai. Changing the name will not change the field value for all the leads where City was set to Madras.

However, take Activity Type for instance. If we were to change the name of an activity from Meeting to Physical Meeting, this would change the activity name in all places where the activity was Meeting to Physical Meeting.

This happens because in the database, the value of Activity Type against each lead is not the display name, but a different field –

In the above example, the activity table has a field – ActivityEvent which is mapped to the display name of the Activity in a separate table. This is an example of a metafield. This is why changing the display name of the activity changes the values across the system, however, changing the value of a field like Lead Stage will not.

In the system data sources provided in SIERA, the meta fields have been handled in fields and groupings. This means, that if we search for Activity Type in an activity data source, we will find the fields.

However, the metafields work differently in Expression Builder. Expression Builder works on the core database which doesn’t contain metafields display names.

Let’s say we want to write an expression that counts activities when the Activity is Auction and Interview Scheduled. As there is no field called Activity Type in the core database, we can’t use the condition of ActivityType. Instead, we need to use the condition through their metafields.

From the table above, we now know the metafield for Activity Type is ActivityEvent. So the expression will be –

CountDistinctIf(`Activity.ProspectActivityId`, `Activity.ActivityEvent` in [202, 103])

We are equating ActivityEvent with 202 and 103. To get the values corresponding to each Activity Type, navigate to Settings>Lead>Custom Activities & Scores and look for the column Code against the Activity Type.

Similarly for Opportunity Type, you can go to Settings>Opportunities>Opportunity Types and use the column Code for writing expressions.

• Lead Owner
• Lead Created By
• Activity Owner
• Activity Created By
• Opportunity Owner
• Opportunity Created By
• Task Owner

To write expressions with user IDs, you can use the following mapping –

To get the values for the user IDs, export the user data from Manage Users, and refer to the column User Id.

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!