Skip to content

Calculating the average number of views per purchase

Imagine we're sending two types of signals.

  1. upgradeDetailViewDidLoad - when a user opens the upgrade detail screen
  2. purchaseSuccessful - when a user completes a purchase

We want to know how many times a user opens the upgrade detail screen before completing a purchase. This is a really good candidate for a groupBy query!

Our objective

At a high level, we want to do these three things:

  1. Only consider signals of type upgradeDetailViewDidLoad and purchaseSuccessful in our query. We can achieve that with an and filter.
  2. Count the number of occurrences of each signal. We'll use filtered aggregators for that.
  3. Calculate the average number of views per purchase. We'll use the arithmetic post-aggregator for that.

Let's look at the various parts of our query in detail. If you just want the finished query, you can find it at the bottom of this article.

If you're new to TQL, you can read more about queries and filters in the documentation.

Boilerplate

We'll start with the boilerplate of our query. We'll use the groupBy query type, and we're setting the granularity to all so that we get a single result.

{
  "queryType": "groupBy",
  "dataSource": "telemetry-signals",
  "granularity": "all",
  "filter": null, // will be set later
  "aggregations": null, // will be set later
  "postAggregations": null, // will be set later
  "dimensions": null // will be set later
}

Filtering

We only want to consider the signals of type upgradeDetailViewDidLoad and purchaseSuccessful. We can achieve that with an or filter.

"filter": {
  "type": "or",
  "fields": [
    {
      "type": "selector",
      "dimension": "type",
      "value": "upgradeDetailViewDidLoad"
    },
    {
      "type": "selector",
      "dimension": "type",
      "value": "purchaseSuccessful"
    }
  ]
}

Aggregation

We want to count the number of occurrences of each signal. We'll use filtered aggregators for that.

"aggregations": [
  {
    "type": "filtered",
    "filter": {
      "type": "selector",
      "dimension": "type",
      "value": "purchaseSuccessful"
    },
    "aggregator": {
      "type": "doubleSum",
      "name": "_purchase_successful_count",
      "fieldName": "count"
    }
  },
  {
    "type": "filtered",
    "filter": {
      "type": "selector",
      "dimension": "type",
      "value": "upgradeDetailViewDidLoad"
    },
    "aggregator": {
      "type": "doubleSum",
      "name": "_views_count",
      "fieldName": "count"
    }
  }
]

{% noteinfo "Why does this use the doubleSum aggregator?" %} When almost-identical TelemetryDeck signals are sent, they are merged into a single database entry. This is why signals have a count field, which will increase with each merge. We're using doubleSum here to make sure that we're counting the number of signals, not the number of database entries.

We're prepending our field names with underscores. This is because they are intermediary steps that we don't want to display in the results. Any field name that starts with an underscore will be hidden in TelemetryDeck insight UI.

Post-Aggregation

We now have the number of views and the number of purchases. To get the average number of views per purchase, we'll use the arithmetic post-aggregator to divide the number of views by the number of purchases, resulting in the average number of views per purchase.

"postAggregations": [
  {
    "type": "arithmetic",
    "name": "Average Views per Purchase",
    "fn": "/",
    "fields": [
      { "type": "fieldAccess", "fieldName": "_views_count" },
      {
        "type": "fieldAccess",
        "fieldName": "_purchase_successful_count"
      }
    ]
  }
]

Optional: Grouping

If we want to group the results by a dimension, we can specify a dimension in our query. This can help us track improvements in the numbers made over time, or compare the numbers between different versions of our app.

For example, if we want to group the results by appVersion, we can add the following to our query:

"dimensions": [
  {
    "dimension": "appVersion",
    "outputName": "App Version",
    "outputType": "STRING",
    "type": "default"
  }
]

This will give us the average number of views per purchase, separate for each version of our app.

{% noteinfo "Grouping by time" %} If we instead want to group by date, we don't even need to specify a dimension. We set the granularity to day, week, month, or year and we'll get the results grouped by that time period.

Putting it all together

Here's our finished query in all its glory:

{
  "queryType": "groupBy",
  "dataSource": "telemetry-signals",
  "granularity": "all",
  "filter": {
    "type": "or",
    "fields": [
      {
        "type": "selector",
        "dimension": "type",
        "value": "upgradeDetailViewDidLoad"
      },
      {
        "type": "selector",
        "dimension": "type",
        "value": "purchaseSuccessful"
      }
    ]
  },
  "aggregations": [
    {
      "type": "filtered",
      "filter": {
        "type": "selector",
        "dimension": "type",
        "value": "purchaseSuccessful"
      },
      "aggregator": {
        "type": "doubleSum",
        "name": "_purchase_successful_count",
        "fieldName": "count"
      }
    },
    {
      "type": "filtered",
      "filter": {
        "type": "selector",
        "dimension": "type",
        "value": "upgradeDetailViewDidLoad"
      },
      "aggregator": {
        "type": "doubleSum",
        "name": "_views_count",
        "fieldName": "count"
      }
    }
  ],
  "postAggregations": [
    {
      "type": "arithmetic",
      "name": "Average Views per Purchase",
      "fn": "/",
      "fields": [
        { "type": "fieldAccess", "fieldName": "_views_count" },
        {
          "type": "fieldAccess",
          "fieldName": "_purchase_successful_count"
        }
      ]
    }
  ],
  "dimensions": [
    {
      "dimension": "appVersion",
      "outputName": "App Version",
      "outputType": "STRING",
      "type": "default"
    }
  ]
}