---
id: casos/relatorios-dashboards
area: BI
usage: widely used
effort: medium
grain: One grain per table, written in the name and description (e.g. branch x day, ad x day, seller x month)
---

# Automatic reports and dashboards

One gold table per dashboard, with a stated grain and closed rules, that the BI tool only reads: the dashboard number matches the source, refreshes on its own and shows when it was last loaded.

## Executive summary

**For:** Companies that build reports by exporting spreadsheets every week and want a dashboard that refreshes on its own and matches the source.

**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.

**Why it matters:** When the calculation lives in the BI tool, each dashboard does the math its own way: one averages rates, another sums the total together with the rows, another sums followers per day. Meetings turn into debates over which number is right. With the rule in the gold layer, BI only draws, the AI agent reads the same table and the number only changes when the data changes.

**Business questions it answers**

- 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

## Detailed

### 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.

### Business rules

1. **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. (Avoid: A generic table mixing day, month and total rows.)
2. **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. (Avoid: Averaging the CTR column in Looker Studio.)
3. **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. (Avoid: Total and detail in the same column, unflagged.)
4. **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. (Avoid: Summing followers per day or summing every breakdown together.)
5. **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. (Avoid: Filtering every metric by the record creation date.)
6. **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. (Avoid: Swap the dashboard and explain the difference afterwards.)
7. **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. (Avoid: A dashboard with no update date.)
8. **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. (Avoid: A fixed 8am schedule regardless of the load.)
9. **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. (Avoid: One transformation per dashboard button.)
10. **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. (Avoid: Sending UTC timestamps and letting the BI tool convert them.)

### How BI aggregates each column type

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

- **Spend, revenue, quantity, clicks** → Additive (`sum`)
- **CTR, CPC, CPM, ticket, conversion** → Rate (`sum of numerator / sum of denominator`)
- **Followers, balance, stock, lifetime reach** → Snapshot (`last value of the period`)
- **Customers, orders, invoices** → Distinct count (`count distinct in the period, do not sum days`)
- **Age, gender, placement** → Long breakdown (`one breakdown type at a time`)
- **All regions, all channels** → Total row (`never sum with detail`)

### Definitions

| Metric | Definition |
|---|---|
| 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

- **Average of rates.** The dashboard showed the average of daily CTR per ad and the number did not match the platform's ads manager. How to avoid: 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. How to avoid: 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. How to avoid: 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. How to avoid: 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. How to avoid: 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. How to avoid: Bring the weight into the gold and document which version is official.

### Acceptance test

- [ ] 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.

### Context document

```text
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.
```

### 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.