measuring cross-purchase with purchase category in both row and column

user095459

I'm trying to create a table that shows cross-purchase with customers, so what customers who purchased in one value of a dimension also purchased in a different value of the same dimension. Kind of like those old distance charts that have different cities plotted against each other on x and y axis, with the count of customers where they intersect, e.g. 10% of customers who have purchased in category A (row) have also purchased in category B (column) and similar percentages for other combinations . I thought perhaps a pivot table would be a good way to do that, but I noticed that I am only allowed to use a dimension in the row or the column in Domo.

user095459

I imagine there is a way to do this, perhaps better than I'm initially imagining, so I welcome any suggestions.

MarkSnodgrass

You might try the Heat Map chart. It is useful for things like this. See the example in this KB article:

https://domohelp.domo.com/hc/en-us/articles/360042924634-Heat-Map

jaeW_at_Onyx

@user095459

I've worked on this problem for a client.

You'd have to build a table that does a CROSS APPLY.

So if you have data,

item_id, order_id, amount

you'd do a self join

SELECT

a.*

, b._item_id

from table a

JOIN table b

ON a.orrder_id = b.order_id

and A.item_id < b.item_id

user011099

thanks jaeW! can you expand on this a bit as I'm struggling to complete this exercise as well? I have all my sales data by customer_id and item_id in one table and I'm trying to do exactly what OP described.

user011099

also to clarify, I'm not trying to show purchases with the same order, just trying to show count of multiple items bought by same customer like the attached picture.

ASINs bought togehter.png

jaeW_at_Onyx

@user01109 it's the exact same code / solution. just JOIN on customer instead of order.

user095459

We are unfortunately limited through through a third-party Domo license, which hosts federated data externally, so we don’t get all of the nice data ETL functionality. I’m limited to Beast Mode for data manipulations. I wondered if maybe there’s something I hadn’t considered, but it seems like we would need to build a separate table or have the ability to do Joins.