Use an existing report to filter a new report

abivelj · July 2, 2024, 4:11am

For example, my client wants to know who purchased an item in a particular category on their first purchase.

Then from there they want to see another report that shows how many other products did those customers purchase.

This is easy in SQL with temp tables.

Would love this feature for a few dynamic reports.

tan · July 2, 2024, 8:36am

Hi Sehab,

Thank you for your request. We don’t currently have the exact functionality you described, but I’m pleased to inform you that we can accommodate your request using AQL. Here’s how you can achieve similar results:

To identify customers who made their first purchase in a particular category: (link)

unique(users.id, orders.id, categories.name) 
| filter(
    rank(
      partition: users.id, 
      order: min(orders.created_at) | of_all(categories.name)
    ) == 1
  )
| filter(categories.name == '[Your Category]')

To then see how many other products these customers purchased:

count(products.id) | where(
  users.id in (
    unique(users.id, orders.id, categories.name) 
    | filter(
        rank(
          partition: users.id, 
          order: min(orders.created_at) | of_all(categories.name)
        ) == 1
      )
    | filter(categories.name == '[Your Category]')
  )
)

You can see this query in action and experiment with it further here: [Link to query playground]

abivelj · July 2, 2024, 2:49pm

How do I add this as part of a condition when building the visualization.
Something like this:

tan · July 3, 2024, 10:33am

Hi Sehab,

We don’t support using custom AQL as a filter yet. But in this case, you can put the whole thing into a metric.

Regards,