Kibana Visuals - count number of customers with more than X buys

Elastic Stack Kibana
1

Hi all,

Image that i have in elastic something like:
Customer id
Product

I need to create a metric that tells me the number of customers that bought the product Y more than X time.

Example number of customer that bought a t-shirt more than 5 times.

Basically i guess we need to filter product = t-shirt and aggregate by customer id with min_doc_count 5.

But i dont see how this can be done in kibana visuals i had read that TSVB dont support min_doc_count, is this possible to archive in any other visual?

i'm running 7.5.

Thanks,

2

You can add { "min_doc_count": 5 } as an advanced JSON input on the Terms aggregation in your Visualize table/bar/line etc. However, I'm not sure that this is what you want, because you said that you want basically the following metrics:

Buckets: Terms of product, sorted by the metric
Metric: Cardinality of customer_id

So if the only one customer buys a product 5 times, then your min_doc_count would be satisfied, but your unique count is only 1.

This leaves you with two main options:

  1. Build a visualization that shows the top products sorted by unique count, but without hiding the bottom products

  2. Use Elasticsearch transforms to pivot your data and then query the pivoted index.

3

Hi Wylie,

Sorry the late response i was reading a bit about what you sent - elastic transforms (seems a bit complex, not sure if i explained correctly my use case, because i think option 1 does not give what i need).

Image i have this data is last 5m and i want the count of customer with more than 5 buys:
customerid product
1 t-shirt
1 t-shirt
1 t-shirt
1 t-shirt
1 t-shirt
1 t-shirt
2 t-shirt
2 t-shirt
3 t-shirt

i want the count of customer with more than 5 buys (i can use filters to filter products if needed).
So in this case the values i m looking for is 1, because only 1 customer had more than 5 buys.

The ideal i have a chart with this value across the time, line, TSVB(time series), bar...

Is there any visual that can give me this? Do we need to use Elastic transforms for this?

Thanks,

4

Yes, we are talking about exactly the same problem. Your data should be pivoted so that it contains { product: 't-shirt', unique: 3 } given your example above. This is something transforms can do for you, or you could do it using some other kind of script.

The main reason is what I already explained above: you can't do this using the terms aggregation. I would welcome you to read through the docs on Elasticsearch bucket aggregations if you'd like to try out a few queries yourself.

Edit: If your data is very small, like under 10000 products, you may be interested in using Vega visualizations, which are the most powerful tool that we offer. If you are able to construct a query you like you can visualize it in Vega.

5

Hi,

I m reading it, but do you think it should be possible with bucket aggregation? from my reading seems that the sub aggregation runs only in each bucket, can you confirm? Or is the pipeline aggregation more suitable for this task?

Trying this pipeline aggregation I m having the error " [cardinality] unknown field [buckets_path], parser not found", i will give it a few more tries.

Thanks,

6

You can do something kind of similar using bucket aggregations, but it's not going to be precise:

a. Kibana visualizations as a whole let you build a Terms/Top Values agg with a Cardinality/Unique Count metric, and then you can see the "number of customers per product". But you can't filter this number because it's aggregated in Kibana.

b. You can build the same query above in Vega, but you can use a bucket selector pipeline agg to limit it to "> 5" only. This requires that your total number of buckets is under 10k or 65k, depending on your ES version

7

Hi,

Still no luck, can you confirm if cardinality is supported in pipeline aggregations?

I have this error:
"reason": "[29:16] [cardinality] unknown field [buckets_path], parser not found"

Thanks,

8

Cardinality is a metric aggregation, not a pipeline aggregation.

9

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