チェックアウトページのエラーを修正して売上と顧客ロイヤルティを高める方法

目次

• チャレンジ
• ゴール
• 解決
  • ステップ1.エラーに関するデータを収集する
  • ステップ2.データを処理する
  • ステップ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スプレッドシートへのデータのインポートを設定しました。このアドオンは、テーブル内の情報を1時間ごとに自動的に更新します。 これは、レポートに過去1時間のエラーに関する最新のデータが常に含まれていることを意味します。

このようなレポートの例を以下に示します。

ステップ3.取得したデータを使用する

結果