Woocommerce -> Sales by Product analysis options

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

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
    • 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
    • Click on category:
      • Takes us to Sales by product filtered to target category

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