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

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.

maybe try something like these



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:

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.

1 Like

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

1 Like