Why AI product analytics hits a wall with manual exports
Data in a product almost always exists: interface events, billing transactions, support tickets, deal history in CRM.
The bottleneck begins where these streams need to be combined into a single picture — and until now this has been done by hand, because there is simply no automatic layer between systems.
Manual data exports look like this: an analyst pulls CSV from four or five systems, joins them with formulas in a spreadsheet, separately reconciles user identifiers and sorts out duplicate events.
At the same time, disparate data sources interpret the same thing differently — somewhere activity is counted by session, somewhere by calendar day, time zones are also not unified.
Because of this, product data quality rests on a specific person and their memory of yesterday's edits, not on reproducible rules.
The second cost is reporting lag: a weekly cohort takes two to three days to assemble, and the question "why did conversion drop at the payment step" is investigated when some users have already left.
AI product analytics closes exactly this layer: a unified event schema, automatic reconciliation of discrepancies, metric calculation on a schedule without an intermediary.
Below we break down how this works and what changes in decisions when the picture is assembled without manual data exports.
What changes after implementation: speed, completeness, trust
Before implementation, the product summary was assembled manually: exports from several systems, reconciliation in spreadsheets, formula edits along the way.
Now the transformation layer on dbt builds marts on a schedule, and the morning summary of key metrics is generated in minutes instead of two or three days of manual work. The head of the unit sees the numbers by the start of the workday, not after a week of reconciliation.
Completeness of product data is event coverage. At the start, less than half of user actions were described in the schema; the rest settled in unprocessed exports and did not make it into reports.
After implementation, a unified event registry, naming convention and intake checks appear: the share of covered paths grows to 90%+, and a new event is added from a template, without edits to reporting code.
Disputed numbers disappear because a metric is calculated once — in a unified definitions layer. Instead of three versions of "active users" with different windows and deduplication rules, one remains, with a fixed description.
The share of discrepancies between reports falls from a quarter of indicators to isolated cases, and time spent figuring out "whose number is correct" leaves regular reviews.
Manual operations — exports, joins, recalculations — are reduced many times over, and the freed-up time is spent by analysts on testing hypotheses.
Mart freshness is controlled automatically: data tests and alerts for delayed delivery show a failure on the same day, not at the end of the month.
Analytics formats: pilot, streaming, autonomous loop
Three formats are not maturity stages, but different modes of operation for the same data layer.
The pilot loop answers the question "is there a signal in the data"; streaming event processing — "what is happening now"; the autonomous loop — "what decisions are made without an analyst".
The choice is based on the number of sources, the required snapshot latency, and who is responsible for event quality.
Transition between formats does not mean rewriting from scratch: the event schema, reference data and storage layer remain; the execution loop and support regulations change.
| Format | When suitable | Limitations |
|---|---|---|
| Pilot loop | 1–3 sources, hypothesis and data completeness checks | Recalculation is launched manually, there is no input quality control, conclusions are valid only for a narrow slice |
| Streaming event processing | Dozens of sources, snapshots needed within the day | Requires an agreed event schema, on-call for failures and metric versioning |
| Autonomous loop | Reporting and alerts work without analyst involvement | More attention to support, degradation rules and a clear model rollback are needed |
Key limitation is in combining modes: a pilot does not turn into a stream by itself; it needs a dedicated execution loop and a metric owner.
Autonomous mode without preliminary data validation starts alerting on garbage events, so it is enabled after the schema and quality have stabilized.
Implementation stages: from data audit to handover to the team
Work proceeds in sequential blocks, and each step relies on the result of the previous one — otherwise the model is calculated on data whose quality no one checked, and the first reconciliation with reporting destroys trust in the numbers.
- Data source audit. We inventory systems, events and reference data, and reconcile the interpretation of the same entities across different teams. The output is a list of gaps, duplicates and places where events are lost during export.
- Data flow schema. We fix the event path: where it originates, what enriches it, where it is stored and who is responsible for its completeness. The schema becomes the basis for investigating any discrepancy in numbers.
- Setting up collection and marts. We bring events to a unified form, deploy storage and transformation layers. For the event layer we use ClickHouse: aggregates over large slices are calculated quickly, and the report opens without waiting.
- Validating the model on history. We run the logic on accumulated data and compare it with what analysts calculated manually. If the discrepancy is above the acceptable level, we fix the calculation rather than fitting the total to the expected result.
- Metric alignment. We bring disputed definitions up for review: one metric — one calculation, one owner, one point of truth for all departments.
- Support regulations. We describe how to add events, who checks quality and what to do if collection fails — without this, the system degrades within a couple of months.
- Handover of the solution to the team. Access, documentation, walkthrough with engineers and analysts, on-call for the first update cycles.
After handover, your team runs the system: new events and metrics are added according to the described regulations; our role is consulting on complex changes. Describe the task — we will come back with an estimate and work plan.
Documentation, schemas and regulations that remain with you
The documentation set is part of acceptance, not an appendix to it. We check it simply: if the system can be raised and run without the authors using the documents, the work is closed; if you have to write to us for every clarification, it is not. Below is what exactly remains with you after launch.
- Data source description. List of donor systems, which events and entities we take from them, at what frequency, where gaps are possible and how they are marked in storage so they do not enter calculations as real values.
- Metric relationship schema. Graph from the primary event to the final metric: where aggregation is, where filtering is, how recalculation works when data arrives late and why the number for a previous period may change.
- Data access policies. Roles and anonymization levels, who sees raw and aggregated layers and on what basis, how rights are granted and revoked, where the access log is kept.
- Instructions for the team. How to add a new metric or source, how to run quality checks before publication, what to do if there is a discrepancy with the source system.
- Operations regulations. Recalculation schedule, freshness control, failure alert thresholds, handover procedure to the on-call person and response window.
- Metric dictionary. Unified definitions: "active customer" in a report, on a monitoring dashboard and in an export for adjacent teams means the same thing.
- Decision log. What compromises were made and why an indicator is calculated exactly this way — so that six months later no one has to reconstruct the logic from memory.
Case study: reporting preparation accelerated threefold
The customer — a product division with four units. Each maintained its own spreadsheet with exports and assembled a weekly summary manually: the analyst spent two full days on it, and another day went to reconciling versions before the meeting. The numbers diverged — conversion by up to 12%, audience retention by up to 18%.
The cause of the discrepancies was found quickly: the same metric was calculated in four files using different formulas, with different period filters and without accounting for late writes.
We assembled a unified definitions layer — each metric got a formula, source and owner in the dictionary, and unit reports began to be built from one mart instead of personal exports. The reporting layer was deployed on Metabase with role-based access and an edit log.
Metrics before and after
Reporting preparation accelerated threefold: instead of two days — several hours, without manual consolidation and "who has the correct number" correspondence.
Manual labor decreased by about two thirds, and disputed situations in meetings disappeared together with their cause.
Before disconnecting the old tables, both loops ran in parallel for three weeks: reconciliation was line by line, and every discrepancy was investigated down to the formula. The check showed that the new calculation matched manually verified samples.
Rollback remained possible: the metric dictionary is versioned, history is not rewritten, previous exports are preserved.
What to do if data is already spread across different storages?
There is no need to migrate: several data storages are linked on top — with a separate layer that reads sources in place. Each storage is connected via an adapter for its access method, schema and access control rules.
Source tables, marts and update regulations remain as they are — only where the number in the report comes from changes.
Convergence is ensured by metric alignment layers. The first — the reference: a metric is described once, together with formula, source, granularity and owner, and all calculations reference this definition, not local agreements.
The second — the query layer, expanding the metric into queries against the required storages. The third — materialization: heavy aggregates are recalculated on a schedule and stored nearby, so the report opens in seconds rather than minutes of waiting.
Linking sources relies on common keys — customer, order, session. Where keys do not match directly, mapping tables and matching rules are added, and source priority is written explicitly: if two systems diverge, it is known in advance which number to trust.
Access rights are inherited from the sources — the layer does not expand data visibility, only aggregates it. Connecting a new storage comes down to an adapter and metric descriptions, while current processes do not stop.
It is reasonable to test the approach on one domain: assemble several metrics from two storages and compare the result with current reports.
Technical concerns before project start
Before start, we discuss not what the model can do, but how the solution will behave in your infrastructure: where data is stored, what state it is in and what happens if the answer turns out to be wrong.
These questions come up before signing the work plan — and each is closed at the design stage, not at launch.
What data access will you need?
Read access from the analytical storage or marts is enough — through a service account with a limited set of schemas. Direct access to the product database is not needed: we take events from an existing export or logs.
Rights are granted for the duration of work and revoked with one command; the exchange is recorded in the access log.
What to do if historical data is incomplete?
First we calculate history suitability: the share of events with a correct user identifier, export continuity, discrepancies between sources. If the period is short or there are gaps, the solution starts on accumulated data and is refined as new data arrives.
This is more honest than filling gaps with guesses — you immediately see which sample the conclusion is based on.
Can implementation be phased?
The first stage — one scenario and one data source, to check signal quality in a limited loop. The second — expansion to other teams and reporting. Each stage ends with a working solution that can be deployed or paused without affecting what is already running.
Who supports the system after implementation?
After launch, our engineers run it: they monitor quality metrics and false positives, and update rules when data changes. Handover to your team is part of the work: schemas, regulations and access rights remain with you, so the system does not depend on the contractor.
Let's discuss the task: where to start the conversation
To come back with an estimate and implementation plan, a ready-made forty-page technical specification is not required. What is needed is a data-oriented problem statement: where events come from, in what form they are stored and what questions the product already has about them.
The more specific the input data for calculation, the fewer assumptions in the estimate and the fewer surprises at the integration stage, when changing the architecture is expensive.
- Event sources: client applications, server logic, billing, CRM — and who is responsible for each.
- Event schema: which actions are recorded now, in what format, whether there is a common user identifier between systems.
- Volumes and history depth: daily event flow and retention period — this determines storage choice and mart recalculation mode.
- Loop constraints: where data is allowed to be placed, whether a closed loop is needed, storage and anonymization requirements.
- What should appear at the output: reports for which roles and what product decisions are made based on this data today.
- Current analytics stack: what already works, which dashboards and models are used, what you plan to keep.
- People and timelines: who on the customer side approves the data schema and by what date the first result is needed.
This is enough to estimate the scope of work, assemble a phased implementation plan and honestly name the dependencies that will affect timelines.
Describe the task in this form — we will come back with an estimate, work plan and a list of input data that was missing for an accurate calculation.
