Once you are familiar with the Adobe Commerce Intelligence platform and have a basic understanding of the tool, you are going to want to start building reports. One of the most common questions you may have is “What should I be looking at?”
The information below outlines some of the common metrics and reports that you might find valuable. Some of these reports exist within your account, so make sure you review the metrics and reports that exist within your account to avoid creating duplicates.
When building a metric, you need to know four pieces of information:
Most likely, the names of the tables used in these examples are slightly different from the column and table names in your database because each database is unique. Reference the below definitions if you need help with identifying a corresponding table or column in your database.
This table contains the key information about each customer, such as a unique customer ID, email address, and so on. The examples below use customer_entity as the name of a sample customer table.
If some of these calculations do not currently exist in your database, any admin user in your account can build them. Also, you want to make sure that these dimensions are groupable for all applicable metrics.
Dimensions
Do you accept guest orders?
If so, this table may not contain all of your customers. Contact the support team to ensure your customer analyses include all customers.
Not sure if you accept guest orders? Refer to this topic to learn more!
In this table, each row represents one order. The columns in this table contain basic information about each order, such as the order’s ID, creation date, status, the ID of the customer who placed the order, and so on. The examples below use sales_flat_order as the name of a sample orders table.
Dimensions
Customers
table.Customers
table, ask the support team to help you build it.In this table, each row represents one item that was sold. This table contains information about the items sold in each order, such as the order reference number, product number, quantity, and so on. The examples below use sales_flat_order_item
as the name of a sample order items table.
Dimensions
Orders
table that tells you which items were purchased in the same order. If an order contains multiple items, this value is repeated.order line items
table from the Orders
table.Order's created_at
dimension, this column is copied from your orders table.This table is used to manage your subscription information, such as subscription id, email address of the subscriber, subscription start date, and so on.
Dimensions
entitiy_id
on the customer_entity
table.When analyzing your marketing spend, you can include Facebook, Google AdWords, or other sources in your analyses. If you have multiple marketing spend sources, contact the Managed Services Team for help with setting up a consolidated table for your marketing campaigns.
Dimensions
facebook_ads_insights_####
table. For Google AdWords, this would be the adCost
column in the campaigns####
table.####
which is appended to each of these tables relates to the specific account ID for your Facebook or Google AdWords account.facebook_ads_insights_####
table. In Google AdWords, this would be the adClicks column in the campaigns####
table.facebook_ads_insights_####
table. In Google AdWords, this would be the impressions the campaigns####
table.facebook_ads_insights_####
table. In Google AdWords, this would be the campaign column in the campaigns####
table.date_start
column in the facebook_ads_insights_####
table. In Google AdWords, this would be the date column in the campaigns####
table.customer's first order's source
in your account. If you do not see this column, you can create the desired column using these instructions.customer's first order's source
in your account. If you do not see this column, you can create the desired column using these instructions.customer's first order's source
in your account. If you do not see this column, you can create the desired column using these instructions.Here are some common examples of reports and metrics that you might find useful:
New Users
is different from Unique Customers
, because New Users
has the timestamp that an account was created with your service (this does not mean they necessarily placed an order) while Unique Customers
have placed at least one order.entity_id
from customer_entity
table ordered by created_at
.New Users
Last Month
By Day
New Users
, because it only tracks customers who have placed at least one order. A distinct customer’s report only tracks a customer once in a given time interval. If you set the time interval to By Day
and a customer makes more than one purchase on that day, the customer is only counted once. If you want to see a total number of purchases in general, look at Number of Orders
.customer_id
from sales_flat_order
table ordered by created_at
.Distinct Customers
Moving range > Last 90 Days
By Day
customer_id
from subscriptions
table ordered by start_date
.New Subscribers
1 Year Ago to 0 Days Ago
By Month
Description: The total number of customers who placed more than one order over a period. In a repeat customers report, you can use the Distinct Customers
metric and the Customer's Order Number
dimension from your orders
table.
Metric Used: Distinct Customers
Report example: Number of 2nd and 3rd purchases placed last year
Distinct Customers
Moving Range > Last Year
By Month
Customer's Order Number
, then select 2
and 3
Report example 2: The number of repeat customers last years
Distinct Customers
Customer's Order Number Greater Than 1
Moving range > Last Year
By Month
Description: A list of the top customers based on their total number of orders. This provides you a direct list of your most frequent shoppers.
Metric Used: Orders
Report Example: Top 25 customers by lifetime number of orders
Orders
All Time
None
customer_email
Description: A list of the top customers based on lifetime revenue.
Metric Used: Average Lifetime Revenue
Report example: Top 25 customers by Lifetime Revenue
Average Lifetime Revenue
All time
None
customer_email
Description: Track the average lifetime revenue of distinct cohorts of users over time to identify top performing cohorts. Cohorts are grouped by a common date, such as first order date or creation date.
Metric Used: Revenue
Report example: Average Customer Lifetime Revenue by Cohort
Revenue
Customer's first order date
Month
12 Month(s)
Customer_entity
Description: A count of the number of customers acquired who have used a coupon/discount code. This can help you get a clear view of your discount seekers vs. full-price purchasers.
Metric Used: New Users
Report example: Coupon and non-coupon customers by month
Non coupon customers
New Users
Coupon customers
New Users
All Time
By Month
Report example 2: Percent of Coupon and Non-coupon customers by month
Non coupon customers
(hide metric)
New Users
Customer's Lifetime Number of Orders Greater Than 0
and Customer's Lifetime Number of Coupons Equal to 0
Coupon customers
New Users
Customers Lifetime Number of Orders Greater Than 0
and Customer's Lifetime Number of Coupons Greater Than 0
All Time
By Month
B/(A+B)
Hide all metrics
Customer's First 30 Day Revenue
from customer_entity
table ordered by created_at
.Average First 30 Day Revenue
All Time
None
Customer's Lifetime Revenue
column on the customer_entity
table based on the created_at
.Average Customer Lifetime Revenue
All Time
None
grand_total
from sales_flat_order
table ordered by created_at
.Revenue
1 Year Ago to 1 Month Ago
By Month
Make sure your revenue metric’s calculation is consistent with the definition that you discuss internally. For example, you may want to count revenue from orders that have been shipped, to convert currencies from different regions, or to exclude tax. Also, you can use Filter Sets to ensure consistency across all metrics built on the same table.
entity_id
from sales_flat_order
table ordered by created_at
.number of orders
1 Year Ago to 1 Month Ago
By Month
Just like the revenue metric, you should have Filter Sets in place to exclude incomplete, test, or returned orders.
Description: The products ordered metric tells you the quantity of items sold over a specific time period.
Metric definition: This metric performs a sum of qty_ordered
from sales_flat_order_item
table ordered by created_at
.
Report example: Items sold by month, YTD
Products ordered
1 Year Ago to 1 Month Ago
By Month
Combine this metric with your number of orders metric to calculate the number of items per order. Next, add coupon codes to the report to determine how your promotions impact cart size, or segment by new vs repeat orders to better understand your customer behavior.
Report example: Products per order: first order vs repeat orders
Products ordered
Customer's order number = 1
Orders
Customer's order number = 1
Products ordered
Customer's order number > 1
Orders
Customer's order number > 1
1 Year Ago to 1 Month Ago
By Week
A/B
C/D
Uncheck the Multiple Y-Axes box
and Hide
all metrics
Description: Track the average value of the orders placed over a period. Use this metric to quickly determine how your average order value (AOV) has fluctuated as a result of your marketing efforts, product offering, and/or other changes in your business.
Metric definition: This metric performs an average of grand_total
from sales_flat_order
table ordered by created_at
.
Report example: AOV vs previous year, YTD
Average order value
1 Year Ago to 1 Month Ago
By Month
Amount Change vs Previous Year
Description: This report provides insight into which products are being sold when you offer promotions or coupons.
Metric used: Products ordered
Report example: Products most purchased with coupons
Products ordered
Order's coupon_code Is Not \[NULL\]
All-Time
None
name
(or SKU
, or any other product identifier)Time since previous order
from sales_flat_order
ordered by created_at
.Average time between orders
Customer's lifetime number of orders ≤ 3
Average time between orders
Customer's lifetime number of orders > 3
All-Time
None
Customer's order number (previous-current)
Uncheck the Multiple Y-Axes
box.
Marketing Spend
table ordered by the date
column.Ad spend
All-Time
None
campaign
Description: In addition to analyzing ad spend, you can analyze your ad impressions and ad clicks.
Metric Definition: This metric performs a Sum on the impressions (or clicks) column in the Marketing Spend
table ordered by the date column.
Report Example: Add impressions and ad clicks by day
Ad impressions
Ad clicks
1 Year Ago to 3 Months Ago
By Day
Ad impressions
Ad clicks
All-Time
None
B/A
%
option.campaign
You can title the formula as CTR
, and hide all metrics.
Ad spend
Ad clicks
All-Time
None
A/B
currency
optioncampaign
You can title the formula as CPC
, and hide all metrics.
New Customers
All-Time
By Month
Customer's first order's source
Check out this article for more examples of reports using acquisition source.
New customers
Customer's first order's medium IN ppc
All-Time
None
Customer's first order's campaign
For the filter in your New Customers
metric, you can add any other mediums that are considered “paid” mediums for your business such as cpc or paid search.
Description: One way to analyze the cost of a campaign is to attribute all costs to only the customers you acquired through the campaign.
Report Example: CAC by campaign
New customers
Customer's first order's medium IN ppc
Ad Spend
All-Time
None
B/A
currency
optionA
, select Customer's first order's campaign
B
, select campaign
You can title the formula as CTR
, and hide all metrics. Also, check out this article for more information.
New customers
metric. Then, add the Average lifetime revenue
metric. Select the desired time frame and choose the interval
as None
. Finally, select the group by
option asCustomer's first order's campaign
.
New Customers
Customer's first order's source
LIKE ‘%google%’Customer's first order's medium IN ppc
Average lifetime revenue
Customer's first order's source
LIKE ‘%google%’Customer's first order's medium IN ppc
All-Time
None
Customer's first order's campaign
For the two filters, you can add any other mediums that are considered “paid” mediums for your business (such as cpc or paid search). You can also add any other sources you would like to analyze such as Facebook. Check out this article for more details on CAC, LTV, and ROI.
New Customers
Customer's first order's source
LIKE ‘%google%’Customer's first order's medium IN ppc
Average lifetime revenue
Customer's first order's source
LIKE ‘%google%’Customer's first order's medium IN ppc
Ad spend
All-Time
None
(B-(C/A))/(C/A)
%
optionA
and B
, select Customer's first order's campaign
C
, select campaign
You can title the formula as “ROI”, and Hide all metrics. In addition, you can adjust the filters in the metrics to analyze alternative sources and mediums. Also, check out this topic for more details on CAC, LTV, and ROI.