measuring cross-purchase with purchase category in both row and column
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.
0 -
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
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"1 -
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.
1 -
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.
0 -
@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"1 -
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.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive