Automatic reports and dashboards

Source

CRM, billing, media and ERP

Rule

Stated grain, business rules and additive columns

Outcome

One gold per dashboard, with load date

Activation

BI, portal and AI agent reading the same table

What it solves

The dashboard refreshes after every load, with nobody exporting CSVs.
The dashboard number matches the source system and the report it replaces.
Everyone sees the last update date and knows whether the number is stale.

Business questions it answers

Ask your agent these questions once the case is live.

How much have we sold this month by seller, and what is each one's conversion rate?

What is the cost per result for each channel over the last four weeks?

Is the pipeline created this quarter above or below last quarter?

Which branch is furthest from this month's target?

Does today's dashboard already include yesterday's numbers?

What you need

The sources connected to Nekt (CRM, billing, media, ERP)
The list of questions the dashboard answers and the current reference report
The BI tool or the portal destination

What you get

One gold table per dashboard, with the grain in the name and the rules applied
Additive columns so the BI tool recomputes rates in the chosen cut
A freshness card with the last load date of each source
A parity test against the old report or the source system

Where to activate

Looker Studio, Power BI or Metabase reading the gold, with rates as sum-over-sum calculated fields.

Your own portal

Reverse ETL to Postgres or Supabase, with the table as the app's load contract.

AI agent via MCP

Reads the same gold and answers with the dashboard number.

Ready to build?

The transformations are straightforward. The work is closing definitions with the dashboard users and proving parity with the current report before switching it off. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.

See detailed view

The problem

The report is exported from the CRM to a spreadsheet every Monday and the math lives in formulas and BI calculated fields. When someone changes a filter, the total does not add up. When ingestion stops, the dashboard keeps showing the old number with no warning. When the report moves to another tool, the number changes too and nobody knows which one is right.

How it is usually done, and the rule

Each row is a mistake that already happened in practice.

The common wayThe rule

A generic table mixing day, month and total rows.

One gold per dashboard, with the grain in the name

Each dashboard reads one gold table with a single grain, written in the name and in one sentence of the description ('one row per ad per day'). A table named monthly does not receive daily data. The update key is the grain itself: a key different from the grain silently duplicates rows.

Averaging the CTR column in Looker Studio.

Rates are recomputed, not summed

CTR, CPC, CPM, average ticket, conversion rate and cost per result stored per row are averages and do not sum. The gold delivers the additive columns (spend, clicks, impressions, revenue, quantity) and the BI tool recomputes the rate as sum over sum in the filtered cut. A rate with no denominator is null, not zero.

Total and detail in the same column, unflagged.

Total rows do not live with the detail

If the gold carries a total row (all regions, all channels), it is flagged and never summed with the detail rows. When in doubt, leave the total to the BI tool.

Summing followers per day or summing every breakdown together.

A snapshot is not a daily metric

Followers, lifetime deduplicated reach, balance and stock are snapshots. When they repeat on every date row, they do not sum: the dashboard uses the last value of the period. Long-format breakdowns (age, gender, placement, device) are filtered one breakdown type at a time, or the total comes out multiplied.

Filtering every metric by the record creation date.

One official date per metric

Each metric has its reference date written down: leads by creation date, sales by won date, revenue by payment or accrual date. The period is closed and in local time, and the current month is labeled as partial.

Swap the dashboard and explain the difference afterwards.

Parity before switching off the old report

When migrating from a spreadsheet, Power BI, Qlik or another BI tool, reproduce the names and numbers of the current report. A measure the BI tool computed as an average weighted by history rows needs the weight in the gold. Compare closed month against closed month and only then switch off the old one. From then on, the gold is the canonical source and other sources are only cross-checks.

A dashboard with no update date.

Freshness visible on the dashboard

Ingestion that stops raises no error, only frozen numbers. The gold carries the last load date of each source (from _nekt_sync_at, converted to local time) and the dashboard shows that card. For daily-volume sources, a completeness alert compares the day with the recent peak, for example fewer than 70% of stores with sales.

A fixed 8am schedule regardless of the load.

Refresh on events, not on the clock

The gold runs after the source finishes, on an event trigger. Scheduling at a fixed time produces a dashboard with half the data on delayed days, and manual triggers get forgotten.

One transformation per dashboard button.

Parameterize instead of copying

One dashboard per branch, service or client does not justify one transformation per button. A gold with the dimension (branch, service, client) and the filter in BI replaces dozens of near-identical copies that drift apart over time.

Sending UTC timestamps and letting the BI tool convert them.

