Excel Sales Pinboards
Sales Baskets
Details about a shoppers basket contents. Basket is a term meaning each individual sale. For statistical purposes seperate sales that we strongly believe are the same customer may be combined. For example if sale #1 is to Bob for X,Y and Z, and then 30 seconds later there is sale #2 to Bob for J, these may be combined and treated as one basket when reporting. The reasoning being they either were prompted to buy something else, or are splitting sales for some reason
- sales.basket.size
Average number of items in a basket. - sales.basket.counts
A list of actual number of baskets for each count size. How many baskets had one item, how many had two etc, progressively increasing the size in each result. See notes for example - sales.basket.samples
Number of baskets (sales) used in calculations - sales.basket.size.stddev
The value of 1 standard deviation from the median - sales.basket.size.min
The smallest number of items in a basket - sales.basket.size.max
The largest number of items in a basket - sales.basket.size.median
The median number of items in a basket
The number of items is the quantity sold for unit based items. For variable measured items each saleline counts as quantity=1.
| Item | Qty | Measured |
| Tin of catfood | 2 | Each item |
| Loose carrots | 752gm | Per gram |
| Total Items | 3 | 2 tins + 1 line of carrots |
| Parameter | Name | Notes | Examples |
| 1 | Start Date | Only include sales after this date. If not provided, the previous 31 days is selected | 1-jan-2020 today-180 |
| 2 | End Date | Only include sales up to this date. If not provided the default is to stop at midnight last night | 1-jan-2021 today-90 |
| 3 | Restrictions | Apply additional 'restrictions' to sales selection. See Notes |
Restrictions provide additional filtering of the sales, the following restrictions are available.
| Keyword | Description | Example |
| store N | Only consider sales made in store N | |
| customer N | Select only sales to customer N | |
| teller N | Only sales completed by teller# N | teller=16 |
| depid N | Select only sales that include at least 1 item from department N | |
| spid N | Select only sales that include at least 1 item from supplier N | |
| maxqty N | Exclude salelines that have a quantity > N. This can be useful to exclude wildly wrong sales that have excessive quantity and distort results. |
Examples
| Formula | Output | Notes |
| =pinboard("sales.basket.size") | 2.18 | Average number of items per basket in last 31 days |
| =pinboard("sales.basket.samples") | 4372 | Number of baskets evaluated |