Hey all, hitting a wall with an issue and seeing if I can get some suggestions.
I've got a BigQuery table added to a Looker dash. It contains data on web events happening during experiments, so the layout is - to simplify - "Experiment" "Variant", "Date", "Visit", "Order" along with other fields containing other metrics / events where they exist. Say an order exists, then the row will contain an order ID. Say a different metric is hit, then in a different row that will have it's unique ID filled in. This leads to multiple rows for the same session with the same experiment and variant tagged, and different events tagged too. Getting anything into a table in Looker therefore involves using Count Distincts - no issues there.
I can create a table with Date as Dimension, Orders as the metric, and get a view of orders per day. I can do a running sum at this point and get the running sum of orders across the duration. I want to be able to compare one variation (the control) against another (the test) and to achieve this I blended the table with itself using an inner join on Experiment and Date. The blend includes orders in table 1 as a CTD, as well as in table 2. I also have "Variant" as a dimension in both. This means that by using dropdowns and selecting an Experiment, I then can select two variations from their own dropdowns, the first being "Variant (Table 1)", second being "Variant (Table 2)". I don't believe I have any way around this given the nature of the data I have, and needing to be able to compare rows in the data, translating them into separate columns via this blend enables this. For 90% of my use cases for this dashboard I have gotten what I need. With a running sum, I am stuck.
The problem is, the daily calls number isn't working in a running sum. I have googled enough to understand this is a known issue / part of how aggregation works, but was wondering if there was any possible solution i'm missing. I've tried creating a unique key at datasource level by combining experiment, date, and a visit id into an identifier, and inner joining on that, as every row should be matched with itself then, but this didn't seem to fix my running sum either. I can't change anything at BigQuery level in the query that creates the dataset itself, so I'm at a wall! Any ideas would be really appreciated, they just need to be doable within Looker Studio ideally. Though if it just does not sound doable then happy to hear ones that are done at query / table creation stage as I can feed them back.
LinkedIn
Twitter
