Pigment in Action: How to calculate streak cumulations

Introduction

Are you trying to count an unbroken streak of values and require a reset of the count when there is a gap in the monthly data? Cumulations can be expensive operations, so it's important to find an efficient solution. In this community article, we'll explore a solution to this problem so that you can achieve your modeling goal.

Example Scenario of a Streak Cumulation

Imagine you are currently counting the consecutive months in which your customers have placed orders with you. You have access to the following data:

• A transaction list that includes your customers' orders.
• A metric that indicates the number of orders per customer per month, derived from the transaction list.

Your goal is to determine the order streak for each customer. You can then use this information to inform a loyalty program and offer discounts, enhancing customer loyalty

Let’s build a Solution!

In this metric, there is a count of the number of orders that the customer makes in a given month. ‘Order Count’ is defined by the formula ‘'Orders by Customer'.'Invoice Value'[BY COUNT: 'Orders by Customer'.Customer, 'Orders by Customer'.'Invoice Month']’. This formula counts the number of orders that have an Invoice Value per Customer and Month as defined in the transaction list ‘Orders by Customer’.

Here, we are generating a flag if there is a value defined in a given month for each customer to show that at least one order was made. To do this, we can simply use the formula ‘ISDEFINED('Order Count')’. This formula returns a value of TRUE whenever there is a value defined in the metric ‘Order Count’. If there is no value (i.e. the source cell is BLANK) then this function also returns a value of BLANK.

In the below metric, we are finally generating our order streak cumulation!

To do this, we are generating a value of 1 when we have flagged that a customer makes at least one order in a given month. We then want to add this to our cumulation to increase the total.

Then, the PREVIOUS(Month)['Customer Has Order'] term of the formula drives the cumulation. The key to ensuring the reset of the cumulation is to also apply a filter to the PREVIOUS(Month) term. The result of this is that the cumulation will only apply when there is a month with an order to cumulate. For months where there are no orders, the PREVIOUS(Month) is disregarded. In addition, if there are no orders, there is no value to add, and therefore the overall result will be 0, thus resetting the cumulation.

You can see here that the customer ‘Umbrella Corporation’ broke their order streak in July, and their streak was reset when they made another order in August.

Additional Tips

• You can alter the conditions for generating the flags in the metric ‘Customer Has Order’ to apply pretty much any kind of conditional logic to meet your needs: i.e. you could alter this formula to only generate a value of TRUE when a customer’s max order value, or total order value exceeds some threshold.
• Essentially any criteria can be applied here to meet the needs of your model.
• Construct your calculation in stages, and break it down into concrete components - this reduces the amount of calculation required at each step, and can actually help improve the overall performance of your formulas!
  • Trying to do too much in one go can cause your formula to time out when working with large data volumes.
• Make sure you filter the data that you bring into your calculations as much as possible!
• You can use the shorthand [’Boolean Metric’] instead of [FILTER: ‘Boolean Metric’] or [FILTER: ‘Boolean Metric’ = TRUE]. Consider who might be maintaining your formulas after you and use whatever is clearest - comments are always your friend!