Question

What is the recommended approach to validate data integrity between the Google Ads UI and Fivetran synced connector tables?

Environment

Connector: Google Ads

Answer

Custom Report or Prebuilt ERD Tables:

1. Build the report in the Google UI Report Editor using the same fields configured in the connector report or prebuilt table. If you have many fields and are only comparing for certain metrics:
   1. Configure all dimension fields. These are fields like ad group name and campaign name 
   2. Configure all segment fields. These fields will be named as "segment.<field_name>" in the Fivetran report
   3. Configure your required metric fields. 
2. For comparisons related to conversions, ensure the conversion window set in the connector configuration matches the conversion window set in Google:
   • View the Instructions section of the About conversion windows Google documentation to learn how to view/edit the conversion window in Google.
3. Query the connector-created warehouse table using a simplified query:
   • Don't sum data if possible.
   • If the metric values are percentages, you cannot Sum these. You will need to get an Average as opposed to a Sum if you want to get the total across multiple records, as is seen in the Google UI. 
   • Compare data in small date ranges.
4. If there is a difference in the comparisons, alter the query to aim to narrow down where the difference may be:
   1. Simplify the query as much as possible.
   2. Reduce the date range.
   3. Remove Sums from the query to aim to identify specific records with different values or are not present in each result.

Cause

It is common practice for customers to complete data validation between source Applications and the Fivetran synced data.

In some cases, these comparisons can show apparent data discrepancies due to how data is viewed or presented in the UI. For information on potential causes of data discrepancies, see this troubleshooting article. 

.