How to analysis new user vs repeater without multiple count and create a dimension with logs

Question & Answer
, ,
1

I have over a thousand rows in the data table below and am trying to create a stacked bar chart. The chart is grouped by new users/repeaters, with the x-axis representing the datehour [month], and the y-axis representing the number of users.

user id | date(timestamp) | item

I’ve tried use below but…

First timestamp = minOver(timestamp, [{user id}], PRE_AGG)
New user or repeater = ifelse(timestamp = {First Timestamp}, ‘New User’, ‘Repeater’)

When the same user buys items many times in the same month, the user number is double-counted as both of new users/repeaters… how can I resolve this? I only need new user count the first month

1 Like
2

Hello @Lisa, rather then checking for their first timestamp, you should check if their min timestamp is equal to their max. That will ensure a repeat user isn’t counted for their first timestamp and their most recent. It would look something like this:

ifelse(
minOver(timestamp, [{user_id}], PRE_AGG) = maxOver(timestamp, [{user_id}], PRE_AGG), 
'New User',
minOver(timestamp, [{user_id}], PRE_AGG) <> maxOver(timestamp, [{user_id}], PRE_AGG),
'Repeat User',
NULL
)

That should resolve the issue you are facing and should only account for each user_id once. I’ll mark this as the solution, but please let me know if you have any remaining questions!

3

It’s still causing problem. for example,

user id | date(timestamp) | item
001 | 2024-04-01 00:00:00.000+0000 | itemA
001 | 2024-04-09 00:00:00.000+0000 | itemA
002 | 2024-04-04 00:00:00.000+0000 | itemA
002 | 2024-04-27 00:00:00.000+0000 | itemA
003 | 2024-04-29 00:00:00.000+0000 | itemA

when if I try to provide monthly report for itemA on April
NewUser counted 1 and Repeater counted 2 by this calculation field… how can I avoid this…?

4

Hello @Lisa, I am a little confused by your question. In the sample data you provided above, users 001 and 002 have 2 timestamps for itemA and user 003 has 1 timestamp for itemA. That looks like 2 repeat users and 1 new. Is there something I am missing? What would you expect the return value to be?