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

user095459
user095459 ⚪️
edited June 2021 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

  • user095459
    user095459 ⚪️

    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

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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

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

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

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

Categories