# Case study: Retail Price & Promotion Reporting Portal

> A client-facing reporting portal where brands and retailers explore advertised prices and promotions, drill down to the deals behind the numbers, and see only the categories they subscribe to. It covers more than 370,000 deals from 270+ retailers. Built from scratch after an earlier attempt didn't deliver.

- URL: https://www.happen.software/case-studies/retail-price-reporting-portal
- Publisher: Happen Software (https://www.happen.software/)

## At a glance

- **Client**: Retail data and insights company
- **Category**: Retail & eCommerce · Platforms & portals
- **Running**: Since 2024 · 2 years
- **Reports**: Built-in reports, plus custom Power BI reports set up per client
- **Engagement**: Full build in 2024, then run and evolve since
- **Users**: 400+ brand and retailer client accounts, and the client's admin team
- **Platforms**: A client-facing reporting site and an admin portal
- **Data source**: The deal capture system we also built, synced three times a day, with 140,000+ deal records a month
- **Access**: Each client limited to its own categories, with permissions per section

## Outcomes

- **370,000+**: Deals clients can explore, across 36,000+ products and 6,500+ brands
- **270+**: Retailers covered, in 140 retailer groups across 16 regions
- **7 weeks**: From the first commit in June 2024 to the portal live for clients in August
- **2,000+**: Exports a quarter, as clients take the data into their own analysis

## Context

Our client captures the deals retailers advertise in their catalogues and turns them into insight for brands and retailers. We also built the system their team uses to capture those deals. This is the other side: the portal their clients log in to.

An earlier attempt at the portal didn't deliver. We started again in June 2024 and had it in production in August. The data is large, so the work since has been about keeping reports fast, keeping both systems in step, and adding new ways to explore it.

## The challenge

- **A fresh start**: An earlier attempt didn't deliver, so the portal had to be built and in clients' hands quickly.
- **A lot of data, fast**: Every client queries large volumes of deals, so reports have to come back quickly without overloading the database.
- **Each client's own view**: Clients subscribe to different categories, and must only ever see the data they pay for.

## Before and after

| Area | Without it | With the portal |
|---|---|---|
| Getting the data | Captured deals sitting in the capture system. | New deals sent across three times a day, then flattened and ready to report on. |
| Finding a deal | Deals looked up one at a time. | Deal and simple search across the client's categories, with the catalogue image a click away. |
| Price trends | Prices compared by hand over time. | Scatter plots and trend graphs of advertised prices. |
| Share of voice | Share worked out in spreadsheets. | Share of voice with a breakdown, exports, and charts saved to a personal dashboard. |
| Access | No way to limit each client to its own data. | Queries limited to the client's subscribed categories, with permissions per section and action. |
| Accuracy | No check that both systems hold the same deals. | An evening comparison of both systems and a nightly repair of the reporting table. |

## From capture to client, end to end

1. **Capture**: The client's team captures the deals from each catalogue in the capture system we also built.
2. **Send**: At 06:00, 12:00, and 17:30, new and changed deals are sent across in compressed chunks. Each chunk is saved and queued, then applied in the background within 30 seconds.
3. **Prepare**: Once a sync is complete, deals are flattened into one wide reporting table, the category tree is updated, and cached results are cleared.
4. **Explore**: Clients open their dashboard, price trends, share of voice, searches, catalogues, or the insights hub, each limited to the categories they subscribe to.
5. **Drill down**: From a chart they open the deals behind it, and from a deal the catalogue image at full size. Grids can be sorted, paged, and exported to Excel.
6. **Check**: At 20:00 the capture system checks that both sides hold the same deals. At 22:00 the portal removes orphaned rows from the reporting table and flattens any that are missing.

## What we built

- **Price trending**: Scatter plots and trend graphs of advertised prices over time, filtered to the client's categories.
- **Share of voice**: Share of voice across the client's categories, with a breakdown, exports of both, and the option to save a chart to a personal dashboard.
- **Search & catalogues**: Deal search opens each deal with its catalogue image at full size, simple search gives a paged, sortable grid with Excel export, and each catalogue can be opened to see the deals it carried.
- **Custom Power BI reports**: Custom Power BI reports built for individual clients are embedded in the portal's Reports menu, set up per client and shared only with the users chosen.
- **Insights hub**: A beta workspace of charts for clients who subscribe to it, with a focus mode and a view of the underlying deals behind the chart.
- **Access by category**: Each client is assigned categories across four levels, and its reports and searches are limited to them. Permissions are set per section and action, and personal dashboards belong to their user.
- **Sync from capture**: Deals arrive in compressed chunks three times a day, are applied by a background queue with retries, and are checked against the capture system every evening.
- **Admin portal**: Clients, their brands and categories, users, and permissions in one place, with usage and export reports, and inactive users deactivated automatically after 90 days by default, unless overridden.

## What each team gets

### Clients

- Prices, trends, and share of voice for their categories
- The deals and catalogue images behind the charts
- Custom Power BI reports inside the portal

### Admin team

- Clients assigned their brands and categories
- Permissions set per section and action
- Usage and export reports across clients

### Capture team

- Their deals in front of clients three times a day
- An evening check that both systems match
- An email if a sync batch keeps failing

### Management

- A log of every search clients run
- A category clients report emailed at 08:00
- Inactive users deactivated automatically

## How it was delivered

- **2024, Build & launch**: Started in late June, with price trending, share of voice, simple search, and client categories, published to production in mid-August, and deal search added in September.
- **2024–2025, Faster at scale**: Server-side caching from August 2024, then indexes tuned for the reporting queries as the data grew.
- **Early 2026, Insights hub**: The insights hub in January, a faster sync in February, and drill-down to the underlying deals in March.
- **Mid 2026, Reliable sync**: Batch sync in compressed chunks with a background queue, retries, and a watermark, plus checks that both systems match.

## Engineering

- Sync files are saved and queued as they arrive, not processed on the request. A background task applies each chunk in a single transaction, retries on failure, and emails once if a batch still fails.
- A forward-only watermark moves on only after a whole sync succeeds, so a failed run is picked up again next time instead of leaving a gap.
- Verification by bucket: deal IDs are split into 2,048 buckets and compared by count and checksum, so only the buckets that differ need their IDs compared.
- Reports read from one flattened deal table with covering indexes, rather than joining many tables each time. A nightly job keeps it in step with the source tables.
- Results are cached on the server for 6 hours per client and filter, and the cache is cleared after each sync, so repeat queries come back quickly and new deals show once a sync completes.
- Every search is logged, even when served from the cache, so usage reports show what clients actually use.
- Kept current with regular technology, library, and framework updates, along with ongoing performance and security improvements.

Stack: .NET, ASP.NET Core, SQL Server, Entity Framework Core, Angular, Power BI, Brotli compression, GitHub Actions

## Get in touch

Lots of data your clients need to explore? We'll talk through how a reporting portal could give each client fast access to its own slice, with a way to drill down to the deals behind the numbers. Book a free 30-minute call with one of our engineers.

Contact: https://www.happen.software/contact or sales@happen.software