How to check the number of transaction hits in BigQuery per date?

When you submit the Measurement Protocol request to the endpoint https://google-analytics.bi.owox.com/UA-XXXXX-X, OWOX BI records all the transaction hits. While Google Analytics records only one hit out of two (or more) hits with the same parameter values in the actionField object. In other words, Google Analytics stores information only about the unique transaction hits. This fact often leads to the discrepancy between OWOX BI and Google Analytics data.

On your streaming pipeline page, OWOX BI automatically calculates the transaction discrepancy rate using the following formula:

([transactions] - [gaTransactions]) / [gaTransactions] * 100% = [discrepancy, %],

where:

[transactions] refers to all the transaction hits (OWOX BI data);

[gaTransactions] refers to the unique transaction hits (Google Analytics data).

The acceptable discrepancy is up to 3.5%. Anytime, you can cross-check figures from the pipeline page with the BigQuery data. To do it, use the sample SQL requests below.

Getting all the transaction hits per date

This query shows the total number of transaction hits that the OWOX BI streaming pipeline collected on the selected date. The query result corresponds to the OWOX BI data in the Discrepancy to GA (transactions) column on the pipeline page.

SELECT
  date,
  COUNT(transaction.transactionId) transactions
  FROM
  `Project.Dataset.owoxbi_sessions_20210513`,
  UNNEST(hits) hits
  WHERE
  transaction.transactionId IS NOT NULL
  GROUP BY 1
  ORDER BY
  1 ASC

Update the following parameters with your own values:

Project is the name of your GBQ project,

Dataset is the name of your GBQ dataset,

owoxbi_sessions_20210513 is the name of the BigQuery table on the selected date.

Getting the unique transaction hits per date

This query shows how many unique transaction hits are in transaction data that the OWOX BI streaming pipeline collected on the selected date. You can compare the query result with the Google Analytics data in the Discrepancy to GA (transactions) column on the pipeline page.

SELECT
  date,
  COUNT (DISTINCT transaction.transactionId) transactions
  FROM `Project.Dataset.streaming_20210513`
  GROUP BY date
  ORDER BY date

Update the following parameters with your own values:

Project is the name of your GBQ project;

Dataset is the name of your GBQ dataset;

streaming_20210513 is the name of the BigQuery table on the selected date.