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

	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	new customer rate	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	order margin rate	the percentage of order margin in the last week	26 Feb 2023, 17:57:40	normal
5	orders	order revenue versus product revenue	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	untracked percentage	the percentage of untracked transactions in the ECM type in the last week	26 Feb 2023, 17:57:40	unmatched
11	google ads	has conversions	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	shopping conversions percentage	the percentage of the google shopping conversions versus all gads conversions	26 Feb 2023, 17:57:40	abnormal
14	google ads	shopping attribution percentage	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`

select o.order_id, any_value(ifnull(olp.property_value, o.order_id)) as external_id 
from `bx-bdp-53322.ACCOUNT_NAME_core.order` as o
left join `bx-bdp-53322.ACCOUNT_NAME_core.order_line_property` as olp on o.order_id = olp.order_id and olp.property_name = 'increment_id'
group by order_id

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:

SELECT * FROM `bx-bdp-53322.boxalino_core.ShoppingPerformance`

union all

select 
  
  'OPTIMIZE' as serving_status
  , segments_product_channel as advertising_channel_type
  , 'UNSPECIFIED' as advertising_channel_sub_type
  , c.c.campaign_name as campaign
  , cast(s.campaign_id as int64) as campaign_id
  , campaign_status as status
  , segments_product_channel as product_channel
  , segments_product_channel_exclusivity as product_channel_exclusivity
  , segments_product_condition as product_condition
  , 'CHF' as currency
  , customer_id
  , '' as customer_name
  , date(_PARTITIONTIME) as day
  , segments_device as device
  , segments_product_item_id as item_id
  , segments_ad_network_type as ad_network_type
  , 'UNKNOWN' as click_type
  , metrics_all_conversions as all_conversions
  , metrics_all_conversions_value as all_conversions_value
  , metrics_average_cpc as average_cpc
  , metrics_ctr as ctr
  , metrics_clicks as clicks
  , case when metrics_clicks> 0 then metrics_all_conversions / metrics_clicks else 0 end as conversion_rate
  , metrics_conversions as conversions
  , metrics_cost_micros/1000000 as cost
  , cast(metrics_cross_device_conversions as string) as crossdevice_conversions
  , metrics_impressions as impressions
  , metrics_all_conversions_value as total_conversion_value

from `GOOGLE_ADS_PROJECT.GOOGLE_ADS_DATASET.p_ads_ShoppingProductStats_GOOGLE_ADS_ACCOUNT_ID` as s
join (
  select campaign_id, date(_PARTITIONTIME) as day, any_value(c) as c
  from `GOOGLE_ADS_PROJECT.GOOGLE_ADS_DATASET.p_ads_Campaign_GOOGLE_ADS_ACCOUNT_ID` as c
  group by campaign_id, day
) as c on s.campaign_id = c.campaign_id and date(s._PARTITIONTIME) = c.day

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:

select
  item_id
  , sum(clicks) as clicks

FROM `bx-bdp-53322.ACCOUNT_NAME_views.ShoppingPerformance` as t
left join `bx-bdp-53322.ACCOUNT_NAME_views.products_top_parent_sku` as pp on lower(pp.sku) = lower(item_id)
where pp.sku is null
group by item_id
order by cl

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

Boxalino Public Knowledge Base

New Account Monitoring

3. orders - new customer rate

What does it mean?

What is the typical cause?

How to solve it?

4. orders - order margin rate

What does it mean?

What is the typical cause?

How to solve it?

5. orders - order revenue versus product revenue

What does it mean?

What is the typical cause?

How to solve it?

10. google analytics - untracked percentage

What does it mean?

What is the typical cause?

How to solve it?

Other causes

How to fix them?

11. google ads - has conversions

What does it mean?

What is the typical cause?

How to solve it?

13. google ads - shopping conversions percentage

What does it mean?

What is the typical cause?

How to solve it?

Other causes

How to fix them?

14. google ads - shopping conversions attribution

What does it mean?

What is the typical cause?

How to solve it?

Other causes

How to fix them?

16. customers - total customers

What does it mean?

What is the typical cause?

How to solve it?