Magento Business Intelligence Help Center

Marketing ROI

If you're spending money on online advertising, you will inevitably want to track your return on this spend and make data-driven decisions on further investments. In this article, we demonstrate how to set up a dashboard that will track your channel analysis – including ROI in aggregate and by campaign.

Before getting started, you'll want to connect your Facebook Ads, Adwords, and Google Ecommerce accounts as well as bring in any additional online ad spend data. This analysis contains advanced calculated columns that can only be created by a Magento BI analyst, so file a support request with the subject [MARKETING ROI ANALYSIS], before getting started with this analysis.

In order to bring together your spend from various sources (like Facebook Ads or Google Adwords), we recommend creating a consolidated table of all of your ad spend. You will need an analyst to complete this step for you. If you haven't already, file a support request with the subject [MARKETING ROI ANALYSIS], and an analyst will create this table.

Columns to create

  • Consolidated Digital Ad Spend table
    • Campaign name will be created by an analyst as part of your [MARKETING ROI ANALYSIS] ticket
  • sales_flat_order table
    • Order's GA campaign
      • Select a definition: Joined Column
      • Create Path:
        • Many: sales_flat_order.increment_id
        • One: ecommerce####.transaction_id
      • Select table: ecommerce####
      • Select column: campaign
      • Path: sales_flat_order.increment_id = ecommerce#####.transactionId
    • Order's GA medium
      • Select a definition: Joined Column
      • Select table: ecommerce####
      • Select column: medium
      • Path: sales_flat_order.increment_id = ecommerce#####.transactionId
    • Order's GA source
      • Select a definition: Joined Column
      • Select table: ecommerce####
      • Select column: source
      • Path: sales_flat_order.increment_id = ecommerce#####.transactionId
  • customer_entity table
    • Customer's first order GA campaign
      • Select a definition: Max
      • Select table: sales_flat_order
      • Select column: Order's GA campaign
      • Path: sales_flat_order.customer_id = customer_entity.entity_id
      • Filter:
        • Orders we count
        • Customer's order number = 1
    • Customer's first order GA source
      • Select a definition: Max
      • Select table: sales_flat_order
      • Select column: Order's GA source
      • Path: sales_flat_order.customer_id = customer_entity.entity_id
      • Filter:
        • Orders we count
        • Customer's order number = 1
    • Customer's first order GA medium
      • Select a definition: Max
      • Select table: sales_flat_order
      • Select column: Order's GA medium
      • Path: sales_flat_order.customer_id = customer_entity.entity_id
      • Filter:
        • Orders we count
        • Customer's order number = 1
  • sales_flat_order table
    • Customer's first order GA campaign
      • Select a definition: Joined Column
      • Select table: customer_entity
      • Select column: Customer's first order GA campaign
      • Path: sales_flat_order.customer_id = customer_entity.entity_id
    • Customer's first order GA source
      • Select a definition: Joined Column
      • Select table: customer_entity
      • Select column: Customer's first order GA source
      • Path: sales_flat_order.customer_id = customer_entity.entity_id
    • Customer's first order GA medium
      • Select a definition: Joined Column
      • Select table: customer_entity
      • Select column: Customer's first order GA medium
      • Path: sales_flat_order.customer_id = customer_entity.entity_id
  • Ad spend
    • In the Consolidated Digital Ad Spend table
    • This metric performs a Sum
    • On the adCost column
    • Ordered by the date timestamp
  • Ad impressions
    • In the Consolidated Digital Ad Spend table
    • This metric performs a Sum
    • On the Impressions column
    • Ordered by the Month timestamp
  • Ad clicks
    • In the Consolidated Digital Ad Spend table
    • This metric performs a Sum
    • On the adClicks column
    • Ordered by the Month timestamp

Note: Make sure to add all new columns as dimensions to metrics before building new reports.

  • Ad spend (all time)
    • Metric A: Ad Spend
      • Metric: Ad Spend
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
  • Ad customer acquisitions (all time)
    • Metric A: Ad customer acquisitions
      • Metric: New customers
      • Filters:
        • User's first order's source LIKE %google%
        • User's first order's source LIKE %facebook%
        • User's first order's source LIKE %fb%
        • User's first order's medium IN cpc, ppc
        • Filter logic: ([A] OR [B] OR [C]) AND [D]
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
  • Ad ROI
    • Metric A: Ad Spend (hide)
      • Metric: Ad Spend
    • Metric B: Ad customer acquisitions (hide)
      • Metric: New customers
      • Filters:
        • User's first order's source LIKE %google%
        • User's first order's source LIKE %facebook%
        • User's first order's source LIKE %fb%
        • User's first order's medium IN cpc, ppc
        • Filter logic: ([A] OR [B] OR [C]) AND [D]
    • Metric C: Average LTV (hide)
      • Metric: Average lifetime revenue
      • Filters:
        • User's first order's source LIKE %google%
        • User's first order's source LIKE %facebook%
        • User's first order's source LIKE %fb%
        • User's first order's medium IN cpc, ppc
        • Filter logic: ([A] OR [B] OR [C]) AND [D]
    • Formula: Ads ROI
      • Formula: ((C - (A / B)) / (A / B))
      • Format: Percentage
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
  • Orders by ga medium
    • Metric A: Orders
      • Metric: Orders
    • Time period: All time
    • Interval: By Month
    • Group by: Order's medium
    • Chart Type: Area chart
  • Ad ROI by campaign
    • Metric A: Ad Spend (hide)
      • Metric: Ad Spend
    • Metric B: Ad customer acquisitions
      • Metric: New customers
      • Filters:
        • User's first order's source LIKE %google%
        • User's first order's source LIKE %facebook%
        • User's first order's source LIKE %fb%
        • User's first order's medium IN cpc, ppc
        • Filter logic: ([A] OR [B] OR [C]) AND [D]
    • Metric C: Average LTV
      • Metric: Average lifetime revenue
      • Filters:
        • User's first order's source LIKE %google%
        • User's first order's source LIKE %facebook%
        • User's first order's source LIKE %fb%
        • User's first order's medium IN cpc, ppc
        • Filter logic: ([A] OR [B] OR [C]) AND [D]
    • Metric D: Average lifetime # of orders
      • Metric: Average lifetime number of orders
      • Filters:
        • User's first order's source LIKE %google%
        • User's first order's source LIKE %facebook%
        • User's first order's source LIKE %fb%
        • User's first order's medium IN cpc, ppc
        • Filter logic: ([A] OR [B] OR [C]) AND [D]
    • Formula: CAC
      • Formula: (A / B)
      • Format: Currency
    • Formula: Avg return
      • Formula: (C - (A / B))
      • Format: Currency
    • Formula: Ads ROI
      • Formula: ((C - (A / B)) / (A / B))
      • Format: Percentage
    • Metric H: adClicks
      • Metric: Ad Clicks
    • Metric I: Impressions
      • Metric: Ad Impressions
    • Formula: CTR
      • Formula: (H / I)
      • Format: Percentage
    • Formula: CPC
      • Formula: (A / H)
      • Format: Currency
    • Time period: All time
    • Interval: None
    • Group by: campaign (Use Customer's first order's campaign for non-ad spend table metrics)
    • Chart Type: Table

After compiling all the reports, you can organize them on the dashboard as you desire. Questions about matching your google ecommerce campaigns to Adwords and Facebook? Check out this help center directory

If you run into any questions while building this analysis, or simply want to engage our professional services team to build the analysis for you, simply open a support ticket here.

Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk