Scoop has an extremely powerful analysis engine that allows you to aggregate data in extremely flexible ways. Scoop has a default method for analyzing every metric (number) in your dataset. That default is based on a combination of information derived from your reports, and generalized best practices. However, there are many more ways those numbers can be summarized and KPIs provide an extremely powerful mechanism to do so. In addition, one might want to create calculations that combine columns from one or more datasets to create powerful calculations. That can also be done with KPIs. As a result there are two types of KPIs that can be created:

• KPIs that aggregate a single column from a dataset, but do so in very precise ways
• KPIs which are calculated from other KPIs and allow one to create very sophisticated metrics that may include calculations across datasets

You can create a KPI by clicking on the plus icon next to the KPIs header in the left panel.

Dataset KPIs

When creating a KPI you must specify which dataset column is being aggregated. After that, you can optionally change how that column should be aggregated with exceptional detail.

In this example, we are going to aggregate sales amount by create date to make a "Created Sales" KPI. This KPI will show opportunity amounts by created date. Notice that you can create.

Aggregation Details

After you have selected the column to aggregate you can customize how that column is aggregated. First and foremost you can select the aggregation rule for that metric (sum, count, count distinct, average, minimum or maximum). This gives you real power in understanding how this column value changes within a group. Additionally, when doing time series summarization (see Visualizing Data by Time) you can also specify a default aggregation period and whether that metric should be aggregated rolling or calendar.

Filtering the KPI

Having the KPI compare to prior periods

Sometimes it is important to compare a value to a prior period one, or even a next period one. The classic example is percent changed vs month ago. To calculate this you need to create a current period metric (this month). And then create a KPI that gives you last month's value. You can then create a compound KPI (see next section) that simply divides one by the other to give a percent change of month over month.

Compound KPIs

A compound KPI is simply a formula composed of other KPIs. This allows you to create complex logic. It also allows you to create calculations based on KPIs that may be based on different datasets (e.g. a financial dataset number indicating a cost and a marketing dataset number indicating a count of leads enabling a cost per lead calculation). Because Scoop leverages spreadsheet logic, these formulas look just like Microsoft Excel formulas and in fact, use identical syntax. Almost all Excel functions will work. So, if you can make a formula work in a spreadsheet, you can use it in Scoop.

Below is a simple example calculating a sales coverage ratio. Amount by Close Date is a KPI that indicates how much sales are expected to close in a given period of time (it's basically the opportunity amount using the close date as the date for aggregation). The Weekly Goal is a target that likely comes from another source. This target could, in fact, be maintained in a spreadsheet at a weekly, monthly or quarterly level, and using that one can get a sense of whether there are enough deals in a pipeline to provide adequate confidence that the target goal can actually be met. Note that when referring to KPIs in your formulas you simply surround their names with a single quote.

When using compound KPIs, you can drill on those values in a chart or table. However, you will only be able to drill on the attributes that all the KPIs that comprise this compound KPI have in common. So, you can always drill on time (going from say Year to Month for example). However, if the KPIs have other attributes in common, like say Product, or Sales Region, then you can drill down on those as well.