Sum specific product sales from one e-mailadress

user046467

I've a data file which contains customer purchases.

Something like:

Product 1 - Email1

Product 2 - Email2

Product 3 - Email3

Product 1 - Email4

Product 2 - Email1

Product 3 - Email1

If the specific e-mailadress purchased both product 1 and product 2 (the bold lines above) the answer should be 1. It doesn't matter if he also purchased product 3.

Hopefully my question is clear, is this possible with beastmode?

Thanks in advance.

jaeW_at_Onyx

are you trying to sum sales? or count distinct appearances of an email? (google count distinct it may be what you need).

what do you want your final report to look like?

user046467

Thanks for your quick reply.

I just want to have the number of occurrences where a customer bought both Product 1 and Product 2. Which is 1 in the above example for all customers/e-mails.

I checked count distinct  but that isn't probably what I am looking for.

GrantSmith

@user046467

How are you displaying your information? Are you wanting to just show a list of customer? What if someone buys product 1 twice and product 2 twice? Do they count as 1 or 2 in that case?

user046467

@GrantSmith I just want the total number of these customers.

If they buy it twice it can count as 2 but the changes of this happening are very low so you don't have to take that in account.

jaeW_at_Onyx

@user046467 , if you only care about Product 1 and 2, then

count ( distinct CASE WHEN product in ('1', '2' ) then email end )

GrantSmith

@jaeW_at_Onyx if a customer only purchases product 1 then they'd appear in the count which isn't desired.

@user046467

The easiest option would be to utilize an ETL to filter your dataset twice (one for each product) then inner join back together based on email address from your product 1 and product 2 filter outputs and then count the number of emails in the resulting output.

jaeW_at_Onyx

@GrantSmith you're right. I think if it were me I would ListAgg all products purchased by email onto one row and then do a CASE with a LIKE with MySQL.

either that or Group By and combine strings in Magic 2. From there you could create a binary isProduct 1 and isProduct 2 using a CASE statement in a formula tile.