How to Create a Mixpanel Report for Wheel and Tyre Sales Tracking

Kevin P. · 2024-09-23T11:07:17.301Z

Hi all, Can anyone help and point me in the right direction to achieve a particular report in Mixpanel? For sales on our website I record 2 types of event: one `order-complete` and one or more `order-complete-item` - where the `order-complete-item` represents a line item on the order. All of the events have a property for `order_number` and the `order-complete-item` event has a property for `product_type` (which can be either Wheel or Tyre). I want to produce a report that shows how many orders have BOTH Wheel and Tyre sales but I simply can’t figure out how to group it. I think I need to use a Custom Property and have tried looking at just `order-complete-items` by Unique User and going from there, but when I filter by Tyre AND Wheel, my report shows no data which I know is not correct If anyone could point me in the right direction here, I’d really appreciate it Cheers

How to Create a Mixpanel Report for Wheel and Tyre Sales Tracking | Mixpanel Community

14 comments

· Sorted by Oldest

Andrew S.
·
·
Hi. I tried to replicate this with my data. It's not exactly an overlap. But see if this example gets you closer to what you want.
Loading attachments
Andrew S.
·
·
So I am taking the event (order-complete-item), doing an aggregate metric as a distinct count of a property (product_type), and then grouping by another property (order_number). Have it sorting from high to low. You can then see how many orders have 2 vs 1.
Andrew S.
·
·
maybe there is a way (which I can't test with my data structure), for you to measure "order-complete" doing an aggregate of a custom property. Have that custom property borrow the product_type from the order-complete-item event (https://docs.mixpanel.com/docs/features/custom-properties#borrow-properties-from-one-event-onto-subsequent-events) but you'd have to play around with that.
Kevin P.
·
·
That’s given me a few ideas, thanks so much for that! I’ve managed to get something so I could do it for various date ranges and compare manually. What I’m really trying to do is get a report which shows me how many orders have Wheel AND Tyre items on them each day and compare that over time but to do that I’d need to GROUP order-complete-item either by order_id or by Unique User/Device then ascertain whether that GROUP has both Tyre and Wheel items in it
Kevin P.
·
·
I will look into the link you’ve sent above though, maybe that’s the answer
Andrew S.
·
·
exactly. one thing you could do on your capture is on the order-complete event, add a list property which has the type(s) in it.
Code Sample: mixpanel.track("Song Played", {"Mood Tags" : ['happy','exciting','romantic'] });
so that attaches an array to the event. you could then do formulas on that property to get to things like "if tyre only, then . . . , if wheel only, then ... if tyre and wheel then...."
Kevin P.
·
·
Yeah good idea… unfortunately not going to help with my previous 18 months of data 😂 hindsight is a wonderful thing!!
Andrew S.
·
·
yeah, I hear you there. if I only knew what I know and do not yet know . . .
Andrew S.
·
·
technically you could export all of your data, modify it with the missing stuff, import it back in to replace your existing data . . . . but I've always just licked my wounds and moved forward.
Kevin P.
·
·
Yeah I think I’ll do the same, would be an extraordinary amount of data, although I expect you wouldn’t have to export it all would you?
Andrew S.
·
·
i'm not positive bc I've never put the effort into trying to do it. one of these days I am going to be getting RudderStack setup to push all of the data into Snowflake where I'd be able to more easily transform what I need and then ingest it back perhaps.
Kevin P.
·
·
Hmm… I’ve added a list property now for items which itself contains a wheel_tyre value of either Wheel or Tyre - but I can’t seem to figure out how to filter it… here’s what I’m currently doing
Loading attachments
Kevin P.
·
·
But I need wheel_tyre to be Tyre AND Wheel not Tyre OR Wheel… but I can’t seem to do that in Mixpanel - any ideas?
Kevin P.
·
·
Got it I think…
Loading attachments
💯1

Andrew S.
·
·
Hi. I tried to replicate this with my data. It's not exactly an overlap. But see if this example gets you closer to what you want.
Loading attachments
Andrew S.
·
·
So I am taking the event (order-complete-item), doing an aggregate metric as a distinct count of a property (product_type), and then grouping by another property (order_number). Have it sorting from high to low. You can then see how many orders have 2 vs 1.
Andrew S.
·
·
maybe there is a way (which I can't test with my data structure), for you to measure "order-complete" doing an aggregate of a custom property. Have that custom property borrow the product_type from the order-complete-item event (https://docs.mixpanel.com/docs/features/custom-properties#borrow-properties-from-one-event-onto-subsequent-events) but you'd have to play around with that.
Kevin P.
·
·
That’s given me a few ideas, thanks so much for that! I’ve managed to get something so I could do it for various date ranges and compare manually. What I’m really trying to do is get a report which shows me how many orders have Wheel AND Tyre items on them each day and compare that over time but to do that I’d need to GROUP order-complete-item either by order_id or by Unique User/Device then ascertain whether that GROUP has both Tyre and Wheel items in it
Kevin P.
·
·
I will look into the link you’ve sent above though, maybe that’s the answer
Andrew S.
·
·
exactly. one thing you could do on your capture is on the order-complete event, add a list property which has the type(s) in it.
Code Sample: mixpanel.track("Song Played", {"Mood Tags" : ['happy','exciting','romantic'] });
so that attaches an array to the event. you could then do formulas on that property to get to things like "if tyre only, then . . . , if wheel only, then ... if tyre and wheel then...."
Kevin P.
·
·
Yeah good idea… unfortunately not going to help with my previous 18 months of data 😂 hindsight is a wonderful thing!!
Andrew S.
·
·
yeah, I hear you there. if I only knew what I know and do not yet know . . .
Andrew S.
·
·
technically you could export all of your data, modify it with the missing stuff, import it back in to replace your existing data . . . . but I've always just licked my wounds and moved forward.
Kevin P.
·
·
Yeah I think I’ll do the same, would be an extraordinary amount of data, although I expect you wouldn’t have to export it all would you?
Andrew S.
·
·
i'm not positive bc I've never put the effort into trying to do it. one of these days I am going to be getting RudderStack setup to push all of the data into Snowflake where I'd be able to more easily transform what I need and then ingest it back perhaps.
Kevin P.
·
·
Hmm… I’ve added a list property now for items which itself contains a wheel_tyre value of either Wheel or Tyre - but I can’t seem to figure out how to filter it… here’s what I’m currently doing
Loading attachments
Kevin P.
·
·
But I need wheel_tyre to be Tyre AND Wheel not Tyre OR Wheel… but I can’t seem to do that in Mixpanel - any ideas?
Kevin P.
·
·
Got it I think…
Loading attachments
💯1