Daily KPI snapshot from Postgres to Google Sheets
Every morning at 6am ET, a fresh row of yesterday's signups, active users, MRR, and support tickets lands in your stakeholder Sheet.
Integrations
PostgreSQL
Google Sheets
Type
Categories
- Operations
- Product
Build a deterministic daily KPI snapshot that writes one row per day from my PostgreSQL database to a Google Sheet for stakeholder dashboards. This is a code workflow: the SQL is fixed, the column order is fixed, and the destination is fixed. No reasoning needed.
Trigger: cron, every day at 6:00 AM US/Eastern.
Step 1: PostgreSQL Custom Query. Run a single SQL statement that aggregates my core KPIs for the prior calendar day (in US/Eastern) and returns exactly one row with these columns in this exact order:
1) snapshot_date (yesterday's date, ISO YYYY-MM-DD). 2) new_signups (count of users created yesterday). 3) active_users (distinct users with activity yesterday). 4) mrr_cents (monthly recurring revenue at end of yesterday, in cents). 5) new_paid_conversions (free-to-paid upgrades yesterday). 6) churned_accounts (paid accounts that cancelled yesterday). 7) support_tickets_opened (new tickets created yesterday).
I will fill in the exact SQL and table names when I configure the workflow. Use parameterized queries for any dynamic values and double-quote case-sensitive identifiers. Treat the prior day in US/Eastern, not UTC.
Step 2: Google Sheets Append Values. Take the single row returned by Postgres and append it as one new row to a tab named 'Daily KPIs' in a Google Sheet I will specify. Columns must land in the same order as the SQL output. Use USER_ENTERED so the date column parses as a real date and the numeric columns parse as numbers.
Failure behavior: if the SQL returns zero rows or more than one row, log the discrepancy and stop without appending. If the append fails, log the row payload so it can be replayed manually. Do not retry blindly.
Outcome: stakeholders open the Sheet and get a clean, growing time series they can chart, filter, or pivot however they want, without ever touching the database or asking an analyst.
What it does
- Run one SQL query at 6am every day that totals yesterday's core KPIs from your Postgres database.
- Append a single new row to a 'Daily KPIs' tab in your Google Sheet, with the date and each metric in its own column.
- Give stakeholders a clean, growing time series they can chart, filter, or pivot however they want.
- No copy-paste, no late-night dashboard refreshes, no human in the loop.
What you’ll need
- A PostgreSQL database you can read from, with the tables that hold your signups, billing, and support data.
- A Google account and a Google Sheet with a tab named 'Daily KPIs' ready to receive the data.
- The exact columns and definitions for each KPI you want logged (new signups, active users, MRR, conversions, churn, tickets, anything else).
How to customize it
- Change the schedule, like a different time zone, weekly instead of daily, or hourly during a launch week.
- Swap in your own KPIs by editing the SQL. Add ARR, retention, refunds, gross margin, anything you can query.
- Point at a different tab or a different Sheet for a separate audience, like an exec view and a board view.
Use cases
- Data Sync
- Daily Digests
