Count No. Of Customers who bought the same product from more than one places

Question & Answer
, ,
1

Hello I just started using Quicksight and I could use some help from the community.
I have to do an analysis wherein I have to show if a customer has bought a product from Shop 1, has gone and bought the same product from Shop 2, or elsewhere.
The product bought is the same.
Basically, this is the snippet from my dataset :

Customer ID | Shops | Money Spent On Product Y

ABC | Amazon | 1000
ABC | Shopee | 1200
ABC | Tokopedia | 400
XYZ | Amazon | 2300
XYZ | UBuy | 1500
XYZ | TikTok Shop | 9500
JKL | TikTok Shop | 4000
JKL | Frwd | 300
SRT | Shopee | 3430

This is what I Want to Visualise:

Shops | No Of Customers | “Customers who bought from elewhere”

Amazon | 2 | 2
Frwd | 1 | 1
TikTok Shop | 2 | 2
UBuy | 1 | 1
Tokopedia | 1 | 1
Shopee | 2 | 1

I need to make a filter to show me “Customers who bought from elewhere” . This field should show that if ABC and XYZ bought from Amazon, did they buy from elsewhere as well.

I hope I’ve made it clear, please help me out if you could.
Thanks.

2

Hi,
I think getting this data ready in SPICE would be more useful using custom SQL.

3

@VaishnaviB If your datasource is RDMS, you can do it using custom SQL as suggested, if it’s a file, then you have to put it into s3 and use Athena.

4

The data is custom SQL and in SPICE

5

Hello @VaishnaviB, were you able to set up your table and the “Customers who bought from elsewhere field” how you wanted? I know you could utilize a distinctCountOver function in QuickSight to get the number of shops a Customer has been to, but I feel like using that with the table layout you want will be difficult to implement without a customer ID in the table. Let me know what you have been able to accomplish so far and we can try to go from there if you are still working on a solution.

6

I managed to solve it. I took my company as the base company and then counted for only these customers whether they took a product from other company as well. In this example, I took Amazon as my base company and then got a count that we had 2 customers and these customers were also customers of 4 other companies. So they took a product from Amazon (us/base company) and then also took products from 4 different companies