Sum specific product sales from one e-mailadress

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.

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    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?

  • 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
    GrantSmith Indiana 🔴

    @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?

  • @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
    jaeW_at_Onyx Budapest / Portland, OR 🟤

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


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

  • GrantSmith
    GrantSmith Indiana 🔴

    @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
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @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.