calculate ratio between aggregation buckets

[orrchen]

Hi,
I'm using the following terms aggregations to get views and clicks of each campaign ( by campaign_id ) :

{
    "aggregations": {
        "campaigns": {
            "terms": {
                "field": "campaign_id",
                "size": 10,
                "order": {
                    "_term": "asc"
                }
            },
            "aggregations": {
                "actions": {
                    "terms": {
                        "field": "action",
                        "size": 10
                    }
                }
            }
        }
    }
}

This is the response I get:

{
    "aggregations": {
        "campaigns": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
                {
                    "key": "someId",
                    "doc_count": 12,
                    "actions": {
                        "doc_count_error_upper_bound": 0,
                        "sum_other_doc_count": 0,
                        "buckets": [
                            {
                                "key": "click",
                                "doc_count": 3
                            },
                            {
                                "key": "view",
                                "doc_count": 9
                            }
                        ]
                    }
                }
            ]
        }
    }
}

Example of a document:

{
    "_index": "action",
    "_type": "click",
    "_id": "AVI2XOTl8otXlszOjypT",
    "_score": 1,
    "_source": {
        "ip": "127.0.0.1",
        "timestamp": "2016-01-12T15:03:23.622743524Z",
        "action": "click",
        "campaign_id": "IypmiroC"
    }
}

I need to be able to retrieve the conversion rate of each campaign ( clicks / views ) , and I can't do it on the client side since I need to be able to sort by conversion rate. Is it possible on elasticsearch?

Any help would be much appreciated.

[clintongormley]

No, it's not possible to do this at the moment, with the current structure of your documents. You can calculate the ratio using a pipeline aggregation as follows:

GET _search?size=0
{
  "aggregations": {
    "campaigns": {
      "terms": {
        "field": "campaign_id"
      },
      "aggregations": {
        "clicks": {
          "filter": {
            "term": {
              "action": "click"
            }
          }
        },
        "views": {
          "filter": {
            "term": {
              "action": "view"
            }
          }
        },
        "ratio": {
          "bucket_script": {
            "buckets_path": {
              "clicks": "clicks._count",
              "views": "views._count"
            },
            "script": "clicks/views"
          }
        }
      }
    }
  }
}

But you can't sort the terms agg on the result of ratio. Possibly you'll be able to sort later on once #14928 is implemented.

What you can do though is to create a summarised view on your indexed documents to group them together and index click and view rates within a single document for each campaign. Then what you're after will be easy to do.

Have a look at this talk about entity centric indexing for more guidance: https://www.youtube.com/watch?v=yBf7oeJKH2Y

[DeeeFOX]

This is not suitable to data which is ambiguous of how many types it does have util aggregation result show up to us. Just like in this example, if we don't know the exactly action name ("clicks" or "views"), we can't get the aggregation result of the <each_type_count>/<total_count>!