mrdipeshashah/big-query-google-analytics-tracking-match-rate

This repository holds the data requirements, BigQuery SQL, and Data Studio dashboard setup for checking how well an online store's sales tracking in Google Analytics 4 matches its actual revenue in an e-commerce platform like Shopify. The idea is that Shopify's own sales records are treated as the source of truth, and Google Analytics 4's tracked purchase revenue is compared against it every day to catch cases where the analytics tag is not recording sales correctly. The repository is mostly documentation rather than a full application. It describes a linked Data Studio dashboard, since renamed Looker Studio, that visualizes the comparison, and it defines the objective plainly: give automated daily visibility into any gap between the two revenue numbers, and flag days where the match rate falls below a target of 90 percent. To build this comparison yourself, the README lists the exact table structure needed: a date column, a column for Shopify revenue, and a column for Google Analytics revenue, aggregated by day. It also spells out the two calculations used in the dashboard: the plain revenue difference between the two sources, which shows how much money the Google Analytics tag under-reported or missed entirely, and the match rate percentage, calculated by dividing Google Analytics revenue by Shopify revenue, with 90 percent or higher considered healthy tracking. The README is short and does not include the actual BigQuery SQL queries or setup instructions in the text shown, so someone using this would need to build the pipeline themselves following the schema and formulas described.