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

user095459
user095459 Member
edited June 2021 in Charting

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 Member

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

  • MarkSnodgrass
    MarkSnodgrass Coach

    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

    **Check out my Domo Tips & Tricks Videos

    **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 Coach

    @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 Member

    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 Member

    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 Coach

    @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 Member

    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