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

user095459
user095459 ⚪️
edited June 24 in Card Building

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.

Answers

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

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

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

    @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

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

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


  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

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

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