This QuickStart (QS) discusses and demonstrates how to use Sigma Metrics against a use case we will detail later on.
Metrics in bi (business intelligence) refer to the quantitative measurements used to evaluate and analyze the performance of a business. These metrics are calculation that are used to support a variety of use cases including financial performance, customer behavior, operational efficiency, and more.
Examples of common metrics used in business intelligence include:
By tracking and analyzing these metrics, businesses can gain valuable insights into their performance and make data-driven decisions to improve their operations, better serve their customers, and increase their profitability.
Metrics in Sigma provide a way to ensure consistent metric logic across tables, visualizations, and pivot tables.
Metrics help avoid:
Analysts can define calculations differently. For example, one may define revenue as the
sum of [Sales Price], where another may define it as the
sum of [Sales Price] - [Discount]. These differences lead to inconsistent results and a lack of trust in the data.
Common business logic for a calculation like profit margin needs to be used all over the place. Today in Sigma, analysts have to derive the definition from scratch in every new workbook. This is a lot of repetitive, tedious work.
Benefits of using metrics include:
Promote accurate, consistent, and governed analysis amongst all your
Incorporate common business calculations into your
business users without having to derive them yourself.
Anyone who is trying to create content in Sigma and those interested in governing the calculations that are being used.
How to apply Metrics in Sigma using Sigma's sample database to build out information based on a Marketing campaign use case.
We will be a simple solution that meets requirements provided by Marketing for active campaign status.
Metrics in Sigma are custom aggregate calculations that can be reused across workbook data elements that share a data source (i.e., dataset or connection table).
They can be as simple or complex as required by the use case. The person creating Sigma content does not need to know the details of the metric to use them in their Workbook and by using an approved metric, they will accelerate their workflow.
Metrics are defined at the data source level to promote consistent metric logic across elements and help users perform standard calculations with ease and efficiency.
There are some basic rules for creating Metrics:
For Connection Tables:
Marketing has requested details on how well the various Salesforce campaigns are performing.
They want specifics on active campaigns to make sure they are staying on budget:
They also want the the ability to drill down into the data to get to the lowest level available. They also want to create some visuals (TBD), using the same data on their own.
Now that we know what the requirements are, let's create a reusable set of data that leverages metrics so that any future use of the data includes the approved (governed) calculations.
Login into Sigma and click
+ Create New and select
Select a Data Source page, select
We will be using the Salesforce data in our cloud data warehouse, as shown:
Recall that Marketing only wants to see active campaigns.
Select the column
Is Active and click it's menu. Select
We want to exclude anything that is not "Active" or
This will also serve to reduce the amount of data returned since it is not required in this use case.
We are now presented with the unpublished Dataset and we can add some metrics.
Metrics tab and click
Adding new Metrics is very simple. All that is required is to provide a name and formula. The description can be very useful, but it is optional.
For this new metric, use:
NAME: FORMULA: Leads by Type Sum([Number of Leads])
Notice that you can also set the default formatting for the Metric. This saves steps for the users later.
The metric is saved as soon as you create it.
All Metrics link to see a list of all available metrics in this Dataset:
From this list you are also able to modify or delete any Metric.
Now create a few more metrics so that we can build our use case out:
NAME: FORMAT: FORMULA: Pipeline Generated Currency Sum([Amount All Opportunities]) Total Closed Won Currency Sum([Amount Won Opportunities]) Budget Allocated Currency Sum([Budgeted Cost]) Actual $ Spend Currency Sum([Actual Cost]) Attributed Margin Currency Sum([Amount Won Opportunities] - [Actual Cost]) Under-Over Budget Automatic If(Sum([Actual Cost]) / Sum([Budgeted Cost]) >= 1, "Over Budget", "Under Budget")
Most of the metrics used are simple enough but as you can see with the
Under-Over Budget formula, you can get as complex as needed and use any of the available Functions.
Click here to see the available functions in Sigma
If you have not already done so, name the Dataset
Sales Campaign Dataset and
Now that we have a Dataset with some Metrics, let's build content.
Sales Campaign Dataset, click the
Explore button in the upper right corner of the page header.
Group the table by
Now lets add some columns but instead of having to do it manually and sort out the correct calculations, we will just use the bre-built ones provided by Metrics.
Element Panel, click the
Drag each Metric to the
Calculations under the
Type in the order in which you want the columns appear:
Lets also make the campaigns that are over budget stand out while we are here:
For the column
Under-Over Budget click the menu icon and select
Configure it so that cells with "Over Budget" will appear red in the column cell:
Now that we have a table using trusted calculations, we can leverage it into a Vizualization to make easy to see campaign spend to date:
We simply create a
Child Element /
Vizualization from the table:
and configure a bar chart as:
We also enabled data labels to display the value on top of each bar:
Rename the chart to
Active Campaign Spend - Budget vs Actual.
Publish your Workbook and see the final results by navigating to the
Now Marketing has all the information they requested and can use Sigma's
Drill Anywhere feature to get to the lowest level of data to explore as they see fit without having us pre-define the drill-path.
For more information on Metrics click here.
In this lab we learned how to create and manage Metrics in Sigma.
Additional Resource Links
Be sure to check out all the latest developments at Sigma's First Friday Feature page!
Help Center Home