From Fragmented Marketing Data to a Unified Plan vs. Actual Reporting System.

Executive summary

A multi-brand organisation needed to replace a fragmented marketing reporting process with a unified system that could compare planned activity with actual performance.

The challenge was not simply to migrate a spreadsheet. The organisation was managing several brands, multiple marketing channels, separate analytics environments, e-commerce data, advertising platforms, and campaign planning information. These sources had been created for different purposes, followed different structures, and often used different identifiers.

The project therefore became a broader data integration and business intelligence initiative with two connected goals: migrate marketing planning data for three brands into a maintainable structure, and build a campaign-level reporting model that could consolidate performance across channels.

The real project was not building a dashboard. It was creating a trustworthy relationship between what the organisation planned, what it executed, and what actually happened.

The business problem

The organisation managed marketing activity across three separate brands. Each brand had its own combination of website analytics, advertising environments, e-commerce activity, campaign structures, historical reporting practices, and planning data.

Marketing planning existed separately from actual performance. Advertising results were split across platforms. Website analytics were maintained separately for each brand. E-commerce transactions followed their own data model. Campaign names and identifiers were not always consistent between planning systems and execution platforms.

This made a basic management question difficult to answer:

What did we plan to achieve with this campaign, what actually happened, and how did the different channels contribute to the result?

Existing reports could provide partial answers. None of them could provide a reliable consolidated view.

Project objectives

1. Create a common structure for three brands

Data from three separate brand environments needed to be brought into a common reporting model without losing the ability to analyse each brand independently.

2. Migrate the Marketing Plan vs. Actual dataset

The existing planning structure had to move from a spreadsheet-oriented format into a structured source suitable for filtering, reporting, maintenance, and future automation.

3. Integrate actual performance data

The model needed to combine web analytics, paid search, social advertising, e-commerce orders, and transaction-level details.

4. Build a campaign-level BI dashboard

Management needed to analyse the performance of a campaign across channels rather than opening each source system separately.

Plan → Campaign → Channel activity → Actual performance

Why this was more than a migration

At first, the work looked like a standard data migration followed by dashboard development. In practice, the complexity came from the relationships between the datasets.

• The marketing plan described what the organisation intended to do.
• Advertising systems described spend and campaign delivery.
• Web analytics described user behaviour.
• E-commerce systems described transactions and revenue.
• The BI layer had to connect them into one coherent business view.

The main challenge was not visualisation. It was creating a reliable semantic relationship between planning data and operational data.

Delivery approach

The project was delivered iteratively. Rather than attempting to define the complete reporting architecture upfront, the solution evolved through repeated cycles of source analysis, integration, model validation, dashboard prototyping, business review, issue discovery, and structural refinement.

This was necessary because several important data-quality and modelling problems only became visible when information from different systems was placed next to each other.

The solution was progressively discovered through implementation. That was not a failure of planning. It was a realistic response to a system where the hidden business rules lived inside the data.

Phase 1: Establish the integration direction

The first step was defining the overall reporting objective and the major source categories that had to be connected: website analytics, paid media, e-commerce transactions, and campaign planning information.

The first architectural choice was whether every brand should remain fully separate or whether the data should be normalised into shared fact structures.

The decision was to build common reporting tables while preserving brand as a first-class analytical dimension. This allowed cross-brand reporting without losing brand-level detail.

Phase 2: Unify multi-brand analytics

Each brand had its own analytics environment. The data contained similar concepts, but the environments were operationally separate.

To support central reporting, the data was consolidated into a common analytics fact structure. The design principle was simple:

Standardise the structure, but preserve the source brand.

This allowed the model to answer both brand-specific and comparative questions. The same principle later became the foundation for the remaining source systems.

Phase 3: Integrate e-commerce data

E-commerce data introduced a different type of complexity. Orders and order-product information had to support revenue analysis, transaction analysis, product-level reporting, and campaign performance evaluation.

A common order structure was created across the relevant brand environments. During this phase, identifier conflicts became a major issue.

An identifier that appeared unique inside one brand was not necessarily unique after several brands were combined. That led to an important modelling rule:

Never assume a local identifier is globally unique when separate business environments are merged.

Where necessary, composite identifiers and brand-aware keys were introduced to prevent unrelated records from being joined.

Phase 4: Standardise advertising data

The next step was to consolidate paid media data across platforms and brand environments.

The reporting model needed to support measures such as advertising cost, conversions, revenue contribution, return on advertising spend, acquisition efficiency, and campaign-level comparison.

Separate source datasets were standardised into common advertising fact structures, following the same rule as before:

Common metrics, common structure, preserved brand and channel dimensions.

This allowed campaign performance to be analysed across platforms instead of platform by platform.

Phase 5: Reframe the reporting model

An early reporting approach focused on comparing two periods or scenarios. It produced useful analysis, but it did not fully match the real management question.

The deeper requirement was not simply:

