Finding and using first values

Q&A
, ,
1

Let’s say I have a dataset that looks like this:

user_email order_timestamp order_amount marketing_channel
bart@email.com 3/4/24 3 email
bart@email.com 3/3/24 5 ads
john@email.com 1/1/24 2 sms
john@email.com 1/2/24 4 push
john@email.com 1/3/24 3 push
mila@email.com 3/5/24 5 ads
mila@email.com 3/3/24 3 email

I was able to build a cohort analysis using the timestamp with “minOver” function with PRE_FILTER aggregation. So I now have “first_order_date” available.
But what I aslo need is “first_marketing_channel” value on each record, so that I can filter my cohort analysis by it.
E.g. I want to see LTV (sum of order amounts regardless of marketing channels) for users who came the very first time from a specific marketing channel. I was hoping to use fistValue, but it doesn’t support PRE_FILTER.

2

Hi @tjk,

You can try to use the rank function with PRE_FILTER and then filter by rank = 1.

3

Already tried that. Problem is - if I use rank as a filter, I won’t get LTV calculation for all orders. It will be limited to first order only.

I need a way for every record to have an additional column that is “initial_marketing_channel”

4

Are you trying to create a visual like this?
image

Do you need to filter your visual by date or marketing channel?

5

The visual you did I think I know how to create, but I need something a bit harder. I need those “first_order"date” and “initial_marketing_channel” to be next to every record in the dataset, because this will allow me to build a cohort analysis that will look smth like this (with ability to filter by initial marketing channel):

image
image976×346 33 KB

Intersections show LTV, which is the sum of order_amount divided by initial cohort size.

6

I think your best bet is to use SQL to calculate your initial marketing channel at the dataset level.

7

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the Quick Sight Community!

1 Like