While reviewing your orders, if you notice that many customer_id values are null or do not have a value to join back to the customers table, this is usually indicative that your store allows guest orders. This means that your customers table is most likely not inclusive of all of your customers.
Let’s discuss what impact guest orders have on your data and what options you have to properly account for guest orders in your Magento BI data warehouse.
In the typical commerce database, there is an orders table that joins to a customers table. Every row on the orders table has a customer_id column that is unique to one row on the customers table.
The most optimal way to account for guest orders is to base all customer-level metrics on the orders table. This setup will use a unique customer ID that all customers have, including guests (normally customer email is used). This ignores registration data from the customers table. With this option, only customers who have made at least one purchase will be included in customer-level reports. Registered users who have not yet made one purchase will not be included. With this option, your New customer metric will be based on the customer's first order date in the orders table.
You may notice that the Customers we count filter set in this type of setup has a filter for “Customer’s order number = 1”. Let’s think about why this is.
In a situation without guest orders, each customer exists as a unique row in the customer table (see Image 1). A metric such as “New customers” can simply count the id of this table based on created_at date to understand New customers based on registration date.
In a guest orders setup where all customer metrics are based on the orders table to account for guest orders, you need to ensure that you are not counting customers twice. If you count the id of the orders table, you will be counting every order. If instead you count the id on the orders table and use a filter, “Customer’s order number = 1”, then you are going to count each unique customer only one time. This is applicable for all customer level metrics such as “Customer’s lifetime revenue” or “Customer’s lifetime number of orders”.
In Image 2 above, you can see that there are null customer_ids in the orders table. If we use the customer_email to identify unique customers, you can see that "email@example.com" has placed three (3) orders. Therefore, we can build a "New customers" metric on your orders table based on the following conditions:
This solution requires assistance from the Magento BI Analyst Team to properly setup. Reach out to firstname.lastname@example.org if you have questions about implementing or altering the above.