DocumentationCommerceCommerce Intelligence User Guide

customer_entity Table

Last update: Mon Jul 24 2023 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Beginner
  • Intermediate
  • Admin
  • Developer
  • User

The customer_entity table contains records of all registered accounts. An account is considered registered if they sign up for an account, regardless of whether they ever complete a purchase. Each row corresponds to one unique registered account, as identified by that account’s entity_id.

This table does not contain records of customers who place an order via guest checkout. If your store accepts guest checkout, see how to account for guest orders for those orders.

Common Columns

Column Name
Description
created_at
Timestamp corresponding to the account’s registration date, stored locally in UTC. Depending on your configuration in Commerce Intelligence, this timestamp may be converted to a reporting time zone in Commerce Intelligence that differs from your database time zone
email
Email address associated with the account
entity_id (PK)
Unique identifier for the table, and commonly used in joins to the customer_id in other tables within the instance
group_id
Foreign key associated with the customer_group table. Join to customer_group.customer_group_id to determine the customer group associated with the registered account
store_id
Foreign key associated with the store table. Join to store.store_id to determine which Commerce store view is associated with the registered account

Common Calculated Columns

Column Name
Description
Customer's first 30 day revenue
Sum total of revenue for all orders placed by this customer within 30 days of the customer’s first order date. Calculated by joining customer_entity.entity_id to sales_order.customer_id and summing the base_grand_total field for all orders where sales_order.Seconds between customer's first order date and this order ≤ 2592000, which is the number of seconds in 30 days
Customer's first order date
Timestamp of the first order placed by this customer. Calculated by joining customer_entity.entity_id to sales_order.customer_id and returning the minimum sales_order.created_at value
Customer's first order's billing region
Billing region associated with the customer’s first order. Calculated by joining customer_entity.entity_id to sales_order.customer_id and returning the Billing address region where sales_order.Customer's order number = 1
Customer's first order's coupon_code
Coupon code associated with the customer’s first order. Calculated by joining customer_entity.entity_id to sales_order.customer_id and returning the sales_order.coupon_code where sales_order.Customer's order number = 1
Customer's group code
Group name of the registered customer. Calculated by joining customer_entity.group_id to customer_group.customer_group_id and returning the customer_group_code field
Customer's lifetime number of coupons
Total number of coupons applied to all orders placed by this customer. Calculated by joining customer_entity.entity_id to sales_order.customer_id and counting the number of orders where the sales_order.coupon_code is not NULL
Customer's lifetime number of orders
Total count of orders placed by this customer. Calculated by joining customer_entity.entity_id to sales_order.customer_id and counting the number of rows in the sales_order table
Customer's lifetime revenue
Sum total of revenue for all orders placed by this customer. Calculated by joining customer_entity.entity_id to sales_order.customer_id and summing the base_grand_total field for all orders placed by this customer
Seconds since customer's first order date
Elapsed time between the customer’s first order date and now. Calculated by subtracting Customer's first order date from the server timestamp at the time the query is executed, returned as an integer number of seconds
Store name
The name of the Commerce store associated with this registered account. Calculated by joining customer_entity.store_id to store.store_id and returning the name field

Common Metrics

Metric Name
Description
Construction
Avg first 30 day revenue
The average revenue per customer for orders placed within 30 days of the customer’s first order
Operation: Average
Operand: Customer's first 30 day revenue
Timestamp: created_at
Filters:

- [A] Seconds since customer's first order date ≥ 2592000 (excludes customers who have not yet reached 30 days since their first order)
Avg lifetime coupons
The average number of coupons applied to orders per customer over their lifetime
Operation: Average
Operand: Customer's lifetime number of coupons
Timestamp: created_at
Avg lifetime orders
The average number of orders placed per customer over their lifetime
Operation: Average
Operand: Customer's lifetime number of orders
Timestamp: created_at
Avg lifetime revenue
The average total revenue per customer for all orders placed over their lifetime
Operation: Average
Operand: Customer's lifetime revenue
Timestamp: created_at
New customers
The number of customers with at least one order, counted on the date of their first order. Excludes accounts who register but never place an order
Operation: Count
Operand: entity_id
Timestamp: Customer's first order date
Registered accounts
The number of accounts registered. Includes all registered accounts, regardless of whether the account ever placed an order
Operation: Count
Operand: entity_id
Timestamp: created_at

Foreign Key Joining Paths

customer_group

  • Join to customer_group table to create columns that return the customer group name of the registered account.
    • Path: customer_entity.group_id (many) => customer_group.customer_group_id (one)

store

  • Join to store table to create columns that return details related to the store associated with registered account.
    • Path: customer_entity.store_id (many) => store.store_id (one)
recommendation-more-help
e1f8a7e8-8cc7-4c99-9697-b1daa1d66dbc