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

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • 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.

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @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.

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


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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • @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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"