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