如何通过修复结帐页面上的错误来提高销售额和客户忠诚度

目录

• 挑战
• 目标
• 解决方案
  • 步骤 1. 收集有关错误的数据
  • 步骤 2. 处理数据
  • Step 3. 使用获取的数据
• 结果

挑战

目标

解决方案

步骤 1. 收集有关错误的数据

步骤 2. 处理数据

    Select date, hour,minute, email, description, City, productIds, productnames, productprice, cartquantity, cartamount, From ( SELECT usererrors.date as date, usererrors.hour as hour, usererrors.minute as minute, usererrors.userEmail as email, usererrors.action as action, usererrors.error as error, case when action = 'disabled' and error = 'buttonCreateOrder' then 'Unable to checkout in cart' when action = 'notAdded' and error = 'productToCart' then 'Unable to add product to cart' when action = 'open' and error = 'emptyCart' then 'Product can not be viewed in cart' when action = 'open' and error = 'HoldingPageCheckout' then 'Maintenance work in progress (after checkout)' when action = 'absent' and error = ƋdiscountCart' then Ƌ% discount unavailable online (in cart)' when action = 'absent' and error = ƋdiscountCheckout' then Ƌ% discount unavailable online (at the checkout page)' when action = 'disabled' and error = 'buttonSubmit' then 'Button "Place Order" not working at the checkout page' else 'Other' end as description, Dimensions.city as City, Dimensions.productids as productIds, Dimensions.productnames as productnames, customMetrics.productprice as productprice, customMetrics.cartquantity as cartquantity, customMetrics.cartamount as cartamount, FROM (SELECT date, hour,minute, hitId, userEmail, eventInfo.eventAction as action, eventInfo.eventLabel as error, hour(CURRENT_TIMESTAMP())+2-hour as currenthour from TABLE_DATE_RANGE([mvideo-ru:Streaming.streaming_], DATE_ADD(CURRENT_TIMESTAMP(), 0 ,'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), 0 , 'DAY') ) where eventInfo.eventCategory = 'Errors' and userEmail is not null group by 1,2,3,4,5,6,7,8) as usererrors left join (SELECT hitId, MAX(IF(customDimensions.index=4, customDimensions.value, NULL)) WITHIN hitId AS city, MAX(IF(customDimensions.index=21, customDimensions.value, NULL)) WITHIN hitId AS productids, Max(IF(customDimensions.index=22, customDimensions.value, NULL)) WITHIN hitId AS productnames from TABLE_DATE_RANGE([mvideo-ru:Streaming.streaming_], DATE_ADD(CURRENT_TIMESTAMP(), 0 ,'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), 0 , 'DAY') )) as Dimensions ON Dimensions.hitId=usererrors.hitId left join (SELECT hitId, MAX(IF(customMetrics.index=1, customMetrics.value, NULL)) WITHIN hitId AS productprice, MAX(IF(customMetrics.index=4, customMetrics.value, NULL)) WITHIN hitId AS cartamount, Max(IF(customMetrics.index=5, customMetrics.value, NULL)) WITHIN hitId AS cartquantity FROM TABLE_DATE_RANGE([mvideo-ru:Streaming.streaming_], DATE_ADD(CURRENT_TIMESTAMP(), 0 ,'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), 0 , 'DAY') )) as customMetrics ON customMetrics.hitId=usererrors.hitId where usererrors.currenthour=0 ) ORDER BY date asc, hour asc, minute asc, email asc
Select date, hour,minute, email, description, City, productIds, productnames, productprice, cartquantity, cartamount, From ( SELECT usererrors.date as date, usererrors.hour as hour, usererrors.minute as minute, usererrors.userEmail as email, usererrors.action as action, usererrors.error as error, case when action = 'disabled' and error = 'buttonCreateOrder' then 'Unable to checkout in cart' when action = 'notAdded' and error = 'productToCart' then 'Unable to add product to cart' when action = 'open' and error = 'emptyCart' then 'Product can not be viewed in cart' when action = 'open' and error = 'HoldingPageCheckout' then 'Maintenance work in progress (after checkout)' when action = 'absent' and error = ƋdiscountCart' then Ƌ% discount unavailable online (in cart)' when action = 'absent' and error = ƋdiscountCheckout' then Ƌ% discount unavailable online (at the checkout page)' when action = 'disabled' and error = 'buttonSubmit' then 'Button "Place Order" not working at the checkout page' else 'Other' end as description, Dimensions.city as City, Dimensions.productids as productIds, Dimensions.productnames as productnames, customMetrics.productprice as productprice, customMetrics.cartquantity as cartquantity, customMetrics.cartamount as cartamount, FROM (SELECT date, hour,minute, hitId, userEmail, eventInfo.eventAction as action, eventInfo.eventLabel as error, hour(CURRENT_TIMESTAMP())+2-hour as currenthour from TABLE_DATE_RANGE([mvideo-ru:Streaming.streaming_], DATE_ADD(CURRENT_TIMESTAMP(), 0 ,'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), 0 , 'DAY') ) where eventInfo.eventCategory = 'Errors' and userEmail is not null group by 1,2,3,4,5,6,7,8) as usererrors left join (SELECT hitId, MAX(IF(customDimensions.index=4, customDimensions.value, NULL)) WITHIN hitId AS city, MAX(IF(customDimensions.index=21, customDimensions.value, NULL)) WITHIN hitId AS productids, Max(IF(customDimensions.index=22, customDimensions.value, NULL)) WITHIN hitId AS productnames from TABLE_DATE_RANGE([mvideo-ru:Streaming.streaming_], DATE_ADD(CURRENT_TIMESTAMP(), 0 ,'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), 0 , 'DAY') )) as Dimensions ON Dimensions.hitId=usererrors.hitId left join (SELECT hitId, MAX(IF(customMetrics.index=1, customMetrics.value, NULL)) WITHIN hitId AS productprice, MAX(IF(customMetrics.index=4, customMetrics.value, NULL)) WITHIN hitId AS cartamount, Max(IF(customMetrics.index=5, customMetrics.value, NULL)) WITHIN hitId AS cartquantity FROM TABLE_DATE_RANGE([mvideo-ru:Streaming.streaming_], DATE_ADD(CURRENT_TIMESTAMP(), 0 ,'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), 0 , 'DAY') )) as customMetrics ON customMetrics.hitId=usererrors.hitId where usererrors.currenthour=0 ) ORDER BY date asc, hour asc, minute asc, email asc

作为查询的结果，分析人员获得了一个包含以下信息的表：

• 日期;
• 小时;
• 分钟;
• 用户电子邮件地址；
• 错误描述；
• 用户城市；
• 购物车中产品的 ID；
• 购物车中的产品名称；
• 购物车中产品的价格；
• 购物车中的产品总数；
• 购物车中产品的总价。

然后，使用 OWOX BI BigQuery Reports 插件设置数据导入到 Google 表格，该插件每小时自动更新表中的信息。 这意味着报告始终包含有关前一小时错误的最新数据。

您可以在下面找到此类报告的示例：

Step 3. 使用获取的数据

结果