# Data Types in BigQuery

BigQuery supports all common data types found in Standard SQL. Google Cloud has [verbose documentation](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types), but here it is presented short and sweet:

## Most Common

| **Name** | **Description** | **Storage Size** | **Note** |
| --- | --- | --- | --- |
| INT64 (Integer) | A whole number that is not a fraction. | 8 bytes | Range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
| NUMERIC | A number with 38 digits of precision, 9 of which can be after the decimal point. | 16 bytes | Range from -99999999999999999999999999999.999999999 to 99999999999999999999999999999.999999999 |
| FLOAT64 (Float) | Double precision (approximate) decimal values. | 8 bytes | [Wikipedia will take this one.](https://en.wikipedia.org/wiki/Double-precision_floating-point_format) |
| BOOL (Boolean) | Represented by keywords TRUE and FALSE |  | Case insensitive so `TRUE` or `false` will work. |
| STRING | Variable-length character (Unicode) data. | | Must be quoted with either single (') or double (") quotation marks. Alternatively, can be triple-quoted with groups of three single (''') or three double (""") quotation marks. |
| BYTES | Variable-length binary data. | | Not to be used interchangeably with STRING.  
| BYTES operates on raw bytes rather than Unicode characters. | | | |
| ARRAY | Ordered list of zero or more elements of any non-ARRAY type. |  |  |

### Time Data Types

| **Name** | **Description** | **Canonical Format** | **Note** |
| --- | --- | --- | --- |
| DATE | Represents a logical calendar date, without time. | `YYYY-[M]M-[D]D` |BYTES operates on raw bytes rather than Unicode characters. Range from 0001-01-01 to 9999-12-31 |
| TIME | Represents a time, independent of a specific date. | `[H]H:[M]M:[S]S[.DDDDDD]` | Range from 00:00:00 to 23:59:59.999999 |
| DATETIME | Represents a year, month, day, hour, minute, second, and subsecond _without a timezone_. † | `YYYY-[M]M-[D]D[(|T)[H]H:[M]M:[S]S[.DDDDDD]]` | Range from 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999 |
| TIMESTAMP | Represents an absolute point in time, with microsecond precision _with a timezone_. | `YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]][time zone]` | Range from 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999 |

† `DATETIME` is seldom used, as it's rare to wish to omit the timezone.

**How to read Canonical Format:**

*   `YYYY`: Four-digit year
*   `[M]M`: One or two digit month
*   `[D]D`: One or two digit day
*   `( |T)`: A space or a `T` separator
*   `[H]H`: One or two digit hour (valid values from 00 to 23)
*   `[M]M`: One or two digit minutes (valid values from 00 to 59)
*   `[S]S`: One or two digit seconds (valid values from 00 to 59)
*   `[.DDDDDD]`: Up to six fractional digits (microsecond precision)
*   `[time zone]`: String representing the time zone, with two canonical formats:
    *   [Time zone name](https://cloud.google.com/dataprep/docs/html/Supported-Time-Zone-Values_66194188) per the tz database
    *   Offset from Coordinated Universal Time (UTC), or the letter Z for UTC

## Less Common

| **Name** | **Description** | **Note** |
| --- | --- | --- |
| STRUCT | A container of ordered fields, each with a type (required) and field name (optional). | [Learn more](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#struct-type) about STRUCT |
| GEOGRAPHY | A collection of points, lines, and polygons, which is represented as a point set, or a subset of the surface of the Earth. | Based on the Open Geospatial Consortium’s (OGC) [Simple Features specification (SFS)](https://www.opengeospatial.org/standards/sfs#downloads) |

Data Types

# Get Started with BigQuery and PopSQL in 5 Minutes

Grab your ⏱ and see if you can beat our 5 min record:

1. Log into your [existing Google Cloud account](https://console.cloud.google.com/home/dashboard) or create a [Google Cloud account for free](https://cloud.google.com/). Just be sure to use the Google Account you'll also use to create a PopSQL account.

2. Create a project within your Google Cloud account. Name it whatever you'd like.

  ![create test project bigquery google cloud console](//images.ctfassets.net/iv1sg9nibjwl/582GaqdASEJVe6WOC55UCX/e1d5c0a0bbc46e63691f54e9882092cf/create_test_project_bigquery_google_cloud_console.png)

3. [Download PopSQL](https://get.popsql.com/).

  > _You can technically use the Google BigQuery web UI, but PopSQL is a modern SQL editor built for teams that works on every platform, for all major databases._

4. Create a [new database connection in PopSQL for BigQuery.](https://popsql.com/docs/getting-started/connecting-to-bigquery/)

    * We recommend connecting via OAuth.
    * Use the same Google Account you used to make your Google Cloud Account.
    * Enter the `Project Name` you created in step 2 as your `BigQuery Project`

5. Want to start querying right away? In PopSQL you can reference any of BigQuery's [surprisingly cool free, public sample datasets](https://popsql.com/learn-sql/bigquery/accessing-bigquery-public-datasets/) 😎.

Get Started with BigQuery and PopSQL in 5 Minutes

# How to Calculate Percentiles in BigQuery using Quantiles

You can calculate percentiles in BigQuery using the `approx_quantiles` function in Standard SQL. To get percentiles, simply ask for 100 quantiles.

```sql
select
  percentiles[offset(10)] as p10,
  percentiles[offset(25)] as p25,
  percentiles[offset(50)] as p50,
  percentiles[offset(75)] as p75,
  percentiles[offset(90)] as p90,
from (
  select approx_quantiles(char_length(text), 100) percentiles
  from `bigquery-public-data.hacker_news.full`
);
```

We're using a [BigQuery public dataset](https://popsql.com/learn-sql/bigquery/accessing-bigquery-public-datasets/) on Hacker News in our example above, so you can follow along. To make this analysis real, we bucketed Hacker News posts into percentiles based on their text length.

As of this writing, here's the breakdown of Hacker News text length by percentile:

```sql
| p10 | p25 | p50 | p75 | p90 |
|-----|-----|-----|-----|-----|
| 64  | 129 | 256 | 486 | 856 |
```

In other words if your next Hacker News post is going to be more than 856 characters, you statistically have a lot (_too much?_) to say 😶.

Since the 50th percentile is the median, this is a helpful way to calculate median in BigQuery.

How to Calculate Percentiles

# How to Write a Common Table Expression in BigQuery

Common table expressions (CTEs) help you break up complex queries in BigQuery. Here's a simple query to illustrate how to write a CTE:

```sql
with beta_users as (
  select *
  from users
  where beta is true
)

select events.*
from events
inner join beta_users on beta_users.id = events.user_id;
```

You can see a real example of using CTEs to [generate a time series to avoid gaps in data](https://popsql.com/learn-sql/bigquery/how-to-generate-series-to-avoid-gaps-in-data-in-bigquery).

How to Write a Common Table Expression

# How to Round Timestamps in BigQuery

Rounding/truncating timestamps, datetimes, etc is helpful when you're [grouping by time](https://popsql.com/learn-sql/bigquery/how-to-group-by-time-in-bigquery/). In BigQuery, the function you need varies depending on the data type of your schema:

## Timestamp

```sql
select timestamp_trunc(current_timestamp, HOUR);
```

## Datetime

```sql
select datetime_trunc(current_datetime, HOUR);
```

## Date

```sql
select date_trunc(current_date, DAY);
```

## Time

```sql
select time_trunc(current_time, HOUR);
```

Depending on the data type, different time units are accepted for each truncating function:

|   | **Timestamp** | **Datetime** | **Date** | **Time** |
| --- | --- | --- | --- | --- |
| microsecond | ✅ | ✅ |   | ✅ |
| millisecond | ✅ | ✅ |   | ✅ |
| second | ✅ | ✅ |   | ✅ |
| minute | ✅ | ✅ |   | ✅ |
| hour | ✅ | ✅ |   | ✅ |
| day | ✅ | ✅ | ✅ |   |
| week | ✅ | ✅ | ✅ |   |
| month | ✅ | ✅ | ✅ |   |
| quarter | ✅ | ✅ | ✅ |   |
| year | ✅ | ✅ | ✅ |   |

How to Round Timestamps

# How to Group by Time in BigQuery

If you want to group by minute, hour, day, or week, don't just group by your timestamp column. You'd get one group per second, which is probably not what you want. Instead, first "truncate" your timestamp to the granularity you want, like minute, hour, day, week, etc.

The BigQuery function you need is `timestamp_trunc`, `datetime_trunc`, `date_trunc`, or `time_trunc` depending on the data type of the schema.

## Timestamp

```sql
select
  timestamp_trunc('minute', created_at), -- or hour, day, week, month, year
  count(1)
from users
group by 1
```

## Datetime

```sql
select
  datetime_trunc('minute', created_at), -- or hour, day, week, month, year
  count(1)
from users
group by 1
```

## Date

```sql
select
  date_trunc('week', created_at), -- or month, year
  count(1)
from users
group by 1
```

## Time

```sql
select
  time_trunc('minute', created_at), -- or second, hour
  count(1)
from users
group by 1
```

Note: each function accepts a slightly different list of `date_part`s (second, hour, minute, etc). Check out the [how to round timestamps in BigQuery guide](https://popsql.com/learn-sql/bigquery/how-to-round-timestamps-in-bigquery/) for a list.

If you don't have new users every minute, you'll have gaps in your data. To have one row per minute, even when there's no data, you'll want to use [generate\_series](https://popsql.com/learn-sql/bigquery/how-to-generate-series-to-avoid-gaps-in-data-in-bigquery/).

How to Group by Time

# How to Query Date and Time in BigQuery

BigQuery has [four date and time data types](https://popsql.com/learn-sql/bigquery/date-and-time-data-types-in-bigquery/). Each data type its own associated functions and accepted parameters.

## Get the date and/or time right now:

```sql
select current_timestamp; -- date and time, with timezone
select current_datetime; -- date and time, without timezone
select current_date; -- date
select current_time; -- time
```

## Find rows between two absolute timestamps:

```sql
select *
from table
where created_at >= timestamp1
and created_at < timestamp2
```

The syntax above works for `datetime`, `date`, and `time`.

## Find rows created within the last week:

```sql
select *
from table
where timestamp > timestamp_sub(current_timestamp, INTERVAL 7 DAY);
```

*   Only integers are accepted, i.e. you can't write `INTERVAL 1.5 DAY`.
*   Keep the time unit singular. It's `INTERVAL 7 DAY`, not `INTERVAL 7 DAYS`.
*   Annoyingly, `DAY` is largest unit accepted for `timestamp_sub()`
*   Use `datetime_sub()`, `date_sub()`, or `time_sub` when working with other data types.

## Find rows created between one and two weeks ago:

```sql
select *
from table
where timestamp > timestamp_sub(current_timestamp, INTERVAL 14 DAY)
and timestamp_sub(current_timestamp, INTERVAL 7 DAY);
```

## Extracting part of a timestamp:

```sql
select extract(minute from timestamp); --or datetime, date, time
```

## Get the day of the week from a timestamp:

```sql
--returns 1-7, where 1 is Sunday and 7 is Saturday.
select extract(dayofweek from timestamp);

-- returns a string like Monday, Tuesday, etc
select format_timestamp('%A',timestamp); --
```

## Converting a timestamp to a unix timestamp (integer seconds):

```sql
select unix_seconds(current_timestamp);
select unix_seconds(timestamp('2020-05-09 14:53:21'));
```

## Calculate the difference between two timestamps:

```sql
 -- timestamp
select timestamp_diff(timestamp2, timestamp1, SECOND);

-- datetime
select datetime_diff(datetime2, datetime1, SECOND);

-- date
select date_diff(date2, date1, DAY);

-- time
select time_diff(time2, time1, SECOND);
```

Here's a handy table of the time units accepted as arguments in BigQuery date and time functions (e.g. `extract`, `timestamp_sub`, or `date_diff`):

|   | **Timestamp** | **Datetime** | **Date** | **Time** |
| --- | --- | --- | --- | --- |
| microsecond | ✅ | ✅ |   | ✅ |
| millisecond | ✅ | ✅ |   | ✅ |
| second | ✅ | ✅ |   | ✅ |
| minute | ✅ | ✅ |   | ✅ |
| hour | ✅ | ✅ |   | ✅ |
| day | ✅ | ✅ | ✅ |   |
| week |   | ✅ | ✅ |   |
| month |   | ✅ | ✅ |   |
| quarter |   | ✅ | ✅ |   |
| year |   | ✅ | ✅ |   |

How to Query Date and Time

# How to Drop a Column in BigQuery

In most databases, it's easy and common to drop a column. In BigQuery data warehouse? Not so much.

You can't use a command like `ALTER TABLE TABLE_NAME DROP COLUMN_NAME` in BigQuery, nor is there a flow in the BigQuery web UI.

Before you get all 😡 at BigQuery, it's important to realize _why_ this common command is missing.

According to [Elliott Brossard](https://stackoverflow.com/questions/45816344/why-bigquery-doesnt-have-an-option-to-remove-column) who helped build BigQuery:

> "Dropping a column would mean removing the data from all of the [Capacitor](https://cloud.google.com/blog/products/gcp/inside-capacitor-bigquerys-next-generation-columnar-storage-format) files that make up the table, which is an expensive operation. If BigQuery were simply to remove metadata related to the column, you would be charged storage for a phantom column that you can't actually query, which wouldn't be ideal. When you add a column, conversely, BigQuery treats the missing column in past files as having all NULL values, which doesn't require modifying them."

## Workaround to Dropping a Column in BigQuery

Select from the original table, but use the `except` command to exclude the column you don't want. You can then save the results as a new BigQuery table. Here's an example:

```sql
select * except(author, ranking) from `bigquery-public-data.hacker_news.comments` limit 10;
```

Then click `Save Results`:

![bigquery ui drop column](//images.ctfassets.net/iv1sg9nibjwl/6plsWC6rG1UyRLsxedNEDh/0a9173041d4234dc5a489248c6453c32/bigquery_ui_drop_column.png)

And choose `BigQuery table` as your desired option:

![bigquery ui save new table](//images.ctfassets.net/iv1sg9nibjwl/6qF6yOTA2uevvyy8yKpyhR/5c7949a72545845000f8bb7e5b8aecf6/bigquery_ui_save_new_table.png)

It's not a perfect solution, but it may work for you. Use with care.

How to Drop a Column

# How to Add a Column in BigQuery

BigQuery does not support `ALTER TABLE` (along with other common DDL statements).

In other words, you can't add a column via a SQL command in BigQuery. Fortunately there are three alternative methods.

## Option 1. Adding a Column in the BigQuery Web UI

In the [BigQuery Web UI](https://console.cloud.google.com/bigquery):

*   select the table you wish to alter,
*   click `Edit Schema`,

![add column bigquery ui](//images.ctfassets.net/iv1sg9nibjwl/6LwklOaKa8Gg6WGWgSoXHh/b0f46181e7d8ec62dc0c2282c2193263/add_column_bigquery_ui.png)

*   click the `+ Add Field` button,

![save new column bigquery ui](//images.ctfassets.net/iv1sg9nibjwl/FvwhK22xJVMENPBspd3Y2/c25b46fd936b481c277b5cff393a43d4/save_new_column_bigquery_ui.png)

*   enter the desired `name`, `type`, and `mode` (e.g. nullable, required, etc),
*   and click `Save`.

## Option 2. Adding a Column in the BigQuery Command Line tool

In the command line, enter:

```sql
bq update project_id:dataset.table schema
```

`schema` refers to the path to the JSON schema file on your local machine. The JSON schema file should look like:

```sql
[
  {
    "mode": "REQUIRED",
    "name": "column1",
    "type": "STRING"
  },
  {
    "mode": "REQUIRED",
    "name": "column2",
    "type": "FLOAT"
  },
  {
    "mode": "REPEATED",
    "name": "column3",
    "type": "STRING"
  }
]
```

Here's an example command with the path specified:

```sql
bq update myproject:mydataset.mytable /tmp/myschema.json
```

## Option 3. Adding a Column by calling the `tables.patch` API method

If you use the `tables.patch` API method, you have to supply the full schema (aka _new and existing columns_) via the API call.

Say we want to add a `birthday` column to our `users` table. We already have columns for:

*   `first_name`, a nullable `string`
*   `last_name`, a nullable `string`
*   `has_tried_popsql`, a nullable `boolean`
*   `number_of_friends`, a required `integer` 😉

We supply the `mode`, `name`, and `type` for all existing columns, plus our new `birthday` column. It should look like this:

```sql
{
  "schema": {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "first_name",
        "type": "STRING"
      },
      {
        "mode": "NULLABLE",
        "name": "last_name",
        "type": "STRING"
      },
      {
        "mode": "NULLABLE",
        "name": "has_tried_popsql",
        "type": "BOOL"
      },
      {
        "mode": "REQUIRED",
        "name": "number_of_friends",
        "type": "INT64"
      },
      {
        "mode": "NULLABLE",
        "name": "birthday",
        "type": "DATE"
      }
    ]
  }
}
```

After you submit via the API patch call, the new `birthday` column will be added 🎂🥳.

> Note: You _can_ also add a column in BigQuery using the `tables.update` command. However `tables.patch` is almost always preferred since `tables.patch` only updates the added/modified fields. The `tables.update` method replaces the entire table resource.

BigQuery documentation lists [additional methods](https://cloud.google.com/bigquery/docs/managing-table-schemas#adding_columns_to_a_tables_schema_definition) for adding a column (with Go, Python, etc).

How to Add a Column

# BigQuery: Duplicate a Table

Sometimes it's useful to copy a table.

You can't directly copy tables via a SQL command in BigQuery, so we'll walk through alternative methods.

In the [BigQuery UI](https://console.cloud.google.com/bigquery), select the table you wish to copy, then push the `Copy Table` button. Enter the desired new table name.

![copy table bigquery ui](//images.ctfassets.net/iv1sg9nibjwl/PSV42U17UThlDKaiReCS3/bb11a55e2485138d4af59916829278b8/copy_table_bigquery_ui.png)

Or you can copy a table in the BigQuery command line tool:

```sql
bq cp mydataset.mytable mydataset2.mytable2
```

BigQuery documentation lists [additional methods](https://cloud.google.com/bigquery/docs/managing-tables#copy-table) for copying a table (via API, with Python, PHP, etc).

> 🤓 Fun fact: copying a table is a key step in ["renaming" a table in BigQuery](https://popsql.com/learn-sql/bigquery/how-to-rename-a-table-in-bigquery/), as there is no method for direct renaming in BigQuery.

How to Duplicate a Table

# How to Rename a Table in BigQuery

There is no option to rename a table in BigQuery 😬.

You can copy a table in BigQuery, though. This doesn't incur any additional charges other than the additional cost of storage. Once copied, you can delete the original table so you only get charged for the storage once.

## Methods to copy a table

In the [BigQuery UI](https://console.cloud.google.com/bigquery), select the table you wish to copy, then push the `Copy Table` button. Enter the desired new table name.

![copy table bigquery ui](//images.ctfassets.net/iv1sg9nibjwl/PSV42U17UThlDKaiReCS3/bb11a55e2485138d4af59916829278b8/copy_table_bigquery_ui.png)

Or you can copy a table in the BigQuery command line tool:

```sql
bq cp mydataset.mytable mydataset2.mytable2
```

BigQuery documentation lists [additional methods](https://cloud.google.com/bigquery/docs/managing-tables#copy-table) for copying a table (via API, with Python, PHP, etc). There does not appear to be a way to copy tables via a SQL command in BigQuery.

How to Rename a Table

# How to Drop a Table in BigQuery

```sql
drop table funky_users;
```

Farewell, `funky_users` table. We hardly knew ye. ✋🎤

How to Drop a Table

# How to Create a Table in BigQuery

Here's an example of creating a users table in BigQuery:

```sql
create table `project-id.dataset.funky_users` (
  first_name STRING,
  last_name STRING,
  has_tried_popsql BOOL,
  number_of_friends INT64 not null -- chance to specify not null constraints
);
```

Table names must:

*   contain only letters (a-z, A-Z), numbers (0-9), or underscores (\_),
*   start with a letter or underscore,
*   be shorter than 1024 characters,
*   and not use table decorators (e.g. snapshot or range decorators like `[new_table@-3600000]`)

🧐 **Points of clarification:**

*   BigQuery uses underscores for table names (e.g. `funky_users`, not `funky-users`). - _Project IDs_ in BigQuery, however, accept hyphens (-) but not underscores (\_). 🤷
*   Note the backticks around the project, database, and table names. - Be sure to use a period instead of a colon between the project, database, and table names too.

Need a refresher on BigQuery data types? We've got [just the thing](https://popsql.com/learn-sql/bigquery/bigquery-data-types/).

How to Create a Table

# Accessing BigQuery Public Datasets

BigQuery offers a number of legitimately interesting public datasets. It's a snap to explore them in PopSQL.

## Choose a Public Dataset

Once you've logged into your [Google Cloud account](https://bigquery.cloud.google.com/), you'll see a number of datasets under the `bigquery-public-data` header:

![bigquery sample datasets](//images.ctfassets.net/iv1sg9nibjwl/2ZUwoxzLi75zZHYETsoxTX/e2b329edb7fe92cd9b814aa0e85fb336/bigquery_sample_datasets.png)

## Connect to BigQuery in PopSQL

If you already have a BigQuery connection in PopSQL, simply select it from the Database Connections dropdown:

![manage connections](//images.ctfassets.net/iv1sg9nibjwl/60M8nciz1AlZnBkx9MnWOy/5c3f909384f19f51effc78e2abf7406b/manage_connections.png)

If you are connecting to BigQuery for the first time in PopSQL, we have a [full guide.](https://popsql.com/docs/getting-started/connecting-to-bigquery/)

If you are _totally new to BigQuery_, read [this guide](https://popsql.com/learn-sql/bigquery/get-started-with-bigquery-and-popsql-in-5-minutes/) first.

## Explore Your BigQuery Public Dataset in PopSQL

Once connected, open a new query in PopSQL and you can query your public dataset:

```sql
SELECT *
FROM `bigquery-public-data.hacker_news.comments`
LIMIT 10;
```

Note the backticks around the project, database, and table name. Be sure to use a period instead of a colon between the `bigquery-public-data` and `hacker_news`.

Accessing BigQuery Public Datasets

# Date and Time Data Types in BigQuery

BigQuery supports 4 main date and time data types:

| **Name** | **Description** | **Canonical Format** | **Note** |
| --- | --- | --- | --- |
| DATE | Represents a logical calendar date, without time. | `YYYY-[M]M-[D]D` | Range from 0001-01-01 to 9999-12-31 |
| TIME | Represents a time, independent of a specific date. | `[H]H:[M]M:[S]S[.DDDDDD]` | Range from 00:00:00 to 23:59:59.999999 |
| DATETIME | Represents a year, month, day, hour, minute, second, and subsecond _without a timezone_. † | `YYYY-[M]M-[D]D[(|T)[H]H:[M]M:[S]S[.DDDDDD]]` | Range from 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999 |
| TIMESTAMP | Represents an absolute point in time, with microsecond precision _with a timezone_. | `YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]][time zone]` | Range from 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999 |

† `DATETIME` is seldom used, as it's rare to wish to omit the timezone.

**How to read Canonical Format:**

*   `YYYY`: Four-digit year
*   `[M]M`: One or two digit month
*   `[D]D`: One or two digit day
*   `( |T)`: A space or a `T` separator
*   `[H]H`: One or two digit hour (valid values from 00 to 23)
*   `[M]M`: One or two digit minutes (valid values from 00 to 59)
*   `[S]S`: One or two digit seconds (valid values from 00 to 59)
*   `[.DDDDDD]`: Up to six fractional digits (microsecond precision)
*   `[time zone]`: String representing the time zone, with two canonical formats:
    *   [Time zone name](https://cloud.google.com/dataprep/docs/html/Supported-Time-Zone-Values_66194188) per the tz database
    *   Offset from Coordinated Universal Time (UTC), or the letter Z for UTC

Be sure to check out our [Date and Time Function cheat sheet](https://popsql.com/blog/sql-date-and-time-cheat-sheet/) to see how querying date and times differs in BigQuery and across databases.

Date and Time Data Types

# How to Measure Cohort Retention in BigQuery

![guest post abdi mohamud](//images.ctfassets.net/iv1sg9nibjwl/6ZdQkjvNc7aNdSFn85bIif/1bacc459e9c96cf40e2b4335c189d609/guest_post_abdi_mohamud.png)

With some simple visitor data, you can tell how you're retaining your customers on a month by month basis. **That's super valuable!**

You can follow along at home, we'll use one of BigQuery's public datasets (details below). It's real visitor data from the Google Store in 2017.

If you want to use your own data, the queries in this guide can be converted to work with your favorite SQL dialect. All your data needs is a timestamp of when your users visit your site and a visitor ID to track them.

## What This Analysis Will Produce

Here's a graph showing what percent of users came back to the Google Store after visiting for the first time. It's grouped by what month they first visited and shows what percent remain after subsequent months.

![google store visitors retained after x months in 2017](//images.ctfassets.net/iv1sg9nibjwl/2LAxadKkPT16bBucdaDwIJ/f47f59c19f825101aec63c4140e52c1f/google_store_visitors_retained_after_x_months_in_2017.png)

And here are the cohort sizes, for the more detail oriented folks.

## Cohort Retention Definition

What's a cohort? Essentially a group of people.

How are we grouping our customers? I've decided to group them by the month that they visited the site.

How do we know if we retained a user? We can know if they visit the site again in a subsequent month. For example if they visited in June then again in July, they were retained. But the months are independent — They could visit in June, and not show up again until August. We wouldn't count July, but would count August.

## Cohort Retention Framework

1.  Group all our users into cohorts based on the month they first came to the store and count the total.
2.  Count how many users came back each month, starting from their cohort month.
3.  Calculate the percentage of cohort remaining after each month

## BigQuery Data

_Feel free to skip this section if you don't want to use the example data from BigQuery._

BigQuery has a large number of public datasets and Google Store Analytics from 2017 is one of them.

You'll need to make a Google Cloud Platform account, create a project, and [add the public dataset to your project.](https://popsql.com/learn-sql/bigquery/accessing-bigquery-public-datasets) Next, since we can't edit the public dataset, you'll need to [copy the data from the public dataset to your project.](https://cloud.google.com/bigquery/docs/copying-datasets)

## SQL

In BigQuery, I named my project "Cohort Analysis" and named my duplicate table "Google Store Analytics" so you'll see the prefix `cohort-analysis-284422.Google_Store_Analytics` often in this article. To follow along, replace this prefix with the name of your project and table in BigQuery.

For each part, I'll also be creating views to make the queries reusable and easier to work with!

### Part One: Find Our Cohorts

First we find out the first time each customer visited the site this year and create a view so that we can reference this query later.

```sql
create view `cohort-analysis-284422.Google_Store_Analytics.cohort` as
select
  extract(date from timestamp_seconds(min(visitStartTime))) as cohort_month,
  fullVisitorId
from `cohort-analysis-284422.Google_Store_Analytics.ga_sessions_2017*`
group by fullVisitorId
```

```sql
Example Results:
| cohort_month | fullVisitorId       |
|--------------|---------------------|
| 2017-05-12   | 0206596068278667912 |
| 2017-05-12   | 3310123534725675314 |
| 2017-04-25   | 3153380067864919818 |
| 2017-06-23   | 1216639212736323098 |
```

We also find out the size of each cohort by counting the number of unique ids that show up for the first time in a month.

```sql
create view `cohort-analysis-284422.Google_Store_Analytics.cohort_size` as
select
  extract(month from cohort_month) as cohort_month,
  count(1) as num_users
from `cohort-analysis-284422.Google_Store_Analytics.cohort`
group by cohort_month
```

```sql
Example Results:
| cohort_month | num_users |
|--------------|-----------|
| 1            | 52613     |
| 2            | 49133     |
| 3            | 55346     |
| 4            | 52873     |
```

### Part Two: Measure Activity After Cohort Month

Next, we find what months there's been activity after their cohort month. For example If their cohort was January and they came to the site on February, then it notes activity on month one — one month after their cohort month.

```sql
create view `cohort-analysis-284422.Google_Store_Analytics.user_activities` as
select
  date_diff(
    extract(date from timestamp_seconds(GA.visitStartTime)),
    C.cohort_month,
    MONTH
  ) as month_number,
  GA.fullVisitorId
from `cohort-analysis-284422.Google_Store_Analytics.ga_sessions_2017*` GA
left join `cohort-analysis-284422.Google_Store_Analytics.cohort` C on GA.fullVisitorId = C.fullVisitorId
group by 2, 1
```

```sql
Example Results:
| month_number | fullVisitorId       |
|--------------|---------------------|
| 0            | 7868243379843705173 |
| 0            | 5418231530547311489 |
| 0            | 4704421551407457903 |
```

Now we count how many users were retained in each month after their cohort month and name it retention\_table.

```sql
create view `cohort-analysis-284422.Google_Store_Analytics.retention_table` as
select
	extract(MONTH from C.cohort_month) as cohort_month,
	A.month_number,
	count(1) as num_users
from `cohort-analysis-284422.Google_Store_Analytics.user_activities` A
left join `cohort-analysis-284422.Google_Store_Analytics.cohort` C on A.fullVisitorId = C.fullVisitorId
group by 1, 2
```

```sql
Example Results:
| cohort_month | month_number | num_users |
|--------------|--------------|-----------|
| 1            | 0            | 52613     |
| 1            | 1            | 2198      |
| 1            | 2            | 908       |
```

### Final: Divide the Number of Remaining Users by the Cohort Size

Finally we get the cohort percent retention after each month.

```sql
create view `cohort-analysis-284422.Google_Store_Analytics.final` as
select
  R.cohort_month,
  S.num_users as total_users,
  R.month_number,
  cast(R.num_users as float64) * 100 / S.num_users as percentage
from `cohort-analysis-284422.Google_Store_Analytics.retention_table` R
left join `cohort-analysis-284422.Google_Store_Analytics.cohort_size` S on R.cohort_month = S.cohort_month
where R.cohort_month is not null
order by 1, 3
```

You should end up with a final result that looks like this.

![cohort analysis final](//images.ctfassets.net/iv1sg9nibjwl/IC0qw8h4JmU0xG6gLH4Rc/22ada048dfb45151b19814ea0ec5e07b/cohort_analysis_final.png)

With this data I also made this bar graph to show the drop off in users each month.

![google store visitors retained after x months in 2017](//images.ctfassets.net/iv1sg9nibjwl/2LAxadKkPT16bBucdaDwIJ/f47f59c19f825101aec63c4140e52c1f/google_store_visitors_retained_after_x_months_in_2017.png)

## Conclusion

Again, figuring out how you're retaining your users is super valuable! You can see in this chart that the January cohort is consistently more retained than other cohorts. This could be for various reasons and knowing why could help your business capitalize on this fact.

## Future Use and Modifications

Anyone who has used Google Analytics on their site knows that this cohort analysis is immediately available to them for free. But this manual method is much more flexible!

Imagine that you also want to find retention numbers for customers who have paid for a product? What about customers who have used your latest feature or were referred by a friend? There are tons of insights you can get from visitor data which makes this data super powerful for guiding your business.

Thank you for reading and I'll see you in the next article! ✌🏾

> Thanks again to our guest author, [Abdi Mohamud](https://abdisalan.com/)!

How to Measure Cohort Retention

# How to Generate Series to Avoid Gaps in Data in BigQuery

If you're [grouping by time in BigQuery](https://popsql.com/learn-sql/bigquery/how-to-group-by-time-in-bigquery/) and don't want gaps in your data, you need to generate a series of time values. In the example below, we're generating a series of timestamps that increment by the hour. In these examples, the data type of the schema is a `timestamp`.

First we generate our series with no gaps:

```sql
select *
from UNNEST(GENERATE_TIMESTAMP_ARRAY('2015-10-01', '2015-10-03', INTERVAL 1 HOUR)) AS hour
```

```sql
| hour                    |
|-------------------------|
| 2015-10-01 00:00:00 UTC |
| 2015-10-01 01:00:00 UTC |
| 2015-10-01 02:00:00 UTC |
| 2015-10-01 03:00:00 UTC |
| 2015-10-01 04:00:00 UTC |
| 2015-10-01 05:00:00 UTC |
...
```

Note: BigQuery allows you to generate ~1 million intervals with this method. If your schema is `datetime`, simply use `GENERATE_DATETIME_ARRAY`. For `date`, use `GENERATE_DATE_ARRAY`.

Next use a common table expression to create a table that contains the gapless series we created above. We'll call it `hours` 🧐. We can now `left join` any table with "gappy" time series data to our gapless `hours` table:

```sql
with hours as (
  select *
  from UNNEST(GENERATE_TIMESTAMP_ARRAY('2015-10-01', '2015-10-03', INTERVAL 1 HOUR)) AS hour
)

select
  hours.hour,
  COUNT(id)
from hours
left join `bigquery-public-data.hacker_news.comments` on timestamp_trunc(`bigquery-public-data.hacker_news.comments`.time_ts,hour) = hours.hour
group by 1
order by 1;
```

We're using a BigQuery public dataset on Hacker News in our example above, so you can follow along. It only takes [5 minutes to get started with BigQuery in PopSQL](https://popsql.com/learn-sql/bigquery/get-started-with-bigquery-and-popsql-in-5-minutes/).

Not only does PopSQL allow you to share queries with your teammates, but it autogenerates charts to save you even more time. Here's the results from the query above:

![bigquery graph example](//images.ctfassets.net/iv1sg9nibjwl/7qo4jaP6v52fpxgi0wphZZ/aef5c5bb54d24c535c46acd52d41d922/bigquery_graph_example.png)

Isn't exploring BigQuery exhilarating? 🎢

How to Generate Series to Avoid Gaps in Data

# How to Use countif to Have Multiple Counts in BigQuery

Say you want to do multiple counts on a table in the same query. In BigQuery you can use the `countif` function. This approach is very similar to the `filter` [command in Postgres](https://popsql.com/learn-sql/postgresql/how-to-use-filter-to-have-multiple-counts-in-postgresql/).

```sql
select
  count(1), -- all posts
  countif(type = 'story'), -- all stories
  countif(type = 'comment'), -- all comments on existing story
  countif(type = 'poll'), -- all polls
  countif(type = 'job') -- all jobs posted
from `bigquery-public-data.hacker_news.full`
```

In case you want to try this at home, we're using a [BigQuery public dataset](https://popsql.com/learn-sql/bigquery/accessing-bigquery-public-datasets/) on Hacker News in our example above.

The results as of this writing:

![hacker news posts by type](//images.ctfassets.net/iv1sg9nibjwl/6lQqn8RMTgENqEyYLjYNQM/7fdb76bf13b1c43c4da8b6d222cb9140/hacker_news_posts_by_type.png)

You can [get started with BigQuery in PopSQL](https://popsql.com/learn-sql/bigquery/get-started-with-bigquery-and-popsql-in-5-minutes/) in less than 5 minutes. Exploring BigQuery is a joy in PopSQL, a modern editor built for teams that supports all major databases and operating systems. 😍

BigQuery

Ready for a modern SQL editor?