Hello everyone,
I would need help to try understanding how to perform a basic segmentation for a cohort report.
I watched the cohort tutorial video but it does not explain what I am trying to achieve, and while the logic behind it is pretty basic to me, I cannot wrap my head around the way to get it in QuickSight.
My starting data is a table containing daily users transactions, and what I want to achieve is to have segments based on the amount spent in a specified time interval.
For example I would select thirty days as time interval.
My segments would be based on how much the users have spent in this time window: between €0 and €100, between €101 and €500 etc…
I would then show them either in a (pivot) table or in a column chart, so that I can see that in the last 30 days, the users who used to spend over €1,000 have dropped, while the smaller ones have increased.
I have been able to segment based on lifetime spend, but not on custom intervals.
My starting dataset is in this format:
Date | User | Spent | Reversed |
---|---|---|---|
01/01/2023 | 123 | €250 | €0 |
02/01/2023 | 123 | €250 | €0 |
03/01/2023 | 123 | €250 | €0 |
04/01/2023 | 123 | €300 | €0 |
05/01/2023 | 123 | €350 | €50 |
06/01/2023 | 123 | €350 | €0 |
01/01/2023 | 456 | €500 | €0 |
02/01/2023 | 456 | €400 | €0 |
03/01/2023 | 456 | €500 | €100 |
04/01/2023 | 456 | €100 | €0 |
05/01/2023 | 456 | €100 | €0 |
06/01/2023 | 456 | €200 | €50 |
01/01/2023 | 789 | €50 | €0 |
02/01/2023 | 789 | €50 | €0 |
03/01/2023 | 789 | €50 | €0 |
04/01/2023 | 789 | €100 | €0 |
05/01/2023 | 789 | €100 | €0 |
06/01/2023 | 789 | €100 | €0 |
I would want to segment based on “Spent - Reversed”:
- €0 - €500
- €500 - €1,000
- €1,000+
If I break it into two three-day time periods, I would get that player 123 is in segment 2 for both the first (€750) and the second (€950) period. While 456 goes from segment 1 (€1,300) to segment 3 (€300).
So if I checked the unique users count I would get:
Segment | P1 | P2 |
---|---|---|
€0 - € 500 | 1 | 2 |
€500 - €1,000 | 1 | 1 |
€1,000+ | 1 | 0 |
Now, if I wanted to get segments based on lifetime data I would do:
revenue
({Spent} - {Reversed})
lifetime_revenue
sumOver({Revenue}, [{User}], PRE_AGG)
revenue_segments
ifelse({lifetime_revenue} >= 1000, '03. €1,000+', {lifetime_revenue} > 500, '02. €500 - €1,000', '01. €0 - €500')
Now I could use revenue_segments and count the unique users, or split the total revenues.
But how do I make it so the segment is for a period only?
Of course I cannot use PRE_AGG so I thought about:
sumOver(sum({Revenue}), [{User}])
And since it did not work out, I tried several other ways, but even though I am sure this problem has an easy solution, I cannot seem to figure it out (in several attempts I got the error that I cannot use a custom aggregation field as a dimension).
Thank you and sorry for the long post, trying to give the full picture.