Note: This article contains instructions for clients that are utilizing the original architecture and new architecture. You are on the new architecture if you have the "Data Warehouse Views" section available after selecting "Manage Data" from the main toolbar.
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.
Consolidated Tables
Original architecture: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.
New architecture: You can follow the example set forth in this Analysis Library article. Consolidated Tables are now known as Data Warehouse Views on the new architecture.
Calculated Columns
Columns to create
Consolidated Digital Ad SpendtableCampaign namewill be created by an analyst as part of your [MARKETING ROI ANALYSIS] ticket (note: see above for new architecture differences)
Original and new architectures:
sales_flat_ordertableOrder'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_entitytableCustomer'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_ordertableCustomer'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
Metrics
- Ad spend
- In the
Consolidated Digital Ad Spendtable - This metric performs a Sum
- On the
adCostcolumn - Ordered by the
datetimestamp - Ad impressions
- In the
Consolidated Digital Ad Spendtable - This metric performs a Sum
- On the
Impressionscolumn - Ordered by the
Monthtimestamp - Ad clicks
- In the
Consolidated Digital Ad Spendtable - This metric performs a Sum
- On the
adClickscolumn - Ordered by the
Monthtimestamp
Note: Make sure to add all new columns as dimensions to metrics before building new reports.
Reports
- Ad spend (all time)
- Metric: Ad Spend
- Metric A: Ad Spend
- Time period: All time
- Interval: None
- Chart Type: Scalar
- Ad customer acquisitions (all time)
- 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 A: Ad customer acquisitions
- Time period: All time
- Interval: None
- Chart Type: Scalar
- Ad ROI
- Metric: Ad Spend
- 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: 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: ((C - (A / B)) / (A / B))
- Format: Percentage
- Metric A: Ad Spend (hide)
- Metric B: Ad customer acquisitions (hide)
- Metric C: Average LTV (hide)
- Formula: Ads ROI
- Time period: All time
- Interval: None
- Chart Type: Scalar
- Orders by ga medium
- Metric: Orders
- Metric A: Orders
- Time period: All time
- Interval: By Month
- Group by:
Order's medium - Chart Type: Area chart
- Ad ROI by campaign
- Metric: Ad Spend
- 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: 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: 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: (A / B)
- Format: Currency
- Formula: (C - (A / B))
- Format: Currency
- Formula: ((C - (A / B)) / (A / B))
- Format: Percentage
- Metric: Ad Clicks
- Metric: Ad Impressions
- Formula: (H / I)
- Format: Percentage
- Formula: (A / H)
- Format: Currency
- Metric A: Ad Spend (hide)
- Metric B: Ad customer acquisitions
- Metric C: Average LTV
- Metric D: Average lifetime # of orders
- Formula: CAC
- Formula: Avg return
- Formula: Ads ROI
- Metric H: adClicks
- Metric I: Impressions
- Formula: CTR
- Formula: CPC
- 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
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.