Hi. First time poster here. :)
We import transaction-level data with revenue and other info. One dimension in the data is “account cohort month”, which lists the month an account was created. This is a dimension list in the calendar of our hub.
However we have many transaction-level rows with a blank “account cohort month”. This is correct, as there are specific internal rules to what is considered an account.
When we try to create metrics pulling revenue from the transaction-level load, it is excluding all those with a blank “account cohort month”. We don’t want to exclude the blanks. When we analyze the cohorts, we want all the cohort months but also a bucket for “no cohort” or similar.
What’s the best practice for handling this? The transaction-level data is MANY rows, so manipulating the data in Pigment is probably ill-advised. We could have blanks populated as “NULL” or “No Cohort” before loading to Pigment, but since this isn’t a calendar item, would that even work? (Or how would we add this to the dimension list screenshoted above, so it recognizes it?)
Another possible workaround is to prepopulate the data using a dummy date (Jan. 1, 1900 or similar), so it is recognized as a date…but this feels like a workaround rather than a solution. Thanks.
Best answer by MKohli
View original
