Rebuilding CRM reporting after Universal Analytics

The request usually arrives on a Tuesday. Someone building the board pack wants to know how many of last quarter's new customers bought again, split by the channel that acquired them. A while ago that was a saved report and a copy-paste.

Now the old property is switched off, the history that sat behind it has gone, and the current interface answers a slightly different question with a number nobody in the room trusts. The email platform says one thing, the finance export says another, and the analytics tool says a third.

This is where most CRM teams are sitting this summer. The migration happened, the tags fire, someone rebuilt the dashboards once in a hurry, and the reconciliation was quietly dropped. Our position is simple: stop treating the analytics interface as the reporting layer for CRM. Use the raw export, in your own warehouse, joined to order data.

Separate the media questions from the lifecycle ones

Before rebuilding anything, split the reporting requests into two lists. Media questions are about traffic, campaigns and the path to a first session. Lifecycle questions are about a known person over months.

The analytics interface is good at the first list. Channel mix, landing page performance, campaign traffic quality: that is what it was designed for, and the standard reports are adequate.

It is poor at the second list, and not because of a configuration mistake. Web analytics reports on sessions and devices. CRM reports on people and their order history. When the question is "what share of the March cohort placed a second order within ninety days", you need a customer key, not a device identifier.

Write both lists down. In most programmes, much of what the analytics tool was being asked to answer belongs in the warehouse, and only ever produced approximations anyway.

Send the raw export to the warehouse and report from there

The export of raw events into a warehouse is the part of the migration worth doing properly. Once a day's events land as their own table, the analytics tool stops being a reporting product and becomes a collector. That is a much easier thing to trust.

What changes in practice:

• Sampling and cardinality limits stop mattering, because you are querying rows rather than a pre-aggregated report.
• Interface data retention stops mattering for history, because your warehouse keeps everything you have loaded.
• You can join events to orders, refunds, subscriptions and message sends on your own keys.
• Definitions live in version-controlled SQL that two people can read and argue about.

Cost sits with warehouse storage and queries, not with the analytics product, so the sensible pattern is ELT: load raw, model afterwards. Build a thin modelled layer on top of the raw tables — sessions, users, orders — and point everything downstream at that layer rather than at the raw export. Which dashboard tool sits on top is a separate and much less urgent decision, one we worked through in choosing between Power BI and Tableau.

Set a rule now that saves months later. Nothing reads the raw export directly except the models. Analysts, dashboards and the CRM platform read the modelled tables.

Rebuild the event schema around the questions, not the tag manager

Most migrated setups inherited whatever the tag manager happened to emit, renamed a few things, and stopped. The result is an event stream that describes page views well and business behaviour badly.

Go back to the list of lifecycle questions and work out the smallest set of events that answers them. For a retailer that is usually product viewed, basket updated, checkout started, order placed, order delivered, order returned. For a subscription business it is trial started, plan changed, payment failed, cancelled.

For each event, write down three things before anyone implements it:

1. The parameters it must carry, including the customer key when one is available.
2. Where its values come from, and which system owns them.
3. The one report or trigger that stops working if it is missing.

That third column is the test. If nothing breaks when an event disappears, it is not event tracking, it is noise. We routinely cut the event list by half at this stage, and the reporting improves because there is less to reconcile.

One implementation note. A web-only event stream will always undercount, because people move between devices and browsers clear storage. Where a signed-in identifier exists, put it on the event. Where it does not, expect a gap and stop trying to close it with clever client-side tricks.

Say out loud where the history is missing

There is now a break in the record. Some teams have a warehouse copy of the old data, some have an incomplete extract, and some have nothing before the switchover. Whatever your situation, the worst response is to stitch the two eras into one chart and hope nobody asks.

What we do instead. Keep the old data, if you have it, in a clearly separate schema and never join it to the new tables in a live report. Document the differences in plain language: what counted as a session then and now, what counted as a conversion, which channel groupings changed.

Then pick a start date for trend reporting and hold to it. A year-on-year comparison drawn across that boundary is not a comparison, and presenting it teaches the business to distrust the whole reporting layer.

Where the question genuinely needs long history, answer it from a source that survived. Order data, subscription records and message logs usually go back further than any analytics property, and for most lifecycle questions they are the better source anyway.

Rebuild attribution as a written rule

The default model in your analytics tool is a setting. Your CRM attribution model should be a written rule that the finance team has seen and agreed to.

Ours is usually blunt on purpose. Define a window, define what counts as a touch, define precedence when two channels both have a claim, and write it in a paragraph that a non-analyst can read. Then implement that paragraph in SQL in the modelled layer, so the number in the dashboard and the number in the rule are the same number.

Expect the rebuilt figures to be lower than the old ones for email and CRM. That is a change in the counting method, not a change in performance, and it needs saying before the first report goes out rather than after. The same discipline applies to the rest of the CRM scorecard, which is why we moved off opens as a headline metric in life after the open rate.

Where to start this month

• Write the two reporting lists — media and lifecycle — and mark which requests the analytics interface can honestly answer.
• Confirm the raw export is landing daily in the data warehouse, and check that yesterday's table exists rather than assuming it does.
• Draft the event list from the lifecycle questions, with the owner and the breaking report named for each event, and cut anything with an empty third column.
• Write the attribution rule as a paragraph, get finance to read it, and only then build the model.