Calculate daily new users (never seen before) to a website

Elastic Stack Kibana
1

I referred the above link but cannot understand how to build a visualization in Kibana for daily unique new users (that we have never seen before.

The following is a simplified look at our mappings in Elastic search

{
    "mappings" : {
        "properties": {
                    "userid": { "type": "keyword" },
                    "yyyy_mm_dd": { "type": "date", "format": "yyyy-MM-dd" },
                    "ts": { "type": "date"}
    }
}

Usecase
eric@cream.com, 2020-03-28, 1585357301265
paul@beatles.com, 2020-03-28, 1585382231269
john@beatles.com, 2020-03-28, 1585383569863
eric@cream.com, 2020-03-28, 1585414906564

paul@beatles.com, 2020-03-29, 1585466637966
ginger@cream.com, 2020-03-29, 1585493882027
george@beatles.com, 2020-03-29, 1585486384984

paul@beatles.com, 2020-03-30, 1585562310061
jackbruce@cream.com, 2020-03-30, 1585571947493
eric@cream.com, 2020-03-30, 1585597746891
john@beatles.com, 2020-03-30, 1585555104127
ginger@cream.com, 2020-03-30, 1585563504459
freddie@queen.com, 2020-03-30, 1585578397198

My desired Visualization

2020-03-28 - 3 unique new users

2020-03-29 - 2 unique new users (george , ginger)

2020-03-30 - 2 unique new users. (jackbruce and freddie)

2

Hi @sanjaysubramanian!

Unfortunately, it looks like that older ticket you linked to seems to come to the conclusion that it's currently not possible to do in Kibana.

At that time, this feature wasn't available in ES and so was not possible in Kibana. The underlying feature is now merged in ES (#43661) but it hasn't been exposed in Kibana yet.

You can track this feature in Kibana under the Aggregations meta issue. (Search for "Cumulative cardinality")

3

The tricky part is to know if a user is a new user. For that you need not only the actual data point but also have to look into the past.

The issue you referenced suggests an entity centric index which you can build using a transform. The idea is basically: you build a secondary index that pivots your original index to get another view on the data. A continuous transform keeps this view up-to-date, which means it refreshes the secondary index with minimal effort. You find Transform under kibana management.

I suggest to group_by username or user_id. As aggregation you use min to find out when the user 1st appeared. Given that, you should be able to build a visualization on top of that (there is probably one problem: you need to adjust the kibana index pattern for the transform destination index regarding the time field). Apart from the users 1st appearance you can build many other useful metrics around your user, e.g. last appearance, number of interactions, unique count (cardinality) of devices he used, e.t.c., whatever your data provides.

The transform documentation should get you started.

Good luck!

4

Thank you @Hendrik_Muhs
Let me try the transform and report back. Thanks a ton !
warmly
sanjay

5

@Hendrik_Muhs

I successfully created a transform (and poited it to an index pattern) which essentially is the following query

SELECT userid, min(ts) from USERS group by userid

From the documentation, I understand that the transform will keep running at regular intervals to keep updating itself. I assume that visualization built on the index pattern will get updated if we refresh the Visualization ?

6

Great!

Yes if you enabled continuous mode in transform it updates the index (on the transform overview page, mode should be continuous (not batch)). For the visualization you can configure auto-refresh(clock symbol).

7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.