the only way I have found to solve this is exporting everything in GoodData in excel and filtering for the cohorts there, but surely there must be a better way? I have set a metric

SELECT COUNT(*Id (CRM deals)*)

but this doesn't seem to stay static per applicant, which is what I am trying to get to.

I assume this is because you are using the metric value filter in the UI which only applies to the whole granularity of the insight. And because in your insight you are viewing the requests by user AND lost reaso, it is counting and filtering deals by this combination. So the filter is only applied if any combination of user+lost reason is one. There is a way how to achieve the filtering you want with a MAQL metric. Something like this should work:

SELECT COUNT(Id (CRM deals)) WHERE (SELECT COUNT(Id (CRM deals)) BY email, ALL OTHER) = 1

Here the inner SELECT (especially the "BY email, ALL OTHER") makes sure it will count Ids BY user and nothing else and use this calculation for the filtering no matter what other attributes you use in your insight.

Thank you @Michal Hauzírek! I am going to try this out today and report back!

You need to enclose the inner SELECT into parentheses. So put ( in front of the second SELECT and put ) before the =1 and that should do the trick.

the ALL OTHER means that it should only aggregate by the attributes in front of this keyword and for any other attribute that might appear in the insight, it should not aggregate by it and instead use the same aggregated value for each value of the other attributes. Well, that sounds strange, I admit. Maybe let me try to explain on an example instead 🙂 So in your case you are counting the

id

by

email

and

lost reason

. Let’s assume this data:

id, email, lost reason
1, john, too early
2, john, too early
3, pete, too early
4, pete, unavailable

And now we can have four metrics: • simple

SELECT COUNT(id)

with split the data based on what attributes you use in the insight •

SELECT COUNT(id) BY email ALL OTHER

will only be split by email but no other attribute (not lost reason in this case). And the value of count id by email will get repeated for all values of all other attribtues. •

SELECT COUNT(id) BY lost reason ALL OTHER

is equivalent, just it splits data only by lost reason and not anything else • and the extreme case is SELECT COUNT(id) BY ALL OTHER (without any attribute mentioned) - that means we do not want to split the value by any attribute ever - so it is total overall number of ids and this value is repeated for every single value of any attribute ◦ this one might be useful for example to calculate percentage of total if you ever wanted that i.e.

SELECT COUNT(id) / (SELECT COUNT(id) BY ALL OTHER)

Thanks Michal, I am going to mirror back to make sure I actually understand this. Assuming the following data:

id, email, lost reason
1, john, too early
2, john, too early
3, pete, too early
4, pete, unavailable

•

SELECT COUNT(id)

- the number of tickets (id) that each email has per lost reason. John has 2 tickets that are too early. Pete has 1 ticket that is too early. Pete has 1 ticket that is unavailable. •

SELECT COUNT(id) BY email ALL OTHER

- the number of tickets per email. John and Pete made two requests each, so this is 2 each time. •

SELECT COUNT(id) BY lost reason ALL OTHER

- the number of tickets per lost reason. There are 3 tickets that are too early, and 1 ticket that is unavailable. •

SELECT COUNT(id) / (SELECT COUNT(id) BY ALL OTHER)

- the number of tickets that each email has per lost reason, divided by the number of tickets that exist in the database in total. So this becomes 50%, 25%, 25% for this table. Question, how would you deal with a situation like this?

id, email, lost reason
1, john, <no data>
2, john, too early
3, pete, too early
4, pete, unavailable
5, andrew, <no data>

Let's say I wanted to know the number of users who have a ticket with a lost reason that isn't empty. How would I write a query that excludes Andrew but includes John? (i.e. a query that returns 2, as John has a ticket with a lost reason that isn't empty, even though he also has a ticket where the lost reason is empty)

Yes! You got it exactly right, Hesseltje! For counting number of users with non-empty lost reasons. If you want to count john in (since he has a row with lost reason) it can be as simple as:

SELECT COUNT(user) WHERE lost reason<>(empty value)

This will filter out rows 1 and 5 and then counts distinct number of users in the remaining rows so should give you 2 (john and pete).