Reporting Dashboard

Context

AAJneeti Connect runs paid marketing for more than a hundred clients at once. The people doing that work needed to see, in one place, what each client had spent, what each client was allowed to spend, how each campaign manager was performing, and whether the ad accounts had been funded that morning.

The work came to me as formal written requirement documents rather than a verbal brief, which meant the modules were specified before any code was written. The frontend was delivered by the in-house team; my scope was the data model, the API and everything behind it. It took roughly four months.

Problem

The difficulty was not the reporting. It was that "spend" means three different things depending on how the client is billed.

• CPL — the client pays per lead, so reconciliation compares delivered leads against ad spend.
• Hybrid — part retainer, part performance, so both sides have to be settled against one another.
• Retainer — a fixed fee, where the question is how much of the retainer the media spend has consumed.

Each model needs its own reconciliation logic, but every model has to appear in the same tables, the same totals and the same exports. Add a tenant boundary — no client's numbers may ever leak into another client's view — and the interesting work is all in the modelling, not the charts.

What I built

• Client Spend Monitor — per-client spend tracked against the billing model that applies to that client.
• Allowed Budget — budget caps with grouping, so related clients or campaigns can share a ceiling rather than each holding their own.
• Campaign Manager Performance — the same underlying data reorganised around the person responsible rather than the client.
• Daily Ad Account Funding Reconciliation — a daily pass that checks what was actually funded into each ad account against what the platform expected.

# apps/cm_dashboard/selectors.py
# Deny by default. Admins see everything; a campaign manager sees their own
# clients plus their team's, down the three-tier reporting hierarchy.

def get_visible_client_ids(user, own_only=False):
    """None = sees all (admin); otherwise the client ids this user may see."""
    if user is None or not user.is_authenticated:
        return set()

    if user.role == Roles.ADMIN:
        return SEES_ALL
    if user.role != Roles.CAMPAIGN_MANAGER:
        return set()

    profile = getattr(user, "cm_profile", None)
    if profile is None or not profile.is_active:
        return set()

    cm_ids = {user.id} if own_only else get_team_member_ids(user) | {user.id}
    return set(
        ClientCMAssignment.objects
        .filter(campaign_manager_id__in=cm_ids, is_active=True)
        .values_list("client_id", flat=True)
    )

Technical decisions

One response envelope, everywhere

Every DRF endpoint returns the same shape: success, message, data, meta. Nothing is returned bare, and errors use the same envelope as successes.

# apps/billing/models.py
# Every derived amount is recomputed on save, so GST, TDS and the rounded
# total can never drift from the base figure.

def save(self, *args, **kwargs):
    base = Decimal(str(self.base_amount or 0))
    tds = Decimal(str(self.tds_amount or 0)) if self.tds_applied else Decimal("0")

    self.excluding_gst = base + tds
    self.gst_amount = (self.excluding_gst * Decimal(str(self.gst_pct or 18)) / 100
                       ).quantize(Decimal("0.01"))
    self.including_gst = self.excluding_gst + self.gst_amount
    self.final_amount = self.including_gst.quantize(Decimal("1"),
                                                    rounding=ROUND_HALF_UP)
    if not self.tds_applied:
        self.tds_amount = Decimal("0")

    # An opening balance carries the exact closing of the month before.
    # It is never re-derived — that is what corrupted it the first time.
    exact = getattr(self, "_exact_amounts", None)
    if exact is not None:
        ex, inc = Decimal(str(exact[0])), Decimal(str(exact[1]))
        self.base_amount = self.excluding_gst = ex
        self.including_gst = self.final_amount = inc
        self.gst_amount = inc - ex
        self.tds_applied, self.tds_amount = False, Decimal("0")

    super().save(*args, **kwargs)

Billing metrics computed once per client, before serialisation

The naive version of this platform asks the database for a client's billing position inside the serialiser, once per row, and dies at a hundred clients. I compute the metrics for the whole result set first, then hand the serialiser a finished mapping. Alongside that, relations are pulled with select_related and prefetch_related, and writes go through bulk operations rather than loops.

Redis in front of the heaviest reports

High-traffic reporting endpoints are cached in Redis and kept warm by a background refresh every ten minutes. Marketing data does not change meaningfully inside ten minutes, but people do refresh the page every thirty seconds. The part that matters more than the TTL is the invalidation: the moment a payment is recorded the cache for that view is cleared, so the cached number is never a stale one. The endpoints also accept a cache-busting parameter for anyone who wants to force a fresh read.

Results

• Billing models reconciled against one set of reports: three — CPL, hybrid and retainer
• Modules delivered: four — client spend, allowed budget with grouping, campaign manager performance, and daily ad-account funding reconciliation
• Caching on the heaviest reporting endpoints: Redis, ten-minute TTL, with an on-demand bust for anyone who needs the live number
• Transformation layers the frontend had to write against the API: none — every endpoint returns the same envelope, errors included
• Client Payments page, roughly 200 clients: 22.74s and 8,997 queries before the cache. 0.01s and a single query after it — about two thousand times faster
• Cache kept warm by a background refresh every ten minutes, and cleared the moment a payment is recorded, so a cached figure is never a stale one

What I would do differently

I would make the client the tenant key from day one. It started life as a free-text field, which was fine until client logins arrived and every row suddenly needed to belong to someone — at which point 1,817 campaign rows had to be migrated onto a foreign key. The modelling decision was cheap to make early and expensive to postpone.