1707576 - Enable ability to filter/pivot on experiment and branch in UJET explores

Status: RESOLVED FIXED

(Data Platform and Tools :: General, enhancement, P1)

Description

[Frank Bertsch [:frank]]

Using experiments and branches is not as straightforward as adding a CROSS JOIN UNNEST. We need to account for Looker's use of symmetric aggregates.

We want to avoid the case of overcounting with SELECT COUNT(*). The problem is that if a user filters on experiment/branch, a single row could be present multiple times, causing the count to be wrong.

We can fix this if we require users to either pivot or select the field. Using Looker's Liquid in-query syntax, we can check whether the field is present. If the user has filtered on experiment/branch, we can require that they also selected it in the query (via view_name.field_name._is_selected).

That only works in this case because we know an experiment/branch cannot be present more than once per-row (that is, it's a unique map). So if we require that it is selected, we know that row will only be present once per-group.

If it were not unique, this method wouldn't work - so we can't use it for all repeated fields.

Comment 1

[Frank Bertsch [:frank]]

This is done. Here is an example viz that shows funnel analysis per-branch for an experiment.

Comment 2

[Frank Bertsch [:frank]]

https://github.com/mozilla/looker-spoke-default/pull/33