How We Designed the Salesforce Extension

Understand how the Salesforce Extension was built and use the design principles to extend your Visier solution.

Overview

Visier Extensions provide an example of the content you can build in Visier to extend your solution. In this guide, we'll walk you through the design decisions that were considered by Visier experts when building the Salesforce Extension. Understanding the design decisions behind Visier Extensions is key to building your own extensions to the analytic model. If you have additional use cases beyond the out-of-the-box content in Visier, examine the design decisions to learn how to extend the analytic model with new objects.

If you aren't familiar with Visier's analytic model, take a moment to read Understand Visier's Analytic Model.

Design considerations

In this guide, we'll explore the following key design considerations:

• Determine your business case
• Figure out what data you need to load
• Decide what objects to create
• Make calculation decisions

Determine your business case

To start the design process, we must understand what business case we are trying to solve. Listing the business questions we want to answer helps us shape the design of the content and add connections between each analytic object. Frame these as a sentence that can be answered with a numerical value; for example, how many employees are in my organization?

In this case, we want to track and analyze the sales pipeline. Because the Visier solution already has employee data, we can easily bring in sales pipeline data and connect it to the Employee data of the sales representatives in Visier. By building new objects to analyze sales data, we can independently extend Visier's analytic model to answer questions about the relationship between sales representatives and sales performance. We want to answer the following questions:

• Who in the team is building the most sales pipeline and how are they doing it?
• How has the number of closed-won deals changed for each sales representative over the last 6 months?
• Will we close enough deals?
• How many sales representatives are we hiring?
• What stages are our current sales opportunities in?
• How much time does an opportunity spend in each stage of the pipeline?
• How do opportunities progress or regress at each stage of the sales cycle?

Figure out what data you need to load

Now that we have the business questions we are trying to solve, we can move on to determining what data we need to load. We can do this by thinking about the data needed to answer the key business questions we identified.

Let’s take a look at the first question: Who in the team is building the most sales pipeline and how are they doing it?

To answer this question, we need the following data:

• Sales opportunity information: The number of opportunities that are newly created and their monetary value (so we can measure revenue). Additionally, we can look at what stage the opportunity is in and whether it is still active.

• Sales activity information: The number of calls, emails, and meetings that the sales team had. This data is linked to each opportunity. More activity could mean a more robust pipeline if it leads to measurable results.

Now that we know we need sales opportunity and activity data, we need to decide which tables to extract from Salesforce. To do that, we need to understand the source data. Salesforce has a list of standard objects and their standard fields we can reference. For more information, see "Standard Objects" in Object Reference for the Salesforce Platform.

Salesforce provides the latest state of each record in one table, as well as a history table that contains all changes of any attribute. For example:

• Opportunity
• OpportunityHistory

During setup, both tables (latest state and history) will be loaded to track the full record history. However, initial data loads may only cover a limited time frame, such as the past 5 years or, for the first load, the past 3 months. Therefore, we need to verify the accuracy of the numbers and ensure there are no unexpected spikes in the first month.

After familiarizing ourselves with the source data from Salesforce, we determined that we need to extract data from the following tables:

• Account
• AccountHistory
• Contact
• ContactHistory
• Opportunity
• OpportunityFieldHistory
• OpportunityHistory
• Task
• User

If you want to further build the Salesforce Extension, then the tables you bring in will depend on your use case, and what data you have available.

Decide what objects to create

Now that we know what data we need to load, we can decide what objects to create. An object can be an analytic object (like a subject or an event), a metric, or an attribute (like a property or dimension).

Subjects, events, and overlays

First, ask yourself what kind of container you should design the sales data as. In Visier, analytic objects (subject, events, and overlays) are containers with attributes. Is the sales data best designed as a subject, an event, or an overlay? To figure that out, we can review the following guidelines.

Create a subject if:

• The entity has a lifespan and you can analyze changes in its state over time.
• The entity has events associated with it at different points in time.
• The entity's state is stable for some period of time.
• The entity has a history over time.

