Nine business intelligence artifacts that quietly became load-bearing

What to take away

Nine business intelligence artifacts recur across companies that have nothing else in common. The same shapes turn up wearing different names, and each one names something structural that has not been built yet.

1. The Salesforce to NetSuite reconciliation that became the source of truth

Two systems disagree, so somebody builds a report that compares them line by line. The usual pair is Salesforce on the sales side and NetSuite, SAP, or Stripe on the revenue side, with the reconciliation living in Excel.

BlackLine sells account reconciliation software for this exact job, yet most teams start in a spreadsheet. Within a year, people stop using either original system and quote the reconciliation instead, because it is the only place the difference is visible.

What it tells you: the reconciliation encodes the mapping between two models, and that mapping exists nowhere else. It belongs in the modeled layer as a documented relationship, not in a report whose original purpose was temporary.

Reconciling systems that each hold their own version of the same entity is the problem master data management exists to address. Informatica, Reltio, and Stibo Systems sell that layer, and a dbt model can carry the crosswalk until it arrives.

2. The Salesforce export that is really a join

Someone pulls a Salesforce report to a spreadsheet every week and pastes a second list beside it. The second list is usually a territory plan, a quota table, or a campaign calendar kept in Google Sheets.

Each half works fine on its own, so nobody calls it an integration problem. The join runs on VLOOKUP or XLOOKUP in Excel, and XLOOKUP ships with Microsoft 365 and Excel 2021.

Power Query performs the same merge as a saved step. A dbt model performs it once in the warehouse, for everyone.

What it tells you: two entities in your business are related and your model does not know it. The paste is a join being performed by hand, and it is performed slightly differently by each person who does it.

3. The Tableau dashboard that should have been an alert

A page exists that one person checks several times a day, looking for a specific condition. It is a Tableau workbook, a Power BI report, or a Looker dashboard watched against a Zendesk queue.

They describe it as monitoring. They also miss it sometimes, usually on the days it matters. What it tells you: the requirement was never a page. It was a condition and a recipient.

Tableau data-driven alerts, Power BI data alerts, and Looker alerts evaluate a threshold on a schedule and notify a named person. Anything a human is checking on a schedule to see whether a threshold was crossed is an alert that has not been written yet.

4. The Zendesk average that is a queue in disguise

A team reports an average age, an average wait, or an average time to resolve. Zendesk Explore reports average resolution time, Jira reports average time in status, and a call center reports average handle time. The number stays comfortably flat while complaints rise.

What it tells you: the population is a queue, and averages over queues hide the tail that everyone actually experiences. The interesting object is the distribution and the oldest item in it. Report the count of things older than a stated threshold, and the average becomes redundant.

5. The scheduled subscription nobody reads and nobody will cancel

A recurring report goes out. It might be a Salesforce report subscription, a Power BI subscription, a Tableau subscription, or a scheduled Google Analytics 4 export. Open rates are low, nobody references it, and every attempt to retire it meets vague resistance.

What it tells you: the report is doing a non-informational job, usually reassurance that something is being watched. Naming that out loud is more productive than arguing about usage numbers.

Offer to replace it with an exception notice that only arrives when something is off. That provides the same reassurance at a fraction of the cost.

6. The allocation spreadsheet that encodes a business rule

One person maintains a file that everyone depends on. It applies an allocation, a mapping, a correction, or a hierarchy that exists in no system. A revenue allocation in Excel and a product hierarchy in Google Sheets are common versions.

What it tells you: a real business rule has no owner and no written definition. The rule is the asset, not the file. It belongs in a data dictionary that outlives whoever maintains the spreadsheet.

Extract it, write it down, get someone to confirm it is still correct, and put it in the modeled layer. dbt docs, Collibra, Alation, and the open source DataHub project keep written definitions beside the data. The spreadsheet then retires itself without an argument.

7. The net revenue retention number that only exists in a meeting

A figure is quoted regularly, everyone accepts it, and no system produces it. Net revenue retention is the classic example: revenue from a cohort of existing customers compared with the same cohort a year earlier. Someone rebuilds it in Excel or Google Sheets the night before the meeting.

What it tells you: the organization has a metric it genuinely cares about and has never asked for. It is often the single highest value thing to build, precisely because demand has already been demonstrated for months.

8. The one off cohort query that became a monthly obligation

An analysis was done once, someone liked it, and it is now expected every month. It may be a cohort query first written in Mode or Hex, or a Jupyter notebook someone reruns by hand. It was never designed to be repeatable, so each run is a partial rebuild.

What it tells you: the boundary between analysis and reporting was crossed without anybody deciding to cross it. Either commit to it properly, with a definition, a model, and tests, or stop it.

BigQuery scheduled queries and Snowflake tasks remove the manual rerun, and dbt tests can assert that a column is unique and never null. The middle state consumes the most time and produces the least trust.

9. The manual territory mapping

Region assignments, account tiers, product groupings, a mapping of teams to owners. Someone updates it manually, and it is always a little out of date. Salesforce territory assignments, HubSpot owner lists, and a CSV in a shared drive all end up doing this job.

What it tells you: a dimension is missing from your data model. The manual list is the specification for it. The update process, whoever remembers to change it and when, is the part that needs designing, not the table itself.

A dbt seed, a CSV committed to the repository and loaded as a table, is often where the list belongs.

Reading these as a set

Every row in that right hand column is a small, specific piece of work. That is the value of pattern matching: it turns a vague sense that things are inefficient into a list you can sequence.

How to find yours

Spend one week writing down what people do in the ten minutes after they receive a number. Not what they asked for, what they do next. Copy, paste, filter. Phone someone, adjust, forward.

Each of those actions is a requirement your system has not met, and the list will be short enough to act on. If three people run the same XLOOKUP on the same two files, you have one model to build.

Related reading on this site

The economics behind why these shapes recur are set out in business intelligence. For the organizational moves that create several of them, see nine mistakes made around a data team.

Patterns three and five are delivery choices covered in dashboard design and recurring reporting. For where the fixes sit relative to each other, see the layers a project passes through.

Common questions

Several of these describe things my team does. Is that bad?

No. Every one of them started as somebody solving a real problem with what they had. The pattern is only a problem when it stays informal long enough to become load bearing.

Which is the most urgent to fix?

The load bearing spreadsheet, because it has a single point of failure who takes holidays. The meeting only number is the highest value to build, because demand is already proven.

How do I raise this without it sounding like criticism?

Describe the pattern, not the person. "We are doing this join by hand every week" is a statement about the model. "You keep exporting to a spreadsheet" is a statement about someone's work.

Do these ever go away on their own?

The manual ones get harder as the company grows, so they surface eventually. Waiting for that means they surface as an incident rather than as a plan.