Magento Business Intelligence Help Center

Understanding and building basic analytics

Once you're familiar with the Magento BI platform and have a basic understanding of the tool, you're going to want to start building reports. One of the most common questions we get from clients is "What should I be looking at?" We've built this guide to help answer that question.

Below, we've outlined some of the common metrics and reports that our clients find extremely valuable. You'll likely find that a number of these reports already exist within your account, so make sure you review the metrics and reports that exist within your account to avoid creating duplicates!

Tables & Columns you'll want to understand

When building a metric, you'll need to know four pieces of information:

  1. The table the data lives on,
  2. The specific action you want to perform,
  3. The column you want to perform that action on,
  4. And the timestamp you want to use for tracking that data.

Most likely, the names of the tables we use in these examples will be slightly different from the column and table names in your database because each database is unique. Reference the below definitions if you need help 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, account creation date, etc.  In the examples below, we'll 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. Additionally, you will want to make sure that these dimensions are groupable for all applicable metrics.

Do you accept guest orders?

If so, this table may not contain all of your customers! Contact our support team to make sure your customer analyses include all customers.

Not sure if you accept guest orders? Check out this article to learn more!

  • 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.

Each row represents one order in your orders table. 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, etc. In the examples below, we'll 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 will often be used to move information between the customer and orders tables. In our examples, we would 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: This is 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 would be "1"; but, if this was the customer's 15th order, this column would show "15" for this order. If this dimension does not exist on your customers table, ask our support team to help you build it!
    • Customer's order number (previous-current): This column is simply 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 would be represented as “1-2” with this calculation.
    • Coupon_code: This column tells you which coupon(s) were used on each order.
    • Seconds since previous order: This column calculates the seconds between a customer’s orders.

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, etc. We'll use sales_flat_order_item as the name of a sample order items table in the examples below.

  • Dimensions:
    • Item_id: This is the unique identifier for each row in the table.
    • Order_id: This is the reference key to your orders table, and it tells you which items were purchased in the same order. If an order contains multiple items, this value would be repeated.
    • Product_id: If you want information about the specific product that was purchased (such as color, size, etc), you would use this column to pull that information from your products table.
    • Order's created_at: This is simply the timestamp that the order was placed, and it is typically copied into your order line items table from the orders table.
    • Order's coupon_code: Similar to the 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, etc.

  • Dimensions:
    • Customer_id: A unique identifier for the customer who placed the order. This will often be your way to build a path between the customers table and the orders table. In our examples, we would expect the customer_id on the sales_flat_order table to align with the entitiy_id on the customer_entity table.
    • Start date: The date a customer's subscription started.

When analyzing your marketing spend, you can include Facebook, Google AdWords, or other sources in your analyses. If you have multiple marketing spend sources, reach out to support@rjmetrics.com so we can help you set up a consolidated table for your marketing campaigns.

    • Dimensions:
      • Spend: This is the total ad spend. In Facebook, this would be the spend column in the facebook_ads_insights_#### table. For Google AdWords, this would be the adCost column in the campaigns#### table.
        • The "####" which is appended to each of these tables relates to the specific account ID for your Facebook or Google AdWords account.
      • Clicks: This calculates 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 the campaigns#### table.
      • Impressions: This calculates 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 the campaigns#### table.
      • Campaign: This calculates 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 the campaigns#### table.
      • Date: This is simply the timestamp that the spend, clicks, or impressions occurred for a particular campaign. In Facebook, this would be the date_start column in the facebook_ads_insights_#### table. In Google AdWords, this would be the date column in the campaigns#### table.
      The following columns can be created in your orders or customers table if you track an order's source, medium, and campaign using Google eCommerce.
      • Customer's first order's source: This column analyzes 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 don't see this column, you can create the desired column using these instructions.
      • Customer's first order's medium: This column analyzes 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 don't see this column, you can create the desired column using these instructions.
      • Customer's first order's campaign: This column analyzes 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 don't 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 users often find useful:

 

