How to send a weekly report from your PostgreSQL database

• A PostgreSQL database on the public internet, with a user for Bizomate that can only read. If your server's firewall lets in listed addresses only, add Bizomate's. See Connect a PostgreSQL database.
• A Google Workspace connection, and a spreadsheet with a Weekly sheet headed Week starting, Orders and Revenue in its first row.
• Nothing for the email. Bizomate's own Send an email step needs no connection.
• PostgreSQL, Google Sheets and email work on every plan.

A user that can only read. A report never needs to change data, so give it a user that cannot. For example:

create user bizomate_reports with password '…';
grant connect on database shop to bizomate_reports;
grant usage on schema public to bizomate_reports;
grant select on orders to bizomate_reports;

Then a mistake in the query can never change or delete a row.

1. Run it every Monday at 09:00

Create a workflow and choose Schedule. Under Run this workflow, choose Every week, on Monday, at 09:00. Next runs shows the next three Mondays. Times are in the time zone of whoever publishes the workflow. See Running a workflow on a schedule.

2. Ask the database for last week

Add PostgreSQL · Query and choose your connection. In Query:

select to_char(date_trunc('week', now()) - interval '7 days', 'YYYY-MM-DD') as week_starting,
       count(*) as orders,
       coalesce(sum(total), 0) as revenue
from orders
where created_at >= date_trunc('week', now()) - interval '7 days'
  and created_at <  date_trunc('week', now())

PostgreSQL's weeks start on Monday, so this is last Monday to Sunday, by the database's clock. Your table and column names will differ; the shape stays the same. Select Run this step and check Output: one row, in rows. See Query a PostgreSQL database.

When a query needs a value from an earlier step, name it @like_this in the SQL and give it under Parameters — never glue text into the query.

3. Add the week to the sheet

Add Google Sheets · Add a row. Under Spreadsheet, select Choose and pick the spreadsheet in Google's picker — Bizomate sees only the files you pick. Choose the Weekly sheet, then fill in its columns:

• Week starting: {{ steps.query.rows.0.week_starting }}
• Orders: {{ steps.query.rows.0.orders }}
• Revenue: {{ steps.query.rows.0.revenue }}

Values are entered as if typed, so numbers stay numbers and a chart over the sheet keeps working. See Add a row to Google Sheets.

4. Email the totals

Add Send an email. In To, up to ten addresses — sam@acme.example, asha@acme.example.

• Subject: Last week: {{ steps.query.rows.0.orders }} orders
• Message:

Week starting {{ steps.query.rows.0.week_starting }}
Orders: {{ steps.query.rows.0.orders }}
Revenue: US${{ steps.query.rows.0.revenue }}

Every week so far is in the Weekly sheet.

The email is plain text, from Bizomate's own address. See Send an email from Bizomate.

On Microsoft SQL ◇ (paid plans), the Weekly report from SQL template installs the schedule, the query and the sheet as a draft; it publishes on a paid plan. On PostgreSQL, build the four steps above, which work on every plan.

Select Test run. A test starts at once, rather than waiting for Monday, so the query runs, the row is added and the email is really sent. Delete the test row from the sheet, then Publish. Test runs never count towards your monthly runs.

One run a week — about four or five a month — however many rows the query reads. See What counts as a run.

Each Monday leaves a run with the query's answer, the row written and the email sent. If a step fails, the run says which one and why. The usual causes:

• The database refused the sign-in, or could not be reached in 15 seconds. Check the firewall lets Bizomate's addresses in on the port.
• The database refused the query. A table or column name is wrong. The reason is in PostgreSQL's own words.
• Revenue is no longer a column in the sheet's first row. A heading was renamed. Select Refresh columns and map it again.