This topic contains instructions for clients that are using 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 are spending money on online advertising, you want to track your return on this spend and make data-driven decisions on further investments. This topic demonstrates how to set up a dashboard that track your channel analysis – including ROI in aggregate and by campaign.
Before getting started, you want to connect your Facebook Ads, Adwords, and Google Ecommerce accounts and bring in any additional online ad spend data. This analysis contains advanced calculated columns.
Original architecture: To bring together your spend from various sources, like Facebook Ads or Google Adwords, Adobe recommends creating a consolidated table of all of your ad spend. You need an analyst to complete this step for you. If you have not, file a support request with the subject [MARKETING ROI ANALYSIS]
, and an analyst creates this table.
New architecture: You can follow the example in this Analysis Library topic. Consolidated Tables are now known as Data Warehouse Views on the new architecture.
Columns to create
Consolidated Digital Ad Spend
tableCampaign name
is created by an Adobe analyst as part of your [MARKETING ROI ANALYSIS] ticketOriginal and new architectures:
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 a table: ecommerce####
Select a column: campaign
Path: sales_flat_order.increment_id = ecommerce#####.transactionID
Order's GA medium
ecommerce####
medium
Order's GA source
ecommerce####
source
customer_entity
table
Customer's first order GA campaign
Max
sales_flat_order
Order's GA campaign
sales_flat_order.customer_id = customer_entity.entity_id
Orders we count
Customer's order number = 1
Customer's first order GA source
Max
sales_flat_order
Order's GA source
Orders we count
Customer's order number = 1
Customer's first order GA medium
Max
sales_flat_order
Order's GA medium
sales_flat_order.customer_id = customer_entity.entity_id
Orders we count
Customer's order number = 1
sales_flat_order
table
Customer's first order GA campaign
Joined Column
customer_entity
Customer's first order GA campaign
sales_flat_order.customer_id = customer_entity.entity_id
Customer's first order GA source
customer_entity
Customer's first order GA source
sales_flat_order.customer_id = customer_entity.entity_id
Customer's first order GA medium
Joined Column
customer_entity
Customer's first order GA medium
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
Make sure to add all new columns as dimensions to metrics before building new reports.
Ad spend (all time)
Metric A
: Ad Spend
Time period: All time
Interval: None
Chart Type: Scalar
Ad customer acquisitions (all time)
New customers
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
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
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
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
Orders
Metric A
: Orders
Time period: All time
Interval: By Month
Group by: Order's medium
Chart Type: Area
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
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
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
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 the Professional Services team, contact support.