Customer Analytics

  • Description: A count of the total number of newly acquired users over a given period of time. 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.
  • Metric Definition: This metric performs a Count of entity_id from customer_entity table ordered by created_at.
  • Report Example: Number of new users created last month
    • Metric: New Users
    • Time Range: Last Month
    • Time Interval: By Day

New Users.Click for a closer look!

 

  • Description: A count of the total number of distinct customers over a given period of time. This is different from New Users because it only tracks customers who have placed at least one order. A distinct customers report will only track 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, they will only be counted once. If you want to see a total number of purchases in general, look at Number of Orders.
  • Metric Definition: This metric performs a Count Distinct of customer_id from sales_flat_order table ordered by created_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

Unique Customers.Click for a closer look!

  • Description: A count of the total number of new subscribers acquired over a given period of time.  
  • Metric Definition: This metric performs a Count Distinct of customer_id from subscriptions table ordered by start_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

SubscribersClick for a closer look!

  • Description: The total number of customers who placed their 2nd, 3rd, 4th, etc. order over a period of time. In a repeat customers report, you will 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 
    • Metric: Distinct Customers 
    • Time Range: Moving Range > Last Year 
    • Time Interval: By Month 
    • Group By: Customer's Order Number, then select 2 and 3 

Repeat Purchases 2Click for a closer look!

  • Report example 2:  Number of repeat customers last year 
    • Metric: Distinct Customers 
    • Filters: Customer's Order Number Greater Than 1 
    • Time Range: Moving range > Last Year 
    • Time Interval: By Month

Repeat Customers Last YearClick for a closer look!

  • 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 

Top 25 Customers by OrdersClick for a closer look!

  • 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 

Top 25 Customers by RevenueClick for a closer look!

  • Description: Track the average lifetime revenue of distinct cohorts of users over time to identify top performing cohorts. Cohorts are grouped together 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 8 cohorts with at least 4 months of data 
    • Duration: 12 Month(s) 
    • Table: Customer_entity 
    • Perspective: Cumulative Average Value Per Cohort Member

Customer Lifetime Revenue by CohortClick for a closer look!

  • 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

Customers by Coupon UsageClick for a closer look!

  • 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 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 
    • Formula: B/(A+B) 
    • Note: Hide all metrics

Coupon UsageClick for a closer look!

  • 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 from customer_entity table ordered by created_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 First 30 Day RevenueClick for a closer look!

  • Description: The average amount of revenue generated by your customers over their lifetime. 
  • Metric Description: This metric performs a Average of the Customer's Lifetime Revenue column on the customer_entity table based on the created_at 
  • Report Description: All time average of customer's lifetime revenue  
    • Metric: Average Customer Lifetime Revenue 
    • Time Range: All Time 
    • Time Interval: None

Customer Lifetime RevenueClick for a closer look!

 

Order Analytics

  • Description:The revenue metric displays the total revenue earned over a chosen time period.
  • This metrics performs a sum of grand_total from sales_flat_order table ordered by created_at.
  • Report Example: Revenue by month, YTD
    • Metric: Revenue
    • Time Range:1 Years Ago to 1 Month Ago
    • Time Interval: By Month
  • TipMake sure your revenue metric’s calculation is consistent with the definition that you discuss internally. For example, you may only want to count revenue from orders that have been shipped, you might need to convert currencies from different regions, and you may want to exclude tax. Further, you can use Filter Sets to ensure consistency across all metrics built on the same table.

RevenueClick for a closer look!

  • Description: A count of the total number of orders over a given period of time. An Orders report will track changes in order volume caused by new product offerings, promotions, or anything else that may increase (or decrease) transaction volume. More often than not, you’ll want to segment this metric by a number of variables to answer your questions.
  • Metric definition: This metric performs a Count of entity_id from sales_flat_order table ordered by created_at.
  • Report example: Orders by month, YTD
    • Metric: number of orders
    • Time Range: 1 Year Ago to 1 Month Ago
    • Time Interval: By Month
  • Tip: Just like the revenue metric, you should have Filter Sets in place to exclude incomplete, test, or returned orders.

