Number of sales per customer segmented by given period - Possible with calculated fields?

[CoachSkip]

Hello,

I have a dataset of 2M+ rows where each row represent one sale / use of a service.

The dataset contains a lot of different information regarding the customer, payment etc., but the relevant fields for what I want to do are:

• Timestamp for when the customer signed up
• Timestamp for when the customer used the service
• Customer id (unique per customer)
• Service id (unique per use of service)

With the timestamps in combination with the customer id I would like to segment customers into different categories, for example:

• A customer who has used the service at least 5 times the last 3 months (more than 5 uses 0-3 months ago)
• A customer who has used the service at least 5 times the last 6 months (more than 5 uses 0-6 months ago)
• A customer who has not used the service the last 3 months, but used it at least 5 times the last 6 months (more than 5 uses 3-6 months ago)
  and so on…

Currently I am able to categorize the customers by:

1. When their last purchase was using:
   maxOver({created_at[order]},[{customer_id}],PRE_AGG)
2. How many times each customer has used the service:
   countOver({unique_service_id}, [{customer_id}], PRE_AGG)

The problem is that I am struggling to figure out how I can count the number of uses within a specific time period for a specific customer. Are there calculations available in Quicksight to calculate this?

Any help to push me in the right direction is much appreciated.

[rbrady]

maybe try something like these

[CoachSkip]

Thank you for your response, rbrady.

With your calculation it seems to categorize each customer_id correctly, but I am not able to visualize the category / segment in a meaningful matter. The calculated field can’t be used as a dimension, with the error message “Custom aggregation field is not allowed as a dimension”.
It has to be used as a value, with “customer_id” as one of the dimensions, for it to work. This results in a table with as many rows as there are customers.
I would assume it’s the countIf function which causes this.

The desired output is to have a dynamic snapshot over the current customer portfolio with the number of users and times the service has been used in each segment. I apologize if this was not clear from the initial post.

Something similar to this:

[CoachSkip]

I think I found a solution. Have not done a full quality control, but the results look reasonable.

First calculation: [PeriodSegment]
Create a calculated field to segment by period alone.
For this I just used an ifelse statement like this:

ifelse(
{created_at[order]} >= addDateTime(-3,'MM',now()),"Period 1, 0-3 months",
{created_at[order]} >= addDateTime(-6,'MM',now()) AND {created_at[order]} <= addDateTime(-3,'MM',now()),"Period 2, 3-6 months",
{created_at[order]} >= addDateTime(-12,'MM',now()) AND {created_at[order]} <= addDateTime(-6,'MM',now()),"Period 3, 6-12 months",
{created_at[order]} >= addDateTime(-24,'MM',now()) AND {created_at[order]} <= addDateTime(-12,'MM',now()),"Period 4, 12-24 months",
{created_at[order]} < addDateTime(-24,'MM',now()),"Period 5, >24 months",
"Period 6, Check for errors") 

Second calculation: [Count within periodsegment]
Count the number of rows / uses each customer has in each period segment:

countOver({package_id},[{customer_id},PeriodSegment],PRE_AGG)

The missing link for me was that I did not know I could use two different attributes using CountOver!

Third calculation: [CustomerSegment]
Use a combination of the first and second calculation in an ifelse statement:

ifelse(
{Count within periodsegment}>=100 AND PeriodSegment="Period 1, 0-3 months","0-3 months: SMB User 100+",
{Count within periodsegment}>=50 AND PeriodSegment="Period 1, 0-3 months","0-3 months: Super user, 50-99",
{Count within periodsegment}>=5 AND PeriodSegment="Period 1, 0-3 months","0-3 months: Super user, 5-49",
{Count within periodsegment}<5 AND PeriodSegment="Period 1, 0-3 months","0-3 months: Active user, 1-4",
{Count within periodsegment}>=100 AND PeriodSegment="Period 2, 3-6 months","3-6 months: SMB User 100+",
.. and so on, until all segments were covered

Then I could finally use the field CustomerSegment in a visual.
If there are other and/or easier solutions to this, feel free to comment in this thread.

[rbrady]

ahh, I didn’t originally consider that but yes, your solution makes sense. Glad you got it worked out!