Library
Excel Product Pinboards - Definitions
Definition pinboards return the data your have used to define a product
Basic Attributes
Pinboard names seperated by dots
- product.description
- product.plucode
- product.unitprice
- product.currentcost
- product.supply.priority
- product.supply.importance
- product.publish
Pinboard names seperated by spaces
- product description
- product supplier rrp
- product wholesale price N (where N is 1,2,3 or 4)
- product size
- product order message
- product is polarised
- product is mirrored
- product placement id
Examples
| Formula | Output | Notes |
| =pinboard("product.description", 1234) | Tackle box | Retrieve the current description of product 1234 |
| =pinboard("product size", 414) | 250gm | Current descriptive size of the product |
Product Departments
- product department N id
- product department N name
Examples
| Formula | Output | Notes |
| =pinboard("product department 2 name", 1234) | Dior | Get Department#2 name for product # 1234 |
| =pinboard("product department 2 id", 1234) | 17 | Get Department#2 internal id# for product # 1234 |
| =pinboard("product department 6 name", 456) | Plastic Ware | Get Department#6 name for product # 456 |
| =pinboard("product department 7 name", 456) | Get Department#7 name for product # 456. The output in this example indicates there is no department 7 assigned |
Marketing Colours
- product marketing color N
Extract the marketing color assigned to a product. The value of N can range from 1 to 4 (incl). Marketing colors contain names of colors and are typically used by eCommerce websites. The word color can be spelt either 'color' or 'colour', both are understood
Examples
| Formula | Output | Notes |
| =pinboard("product marketing color 3", 123) | Cherry Red | The word assigned to 'marketing color 3' for product 123 |
Simple Analytics
Sales Volumes and Revenue
- product.sales.revenue.period
- product.sales.revenue.notax.period
- product.sales.cost.period
- product.sales.count.period
Sum of quantity for all saleline. If a single saleline has a quantity of 2, it is counted as 2. For non unit items this figure is in raw measurement units, so can result in quite large results. - product.sales.linecount.period
Total number of salelines. If a single saleline has a quantity of 2, it is counted as one, as there is only one saleline.
Calculate sales totals for a single product
Arguments
| Parameter | Name | Notes | Examples |
| 1 | Pid | Required, with exceptions, see notes | 123 |
| 2 | Start Date | Only include sales after this date. If not provided, then yesterdays date is used is used | 1-jan-2020 today-180 |
| 3 | 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 |
| 4 | Location | Select a single location. The default is "0" which means all locations | 14 |
| 5 | Sales Type | ||
| 6 | Restrictions | Apply additional 'restrictions' to sales selection. See Notes |
Restrictions provide additional filterig of the sales, the following restrictions are available.
| Keyword | Description | Example |
| kit or combo | Select only salelines that were repriced due to a kit/combo | kit |
| markdown or oneoff | Select only salelines that were sold using a markdown label | |
| reward | Select only salelines that were as a result of loyalty rewards being redeemed | |
| supplier N or spid N | ||
| department N or depid N | ||
| teller N | Only sales completed by teller# N (From version P202) | teller=16 |
N
Examples
| Formula | Output | Notes |
| =pinboard("product.sales.revenue.period", 123) | 4056.92 | Total revenue for pid 123 yesterday |
| =pinboard("product.sales.count.period", 123, "1-jan-2021", "1-feb-2021",,,"markdown") | 129 | Sum of all quantities, sold in Jan 2021, for Pid 123, that were sold due to a markdown price. |
| =pinboard("product.sales.revenue.notax,period", 123, "1-jan-2020", "1-jan-2021",,,"teller=16") | 43218.06 | Total revenue for pid 123 for all of 2020, sold by teller#16 |
Purchased With
- product.commonpurchase
Returns the product most commonly sold with a product, based on number of occurances, not quantity involved.
Arguments
| Parameter | Name | Notes | Examples |
| 1 | Pid | Required | 123 |
| 2 | Start Date | Only include sales after this date. If not provided the previous 90 days are used | 1-jan-2020 today-180 |
| 3 | End Date | Only include sales up to this date | 1-jan-2021 today-90 |
Examples
Sample Data
| Sale# | Pid | Qty |
| 80100 | 123 | 1 |
| 80100 | 17182 | 1 |
| 80101 | 123 | 1 |
| 80101 | 17 | 10 |
| 80101 | 17182 | 2 |
| Formula | Output | Notes |
| =pinboard("product.commonpurchase", 123) | 17182 | Pid 17182 is in 2 sales. While pid 17 has more quantity it is only in 1 sale |
| =pinboard("product.commonpurchase", 123, "year") | 17182 | Includes all sales this calender year |
| =pinboard("product.commonpurchase", 123, "year-1", "year") | 17182 | Includes all sales for the previous calender year |
Strikerate
- product.strikerate
The percentage of sales that included a given product. This is the number of sales the product appears in, regardless of quantity sold.
Arguments
| Parameter | Name | Notes | Examples |
| 1 | Pid | Required | 123 |
| 2 | Location Id | ||
| 3 | Start Date | Only include sales after this date. | 1-jan-2020 today-180 |
| 4 | End Date | Only include sales up to this date | 1-jan-2021 today-90 |
Examples
| Formula | Output | Notes |
| =pinboard("product.strikerate", 123, 0, "1-jan-2020", "1-jan-2021") | 14 | Pid 123 is in 14% of all sales, all stores in 2020 |
| =pinboard("product.strikerate", 123, 4, "1-jan-2020", "1-jan-2021") | 17 | Pid 123 is in 17% of all sales in store #4, in 2020 |