OrdersClick for a closer look!

  • Description: The products ordered metric tells you the quantity of items sold over a specific time period.
  • Metric definition: This metrics performs a sum of qty_ordered from sales_flat_order_item table ordered by created_at.
  • Report example: Items sold by month, YTD
    • Metric: Products ordered
    • Time Range: 1 Year Ago to 1 Month Ago
    • Time Interval: By Month

Products OrderedClick for a closer look!

  • 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: 1st order vs repeat orders
    • Metric A: Products ordered: 1st order
      • Metric: Products ordered
      • Filter: Customer’s order number = 1
    • Metric B: Orders: 1st order
      • Metric: Orders
      • Filter: Customer’s order number = 1
    • Metric C: Products ordered: repeat orders
      • Metric: Products ordered
      • Filter: Customer’s order number > 1
    • Metric D: Orders: Repeat orders
      • Metric: Orders
      • Filter: Customer’s order number > 1
    • Time Range: 1 Year Ago to 1 Month Ago
    • Time Interval: By Week
    • Formula 1: A/B
    • Formula 2: C/D
    • Notes: Uncheck the Multiple Y-Axes box and Hide all metrics

Products Ordered 2Click for a closer look!

  • Description: Track the average value of the orders placed over a period of time. 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
    • Metric: Average order value
    • Time Range: 1 Year Ago to 1 Month Ago
    • Time Interval: By Month
    • Perspective: Amount Change vs Previous Year

AOVClick for a closer look!

  • 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 (or SKU, or any other product identifier)
    • Show top/bottom: Top 25 sorted by Products ordered

Products with CouponsClick for a closer look!

  • 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 haven’t placed a fourth order wait 14 months before making a second purchase.
  • Metric definition: This metric performs an average of Time since previous order from sales_flat_order ordered by created_at.
  • Report example:
    • Metric 1: ≤ 3 orders
      • Metric: Average time between orders
      • Filter: Customer’s lifetime number of orders ≤ 3
    • Metric 2: > 3 orders
      • Metric: Average time between orders
      • Filter: Customer’s lifetime number of orders > 3
    • Time Range: All-Time
    • Time Interval: None
    • Group By: Customer’s order number (previous-current)
    • Notes: Uncheck the Multiple Y-Axes box

Time Between OrdersClick for a closer look!

 

Marketing Spend Analytics

  • 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 the date column.
  • Report Example: Ad spend by campaign
    • Metric: Ad spend
    • Time Range: All-Time
    • Time Interval: None
    • Group By: campaign

Ad SpendClick for a closer look!

    • 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

Ad ImpressionsClick for a closer look!

  • 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
  • Notes: You can title the formula as "CTR", and hide all metrics.

CTRClick for a closer look!

  • Description: Using the ad spend and ad clicks metrics 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
  • Notes: You can title the formula as "CPC", and hide all metrics.

CPCClick for a closer look!

  • Description: If you track an order's source, medium, and campaign using Google eCommerce, you can analyze your customers by their acquisition source. This will help 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 
  • Notes: Check out this article for more examples of reports using acquisition source.

Acquisition SourceClick for a closer look!

  • 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
  • Note: 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.

Acquisition MediumClick for a closer look!

    • 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, select "Customer's first order's campaign"
        • For metric B, select "campaign"

New Users.Click for a closer look!

  • Notes: You can title the formula as "CTR", and hide all metrics. Also, check out this article for more details on CAC, LTV, and ROI.

CAC 1Click for a closer look!

CAC 2Click for a closer look!

  • Description: Alongside analyzing the number of customers acquired by each campaign, you can analyze the average lifetime revenue of these customers. This will help 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: To create this report, first add the 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 as Customer'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
  • Notes: For the two filters, you can add any other mediums that are considered "paid" mediums for your business such as cpc or paid search, and you can add any other sources you'd like to analyze such a Facebook. Also, check out this article for more details on CAC, LTV, and ROI.

Lifetime value by acquisition source, medium, and campaignClick for a closer look!

  • 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's 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've 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 and B, select "Customer's first order's campaign"
      • For metric C, select "campaign"
  • Notes: 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 article for more details on CAC, LTV, and ROI.

ROI 1Click for a closer look!

ROI 2Click for a closer look!
Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk