Join explosion when trying to compute metrics from two different views

[alex_acl]

We are trying to compute measures that need to use fields from different views/tables. For example, we have the CAC metric (Cost to acquire a customer). For this, we need to divide the total marketing cost from our marketing table between the new customers metric from the customers table.

Since there is no key to join these tables, we tried joining by the date. This made the table huge and the SQL query wasn't even finished running.

We tried also creating pre-aggregated daily tables for each of these tables with important measures to reduce the data. The problem with this approach is that now we are creating the same looker measures  in two different explores. For example, we now have new customers as a measure in the customers view and also in the daily aggregated table. We are worried this can be problematic in the future when we change a measure in one place but not in the other one.

We would like a solution that involves having only one place to define each of our measures.

Any ideas?

[GavinW]

One option might be to use the merged query functionality in Looker - it has limitations as it needs to bring all of the data into memory for processing, and you are limited to 5000 rows of data, but for a specific narrow use case like this it might be worth investigating?

[alex_acl]

Yes, that's an option we have also investigated and tried, but we would like to have the measures in LookML in order to have them standardized and allow non-technical users to access these measures easily (they get quite confused about merged queries).

[GavinW]

That makes sense. Have you considered rolling up the data in both tables and creating derived tables at a level that makes sense (e.g. daily, or monthly, or whatever) and then joining those derived tables in an explore on the date element?

[alex_acl]

Yes, we have also tried that. The issue is that in that case, we will be having the same measures on different views being created. This can be problematic in the future if we want to modify one measure and forget the other one. It also increases complexity on the model so we wanted to avoid it.