Create an event if:

• The data observes transactional behavior that happens to an entity at a particular time instant.

Create an overlay if:

• The object stores aggregate values for a population. It doesn't store individual members' attribute values.
• The object contains aggregated data that Visier can compare against metrics and concepts.
• The object isn't connected to any other analytic objects.
• The object doesn't require Detailed View access.

After some brainstorming, considering the necessary data and guidelines, we decided to create four subjects called Sales Opportunity, Account, Task, and Contact. We know that we need sales opportunity data, and since opportunities could have long lifetimes and status changes, we should create a subject to store this information. We also need to create a task subject to capture activity data through the tasks created for each sales representative. In order to know who the task is linked to, we need a contact subject. Finally, we need the details of the account linked to the sales opportunity, so we will also create an account subject.

The following diagram shows the subjects we need to create.

• Sales Opportunity: An opportunity within the sales process to sell to a potential customer.
• Task: A task performed by a sales representative.
• Contact: A representative from an account who may interact with a sales representative.
• Account: An organization that is a current or potential customer.

Now, we need to consider how all these subjects are connected to each other. Additionally, we need to connect the sales opportunity data with our existing employee data to know which team and sales representatives built the most sales pipeline.

The following diagram illustrates how these elements are interconnected. For example, each sales opportunity only has one primary sales representative (employee), but an employee could have many sales opportunities. Therefore, the connection should be a binding reference from Sales Opportunity to Employee. For more information about references, see References.

Metrics and attributes

Next, let’s consider what metrics and attributes we need to create.

To answer a business question, we can create a metric. Metrics calculate a business concern that can be quantified as a number. Before writing a metric formula, ask yourself the following questions:

• What business question do you want to answer? In this example, we can use the business questions we identified earlier.
• What is the metric calculating? In Visier, a metric calculates at least one analytic object, such as Employee.
• How does the metric handle time? In Visier, a metric must know what records to retrieve in a time period, such as all valid records in the selected time period or records that are valid at the end of the period.
• Which records does it select? Usually, a metric calculates a specific attribute on the analytic object, such as gender or performance rating.
• What type of calculation is it? In Visier, you have many aggregation options, such as count, average, and median.
• What data type is the metric value? Metrics can return a value as an integer, a percent, in years, in months, as a number, and more.
• Is the metric additive? Some metric values can be additive over any dimension or concept, like Headcount, or additive over time, like Employee Exit Count, or non-additive and cannot be summed over time, such as Average Headcount.

Additionally, we can create analytic object attributes (properties, dimensions, and concepts) that describe and give meaning to the object. To decide whether an attribute should be created as a property, dimension, or concept, we can review the following guidelines.

Create a property if:

• The attribute evaluates the analytic object on a per-ID basis. For example, the Age property evaluates on each employee ID to provide an age for every employee.
• The attribute won't be used as a group by in visualizations.
• The attribute can be used in other objects, like metric formulas or dimensions.

Create a dimension if:

• The attribute groups data. For example, the Age Range dimension groups employees into specific age ranges based on the Age in Months property.
• The attribute will be used as a filter or group by in visualizations.
• The attribute can be used in other objects, like concepts or custom dimensions.

Create a concept if:

• The attribute gets its values from a property or dimension.
• The attribute will be used as a filter or group by in visualizations.
• The attribute reclassifies data from other objects into new groups, such as:
  • Selects values that fit into the same group. For example, Millennials is a concept that selects all employees with a Birth_Date property between January 1, 1981 and December 31, 1996.
  • Selects values to represent hierarchical relationships between calculations. For example, Employee Exit Model is a concept that groups exit-related calculations into a hierarchy where Resignation, Retirement, and Other Voluntary exits roll up to calculate Voluntary Turnover.
  • Selects values to represent the processes or sequences that an analytic object goes through. For example, Applicant Process is a concept that groups applicants by the stages of their application, such as Applied, Screen, Interviews Started, Offered, and Background Checked.
  • Selects values to represent movement that an analytic object experiences. For example, Employee Movement is a concept that groups changes to an employee's location or organization as a move in, out, or within a location or organization.

