Best practice for blank dates?

  • 4 May 2023
  • 1 reply

Userlevel 1

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 4 May 2023, 20:08

View original

1 reply

Userlevel 4
Badge +8

Hey @Bill Lustgarten - When dealing with issues like these I have takn the approach of adding a “Null” or “No Cohort” Dimension in your original “Account Cohort Month” Dimension.

Then on your transaction list I would insert a Column beside the original data load and label it something like Adjusted Account Cohort Month or Clean Cohort Month etc. and use the following formula structure.


if(isdefined('Transaction List Name'.'Account Cohort Month'),'Transaction List Name'.'Account Cohort Month','Account Cohort Month'."No Cohort")