What is the BigQuery connector?

The BigQuery connector lets you query your Google BigQuery data warehouse using plain English. Better Analyst translates your questions into optimized SQL, runs the query on your BigQuery project, and returns the results — all without you writing a single line of SQL.

What permissions does Better Analyst need?

Better Analyst requests read-only BigQuery authorization by default. We use OAuth 2.0 to authenticate securely through Google's official API. Read and write is requested only when you explicitly enable a Data Flow destination, and writes stay restricted to the approved dataset.

How do I connect BigQuery to Better Analyst?

Click "Connect BigQuery," sign in with your Google account, and select the project and dataset you want to analyze. The entire process takes less than a minute. No service account keys or manual configuration needed.

What data can I query with Better Analyst?

You can query any table or view in your BigQuery datasets that your Google account has access to. This includes standard tables, partitioned tables, and views. Ask questions about any data — Better Analyst will generate the appropriate SQL automatically.

Is my BigQuery data secure?

Yes. OAuth tokens and service-account credentials are encrypted, and results are encrypted in transit. Read only is the default. Read and write requires explicit authorization, a successful live probe, and Google IAM permission for the approved dataset.

Will Better Analyst affect my BigQuery costs?

Better Analyst runs standard BigQuery queries on your behalf, so standard BigQuery pricing applies. We optimize generated SQL to minimize data scanned. You can monitor query costs in your Google Cloud Console just like any other BigQuery usage.

Cohort Retention Analysis in BigQuery: Monthly Table

Build a monthly cohort retention table in BigQuery from an orders or events table: cohort by first month, retention by months since, and how to read it.

By David Bressler
Published November 1, 2026
Updated November 1, 2026

Summary

A cohort retention analysis groups customers by the month of their first purchase (or signup) and shows what share came back in each later month. In BigQuery it takes a first-activity CTE, a month-difference calculation and a pivot; in Better Analyst you ask for the table and check the SQL it shows you.

On this page

Who this report is for
What the report includes
How to build it in Better Analyst from Google BigQuery
Questions to ask
Sample output
Charts to use
Keep it up to date
Further reading
FAQ

Who this report is for

Ecommerce and subscription analysts, growth teams and founders with order or activity data in BigQuery who want to know whether newer customers retain better than older ones.

What the report includes

Metrics and dimensions in this report

Metric	Source field	Why it matters
Customer	orders.customer_id (example schema)	The unit being retained; use your customer or user key.
Activity date	orders.order_date (example schema)	Each customer's purchases, from which the first month and later active months are derived.
Cohort month	Calculated: DATE_TRUNC(MIN(order_date), MONTH) per customer	Groups customers who started in the same month.
Months since first order	Calculated: DATE_DIFF(order month, cohort month, MONTH)	Aligns every cohort on month 0, 1, 2 … for comparison.
Retention	Calculated: active customers in month N ÷ cohort size	The share of each cohort still buying N months later.

How to build it in Better Analyst from Google BigQuery

In Better Analyst, open Connectors, choose Add Connector, then BigQuery.
Connect with Google sign-in, or upload a service-account JSON key for an account with the BigQuery Data Viewer and BigQuery Job User roles.
Select the project and dataset, test the connection and save. Queries are read-only, and BigQuery's normal query pricing applies to what you scan.
Tell Better Analyst which table holds orders or activity and which columns are the customer ID and date, or add that to Data Context.
Ask for a monthly cohort retention table for the last 12 cohorts, with cohort sizes, as percentages.

Questions to ask

“Build a monthly cohort retention table from the orders table for customers who first ordered in the last 12 months.”
“Do customers acquired in Q3 retain better at month 3 than those from Q1?”
“Show retention curves for customers whose first order used a discount code versus those who didn't.”
“What share of revenue in month 6 comes from repeat customers in each cohort?”

Sample output

Sample data for illustration, not real account data.

Sample data: monthly cohort retention, % of cohort ordering again (illustrative)

Cohort	Customers	Month 1	Month 2	Month 3	Month 6
Jan 2026	1,840	22.4%	16.1%	13.8%	10.2%
Feb 2026	1,615	23.9%	17.0%	14.6%	11.0%
Mar 2026	2,210	19.7%	14.2%	12.1%	9.1%
Apr 2026	1,930	25.3%	18.4%	15.9%	n/a

Charts to use

Cohort retention heatmap (Heatmap table): Cohorts as rows and months since first order as columns, shaded by retention.
Retention curves (Line chart): One line per cohort, showing whether newer cohorts flatten at a higher level.

Keep it up to date

Refresh the cohort table monthly with a scheduled task, or have a Data Flow write it to a BigQuery table that a dashboard reads.

Frequently asked questions

Should retention count any order or only paid ones?

Define 'active' to match the question: any order for repeat purchase behaviour, a paid renewal for subscriptions, or a meaningful product event for apps. Keep the definition the same across cohorts.

Why does the newest cohort have empty cells?

It hasn't existed long enough. A cohort that started three months ago can only have months 1–3 filled; mark later cells n/a rather than 0%.

Is this the same as churn?

Related but different: retention by cohort shows the share of each starting group still active in month N, while churn is usually measured across your whole active base for one period.