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