Adobe Commerce Intelligence allows you to easily marry advertising cost data and revenue data from your database. This helps you identify which campaigns have the highest return on investment (ROI). This topic explores a few different methods of evaluating your campaign performance.
Campaigns targeted at user acquisition can be measured from many perspectives, including:
Analyses (1) and (2) above are explored in a separate tutorial on identifying your top marketing channels. Here, you explore analysis (3) to measure campaign ROI over time. This answers whether users acquired from a particular campaign generated enough lifetime revenue to cover the cost of acquisition.
This example assumes that all campaign costs were exclusively used to acquire new users. In reality, your campaign cost is also shared with acquiring unconverted visits, repeat purchasers and such. By assuming that all cost is used to acquire new registered users, the resulting ROI accounts for the worst case scenario (highest cost per acquisition). You can be sure that your actual ROI is higher than your calculation.
Example: Assuming that you spent $20 on a campaign that generated 10 new users and 10 repeat buyers, your actual cost per new user is $1. But, with the assumption that all cost went to acquire new users, the cost per acquisition is $2.
1. Start by creating a chart that segments your Ad Cost by Campaigns:
Create a Metric that sums your spend over time
Go to Data > Metrics
Select Add New Metric
and select the Adwords...
table that is recording your AdWords cost data.
In the metric editor, give your metric a name (for example, AdWord Cost)
Using the dropdowns, perform a Sum on the adCost
column in the Adwords… table (Change) ordered by the date
column.
Click Back to Metric List
at the top and go to any dashboard.
Create a report that segments spend by campaigns
In any dashboard, click Add Report > Create report
Select the Adword Cost metric that you just created
Set the Time period to All-time
, and Interval to None
Under the Group by
tab, add campaign
as grouping field, and click Add All
in the box.
This report shows your all-time AdWords cost by campaigns
2. Create a report that counts new users by campaigns:
New users
metric that counts the number of new registered users over timeAll-time
, and Interval to None
Group by
tab, add campaign
as grouping field
, and click Add All
in the box3. Create a report that segments average user LTV by campaigns:
Average lifetime revenue
metric that calculates an average user’s lifetime revenueAll-time
, and Interval to None
Group by
tab, add campaign
or utm\_campaign
as grouping field, and click Add All
in the boxFinally, calculate campaign ROI by bringing together these three analyses in one report:
In any dashboard, click Add Report > Create new report
Add as input, use the three metrics used above. Each is assigned a letter (for example,[A
], [B
], and [C
])
Cost: Add the metric AdWords cost - this is variable [A]. This returns cost by campaigns.
Users: Add the metric New Users - this is variable [B]. This returns the number of users by campaigns.
LTV: Add the metric Average Lifetime Revenue - this is variable [C
]. This returns LTV by campaigns.
Click the hide icon beside the word Chart so you can focus on the table
Now use Add Formula
to combine these metrics, as follows:
ROI: Enter the formula (\[C\]-\[A\]/\[B\])/(\[A\]/\[B\])
, if [A
] represents Ad Cost by Campaigns
, [B
] represents New users by campaigns
, and [C
] LTV by campaigns
. This returns the ratio of (average user LTV - average cost per acquisition) / (average cost per acquisition)
Avg Return per User: Enter the formula [C
]-([A
]/[B
]). This return the average margin made on a user by calculating (average user LTV) - (average cost per acquisition).
CPA: Enter the formula \[A\]/\[B\]
. This returns the actual campaign’s cost per acquisition.
Other potential metrics to include from AdWords data include sums of Impressions
and adClicks
(from AdWords data), along with the total number of orders
made via a particular campaign.
It may also be interesting to calculate the ROI based on LTV 30 days and 90 days after a user registers or makes a first purchase.
Feel free to click and drag your metrics and formulas to reorder the columns of your report
Name your report and be sure to save as a table.
Are you running product-specific advertisements? If so, you can measure ROI on those campaigns by calculating revenue / cost for specific product(s).
This example assumes that all campaign costs were exclusively used to generate purchases of specific product(s). By assuming that all cost was spent on generating purchases, the resulting ROI accounts for the worst case scenario (highest cost per purchase). You can be sure that your actual ROI is higher than this calculation. Example: Assuming that you spent $20 on a campaign that generated 10 new users and 10 purchases, your actual cost per purchase is $1. Under the assumption that all cost went to acquire new users, the cost per purchase is $2.
Before you start, submit a support ticket to join the following dimensions to your line items table (sales\_flat\_order\_item, order\_item
):
1. Now start by creating a chart that returns revenue per campaign for specific product(s):
Revenue by items
metric that calculates revenue at the line items levelAll-time
, and Interval to None
Filter by
tab, add product name 'IN'
Product A
, Product B
, Product C
, …" and include all product names targeted by your campaign separated by a comma (for example, product name 'IN' yellow t-shirt
, red t-shirt, blue t-shirt
)Group by
tab, add order's campaign
or order's utm\_campaign
as grouping
field, and click Add All in the box2. To calculate ROI, you again combine metrics in one report:
In any dashboard, click Add Report > Create new report
Add the Revenue by items
metric, following the filter and group by directions from the campaign for specific product(s) report above and click Hide beneath the metric’s scalar value
Now add the AdWords Cost metric, following the filter and group by directions from the Ad cost by campaigns
report you explored in the User acquisition campaigns
section above; then click Hide beneath the metric’s scalar value
With these metrics in place, add formulas:
ROI: Enter the formula \[A\]/\[B\]
, if \[A\]
represents Revenue per campaign for specific product(s)
and \[B\]
represents Ad cost by campaigns
. This returns the ratio of (Revenue for specific product(s)) / (Campaign Cost)
Return: Enter the formula \[A\]-\[B\]
. This returns the average margin made on a user by calculating (average user LTV) - (average cost per acquisition)
(Optional) Revenue: Unhide the Revenue by items
metric to see revenue for specific product(s) per campaigns
(Optional) Cost: Unhide the AdWords Cost
metric to see the cost for campaigns
Give your report a name and be sure to save it as a table
3. Repeat steps 1 and 2 above for each of your advertised product(s) or product group(s).