Analytics针对Web和移动设备交互的分析

Last update: Tue Jul 16 2024 00:00:00 GMT+0000 (Coordinated Universal Time)

主题：
查询

创建对象：

用户
开发人员

Adobe Experience Platform允许您使用Experience Data Model (XDM)字段从Adobe Analytics报表包中摄取数据以填充数据集。修改此分析数据以符合XDM ExperienceEvent类。然后，查询服务可以通过运行SQL查询来利用此数据，从用户在数字平台上的行为中生成有价值的见解。

本文档提供了各种示例SQL查询，这些示例演示了根据Web和移动Analytics数据创建见解时的常见用例。

有关摄取和映射Analytics数据的更多信息，请参阅Analytics字段映射文档。

快速入门

对于以下每个用例，都会提供一个参数化SQL查询示例作为模板供您自定义。在SQL示例中看到{ }的任何位置为您希望评估的数据集、eVar、事件或时间范围提供参数。

目标

以下示例显示了用于分析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;

确定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;

根据用户活动确定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;

确定查看次数最多的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;

确定前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;

recommendation-more-help

ccf2b369-4031-483f-af63-a93b5ae5e3fb