HELP CENTER

HELP CENTER

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Dojo
- :
- Using Domo
- :
- Beast Mode, ETL/Dataflow
- :
- Average Basket Size

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-25-2019
10:08 AM

03-25-2019
10:08 AM

I am looking for a way to create an average basket size by date

Assuming I have data set that looks like this:

Invoice | Date | SKU | Sold Price |

111 | Jan 4 | Product A | 10 |

111 | Jan 4 | Product B | 20 |

112 | Jan 4 | Product C | 5 |

113 | Jan 4 | Product A | 10 |

113 | Jan 4 | Product B | 20 |

113 | Jan 4 | Product C | 5 |

114 | Jan 20 | Product A | 10 |

115 | Jan 20 | Product A | 10 |

115 | Jan 20 | Product C | 5 |

116 | Jan 20 | Product D | 45 |

In the example above the Average basket size during Jan 4 would be the average of invoices 111, 112, and 113: (30 + 5 + 35) / 3 = 23.333

Whereas on Jan 20, the average basket size was average of invoices (114,115,116): (10+5+45)/3 = 20

The goal would be to ultimately create a chart that will show what the average basket size is over time (likely grouping the individual dates by week), but before I get to that point, I assume I need to do a beastmode calculation to actually populate those values first

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-27-2019
07:44 AM

All Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-25-2019
11:04 AM

03-25-2019
11:04 AM

Re: Average Basket Size

Good morning,

I believe you could do a standard bar chart. the Y axis would be your date field, and the X, your Sold Price. Then chage the Claculation to Average. It will probably try to SUM or Count it at first. Is that what you're looking for?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-25-2019
11:18 AM

03-25-2019
11:18 AM

Re: Average Basket Size

Hi, a beastmode calculation would be

Sum(`Sold Price`) / Count(Distinct `SKU`) but, the problem with it would be at the moment of displaying the data , can be tricking since you will have to group by date ... the moment you add by SKU , the calculation will give you the same value as the sum of Sold Price.

If I undestand well , you want to add a column with the Average Basket Size for the day to apply to all rows

Like this Magic ETL shows :

If this is your desired outcome follow this qucik steps to build a magic ETL flow to:

1) Group by date the sum of Sold Price and the Count distinct of SKUs

2) Use the calculator to create the Average Basket Size for the day

3) Join the calculation back to the original dataset using a left join , then select the columns you want to display in the dataset and generate a new output dataset

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.

**Please mark the post that solves your problem as 'Accepted Solution'

**Say 'Thanks' by clicking the thumbs up in the post that helped you.

**Please mark the post that solves your problem as 'Accepted Solution'

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-26-2019
07:36 AM

03-26-2019
07:36 AM

Re: Average Basket Size

@Sweep_The_Leg wrote:Good morning,

I believe you could do a standard bar chart. the Y axis would be your date field, and the X, your Sold Price. Then chage the Claculation to Average. It will probably try to SUM or Count it at first. Is that what you're looking for?

Unfortunately this wouldn't show the right numbers in my case.

Reason being, each product purchased shows up on a separate line of the data. So in the example above, for january 4, it would average 10,20,5,10,20,5 = 11.67 instead of combining the 3 transactions there to arrive at a basket size average of (30,5,35) = 23.33.

Thanks for the suggestion though

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-26-2019
08:12 AM

03-26-2019
08:12 AM

Re: Average Basket Size

gotcha, you're grouping by Date and Invoice. Sorry i missed that. I would recocommend the other suggestion about going through an ETL.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-26-2019
08:32 AM

03-26-2019
08:32 AM

Re: Average Basket Size

Ha! So I messed up my original numbers, and just so happened to pick numbers that accidentally resulted in the exact same average basket size on both Jan 4 and Jan 20. Whoops!

I looked in to instead using the Magic ETL flow and it works perfectly well on the per-day level.

The challenge ends up being when you group more than two days in an actual chart /card

Invoice | Date | SKU | Sold Price |

111 | 4-Jan | Product A | 10 |

111 | 4-Jan | Product B | 20 |

112 | 4-Jan | Product C | 5 |

113 | 4-Jan | Product A | 10 |

113 | 4-Jan | Product B | 20 |

113 | 4-Jan | Product C | 5 |

114 | 20-Jan | Product A | 10 |

115 | 20-Jan | Product A | 10 |

115 | 20-Jan | Product C | 5 |

116 | 20-Jan | Product D | 45 |

117 | 20-Jan | Product E | 2 |

118 | 20-Jan | Product E | 2 |

119 | 23-Jan | Product E | 2 |

120 | 23-Jan | Product E | 2 |

121 | 23-Jan | Product E | 2 |

122 | 23-Jan | Product E | 2 |

123 | 23-Jan | Product E | 2 |

So If I add a few new sales, (transaction 117 - 123)

The avg basket size on Jan 4 ends up being 23.33 and the avg basket size on Jan 20 ends up being 14.8, and the average for Jan 23 is 2.

The challenge ends up being that when I move this dataset output in to creating a Domo Card. If I try to group by Month, the average basket size ends up averaging those 3 subtotals, rather than all of the individual transactions. In other words, the average basket size reported by the domo card ends up being (23.33 + 14.8 + 2) / 3 = 13.38 whereas the ACTUAL average basket size would be the average of all the individual transactions. Averaging the sold prices below gives us a real average basket size of 11.85

Invoice | Sum of Sold Price |

111 | 30 |

112 | 5 |

113 | 35 |

114 | 10 |

115 | 15 |

116 | 45 |

117 | 2 |

118 | 2 |

119 | 2 |

120 | 2 |

121 | 2 |

122 | 2 |

123 | 2 |

It seems to me that there may not be a way to have a true calculation of basket size. my only thought would be if within beast mode there would be a way to write a more complicated mysql statement, but not sure if such a statement would be possible.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-26-2019
08:37 AM

03-26-2019
08:37 AM

Re: Average Basket Size

what about two groupings in the ETL?

group 1

group by date, invoice, SUM of Sold Price

Group 2 (grouping off of group 1)

group by Date, average of Sum of Sold Price.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-27-2019
06:56 AM

03-27-2019
06:56 AM

Re: Average Basket Size

Sorry, I'm not sure I understand exactly how two groupings would help me. Would you be able to elaborate?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-27-2019
07:44 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

03-28-2019
11:15 AM

03-28-2019
11:15 AM

Re: Average Basket Size

This will work for what I need. Looks like the answer is that you effectively need to hard-code the averages in to the data set through magic ETL before you apply it to a domo card. Was able to make this work for my data, and was able to set up domo cards that will accurately represent the average Monthly basket size.

Then, the domo card shows an average across the months I've selected - that average isn't 100% accurate, since it's the average of 3 averages. If one month had 100 transactions with average basket size of 100, and another month had 1 transaction with an average basket size of 50, the card would just show me an average of 75. Fortunately, the data sets i'm working with are large enough that I don't think I will have this issue.

thanks for all your help!

Announcements