選択したAdobe Analyticsのレポートスイートのデータは、XDM ExperienceEventクラスに従うデータに変換され、データセットとしてAdobe Experience Platformに取り込まれます。
このドキュメントでは、Adobe Experience PlatformQuery Serviceがこのデータを使用する様々な使用例の概要を説明します。この例には、サンプルクエリがAdobe Analyticsのデータセットと連携する必要がある場合も含まれます。 Experience Eventsへのマッピングについて詳しくは、Analyticsフィールドマッピングのドキュメントを参照してください。
このドキュメント全体の SQL の例では、SQL を編集し、評価するデータセット、eVar、イベントまたは期間に基づいて、クエリに対して期待されるパラメーターを入力する必要があります。後述の SQL の例では、{ }
では常に任意のパラメータを指定します。
次の例は、Adobe Analyticsデータの分析に一般的に使用されるSQLクエリを示しています。
SELECT Substring(from_utc_timestamp(timestamp, 'America/New_York'), 1, 10) AS Day,
Substring(from_utc_timestamp(timestamp, 'America/New_York'), 12, 2) AS Hour,
Count(DISTINCT enduserids._experience.aaid.id) AS Visitor_Count
FROM {TARGET_TABLE}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
GROUP BY Day, Hour
ORDER BY Hour;
SELECT web.webpagedetails.name AS Page_Name,
Sum(web.webpagedetails.pageviews.value) AS Page_Views
FROM {TARGET_TABLE}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
GROUP BY web.webpagedetails.name
ORDER BY page_views DESC
LIMIT 10;
SELECT enduserids._experience.aaid.id AS aaid,
Count(timestamp) AS Count
FROM {TARGET_TABLE}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
GROUP BY enduserids._experience.aaid.id
ORDER BY Count DESC
LIMIT 10;
SELECT concat(placeContext.geo.stateProvince, ' - ', placeContext.geo.city) AS state_city,
Count(timestamp) AS Count
FROM {TARGET_TABLE}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
GROUP BY state_city
ORDER BY Count DESC
LIMIT 10;
SELECT Product_SKU,
Sum(Product_Views) AS Total_Product_Views
FROM (SELECT Explode(productlistitems.sku) AS Product_SKU,
commerce.productviews.value AS Product_Views
FROM {TARGET_TABLE}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
AND commerce.productviews.value IS NOT NULL)
GROUP BY Product_SKU
ORDER BY Total_Product_Views DESC
LIMIT 10;
SELECT Purchase_ID,
Round(Sum(Product_Items.priceTotal * Product_Items.quantity), 2) AS Total_Order_Revenue
FROM (SELECT commerce.`order`.purchaseid AS Purchase_ID,
Explode(productlistitems) AS Product_Items
FROM {TARGET_TABLE}
WHERE commerce.`order`.purchaseid IS NOT NULL
AND TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
GROUP BY Purchase_ID
ORDER BY total_order_revenue DESC
LIMIT 10;
SELECT Substring(from_utc_timestamp(timestamp, 'America/New_York'), 1, 10) AS Day,
Substring(from_utc_timestamp(timestamp, 'America/New_York'), 12, 2) AS Hour,
Sum(_experience.analytics.event1to100.{TARGET_EVENT}.value) AS Event_Count
FROM {TARGET_TABLE}
WHERE _experience.analytics.event1to100.{TARGET_EVENT}.value IS NOT NULL
AND TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
GROUP BY Day, Hour
ORDER BY Hour;
Adobe Analyticsでは、カスタムの製品レベルのデータは、マーチャンダイジング変数と呼ばれる特別に設定された変数を通じて収集できます。 これらは、eVarまたはカスタムイベントに基づいています。 これらの変数とその標準的な使用法の違いは、ヒットの単一の値ではなく、ヒットの各製品の個別の値を表すという点です。
これらの変数は、製品構文マーチャンダイジング変数と呼ばれます。 これにより、顧客の検索結果で、製品ごとの「割引金額」や、製品の「ページ上の場所」に関する情報などの情報を収集できます。
製品の構文の使用について詳しくは、製品の構文を使用したeVarの実装に関するAdobe Analyticsのドキュメントを参照してください。
以下の節では、Analyticsデータセット内のマーチャンダイジング変数にアクセスするために必要なXDMフィールドの概要を説明します。
productListItems[#]._experience.analytics.customDimensions.evars.evar#
#
:アクセスするアレイのインデックス。evar#
:アクセスする特定のeVar変数。productListItems[#]._experience.analytics.event1to100.event#.value
#
:アクセスするアレイのインデックス。event#
:アクセスする特定のカスタムイベント変数。以下に、productListItems
にある最初のクエリのマーチャンダイジング eVar とイベントを返すクエリ例を示します。
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
次のクエリはproductListItems
配列を探索し、各マーチャンダイジングeVarとイベントを製品ごとに返します。 元のヒットとの関係を示すために、_id
フィールドが含まれます。_id
値は、データセットの一意の主キーです。
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
現在のデータセットに存在しないフィールドを取得しようとすると、「No suct field」エラーが発生します。 エラーメッセージに返された理由を評価して使用可能なフィールドを特定し、クエリを更新して再実行します。
ERROR: ErrorCode: 08P01 sessionId: XXXX queryId: XXXX Unknown error encountered. Reason: [No such struct field evar1 in eVar10, eVar13, eVar62, eVar88, eVar2;]
Adobe Analyticsにある別のタイプのマーチャンダイジング変数は、コンバージョン構文です。 製品の構文を使用すると、製品と同時に値が収集されますが、そのためにはデータが同じページに存在する必要があります。 製品に関連するコンバージョンや関心のあるイベントの前に、ページでデータが発生するシナリオがあります。例えば、商品を探す方法の使用例を考えてみましょう。
Product View
イベントが発生します。Product View
がバインディングイベントとして設定されているので、製品の「ワッフルビーニー」は「内部検索:冬帽子」の eVar6 値にバインドされるようになります。「ワッフルビーニー」製品が収集されるたびに、(1) 有効期限の設定に達するか、(2) 新しい eVar6 値が設定され、その製品で再びバインディングイベントが発生するまで、「内部検索:冬帽」に関連付けられます。Cart Add
イベントが発生します。Product View
イベントが発生します。Product View
イベントは引き続きバインディングイベントなので、製品「スポーティーな T シャツ」が「内部検索:夏シャツ」の eVar6 値に、以前の製品「ワッフルビーニー」が「内部検索:ワッフルビーニー」の eVar6 値にバインドされています。Cart Add
イベントが発生します。レポートでは、注文件数、売上高、製品表示数、買い物かごへの追加数が eVar6 に対してレポートされ、連結された製品のアクティビティに合わせて調整されます。
eVar6(製品検索方法) | 売上高 | 注文件数 | 製品表示 | 買い物かごへの追加 |
---|---|---|---|---|
内部検索:夏のシャツ | 19.99 | 1 | 1 | 1 |
内部検索:冬帽子 | 12.99 | 3 | 1 | 1 |
変換構文の使用について詳しくは、コンバージョン構文を使用したeVarの実装に関するAdobe Analyticsのドキュメントを参照してください。
Analyticsデータセットに変換構文を生成するためのXDMフィールドを次に示します。
_experience.analytics.customDimensions.evars.evar#
evar#
:アクセスする特定のeVar変数。productListItems[#].sku
#
:アクセスするアレイのインデックス。次に、値を特定の製品とイベントのペア(この場合は製品表示イベント)に連結するクエリ例を示します。
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
それぞれの製品の後続のクエリに連結値を永続化するサンプルのオカレンスを示します。最も低いサブクエリは、宣言されたバインディングイベントの製品と値の関係を確立します。次のサブクエリは、それぞれの製品との後続のインタラクションにわたって、その連結値のアトリビューションを実行します。最上位レベルでは、集計を選択して結果を生成します。
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