Advertising Campaigns and ROI
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.
Prerequisites
-
Import your advertising cost data:
- Connect your Google AdWords to Commerce Intelligence: This syncs your Adwords spend in Commerce Intelligence
- Upload other advertising cost data: This is recommended for channels without a direct connector to Commerce Intelligence
- If you import cost data from multiple sources, you can consolidate the data in Commerce Intelligence. Simply submit a support ticket.
User acquisition campaigns
Campaigns targeted at user acquisition can be measured from many perspectives, including:
- The number of new users acquired of campaigns
- The conversion rate from registration to purchase of campaigns
- The ROI of campaigns based on average user lifetime value (LTV)
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.
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 theAdwords...
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 thedate
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 toNone
-
Under the
Group by
tab, addcampaign
as grouping field, and clickAdd All
in the box. -
This report shows your all-time AdWords cost by campaigns
2. Create a report that counts new users by campaigns:
- In any dashboard, click Add Report > Create report
- Select the
New users
metric that counts the number of new registered users over time - Set the Time period to
All-time
, and Interval toNone
- Under the
Group by
tab, addcampaign
asgrouping field
, and clickAdd All
in the box - This report shows your all-time registered users by campaigns
3. Create a report that segments average user LTV by campaigns:
- In any dashboard, click Add Report > Create report
- Select the
Average lifetime revenue
metric that calculates an average user’s lifetime revenue - Set the Time period to
All-time
, and Interval toNone
- Under the
Group by
tab, addcampaign
orutm\_campaign
as grouping field, and clickAdd All
in the box - This report shows your average user lifetime revenue by campaigns
Finally, 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
] representsAd Cost by Campaigns
, [B
] representsNew 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
andadClicks
(from AdWords data), along with the totalnumber 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.
Product campaigns
Are you running product-specific advertisements? If so, you can measure ROI on those campaigns by calculating revenue / cost for specific product(s).
Before you start, submit a support ticket to join the following dimensions to your line items table (sales\_flat\_order\_item, order\_item
):
- Order’s source (if you only track referral source at the user level, then join user’s source)
- Order’s campaign (if you only track referral source at the user level, then join user’s campaign)
- Order’s medium (if you only track referral source at the user level, then join user’s medium)
1. Now start by creating a chart that returns revenue per campaign for specific product(s):
- In any dashboard, click Add Report > Create new report
- Select the
Revenue by items
metric that calculates revenue at the line items level - Set the Time period to
All-time
, and Interval toNone
- Under the
Filter by
tab, addproduct name 'IN'
ProductA
, ProductB
, ProductC
, …" 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
) - Under the
Group by
tab, addorder's campaign
ororder's utm\_campaign
asgrouping
field, and click Add All in the box - This report shows you the revenue for specific product(s) by campaigns
2. 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 theUser 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\]
representsRevenue per campaign for specific product(s)
and\[B\]
representsAd 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).
Related documentation
- Track order referral source via Google Analytics E-Commerce
- Track user referral source in your database
- Track user device, browser, and OS data in your database
- Discover your most valuable acquisition sources and channels
- Connect your Google Adwords account
- How does Google Analytics UTM attribution work?
- Five best practices for UTM tagging in Google Analytics