Sales – Calculations

Sales – Calculations

Statements can contain all sorts of lines of data surplus to your reporting needs. “Totals” lines for units or revenue, or odd lines detailing balance transfers. In other instances the data may contain a Gross Amount & Distribution Fee but no Net Amount. Rules can be set to omit lines of data depending on certain conditions, run sums to generate required missing values, or concatenate data fields. Our goal is that with carefully programmed templates utilising appropriate necessary rules you should not need to make manual changes to your sales files prior to uploading. These ‘rules’ are the Calculations.

There are two aspects to a Calculation. First, the Condition, specifies under which circumstance this calculation should happen. Second, the Calculation & Output, specify the calculation that should happen once the condition is met. As a bonus, at the bottom of your Calculation you have a Notes field where you can store information such as why you decided to create this calculation.

Calculations are made in the order they appear on your Calculations tab. So contrary to your Contract Terms, the order of your calculations does make a difference.

CONDITION

Here you specify under which condition the following calculation should happen. You have three options.

Any One Of The Below – The calculation will happen when at least one condition in a list of conditions is met.
All Of The Below – The calculation will only happen when all of the conditions are met.
Always – The calculation will always happen. To put it in other words, there is no condition.

In the below example, we have selected “Any One Of The Below”, meaning our calculation will happen when either Column 1 or Column 2 of a particular sales line is empty. Had we selected “All Of The Below”, our calculation would only happen when both Column 1 & Column 2 were empty.

CALCULATION & OUTPUT

Calculations allow you to determine the value of a field by combining the data of a field/column/value with another field/column/value. It covers all basic mathematical calculations such as sums, differences, multiplications & divisions. Plus it has the ability to extract or concatenate characters from a value. When creating a new calculation, you will be presented the following fields:

Output Field – This decides the field in which the result of your calculation will be stored. One special option is the “Skip Row”, which allows you to skip an entire row entirely. For more information on this, we refer to the example at the bottom of this topic.
Value Type / Value – Here you decide which Values should be part of the Calculation. As input of the calculation you can pick either a column, field or value. “Column” takes the starting value from a column in the sales file. You then direct to which column in the subsequent Value table by entering a number, counting the columns in the data from left to right. “Field” takes the starting value from a field matched in the sales file. So when selecting this the subsequent Value box is populated with the various Field names that are matched to columns on the template overview tab. Make sure the directed field actually contains some data & is linked to a column when setting up the template. With “Value” as a Value Type, you enter an absolute number that will be used in the calculation.
Calculation – This decides which calculation should be made between the chosen Values. You can select Plus, Minus, Multiplied By, Divided By, Get First X Numbers Of Characters, Get Last X Numbers Of Characters, or combine the the data via a Concatenate function.

Let’s look at some examples of how to create calculation outputs.

PLUS/MINUS/MULTIPLY/DIVIDE

This example calculates & stores the Net Amount value by multiplying the mapped Gross Amount field by 0.9. It performs this calculation on every individual sales line in the statement. Essentially this is applying a 10% commission. For this to work the Gross Amount field would need to be set to a Column in the Template.

Additions, subtractions & divisions are done in the same way. It is possible to perform a series of calculations by adding a new calculation layer; just hit the + Output Calculation button. The system will make the calculations in the order they are added.

GET X NUMBERS OF CHARACTERS

This function trims the data to the first or last specified number of characters. The following example will take the first 10 characters of Column 5 & store it as the Barcode.

This calculation would store the data as below.

Column 5

Barcode

7856957595 GTIN

7856957595

8567856845 GTIN

8567856845

3256897598 GTIN

3256897598

CONCATENATE

The Concatenate function combines data from different columns into one. This is typically used to combine text data. The following example combines the data in Column 5 & Column 6 to store it as the Original Configuration.

Imagine column 6 stores the consumption description such as Download or Stream; whereas column 5 stores the commercial type such as Ad Supported, Subscription, Pay As You Go etc. Depending on your Configurations Settings you may need the data in both of these columns to map a sales line to Premium Stream or Ad-Supported Stream. Therefore we need the Original Configuration to concatenate data from both of these columns.

Column 5

Column 6

Original Configuration

Ad-Supported

Stream

Ad-SupportedStream

Subscription

Stream

Subscription Stream

Pay As You Go

Download

Pay As You GoDownload

Using the Mapping tool, the Original Configuration “Ad-SupportedStream”, “SubscriptionStream” and “Pay As You GoDownload” can then be mapped to your applicable Configurations.

EXAMPLES

Example 1: One Condition

The following example will store the value in Column 4 as the ISRC, but only if there is no value in Column 3. The context here is that the ISRC field has been mapped to column 3. But, the files do not always contain an ISRC. If you have ever seen YouTube statement data this will be familiar to you. User Generated Content often omits the ISRC. Whereas column 4 contains the YouTube asset ID, which can be used to map the sales line to the track in your catalogue when we come to mapping at the Sales Ingestion stage. As there is only one condition, it does not matter if you select “Any One Of The Below” or “All Of The Below”.

Example 2: Multiple Conditions

The following example will store the value in Column 5 as the ISRC, but only if both Column 3 and Column 4 of the sales line are blank. Sticking with YouTube, sometimes neither an ISRC nor an Asset ID are reported. But a video ID is always present. So you want the template to first look for the ISRC in column 3, if this is blank the template then needs to look at column 4 (Asset ID), & if both columns 3 & 4 are blank the template should ingest the video ID (column 5). This way if an ISRC is reported, it will hopefully match to your catalogue. If the ISRC is missing, the Asset ID will be ingested as the ISRC for you to reference in YouTube CMS & match to the appropriate ISRC in your catalogue. Or the Video ID can be used to search the original video on Youtube for you to review the video & decide which of your tracks to map to.

Because “All Of The Below” is selected as the WHEN setting, column 5 is only ingested as the ISRC when columns 3 AND 4 are blank. If “Any Of The Below” had been selected, the calculation would refer to column 5 for the ISRC if 3 OR 4 were blank.

Example 3: Skip Row

A very specific use of the complex calculation type is a Skip Row. This allows the system to ignore an entire sales line when a condition has been met.

The following example is for the specific case of Bandcamp statements, where the data includes sales lines but also a line detailing every time Bandcamp makes a payment to the user. See below.


These ‘payment’ lines are specified with the value “payout” in column 3. As this is not sales data, we don’t want to ingest it – there’s nothing to account on them. So the following calculation instructs the template to ignore a line of data when column 3 reads “payout”.

 

These are just a small number of examples of what is possible with calculations. To re-iterate the statement in the opening paragraph of this article: it is our goal that you should not need to make any changes to your sales files in order to upload them. If you have any scenarios where you are prepping your sales files before ingesting into Curve, please think about how Calculations might automate your manual actions. You can always get in touch with your Curve Client Manager if you need any guidance – we would be very happy to help.