Period A vs. Period B

It was:

What was planned vs. what actually happened?

This was a significant turning point. The reporting model was reframed around a true Plan vs. Actual structure, which meant the planning data had to become part of the reporting architecture instead of remaining a separate reference spreadsheet.

Phase 6: Migrate the marketing plan

The existing marketing plan had been designed primarily for human use. It was useful as a working document, but it was not a clean analytical dataset.

The migration therefore required more than copying records. The work included:

• reviewing the existing structure,
• identifying reporting-relevant fields,
• standardising brand information,
• standardising campaign types,
• cleaning legacy formats,
• resolving duplicates,
• and converting the data into a structure suitable for ongoing reporting.

The planning data was migrated into a structured list-based source. This created a maintainable foundation for future campaign planning and allowed the BI model to consume the information consistently.

Phase 7: Connect plan and actuals

Once planning data and actual performance data were available, the central challenge became matching them correctly.

The same campaign could appear differently in the planning system, advertising platforms, analytics environments, and internal reports. The solution therefore required careful handling of campaign names, campaign identifiers, brand ownership, channel definitions, campaign types, and reporting periods.

Where direct technical identifiers were reliable, they were used. Where they were not, the model required controlled mapping logic.

A BI dashboard is only as reliable as the business definitions connecting its source systems.

Phase 8: Build the campaign dashboard

The final dashboard was designed around campaign-level decision-making. Instead of forcing users to analyse each channel separately, the reporting view brought different data sources together under the campaign.

A campaign could therefore be analysed through dimensions such as:

• brand,
• campaign,
• time period,
• channel,
• planned activity,
• actual spend,
• traffic,
• conversions,
• transactions,
• revenue,
• and efficiency indicators.

The goal was not to display more metrics. The goal was to create a coherent performance story: what was expected, where activity happened, what results followed, and how actual performance compared with the original plan.

Key challenges

Multi-brand data standardisation

The same business concept was represented differently across separate brand environments. The solution needed shared structures without destroying brand-specific context.

Non-global identifiers

Identifiers that were unique inside individual systems became duplicated after consolidation. The model needed brand-aware and, in some cases, composite keys.

Campaign matching

Campaign naming and identification were not fully consistent across planning and execution systems. This required mapping logic and governance rather than simple automated joins.

Legacy data quality

Historical planning data contained inconsistent formats, duplicate values, and classifications that needed to be cleaned before reliable reporting was possible.

Changing the reporting concept

The first comparison model did not fully reflect the business decision process. The project had to move from generic performance comparison to a genuine Plan vs. Actual model.

Cross-channel interpretation

Different platforms define performance differently. The BI model had to avoid presenting technically similar metrics as if they always meant the same thing.

Solution architecture

Planning layer

Structured campaign planning data containing campaign information, brand, campaign type, planned timing, planned activity, and other business planning attributes.

Performance layer

Standardised fact structures for web analytics, paid media, orders, and transaction-level details.

Common dimensions

Shared analytical dimensions including date, brand, campaign, channel, and other reporting categories.

BI layer

A campaign-centred reporting experience combining planned activity, actual marketing activity, channel-level performance, commercial outcomes, and efficiency measures.

Business value

The project changed marketing reporting from a collection of disconnected platform views into a more integrated management system.

The organisation gained the ability to:

• review several brands within a consistent reporting framework,
• compare marketing plans with actual performance,
• analyse campaigns across multiple channels,
• connect marketing activity with website and commercial outcomes,
• reduce manual consolidation work,
• improve the consistency of campaign reporting,
• and expose data-quality problems that had previously remained hidden.

The solution also created a stronger foundation for future automation and reporting governance.

Key lessons

1. The dashboard is rarely the hardest part

Most of the effort happened before visualisation. Data definitions, identifiers, campaign structures, and business rules determined whether the dashboard could be trusted.

2. Data integration exposes organisational inconsistencies

When systems are analysed independently, inconsistent naming and classification can remain invisible. A shared model makes these problems immediately visible.

3. Planning data must be treated as operational data

A spreadsheet can work well for manual planning while still being unsuitable for automated reporting. Migration often requires redesign, not copying.

4. Multi-brand reporting requires explicit data governance

Brand is not just a cosmetic filter. It can be necessary for keys, relationships, ownership, and interpretation.

5. The reporting question matters more than the first dashboard design

The project initially explored one type of comparison, but the real business need was Plan vs. Actual. Recognising that changed the architecture of the solution.

Final outcome

The project delivered a unified foundation for marketing performance reporting across three separate brands.

Historical and active planning data was migrated into a structured reporting source. Multiple marketing and commercial data sources were standardised into a common analytical model. A campaign-centred business intelligence dashboard was developed to show the combined performance of different marketing channels.

The result was not simply a new dashboard.

It was a reporting structure connecting what the organisation planned, what it executed, and what results followed.