Considering these guidelines, let's look at the first question of who in the team is building the most sales pipeline and how are they doing it. To answer this, we need to know how many opportunities have been created, how many tasks are completed, and how many accounts were contacted. Based on this information, we need to create the following metrics:

• New Sales Opportunities (both count and value)
• Completed Sales Tasks
• Accounts Contacted

For completeness, let's decide what objects we want to create for each business question we identified earlier.

How has the number of closed-won deals changed for each sales representative over the last 6 months?

To answer this, we need to know how many opportunities were closed-won versus closed-lost. Based on this information, we need to create the following metrics:

• Closed-Won Sales Opportunities Value per Rep (this helps determine the distribution of closed-won opportunities per sales representative)
• Closed-Lost Sales Opportunities (both count and value)

Will we close enough deals?

To answer this, we need to know the projected win rates and forecasted opportunities. Based on this information, we need to create the following metrics:

• Forecasted Opportunities Value
• Sales Win Rate

How many sales representatives are we hiring?

To answer this, we need to know how many employees we hired that are sales representatives. We also need to establish which employee owns each opportunity. We used Visier's existing Employee Starts Count metric filtered by the existing Sales Employee concept to determine the number of sales representatives hired, eliminating the need to create new objects.

What stages are our current sales opportunities in?

To answer this, we don't need to create a new metric. Instead, we need to create a dimension to monitor the various stages an opportunity progresses through. Additionally, we need to create a Sales Process concept to track the opportunity's advancement through these stages and to facilitate the evaluation of the duration spent in each stage until a final outcome is reached.

How much time does an opportunity spend in each stage of the pipeline?

To answer this, we need to track the time in stage for each opportunity. Based on this information, we need to create the following metrics:

• Time in Current Stage
• Time to Closed-Won

How do opportunities progress or regress at each stage of the sales cycle?

To answer this, we need to track the movement of the opportunities through the stages. This includes forward and backwards movement. Based on this information, we need to create the following metrics:

• Forwards Sales Opportunities
• Backwards Sales Opportunities

Make calculation decisions

Now that we've determined which metrics to create, we need to define their value calculations. This determines how we write the metric formulas. To write a metric formula, we must be familiar with the Visier Formula Language and the structure of a metric formula. If you aren't familiar with the Visier Formula Language, take a moment to read Visier Formula Language (VFL).

Let's examine the key considerations that guide our calculation decisions.

How should we calculate sales opportunity value?

In Salesforce, we track the opportunity value using the Amount field. According to Salesforce, "Amount" is defined as follows:

• Amount: Estimated total sale amount. For opportunities with products, the amount is the sum of the related products. For more information, see "Amount" in Object Reference for the Salesforce Platform.

To calculate this value, we have the option of using the latest opportunity amount or the amount of the opportunity when it moved to its current stage in the period. Let's start by exploring what happens if we use the current stage in the period.

If we use the current stage in the period, then we would use the Converted Offer Price in the calculation: Sales_Opportunity.Converted_Offer_Price

It's important to note that the opportunity amount changes as it progresses through different stages. Therefore, if we use current stage in the period and look at the value of the metric when the opportunity was first created, then it would use the initial amount at creation which could result in zero. Since Salesforce displays only the most recent amounts of opportunities, presenting historical amounts in Visier could lead to concerns about data accuracy at first glance. Furthermore, corrections of the amount in Salesforce do not apply back in history, only to the date when it was corrected.

Therefore, when calculating the value of sales opportunities, it is more common and useful to use the latest opportunity amount rather than the amount at its current stage.

