Using DateFacts and other Facts to parse a COUNT


Let’s say you have a relationship table that stores interactions between people and campaigns.

So one campaign can have multiple people, and one person can interact with multiple campaigns.

Data Model:

Base case is you want to know how many people were created by all Campaigns, within a date range.
That’s simple:

SELECT COUNT(Id(People),  Id(CampaignMemberId))

A dashboard or report filter can then filter this metric to the appropriate CreateDate

However, if you were to slice this metric by Campaign, you would suddenly end up with a record of a person for every campaign that it has interactions with. So if 1 person interacts with 3 campaigns, that person will be duplicated 3 times.

So in order to avoid this, let’s say we want to attribute this person’s creation to the campaign that has the earliest interaction with this contact.

This is the answer:

SELECT COUNT(Id(People),  Id(CampaignMemberId)) WHERE InteractionDateFact = (SELECT MIN(InteractionDateFact) BY Id(People), ALL OTHER WITHOUT PF)

We are telling GoodData that to find the global minimum interaction date for each person, without parent filters.

WITHOUT PF is necessary otherwise the metric will be looking only at the data set of whatever dashboard or report level filters restrict it to, which will then have a moving minimum, depending on people shifting those filters. WITHOUT PF makes sure we have the global minimum.

After finding the global minimum, we’re telling GoodData to only give us the row in the CampaignMember table that has a date fact that is equivalent to the minimum date fact for each person, and voila, we have the record with the earliest touch date.