Types the BI tool and destination understand

Some BI connectors break on timestamps: deliver dates as dates and, if needed, timestamps as text. Postgres destinations may reinterpret midnight UTC in local time and push sales to the previous day: store the business date as a date, in the gold itself.

How BI aggregates each column type

The gold delivers the right type; the dashboard only applies the aggregation.

Spend, revenue, quantity, clicksAdditivesum

CTR, CPC, CPM, ticket, conversionRatesum of numerator / sum of denominator

Followers, balance, stock, lifetime reachSnapshotlast value of the period

Customers, orders, invoicesDistinct countcount distinct in the period, do not sum days

Age, gender, placementLong breakdownone breakdown type at a time

All regions, all channelsTotal rownever sum with detail

Definitions

Grain

what one row represents, written in one sentence; equal to the table's update key

Additive column

value that sums across any dimension and period: spend, revenue, quantity, clicks, impressions

Rate for the cut

sum of numerator / sum of denominator in the filtered cut; null when the denominator is zero

Snapshot

value measured at a point in time (followers, balance, stock); over a period, the last value holds

Last load

latest _nekt_sync_at of the source table, converted to local time

Parity

difference between the gold number and the reference report number, for the same closed period, per metric

Partial month

current month up to the last closed day, always labeled on the dashboard

Pitfalls that already happened

Average of rates

The dashboard showed the average of daily CTR per ad and the number did not match the platform's ads manager.

Deliver clicks and impressions and recompute in BI.

Ingestion stopped for over a month

A source stopped without errors. The month showed about a quarter of the real entries and the client report inherited the stale number.

Freshness card on the dashboard and a completeness alert.

Dozens of copies of the same query

Each dashboard button had its own near-identical transformation. A rule fix had to be repeated in all of them and some were left behind.

One gold parameterized by the dimension.

Sales on the wrong day

The Postgres destination reinterpreted midnight UTC in local time and each day's sales landed on the previous day.

Store the business date as a date in the gold.

Multiplied followers

The dashboard summed the follower count repeated on every date row, and the monthly total came out multiplied by the number of days.

Snapshots use the last value of the period.

A number different from the old BI

The old BI computed averages weighted by history rows while the new table did a simple per-record average. Both numbers circulated side by side.

Bring the weight into the gold and document which version is official.

Acceptance test

The case is done when every item passes.

For a closed month, every dashboard metric matches the reference report (difference below 1%).
The gold table has exactly one row per value of the stated grain, with no duplicate key.
Changing the dashboard cut (period, branch, channel) keeps each rate equal to the sum over sum of the additive columns.
The freshness card shows the same date as the source's last load, and with the source stopped the alert fires the next day.

Agent package

Paste into Claude, ChatGPT or any client with the Nekt MCP connected.

Full Markdown

Dashboards and metric gold tables
- Each dashboard's official number comes from its gold table: <dashboard> = <table>. Silver, raw and the BI tool itself are cross-checks, not sources.
- Grain of each table: <table>: one row per <grain>.
- Rates (CTR, CPC, CPM, average ticket, conversion, cost per result) are never summed or averaged: sum of numerator / sum of denominator in the cut. Zero denominator = null.
- Snapshots (followers, balance, stock, lifetime reach) use the last value of the period, never the sum.
- Rows flagged as total are never summed with detail rows.
- Official date: lead = <creation date>; sale = <won date>; revenue = <payment or accrual date>.
- The current month is partial and labeled. Comparisons use closed periods of the same length.
- Every answer includes the source's last load date. If the load is older than <n> hours, warn that the number may be out of date.
Client exceptions:
- <client>: dashboard <name> reproduces the <legacy> report; measure <x> is an average weighted by <weight>.
- <client>: branch <code> is a head office with no direct sales; missing rows are not an error.

Build the "Automatic reports and dashboards" case in my Nekt workspace following the rules at https://academy.nekt.com/en/casos/relatorios-dashboards.md. 1) Find the source tables and confirm the columns the rules use. 2) Create a transformation at the stated grain applying every rule. 3) Run the acceptance test and show me the result of each item. 4) Create the case's context document in the workspace, leaving my clients' exceptions open for me to fill in.

Variations

Legacy BI migration

Reproduce the names and numbers from Qlik, Power BI or a spreadsheet before cutover, with a parity test per closed month.

Client portal

A filter per client or one table per client, with no personal data, sent to the client's app.

Spreadsheet as dashboard

Automatic tabs written only by the routine, with manual tabs preserved.