营销ROI
如果您正在在线广告上花钱,则希望跟踪您的投资回报,并在进一步投资方面做出以数据为导向的决策。 本主题将演示如何设置一个功能板来跟踪您的渠道分析,包括汇总的ROI和按促销活动列出的分析。
在开始之前,您需要连接您的[Facebook Ads]、[Adwords]和[Google Ecommerce]帐户,并引入任何其他在线广告支出数据。 此分析包含高级计算列。
统一表
原始架构: 若要汇总来自各种来源(如Facebook Ads或Google Adwords)的支出,Adobe建议创建所有广告支出的 整合表。 您需要一名分析师为您完成此步骤。 如果您尚未这样做,请提交主题为[MARKETING ROI ANALYSIS]
的支持请求,分析人员将创建此表。
新架构: 您可以按照此分析库主题中的示例进行操作。 在新体系结构中,统一表现在称为Data Warehouse视图。
计算列
要创建的列
Consolidated Digital Ad Spend
表Campaign name
由Adobe分析师创建,作为 [MARKETING ROI ANALYSIS] 票证的一部分
原始架构和新架构:
-
sales_flat_order
表-
Order's GA campaign
-
选择定义:
Joined Column
-
Create Path:
-
Many:
sales_flat_order.increment_id
-
One:
ecommerce####.transaction_id
-
选择table:
ecommerce####
-
选择column:
campaign
-
Path:
sales_flat_order.increment_id = ecommerce#####.transactionID
-
-
Order's GA medium
- 选择定义:联接列
- 选择table:
ecommerce####
- 选择column:
medium
- Path: sales_flat_order.increment_id = e-commerce#####.transactionId
-
Order's GA source
- 选择定义:联接列
- 选择table:
ecommerce####
- 选择column:
source
- Path: sales_flat_order.increment_id = e-commerce#####.transactionId
^
-
-
customer_entity
表 -
Customer's first order GA campaign
-
选择定义:
Max
-
选择table:
sales_flat_order
-
选择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
-
选择定义:
Max
-
选择table:
sales_flat_order
-
选择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
-
选择定义:
Max
-
选择table:
sales_flat_order
-
选择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
表 -
Customer's first order GA campaign
- 选择定义:
Joined Column
- 选择table:
customer_entity
- 选择column:
Customer's first order GA campaign
- Path:
sales_flat_order.customer_id = customer_entity.entity_id
- 选择定义:
-
Customer's first order GA source
- 选择定义:联接列
- 选择table:
customer_entity
- 选择column:
Customer's first order GA source
- Path:
sales_flat_order.customer_id = customer_entity.entity_id
-
Customer's first order GA medium
- 选择定义:
Joined Column
- 选择table:
customer_entity
- 选择column:
Customer's first order GA medium
- Path:
sales_flat_order.customer_id = customer_entity.entity_id
- 选择定义:
量度
-
广告支出
-
在
Consolidated Digital Ad Spend
表中 -
此量度执行 总和
-
在
adCost
列上 -
按
date
时间戳排序 -
广告展示次数
-
在
Consolidated Digital Ad Spend
表中 -
此量度执行 总和
-
在
Impressions
列上 -
按
Month
时间戳排序 -
广告点击次数
-
在
Consolidated Digital Ad Spend
表中 -
此量度执行 总和
-
在
adClicks
列上 -
按
Month
时间戳排序
报告
-
广告花费(所有时间)
- Metric:广告支出
-
量度
A
:广告支出 -
Time period:
All time
-
间隔:None
-
Chart Type:Scalar
-
广告客户获取(所有时间)
-
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
]或[B
]或[C
])和[D
]
-
-
量度
A
:Ad customer acquisitions
-
Time period:
All time
-
间隔:None
-
Chart Type:Scalar
-
广告ROI
-
Metric:广告支出
-
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
]或[B
]或[C
])和[D
]
-
Metric:平均生命周期收入
-
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
]或[B
]或[C
])和[D
]
-
Formula:
((C - (A / B)) / (A / B))
-
Format:
Percentage
-
-
量度
A
:Ad Spend (hide)
-
量度
B
:Ad customer acquisitions (hide)
-
量度
C
:Average LTV (hide)
-
Formula:
Ads ROI
-
Time period:
All time
-
间隔:None
-
Chart Type:Scalar
-
按Ga排列的订单数
-
量度:
Orders
-
-
量度
A
:Orders
-
Time period:
All time
-
Interval:
By Month
-
Group by:
Order's medium
-
Chart Type:Area
-
按营销活动划分的广告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
]或[B
]或[C
])和[D
]
-
Metric:平均生命周期收入
-
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
]或[B
]或[C
])和[D
]
-
Metric:平均生命周期订单数
-
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
]或[B
]或[C
])和[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
-
-
量度
A
:Ad Spend
(隐藏) -
量度
B
:Ad customer acquisitions
-
量度
C
:Average LTV
-
量度
D
:Average lifetime # of orders
-
公式:CAC
-
Formula:
Avg return
-
Formula:
Ads ROI
-
量度
H
:adClicks
-
量度
I
:Impressions
-
公式:CTR
-
公式:CPC
-
Time period:
All time
-
间隔:None
-
分组依据:campaign
(将“客户的第一个订单”促销活动用于非广告支出表量度) -
Chart Type:Table
如果您在构建此分析时遇到任何问题,或只是想与专业服务团队接洽,请联系支持人员。