New Account Monitoring
Boxalino provides a standardize Monitoring view which catches all the typical set-up issues so it is easy to monitor what has been done (and should work) from the to do / to fix.
category | label | signal.description | last check | state | |
---|---|---|---|---|---|
1 | products | total products | the total number of distinct product (sku) ids ever imported | 26 Feb 2023, 17:57:40 | normal |
2 | orders | last transaction | the timestamp of the last transaction imported | 26 Feb 2023, 17:57:40 | normal |
3 | orders | the percentage of transactions which are the first order of a customer in the last week | 26 Feb 2023, 17:57:40 | abnormal | |
4 | orders | the percentage of order margin in the last week | 26 Feb 2023, 17:57:40 | normal | |
5 | orders | the percentage of products product revenue should be 90% similar to the order revenue | 26 Feb 2023, 17:57:40 | normal | |
6 | orders | product id match rate | the percentage of products from the orders that are found in the products data | 26 Feb 2023, 17:57:40 | normal |
7 | orders | total transactions | the total number of distinct transaction identifiers ever imported | 26 Feb 2023, 17:57:40 | normal |
8 | google analytics | last Google Analytics event | the timestamp of the last Google Analytics event | 26 Feb 2023, 17:57:40 | normal |
9 | google analytics | total Google Analytics events | the total number of Google Analytics events ever | 26 Feb 2023, 17:57:40 | normal |
10 | google analytics | the percentage of untracked transactions in the ECM type in the last week | 26 Feb 2023, 17:57:40 | unmatched | |
11 | google ads | the number of conversions for the last week > 10 | 26 Feb 2023, 17:57:40 | abnormal | |
12 | google ads | last Google Ads Campaign entry | the timestamp of the last Google Ads Campaign table entries | 26 Feb 2023, 17:57:40 | normal |
13 | google ads | the percentage of the google shopping conversions versus all gads conversions | 26 Feb 2023, 17:57:40 | abnormal | |
14 | google ads | the percentage of the google shopping conversions versus attributed purchases | 26 Feb 2023, 17:57:40 | abnormal | |
15 | google ads | total Google Ads Campaign entries | the total number of Google Ads Campaign table entries ever | 26 Feb 2023, 17:57:40 | normal |
16 | customers | total customers | the total number of distinct customer identifiers ever imported | 26 Feb 2023, 17:57:40 | normal |
17 | Online Marketing Report | Paid Search Unique Purchase | the total unique purchases (products) attributed to Paid Search during the last month with session source | 26 Feb 2023, 17:57:40 | normal |
18 | Online Marketing Report | Paid Search purchases | the total purchases attributed to Paid Search during the last month with session source | 26 Feb 2023, 17:57:40 | normal |
19 | Google Shopping Feed | Google Shopping Feed | there is at least 1 google shopping feed active | 26 Feb 2023, 17:57:40 | normal |
20 | Boxalino js tracking | last Boxalino JS tracking event | the timestamp of the last Boxalino javascript tracking event | 26 Feb 2023, 17:57:40 | missing |
21 | Boxalino js tracking | total Boxalino JS tracking events | the total number of boxalino javascript tracking events ever | 26 Feb 2023, 17:57:40 | missing |
22 | Boxalino API requests | last Boxalino API request | the timestamp of the last Boxalino API request | 26 Feb 2023, 17:57:40 | missing |
23 | Boxalino API requests | total Boxalino API requests | the total number of Boxalino API requests ever | 26 Feb 2023, 17:57:40 | missing |
3. orders - new customer rate
the percentage of transactions which are the first order of a customer in the last week
What does it mean?
Either there are less then 10 customers or the percentage of new customers is higher(>90%) or lower(<10%) then expected.
What is the typical cause?
Orders are completely/partially missing in the core.
How to solve it?
Check why Orders are not updated.
4. orders - order margin rate
the percentage of order margin in the last week
What does it mean?
Either there are less then 10 orders or the margin rate is higher(>90%) or lower(<10%) then expected.
What is the typical cause?
Orders are completely/partially missing in the core.
Missing Margin Data.
How to solve it?
Check why Orders are not updated.
Check for standard margin fields in doc_order and doc_product. If none is available ask the client directly.
The configuration flow is the following:
views.product_margin defines what is the product margin ( (product_sales_price - product_purchase_price) / product_sales_price) which is by default set from the value of the doc_product grossMargin and, if none set with a fall-back to a fix value of 30%
reports.product_version is then set historically with the margin at the time of each processing (typically daily)
views.order_line_margin is by default using the margin of reports.product_version
version.order_margin is by default using the margin of views.order_line_margin
the views order_line_margin and order_margin are used in the order_primary and order_product reports tables
Each of the 3 views can be overwritten in case more precise information is available, but the only one which must be overwritten if the purchase sales price is available is the first view.
Here is an example of how to update the views.product_version with a field called 'purchasePrice
':
SELECT
product_id
, exact_margin
, round(exact_margin * 20) / 20 as rounded_margin
from
(
select
p.product_id,
least(case when any_value(dp.property_value) is null or safe_cast(any_value(dp.property_value) as float64) <= 0 or any_value(m.property_value) is null or safe_cast(any_value(m.property_value) as float64) = 0 then 0.3
else
(
safe_cast(any_value(dp.property_value) as float64)
- safe_cast(any_value(m.property_value) as float64))
/ safe_cast(any_value(dp.property_value) as float64)
end, 1) as exact_margin
FROM `bx-bdp-53322.ACCOUNT_NAME_views.product` as p
left join `bx-bdp-53322.ACCOUNT_NAME_views.product_property_optimized` as dp on dp.product_id = p.product_id and dp.property_name = 'discountedPrice'
left join `bx-bdp-53322.ACCOUNT_NAME_views.product_property_optimized` as m on m.product_id = p.product_id and m.property_name = 'purchasePrice'
group by p.product_id
)
As the reports product_version keeps the historical data, you can then either truncate it or, if you don’t want to lose the changes in product prices, to update it with a query like follows:
update `bx-bdp-53322.ACCOUNT_NAME_reports.product_version` as p
set p.margin = m.exact_margin
from `bx-bdp-53322.ACCOUNT_NAME_views.product_margin` AS m
where p.margin != m.exact_margin and p.product_id = m.product_id
5. orders - order revenue versus product revenue
the percentage of products product revenue should be 85% similar to the order revenue
What does it mean?
the product revenue is either below 85% or above 115% of the order revenue.
What is the typical cause?
order revenue includes shipping cost
order revenue includes additional service cost (custom packaging, greeting card)
(issue in order_line so that order and product revenue are a complete mismatch)
How to solve it?
???
???
10. google analytics - untracked percentage
the percentage of untracked transactions in the ECM type in the last week
What does it mean?
Too many purchases cannot be mapped with the Google Analytics data.
What is the typical cause?
The order identifiers exported in doc_order do not match the ones in Google Analytics tracking
How to solve it?
Modify in BigQuery the view : views.order_external_id in way similar to these ones:
select internal_id as order_id, external_id
from `bx-bdp-53322.ACCOUNT_NAME_core.doc_order`
Other causes
Returns are in our reports but not tracked by GA4.
How to fix them?
Do not include Returns in reports.
11. google ads - has conversions
the number of conversions for the last week > 10
What does it mean?
There is a problem with the AdsTransfer or its configuration.
What is the typical cause?
AdsTransfer not set up
Configuration script not executed
How to solve it?
Ask Client to set up the Transfer (How to transfer your Google Ads Data to BigQuery )
Check the views created in 'bx-bigquery-google-ads-analytics-views.sql'
13. google ads - shopping conversions percentage
the percentage of the google shopping conversions versus all gads conversions
What does it mean?
The google shopping data do not represent a relevant percentage of the overall google ads conversions, which is most likely because it is not properly detected.
What is the typical cause?
First, it can be that the table reports.gads_campaign_offer_analytics is completely empty, which might be because the view views.ShoppingPerformance was not configured at all.
How to solve it?
Update the views.ShoppingPerformance as follows:
Other causes
There is an issue with the transfer where the shopping tables are not filled properly.
How to fix them?
PMax not enabled in the transfer.
14. google ads - shopping conversions attribution
the percentage of the google shopping conversions versus attributed purchases
What does it mean?
The google shopping data have conversions, but these conversions are not attributed to specific product sales (or not enough: < 80%)
What is the typical cause?
It might be that the product identifier from ShoppingPerformance (google shopping offerid) doesn’t match with the product identifier of the order product data.
How to solve it?
Then, if you have Google Shopping Ads Data but no Attributed (Shopping) Metrics, it is probably because the SKU mapping doesn’t work and should be revealed by this SQL query:
in general, we try to extend the views.products_top_parent_sku
to detect new cases automatically in a generic way. so we don’t have to implement a view every time. Therefore, the recommended resolution is a generic extension of that view.
Other causes
There is an issue with the transfer where the shopping tables are not filled properly.
How to fix them?
PMax not enabled in the transfer.
16. customers - total customers
the total number of distinct customer identifiers ever imported
What does it mean?
if “missing” no customer identifiers are exported to bx-bdp-53322.ACCOUNT_NAME_core.doc_customer.
if “low“ less then 100.
What is the typical cause?
Customer Data not available
How to solve it?
Wait for Client to provide Customer Data