Customers

1-- core.woocommerce.customers
2-- Recreates WooCommerce Analytics' Customers report: every customer, registered or
3-- guest, with orders, spend, average order value and first and last order.
4--
5-- Grain: store x customer (wc_customer_lookup.customer_id). Customers who have
6-- never placed a counted order are kept, with zero orders, as Analytics lists them.
7--
8-- Built on sales_over_time, so the same status and report date rules apply and a
9-- customer's totals add up to the store's.
10--   total_spend      Analytics' "Total spend": order totals including tax and
11--                    shipping, net of refunds.
12--   net_sales        the same before tax and shipping; usually what finance wants.
13--   avg_order_value  total_spend / orders, as Analytics computes it. A ratio of
14--                    sums, so it stays correct when you re-aggregate.
15--
16-- Location is the customer's latest billing address, not the address of each
17-- order.
18--
19-- Depends on: core.woocommerce.sales_over_time, staging.woocommerce.customer_lookup
20
21WITH by_customer AS (
22    SELECT
23        woocommerce_store,
24        customer_id,
25        ANY_VALUE(currency)                    AS currency,
26        SUM(orders)                            AS orders,
27        SUM(items_sold)                        AS items_sold,
28        SUM(net_sales)                         AS net_sales,
29        SUM(total_sales)                       AS total_spend,
30        SUM(returns)                           AS returns,
31        MIN(IF(row_type = 'SALE', date, NULL)) AS first_order_date,
32        MAX(IF(row_type = 'SALE', date, NULL)) AS last_order_date
33    FROM {{core.woocommerce.sales_over_time}}
34    WHERE customer_id IS NOT NULL
35    GROUP BY woocommerce_store, customer_id
36)
37
38SELECT
39    c.woocommerce_store,
40    c.customer_id,