Basic Analytics
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.
Tables and columns you want to understand
When building a metric, you need to know four pieces of information:
- The table the data lives on,
- The specific action that you want to perform,
- The column you want to perform that action on, and
- The timestamp you want to use for tracking that data.
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.
Customers table
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
- Entity_id: A unique identifier for each customer. This may also be a unique customer number or a customer email address, and it should act as a reference key to your order’s table.
- Created_at: The date the customer’s account was created and added to your database.
- Customer’s lifetime revenue: The total lifetime revenue generated by a customer.
- Customer’s first 30-day revenue: The total amount of revenue generated by a customer in their first 30 days.
- Customer’s lifetime number of orders: The number of orders placed by a customer over their lifetime.
- Customer’s lifetime number of coupons: The total number of coupons used by a customer over their lifetime.
- Customer’s first order date: The date of a customer’s first order. This may be different from the created_at date if a customer did not place an order at the time of their creation.
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!
Orders table
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
- Customer_id: A unique identifier for the customer who placed the order. This is often used to move information between the customer and orders tables. In these examples, you expect the customer_id on the sales_flat_order table to align with the entitiy_id on the customer_entity table.
- Created_at: The date the order was created or placed.
- Customer_email: The email address of the customer who placed the order. This may also be the unique identifier for the customer.
- Customer’s lifetime number of orders: A copy of the column with the same name on your
Customers
table. - Customer’s order number: The customer’s sequential order number associated with the order. For example, if the row you are looking at is a customer’s first order, this column is “1”; but, if this was the customer’s 15th order, this column shows “15” for this order. If this dimension does not exist on your
Customers
table, ask the support team to help you build it. - Customer’s order number (previous-current): A concatenation of two values in the Customer’s order number column. It is used in a sample report below to display the elapsed time between any two orders. For example, the time between a customer’s first order date and their second order date is represented as “1-2” with this calculation.
- Coupon_code: Shows which coupons were used on each order.
- Seconds since previous order: The time (in seconds) between a customer’s orders.
Order Items table
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
- Item_id: The unique identifier for each row in the table.
- Order_id: The reference key to your
Orders
table that tells you which items were purchased in the same order. If an order contains multiple items, this value is repeated. - Product_id: If you want information about the specific product that was purchased (such as color, size, and so on), you would use this column to pull that information from your products table.
- Order’s created_at: The timestamp that the order was placed, typically copied into your
order line items
table from theOrders
table. - Order’s coupon_code: Similar to the
Order's created_at
dimension, this column is copied from your orders table.
Subscriptions 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
- Customer_id: A unique identifier for the customer who placed the order. This is a common way to build a path between the Customers table and the Orders table. In these examples, you expect the customer_id on the sales_flat_order table to align with the
entitiy_id
on thecustomer_entity
table. - Start date: The date a customer’s subscription started.
Marketing Spend 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
- Spend: The total ad spend. In Facebook, this would be the spend column in the
facebook_ads_insights_####
table. For Google AdWords, this would be theadCost
column in thecampaigns####
table. - The
####
which is appended to each of these tables relates to the specific account ID for your Facebook or Google AdWords account. - Clicks: The total number of clicks. In Facebook, this would be the clicks column in the
facebook_ads_insights_####
table. In Google AdWords, this would be the adClicks column in thecampaigns####
table. - Impressions: The total number of impressions. In Facebook, this would be the impressions in the
facebook_ads_insights_####
table. In Google AdWords, this would be the impressions thecampaigns####
table. - Campaign: The total number of clicks. In Facebook, this would be the campaign_name column in the
facebook_ads_insights_####
table. In Google AdWords, this would be the campaign column in thecampaigns####
table. - Date: The time and date that the activity (spend, clicks, or impressions) occurred for a particular campaign. In Facebook, this would be the
date_start
column in thefacebook_ads_insights_####
table. In Google AdWords, this would be the date column in thecampaigns####
table. - Customer’s first order’s source: The order’s source from a customer’s first order. First, check to see if you have a column named
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 medium: The order’s medium from a customer’s first order. First, check to see if you have a column named
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 campaign: The order’s campaign from a customer’s first order. First, check to see if you have a column named
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.
Common reports and metrics
Here are some common examples of reports and metrics that you might find useful:
Customer analytics
New users
-
Description: A count of the total number of newly acquired users over a given period.
New Users
is different fromUnique Customers
, becauseNew Users
has the timestamp that an account was created with your service (this does not mean they necessarily placed an order) whileUnique Customers
have placed at least one order. -
Metric Definition: This metric performs a Count of
entity_id
fromcustomer_entity
table ordered bycreated_at
. -
Report Example: Number of new users created last month
- Metric:
New Users
- Time Range:
Last Month
- Time Interval:
By Day
- Metric:
Unique customers
-
Description: A count of the total number of distinct customers over a given period. This is different from
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 toBy 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 atNumber of Orders
. -
Metric Definition: This metric performs a Count Distinct of
customer_id
fromsales_flat_order
table ordered bycreated_at
. -
Report Example: Distinct customers by week over the last 90 days
- Metric:
Distinct Customers
- Time Range:
Moving range > Last 90 Days
- Time Interval:
By Day
- Metric:
New subscribers
-
Description: A count of the total number of new subscribers acquired over a given period.
-
Metric Definition: This metric performs a Count Distinct of
customer_id
fromsubscriptions
table ordered bystart_date
. -
Report Example: New subscribers this year by month
- Metric:
New Subscribers
- Time Range:
1 Year Ago to 0 Days Ago
- Time Interval:
By Month
- Metric:
Repeat customers
-
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 theCustomer's Order Number
dimension from yourorders
table. -
Metric Used:
Distinct Customers
-
Report example: Number of 2nd and 3rd purchases placed last year
- Metric:
Distinct Customers
- Time Range:
Moving Range > Last Year
- Time Interval:
By Month
- Group By:
Customer's Order Number
, then select2
and3
- Metric:
-
Report example 2: The number of repeat customers last years
- Metric:
Distinct Customers
- Filters:
Customer's Order Number Greater Than 1
- Time Range:
Moving range > Last Year
- Time Interval:
By Month
- Metric:
Top customers by lifetime number of orders
-
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
- Metric:
Orders
- Time Range:
All Time
- Time Interval:
None
- Group By:
customer_email
- Show Top/Bottom: Top 25 sorted by Orders
- Metric:
Top customers by lifetime revenue
-
Description: A list of the top customers based on lifetime revenue.
-
Metric Used:
Average Lifetime Revenue
-
Report example: Top 25 customers by Lifetime Revenue
- Metric:
Average Lifetime Revenue
- Time Range:
All time
- Time Interval:
None
- Group By:
customer_email
- Show Top Bottom: Top 25 sorted by Lifetime Revenue
- Metric:
Average lifetime revenue by cohort
-
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
- Metric:
Revenue
- Cohort Date:
Customer's first order date
- Time Interval:
Month
- Time Period: Moving Set of Cohorts of the most recent eight cohorts with at least four months of data
- Duration:
12 Month(s)
- Table:
Customer_entity
- Perspective: Cumulative Average Value Per Cohort Member
- Metric:
Customers by coupon usage
-
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
- Metric A:
Non coupon customers
- Metric:
New Users
- Filters: Customer’s Lifetime Number of Orders Greater Than 0 and Customer’s Lifetime Number of Coupons Equal to 0
- Metric B:
Coupon customers
- Metric:
New Users
- Filters: Customers Lifetime Number of Orders Greater Than 0 and Customer’s Lifetime Number of Coupons Greater Than 0
- Time range:
All Time
- Time interval:
By Month
- Metric A:
-
Report example 2: Percent of Coupon and Non-coupon customers by month
-
Metric A:
Non coupon customers
(hide metric)- Metric:
New Users
- Filters:
Customer's Lifetime Number of Orders Greater Than 0
andCustomer's Lifetime Number of Coupons Equal to 0
- Metric:
-
Metric B:
Coupon customers
- Metric:
New Users
- Filters:
Customers Lifetime Number of Orders Greater Than 0
andCustomer's Lifetime Number of Coupons Greater Than 0
- Metric:
-
Time Range:
All Time
-
Time Interval:
By Month
-
Formula:
B/(A+B)
-
Average first 30-day revenue
- Description: The average of the amount of revenue generated by customers within their first 30 days as a customer.
- Metric Description: This metric performs an Average of
Customer's First 30 Day Revenue
fromcustomer_entity
table ordered bycreated_at
. - Report Description: All-time average of Customer’s first 30-day revenue
- Metric:
Average First 30 Day Revenue
- Time Range:
All Time
- Time Interval:
None
Average customer lifetime revenue
-
Description: The average amount of revenue generated by your customers over their lifetime.
-
Metric Description: This metric performs an Average of the
Customer's Lifetime Revenue
column on thecustomer_entity
table based on thecreated_at
. -
Report Description: All-time average of Customer’s lifetime revenue
- Metric:
Average Customer Lifetime Revenue
- Time Range:
All Time
- Time Interval:
None
- Metric:
Order analytics
Revenue
-
Description: The revenue metric displays the total revenue earned over a chosen time period.
-
This metric performs a sum of
grand_total
fromsales_flat_order
table ordered bycreated_at
. -
Report Example: Revenue by month, YTD
- Metric:
Revenue
- Time Range:
1 Year Ago to 1 Month Ago
- Time Interval:
By Month
- Metric:
Orders
-
Description: A count of the total number of orders over a given period. An Orders report tracks changes in order volume caused by new product offerings, promotions, or anything else that may increase (or decrease) transaction volume. You may often want to segment this metric by some variables to answer your questions.
-
Metric definition: This metric performs a Count of
entity_id
fromsales_flat_order
table ordered bycreated_at
. -
Report example: Orders by month, YTD
- Metric:
number of orders
- Time Range:
1 Year Ago to 1 Month Ago
- Time Interval:
By Month
- Metric:
Products ordered
-
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
fromsales_flat_order_item
table ordered bycreated_at
. -
Report example: Items sold by month, YTD
- Metric:
Products ordered
- Time Range:
1 Year Ago to 1 Month Ago
- Time Interval:
By Month
- Metric:
-
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
-
Metric A: Products ordered: first order
- Metric:
Products ordered
- Filter:
Customer's order number = 1
- Metric:
-
Metric B: Orders: first order
- Metric:
Orders
- Filter:
Customer's order number = 1
- Metric:
-
Metric C: Products ordered: repeat orders
- Metric:
Products ordered
- Filter:
Customer's order number > 1
- Metric:
-
Metric D: Orders: Repeat orders
- Metric:
Orders
- Filter:
Customer's order number > 1
- Metric:
-
Time Range:
1 Year Ago to 1 Month Ago
-
Time Interval:
By Week
-
Formula 1:
A/B
-
Formula 2:
C/D
-
Multiple Y-Axes box
and Hide
all metrics
Average order value
-
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
fromsales_flat_order
table ordered bycreated_at
. -
Report example: AOV vs previous year, YTD
- Metric:
Average order value
- Time Range:
1 Year Ago to 1 Month Ago
- Time Interval:
By Month
- Perspective:
Amount Change vs Previous Year
- Metric:
Products most purchased with coupons
-
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
- Metric:
Products ordered
- Filter:
Order's coupon_code Is Not \[NULL\]
- Time Range:
All-Time
- Time Interval:
None
- Group By:
name
(orSKU
, or any other product identifier) - Show top/bottom: Top 25 sorted by Products ordered
- Metric:
Time between orders
-
Description: Test your assumptions and expectations about your customers’ purchase cycles with a time between orders analysis that looks at the average (or median!) amount of time between purchases. In the chart below, you can see that your best customers – those who place more than three orders – make their second purchase in less than six months. Customers who have not placed a fourth order wait 14 months before making a second purchase.
-
Metric definition: This metric performs an average of
Time since previous order
fromsales_flat_order
ordered bycreated_at
. -
Report example:
-
Metric 1: ≤ 3 orders
- Metric:
Average time between orders
- Filter:
Customer's lifetime number of orders ≤ 3
- Metric:
-
Metric 2: > 3 orders
- Metric:
Average time between orders
- Filter:
Customer's lifetime number of orders > 3
- Metric:
-
Time Range:
All-Time
-
Time Interval:
None
-
Group By:
Customer's order number (previous-current)
-
Multiple Y-Axes
box.
Marketing spend analytics
Ad spend
-
Description: You can analyze your marketing spend over various time periods and intervals, by campaigns or ad sets, or other segmentations.
-
Metric Definition: This metric performs a Sum on the spend column in the
Marketing Spend
table ordered by thedate
column. -
Report Example: Ad spend by campaign
- Metric:
Ad spend
- Time Range:
All-Time
- Time Interval:
None
- Group By:
campaign
- Metric:
Ad impressions and ad clicks
-
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
- Metric A:
Ad impressions
- Metric B:
Ad clicks
- Time Range:
1 Year Ago to 3 Months Ago
- Time Interval:
By Day
- Metric A:
Click-through-rate (CTR)
-
Description: Using the ad impressions and ad clicks metrics you created above, you can analyze your click-through-rate by different campaigns over time.
-
Report Example: CTR by campaign
- Metric A:
Ad impressions
- Metric B:
Ad clicks
- Time Range:
All-Time
- Time Interval:
None
- Formula:
B/A
- Select the
%
option. - Group By:
campaign
- Metric A:
CTR
, and hide all metrics.
Cost per click (CPC)
-
Description: Using the ad spend and ad clicks metrics that you created above, you can analyze your cost per click by different campaigns over time.
-
Report Example: CPC by campaign
- Metric A:
Ad spend
- Metric B:
Ad clicks
- Time Range:
All-Time
- Time Interval:
None
- Formula:
A/B
- Select the
currency
option - Group By:
campaign
- Metric A:
CPC
, and hide all metrics.
Customers by acquisition source
-
Description: If you track an order’s source, medium, and campaign using Google eCommerce, you can analyze your customers by their acquisition source. This helps you identify which marketing sources are acquiring customers and answer questions such as “are most of your customers making their first orders through Google, Facebook, or some other source?”
-
Report Example: Customers by acquisition source
- Metric Used:
New Customers
- Time Range:
All-Time
- Time Interval:
By Month
- Group By:
Customer's first order's source
- Metric Used:
Customers by acquisition medium and acquisition campaign
-
Description: Similar to analyzing customers by acquisition source, you can also analyze your customers by their first order’s medium and campaign. This can help you answer questions such as “which campaigns are attracting new customers?”
-
Report Example: Customers by acquisition campaign with paid medium
- Metric Used:
New customers
- Filter:
Customer's first order's medium IN ppc
- Time Range:
All-Time
- Time Interval:
None
- Group By:
Customer's first order's campaign
- Metric Used:
New Customers
metric, you can add any other mediums that are considered “paid” mediums for your business such as cpc or paid search.
Customer acquisition cost (CAC) or cost per acquisition (CPA)
-
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
-
Metric A:
New customers
-
Filter:
Customer's first order's medium IN ppc
-
Metric B:
Ad Spend
-
Time Range:
All-Time
-
Time Interval:
None
-
Formula:
B/A
-
Select the
currency
option -
Group By:
- For metric
A
, selectCustomer's first order's campaign
- For metric
B
, selectcampaign
- For metric
-
CTR
, and hide all metrics. Also, check out this article for more information.
Lifetime value by acquisition source, medium, and campaign
-
Description: Alongside analyzing the number of customers acquired by each campaign, you can analyze the average lifetime revenue of these customers. This helps you identify:
- If certain campaigns attract a large volume of customers, but those customers have a low lifetime value.
- If certain campaigns attract a low volume of customers, but those customers have a high lifetime value.
-
Report Example: First add the
New customers
metric. Then, add theAverage lifetime revenue
metric. Select the desired time frame and choose theinterval
asNone
. Finally, select thegroup by
option asCustomer's first order's campaign
.- Metric A:
New Customers
- Filter A:
Customer's first order's source
LIKE ‘%google%’ - Filter B:
Customer's first order's medium IN ppc
- Metric B:
Average lifetime revenue
- Filter A:
Customer's first order's source
LIKE ‘%google%’ - Filter B:
Customer's first order's medium IN ppc
- Time Range:
All-Time
- Time Interval:
None
- Group By:
Customer's first order's campaign
- Metric A:
Return on investment (ROI)
-
Description: One way to calculate ROI by campaign is by analyzing all orders placed through the campaign. However, an alternate method is analyzing the lifetime value of customers acquired through a campaign. To analyze ROI, it is important that the campaign names are consistent across your spend data and transactional data. If you create the following report and there exist no ROI values due to mismatched campaign names, you may need to look into the UTM tagging you have implemented.
-
Report Example: ROI by campaign
-
Metric A:
New Customers
-
Filter A:
Customer's first order's source
LIKE ‘%google%’ -
Filter B:
Customer's first order's medium IN ppc
-
Metric B:
Average lifetime revenue
-
Filter A:
Customer's first order's source
LIKE ‘%google%’ -
Filter B:
Customer's first order's medium IN ppc
-
Metric C:
Ad spend
-
Time Range:
All-Time
-
Time Interval:
None
-
Formula:
(B-(C/A))/(C/A)
-
Select the
%
option -
Group By:
- For metric
A
andB
, selectCustomer's first order's campaign
- For metric
C
, selectcampaign
- For metric
-