If we use the latest opportunity amount, then we would use the Latest Converted Offer Price in the calculation: Sales_Opportunity.Latest_Converted_Offer_Price

Using the latest opportunity amount provides a more accurate picture of the current value of the deal, reflecting what is actually on the pipeline at this moment in time. It helps identify which opportunities deserve the most focus and sales effort. Using this logic, we can write the formula for a metric such as Closed-Won Sales Opportunities Value as follows, using the latest opportunity amount identified above:

on Sales_Opportunity validUntil instant filterBy (hasOpportunityClosedDateInPeriod &&  Sales_Pipeline_Process.Stage.Closed_Won) aggregate sum(Sales_Opportunity.Latest_Converted_Offer_Price)

How should we calculate new sales opportunities?

One of the common ways to identify newly created subject records (i.e., new sales opportunities) is to use previousChanges to transform a subject into a series of events representing its state changes. Then, filter those events to conception events.

However, it's important to note that this would not work well if we only load a subset of data (e.g., three months of data). Using previousChanges would create a large spike in the first period of data, since that is when all the opportunities first appear in the uploaded data. Any opportunity that closed more than three months ago would not have all the stages included in the uploaded files. In this scenario, the formula would look like this:

on previousChanges(Sales_Opportunity) occurredIn interval filterBy (Sales_Process.Stage.ClosedWon and (previous(!Sales_Process.Stage.ClosedWon) || conception) ) aggregate count(Sales_Opportunity.Sales_OpportunityID

To solve for this problem, another common way to identify newly created subject records is to use the date it was created (if it's available in the data). Fortunately, Salesforce has the data for Created Date that we can use to determine if the opportunity was created in the selected period. Using this logic, we can write the formula for a metric such as New Sales Opportunities using the created date as follows:

on Sales_Opportunity validUntil instant filterBy Sales_Opportunity.Created_Date >= start(periodOf(instant)) && Sales_Opportunity.Created_Date < end(periodOf(instant)) aggregate count(Sales_Opportunity.Sales_OpportunityID)

How should we calculate forwards and backwards sales opportunities?

Forward and backward sales opportunity metrics track stage changes, revealing how opportunities progress or regress. By monitoring both forward and backward movement, we can gain insights into sales strategy effectiveness, identify bottlenecks, and assess the sales pipeline's overall health. This tracking uses the predefined stages established within the Sales Process concept. These predefined stages and outcomes are as follows:

Stages:

• Prospecting
• Lead Qualification
• Needs Analysis
• Proposal/Price Quote
• Negotiation & Commitment

Outcomes:

• Closed-Won
• Closed-Lost
• Closed-Other

The calculation logic we implemented tracks the net movement of the opportunity by comparing the initial stage (first day of the selected period) with the final stage (last day of the selected period).

How should we calculate forecasted sales opportunities?

By leveraging opportunity forecast data, we can gain crucial insights for informed decision-making and optimized sales strategies. This precalculated data, sourced directly from Salesforce, assigns an estimated forecasted revenue to each opportunity based on its expected close date and forecast category.

The forecast amounts that have the same forecast start date are aggregated together. This is because every opportunity can go through multiple forecasts as the sales process progresses and as the sales team learns more about each deal. Therefore, it's important to make sure we only add together the opportunities that are part of the same forecast period. Forecast data is loaded on the Sales Opportunity subject with the following attributes:

• Forecast Start Date: The first day of the forecast period.
• Forecast End Date: The last day of the forecast period.
• Forecast Amount: The dollar value of the forecast.
• Forecast Category: Based on its opportunity stage, each opportunity is assigned to a specific forecast category within the sales process. The standard forecast categories are Pipeline, Best Case, Commit, and Closed. Salesforce administrators can customize the forecast category names.

Next steps

Using these design principles, we extended the Visier solution by creating new sales opportunity objects to track and analyze the sales pipeline! You can now leverage the same principles, as demonstrated by the Salesforce Extension, to extend your analytic content further.