Use Query Service to manage the data ingested from Adobe Analytics into Adobe Experience Platform as datasets. The following sections provide sample queries that you can use to access the merchandising variables in your Analytics datasets. See the documentation for more information on how to ingest and map Adobe Analytics data through the Analytics source
Merchandising variables can follow one of two syntaxes:
In Adobe Analytics, custom product-level data can be collected through specially configured variables called merchandising variables. These are based on either an eVar or custom events. The difference between these variables and their typical use is that they represent a separate value for each product found on the hit rather than only a single value for the hit.
These variables are referred to as product syntax merchandising variables. This allows for the collection of information, such as a per-product “discount amount” or information about the product’s “location on page” in the customer’s search results.
To learn more about using the product syntax, please read the Adobe Analytics documentation on implementing eVars using product syntax.
The sections below outline the XDM fields needed to access the merchandising variables in your Analytics dataset:
productListItems[#]._experience.analytics.customDimensions.evars.evar#
#
: The index of the array you are accessing.evar#
: The specific eVar variable that you are accessing.productListItems[#]._experience.analytics.event1to100.event#.value
#
: The index of the array you are accessing.event#
: The specific custom event variable that you are accessing.The following use cases focus on returning a merchandising eVar from the productListItems
array using SQL.
The query below returns a merchandising eVar and event for the first product found in the productListItems
array.
SELECT
productListItems[0]._experience.analytics.customDimensions.evars.eVar1,
productListItems[0]._experience.analytics.event1to100.event1.value
FROM adobe_analytics_midvalues
WHERE timestamp = to_timestamp('2019-07-23')
AND productListItems[0].SKU IS NOT NULL
AND productListItems[0]._experience.analytics.customDimensions.evars.eVar1 IS NOT NULL
AND productListItems[0]._experience.analytics.event1to100.event1.value IS NOT NULL
LIMIT 10
This next query explodes the productListItems
array and returns each merchandising eVar and event per product. The _id
field is included to show the relationship to the original hit. The _id
value is a unique primary key for the dataset.
The explode function separates the elements of an array into multiple rows. It excludes null values.
SELECT
_id,
productItem._experience.analytics.customDimensions.evars.eVar1,
productItem._experience.analytics.event1to100.event1.value
FROM (
SELECT
_id,
explode(productListItems) as productItem
FROM adobe_analytics_midvalues
WHERE TIMESTAMP = to_timestamp('2019-07-23')
AND productListItems[0].SKU IS NOT NULL
AND productListItems[0]._experience.analytics.customDimensions.evars.eVar1 IS NOT NULL
AND productListItems[0]._experience.analytics.event1to100.event1.value IS NOT NULL
)
LIMIT 20
If you attempt to retrieve a field that does not exist in your current dataset, the “No such struct field” error occurs. Evaluate the reason returned in the error message to identify an available field, then update your query and rerun it.
ERROR: ErrorCode: 08P01 sessionId: XXXX queryId: XXXX Unknown error encountered. Reason: [No such struct field evar1 in eVar10, eVar13, eVar62, eVar88, eVar2;]
Another type of merchandising variable that is found in Adobe Analytics is conversion variable syntax. Conversion variable syntax is used when the eVar value is not available to be set in the products variable. This scenario typically means that your page has no context of the merchandising channel or finding method. In these cases, you should set the merchandising variable before the user arrives at the product page, and the value persists until the binding event occurs.
For example, the product-finding scenario below illustrates how the required data can be present on a page before the conversion or event related to the product occurs.
Product View
event for the “waffle beanie” for $12.99.Product View
is configured as a binding event, the product “waffle beanie” is now bound to the eVar6 value of “internal search:winter hat”. Anytime the “waffle beanie” product is collected, it is associated with “internal search:winter hat”. This happens until either the eVar expiration setting is reached, or, a new eVar6 value is set and the binding event occurs with that product again.Cart Add
event.Product View
event for "sporty t-shirt for $19.99.Product View
event is the binding event, the product “sporty t-shirt” is now bound to the eVar6 value of “internal search:summer shirt”. The prior product “waffle beanie” is still bound to an eVar6 value of “internal search:waffle beanie”.Cart Add
event.In reporting, the orders, revenue, product views, and cart adds are reportable against eVar6 and align to the activity of the bound product.
eVar6 (product-finding method) | revenue | orders | product views | cart adds |
---|---|---|---|---|
internal search:summer shirt | 19.99 | 1 | 1 | 1 |
internal search:winter hat | 12.99 | 1 | 1 | 1 |
To learn more about using the conversion variable syntax, please read the Adobe Analytics documentation on implementing eVars using conversion variable syntax.
Displayed below are the XDM fields to produce the conversion variable syntax in your Analytics dataset:
_experience.analytics.customDimensions.evars.evar#
evar#
: The specific eVar variable that you are accessing.productListItems[#].sku
#
: The index of the array you are accessing.The use cases below reflect scenarios that require conversion variable syntax.
The query below binds the value to the specific product and event pair. In this example, the value is bound to the product view event.
SELECT
endUserIds._experience.aaid.id AS AAID,
timestamp,
CASE WHEN commerce.productViews.value = 1 THEN ATTRIBUTION_LAST_TOUCH(timestamp, 'bindConversionSyntaxMerchVariable_eVar1', _experience.analytics.customDimensions.eVars.eVar1)
OVER(PARTITION BY endUserIds._experience.aaid.id
ORDER BY timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).value
END AS eVar1Bind,
EXPLODE(productListItems) AS Product_List,
commerce.productViews.value AS prodView,
commerce.purchases.value AS purchase
FROM adobe_analytics_midvalues
WHERE commerce.productViews.value = 1 OR commerce.purchases.value = 1 OR _experience.analytics.customDimensions.eVars.eVar1 IS NOT NULL
LIMIT 100
The sample query below persists the bound value to subsequent occurrences of the respective product. The lowest sub-query establishes the value’s relationship with the product on the declared binding event. The next sub-query performs the attribution of that bound value across subsequent interactions with the respective product. The top-level SELECT aggregates the results to produce the reporting.
SELECT
Product_List.SKU,
eVar1101ConversionSyntax,
SUM(prodView) AS Product_Views,
SUM(purchase) AS Purchases
FROM
(
SELECT
Product_List,
ATTRIBUTION_LAST_TOUCH(timestamp, 'ConversionSyntax_eVar1', eVar1Bind)
OVER(PARTITION BY AAID, Product_List.SKU
ORDER BY timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).value
AS eVar1ConversionSyntax,
prodView,
purchase
FROM
(
SELECT
endUserIds._experience.aaid.id AS AAID,
timestamp,
CASE WHEN commerce.productViews.value = 1 THEN ATTRIBUTION_LAST_TOUCH(timestamp, 'bindConversionSyntaxMerchVariable_eVar1', _experience.analytics.customDimensions.eVars.eVar1)
OVER(PARTITION BY endUserIds._experience.aaid.id
ORDER BY timestamp
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).value
END AS eVar1Bind,
EXPLODE(productListItems) AS Product_List,
commerce.productViews.value AS prodView,
commerce.purchases.value AS purchase
FROM adobe_analytics_midvalues
WHERE commerce.productViews.value = 1 OR commerce.purchases.value = 1 OR _experience.analytics.customDimensions.eVars.eVar1 IS NOT NULL
)
)
WHERE eVar1ConversionSyntax IS NOT NULL
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 100
By reading this document, you should have a better understanding of how to return a merchandising eVar using product syntax and bind a value to a specific product with the conversion variable syntax.
If you have not already done so, you should read the Analytics insights for web and mobile interactions documentation next. It provides common use cases and demonstrates how to use Query Service to create actionable insights from web and mobile Adobe Analytics data.