Potential dashboard / tabular reports for Woocommerce product to sales analysis
Top view = ‘Sales by product’
- Product centric view
- Columns:
- General Meta:
- Sku
- Name
- Category count = how many categories product appears in
- Category, (1st lowest only)
- Sales
- Net $ total
- Net $ per prev 12 months
- Net $ per prev months 13-24
- Net $ prev months 25 and earlier
- Net $ per month last 13 months
- Unit $ per month last 13 months (where sold by length is per meter)
- Unit $ per year (where sold by length is per meter)
- Qty $ per month last 13 months (where sold by length is meters)
- Qty $ per year (where sold by length is meters)
- Unit Min $
- Unit Max $
- Unit Avg $
- Costs
- Now: Cost per unit or as close as we can get
- Later:
- Show purchasing history
- Reconcile Stock levels vs sold vs purchased to identify gaps
- General Meta:
- Columns:
To consider: version of ‘Sales by Product’ but is ‘Sales by Length’ for products sold per custom lengths
Top view = ‘Sales by category‘:
- Product category centric view
- Columns:
- Category
- Issue: Which category level? Highest or lowest?
- Now:
- Lowest
- Net $ per month last 13 months
- Net $ per year
- Net $ total
- Category
- Issue: products in multiple categories, and currently no mechanism to identify primary category
- Now:
- show the product in each category, will skew any totals but will assume is acceptable for now
- Include smaller table showing products in multiple categories so reminded they exist
- Later:
- Add method to identify primary category and use that only per product
- Now:
- Click on category:
- Takes us to Sales by product filtered to target category
- Columns:
View for creating a monthly ‘snapshot’ into a table
Note, used https://codebeautify.org/sqlformatter thank you very much
alter VIEW `vProductSales` AS -- 20181110 LSR -- Initial version -- 20181111 Added sku, stock (on hand) select now() AS `snapped`, `pr`.`ID` AS `ID`, pm_sku, `oim`.`order_item_type` AS `order_item_type`, coalesce( `pr`.`post_title`, `oim`.`order_item_name` ) AS `coalesce(pr.post_title, order_item_name)`, pm_stock, sum( `oim`.`oim->line_item->_line_total` ) AS `net_forever`, round( sum( coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) ), 2 ) AS `qty_forever`, sum( ( case when (`om`.`monthdiff` = 0) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_current_mth`, sum( ( case when (`om`.`monthdiff` = 1) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_m1`, sum( ( case when (`om`.`monthdiff` = 2) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_m2`, sum( ( case when (`om`.`monthdiff` = 3) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_m3`, sum( ( case when (`om`.`monthdiff` = 4) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_m4`, sum( ( case when (`om`.`monthdiff` = 5) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_m5`, sum( ( case when (`om`.`monthdiff` = 6) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_m6`, sum( ( case when (`om`.`monthdiff` = 7) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_m7`, sum( ( case when (`om`.`monthdiff` = 8) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_m8`, sum( ( case when (`om`.`monthdiff` = 9) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_m9`, sum( ( case when (`om`.`monthdiff` = 10) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_m10`, sum( ( case when (`om`.`monthdiff` = 11) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_m11`, sum( ( case when (`om`.`monthdiff` = 12) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_m12`, sum( ( case when ( `om`.`monthdiff` between 1 and 12 ) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_Y1`, sum( ( case when ( `om`.`monthdiff` between 13 and 24 ) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_Y2`, sum( ( case when (`om`.`monthdiff` > 24) then `oim`.`oim->line_item->_line_total` else 0 end ) ) AS `net_prev_Y3+`, round( sum( ( case when (`om`.`monthdiff` = 0) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_mnow`, round( sum( ( case when (`om`.`monthdiff` = 1) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_m1`, round( sum( ( case when (`om`.`monthdiff` = 2) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_m2`, round( sum( ( case when (`om`.`monthdiff` = 3) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_m3`, round( sum( ( case when (`om`.`monthdiff` = 4) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_m4`, round( sum( ( case when (`om`.`monthdiff` = 5) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_m5`, round( sum( ( case when (`om`.`monthdiff` = 6) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_m6`, round( sum( ( case when (`om`.`monthdiff` = 7) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_m7`, round( sum( ( case when (`om`.`monthdiff` = 8) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_m8`, round( sum( ( case when (`om`.`monthdiff` = 9) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_m9`, round( sum( ( case when (`om`.`monthdiff` = 10) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_m10`, round( sum( ( case when (`om`.`monthdiff` = 11) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_m11`, round( sum( ( case when (`om`.`monthdiff` = 12) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_m12`, round( sum( ( case when ( `om`.`monthdiff` between 1 and 12 ) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_Y1`, round( sum( ( case when ( `om`.`monthdiff` between 13 and 24 ) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_Y2`, round( sum( ( case when (`om`.`monthdiff` > 24) then coalesce( ( ( `oim`.`oim->line_item->length` * `oim`.`oim->line_item->_qty` ) / 1000 ), `oim`.`oim->line_item->_qty` ) else 0 end ) ), 2 ) AS `qty_prev_Y3+` from ( ( `vProductMetaData` `pr` left join `vOrderItemMetaDataV3` `oim` on( ( `pr`.`ID` = `oim`.`oim->line_item->_product_id` ) ) ) left join `vOrderMetaData` `om` on( (`om`.`ID` = `oim`.`order_id`) ) ) where ( (1 = 1) and ( `oim`.`order_item_type` = 'line_item' ) and ( `om`.`post_status` like '%complete%' ) ) group by `oim`.`oim->line_item->_product_id`, coalesce( `pr`.`post_title`, `oim`.`order_item_name` );
xx