Woocommerce – Sales snapshot for analysis

20191218

As our hosting plan is somewhat restrictive, I don’t wanna fire off complex queries unnecessarily so maybe creation of like a mini DW snapshots

Sample questions:

  • Who bought product xyz
  • Click on an order and see everything the customer has ever bought in a single view
    • – different aggregations in case not registered:
      • name
      • phone
      • address
  • Sufficient sales data so we can do moving average over days/weeks/months in javascript

Mechanism:

  • Maintenance allows running for selected month:
    • Delete for that month then insert for that month
    • So only need to run for historical months if an edit made in historical months

Result:

  • Just 1 big flat very wide file, 1 record per invoice/credit line item
  • Transaction:
    • Date
    • Order ID
    • Payment method
    • Status (may as well dump all records)
    • Customer Note
  • Customer attributes:
    • Shipping & Billing Names / Addresses
    • Email
    • Phone
  • Product:
    • ID
    • SKU at the time
    • Title at the time
    • Quantity / length
    • What about Product categories? Still issues when > 1 category..
    • Wjhat about Product addons, can’t remember if separate records or just meta data
  • Cost information? or can we calculate this later