Skip to main content
Data Runs Deep Menu

Main navigation

  • Home
  • Who We Are
    • Careers
    • What We're Up To
    • What We Believe In
    • Ethical Policy
  • What We Do
  • Google Marketing Platform
    • Analytics 360
    • Tag Manager 360
    • Salesforce and GA360
    • Google Analytics App+Web
    • Data Studio
    • Optimize
  • Google Cloud Platform
    • Google BigQuery
      • Google BigQuery Training
      • Google Analytics Integration
  • Consulting
    • Strategy
    • Implementation
      • Google Analytics Audit
      • Google Tag Manager Audit
      • Enhanced Ecommerce Implementation
      • Google Tag Manager
    • Analysis
      • Google Data Studio
        • Google Data Studio Case Study - Mona
      • UX Analysis
  • Training
    • Five Day Google Analytics Online Workshop
    • Advanced Google Analytics Training
    • Google Tag Manager Training
    • Google Analytics 360 Training
    • Advanced Analytics Bootcamp
  • Partnerships
    • Conductrics
  • Blog
  • The Flying Beagle
  • Merch
  • Contact

How To Calculate Table Level Percentages In BigQuery

Calculating What Percentage Of Rows Are In Each Group
By: Balkan Misirli
Posted in
  • Google Cloud Platform
  • Google BigQuery
Article Date
June 26, 2019

In a lot of exploratory queries, you want to know not only how many rows had a certain value but what percentage of all rows in the table this count value represents.

For example, I have a table with 240 customers’ records. Of these customers, 115 are located in Australia, 55 in the US, 36 in China and 34 in Germany. To find these aggregate numbers is easy enough with a GROUP BY query like this:
 

But what if I also want to know what percentage of my customers are from each country? What percentage of my customers are within Australia? To do this, we can tweak the above query a little by introducing a window function.

A window function (also called an analytic function) calculates aggregate values over a group of rows. Unlike aggregate functions (GROUP BY), which return a single aggregate value for a group of rows, window functions return a single value for each row by computing the function over a group of input rows. You can read more about them in the BigQuery docs.

The result of this query will be a table with 4 fields:

  • Each country
  • Number of customers in that country
  • Total number of customers in the table (will be the same for each row)
  • Percentage of all customers that are in that country

Please note that the second and third fields are not necessary for this query to work. You may remove them if you only want to see the country names and percentages.
 


Related Posts

  • Cool things you can do using window functions in BigQuery

    Article Date
    September 11, 2020
  • Identify Potential Session Breakage Using BigQuery

    Article Date
    May 18, 2020
  • How to re-attribute your conversions to the correct traffic source in BigQuery

    Article Date
    December 04, 2020
  • Remove Duplicate Transactions From Your Reporting in BigQuery

    Article Date
    November 20, 2020
  • Use our BigQuery Statement Builder To Better Understand The Behaviours Of Your Customers

    Article Date
    February 22, 2020

Get in touch

To find out more, email us on hello@datarunsdeep.com.au or complete a quick contact form.

Get in touch

Talk to us: 1300 737 885

08:30 - 16:30, Monday to Friday. We love to chat

Visit us, we’re at:

Level 8, 313 Little Collins Street Melbourne, VIC 3000

Footer

  • Home
    • Who We Are
      • What We Do
      • What We're Up To
      • Careers
      • Contact
  • Training
    • Google Analytics Training Courses
    • Advanced Google Analytics Training
    • Google Tag Manager Training
  • Consulting
    • Analysis
    • Implementation
    • Strategy
    • Google Analytics Audit
    • Google Tag Manager Audit
  • Google Cloud Partner
  • Google Marketing Platform Sales Partner
  • Bitcoin accepted here
  • Registered Looker Consulting Partner Melbourne
  • Twitter
  • LinkedIn
  • Google Plus
  • Blog RSS feed
Copyright © 2009-2021 Data Runs Deep Pty Ltd. All Rights Reserved. Google Analytics is a trademark of Google Inc.
Website by Marin Sekesan