A pharmacy chain replaced its Monday spreadsheet ritual with one Looker Studio report its board trusts

Representative examplesEvery case study in this library is an illustrative composite of the kind of engagement we deliver — written to show our method and standards, not to name clients.

Where they started

The chain operates pharmacies across a region, with a loyalty program, weekly flyers, and a small marketing team that reports to leadership every Monday morning. The data sources are fixed by the company's systems: GA4, the ad platforms, the loyalty program's export, and two store-system spreadsheets, none of which this project could change. A marketing coordinator assembles the numbers, and store-level comparisons carry real political weight — a wrong number about a store is a phone call from that store's manager. The leadership meeting is the fixed point around which the whole week is organized.

What it was costing

Every Monday, a marketing coordinator assembled a spreadsheet from five sources for the leadership meeting; versions conflicted, definitions drifted between stores, and the meeting argued about numbers instead of decisions.

What they could see

• Every Monday began with the coordinator reconciling five sources that disagreed with each other in different directions.
• Definitions drifted between stores — a loyalty sign-up meant one thing in one district and another in the rest.
• Meetings argued about which number was right rather than what to do, and the coordinator defended instead of presented.
• Versioned spreadsheet copies circulated before meetings, and two versions of the same chart routinely appeared in the room.

The constraints we worked inside

• Data sources are fixed — GA4, the ad platforms, the loyalty program's export, and two store-system spreadsheets — and none can be changed by this project.
• Store-level comparisons are politically sensitive; the report must be accurate enough to be argued with, not defended.
• The coordinator must own the report after handover; if maintenance needs a developer, it will die.

What had been tried before

The company bought a business-intelligence tool seat for the coordinator, expecting the connector library to solve the joins.

The connectors needed licensed upkeep and modeling help the coordinator was never given, so the tool became another export destination rather than a source of truth.

Leadership tried standardizing by email, asking store managers to submit weekly numbers on a shared template.

Submission templates standardize format, not definitions — each store kept its own meaning for sign-ups and transfers, and the disagreements simply arrived in matching fonts.

What we proposed

We proposed landing each fixed source in BigQuery on a schedule, writing an explicit definition sheet for every metric — what a prescription transfer counts as, what a loyalty sign-up is — and doing all blending in the warehouse rather than in chart settings. On top sits one Looker Studio report with store, region, and chain views, date defaults set to the reporting week, and every chart traceable to a definition. The coordinator owns the pipeline after a handover built around her workflow, because a report that needs a developer to maintain would die within a quarter however good it looked on day one.

Just as important is what we ruled out, and why:

• A full business-intelligence platform rollout with IT ownership — IT runs the pharmacy systems, not marketing reporting, and the constraint was explicit — if maintenance needs a developer, the report dies; the platform guaranteed that outcome.
• Connecting Looker Studio directly to each source with native connectors — It skips the definition layer entirely, so every blend happens invisibly in chart settings and the store-level disputes that broke the spreadsheet would return inside the tool.

How the work ran

Delivered by the growth pod — analytics specialist over 5 weeks, with working increments reviewed with the client every week.

The stack, and the reasoning

Looker Studio

A coordinator owns this report without a developer behind her; a browser tool she edits alone, at no license cost, was the only shape a small marketing team could sustain.

BigQuery

Blending five sources with fixed definitions needs a warehouse join, not chart-level math; the monthly cost of the company's existing cloud account covered it.

Scheduled source exports

The sources are immovable by this project, so scheduled exports meet each system where it already lives instead of demanding new integrations from other teams.

Definition documentation

Store comparisons are politically sensitive — a written definition per metric is what makes the report accurate enough to be argued with rather than defended.

GA4

Web numbers needed no new tooling, only fixed placement in the definition sheet so a session means the same thing in every store's row.

What went wrong

How we worked together

Cadence

A working session each Tuesday with the coordinator, building against the real Monday meeting; a checkpoint with the marketing director at weeks two and four; no meetings on Mondays, by request.

Client side

The coordinator was the client — she supplied every source, every workaround, and the unwritten store-level definitions; the marketing director owned adoption in the leadership meeting.

Decisions

Definitions were agreed with the coordinator and the district leads she trusted; layout and defaults were ours to iterate; anything touching store-level visibility went to the marketing director.

They provided

Standing access to all five sources, the coordinator's time for two sessions weekly, the loyalty program's export documentation, and honest answers about which historical numbers were soft.

What changed

The headline: leadership reviewing from the live report with definitions on record — Monday spreadsheet ritual → one standing report, read from Report adoption in meeting logs. A second check: sources of truth for marketing performance at 5 → 1.

Monday changed from a defense to a review. The coordinator walks in with one report, definitions on record, and the meeting argues about promotions and staffing instead of arithmetic — she describes the difference as carrying the report instead of carrying the blame. The version-control problem vanished because there is one report and it is always current. Store managers, who spent years assuming marketing's numbers were invented, now quote the dashboard back at meetings, which is the adoption that no amount of training would have bought.

The result was read from Report adoption in meeting logs against the pre-engagement baseline over the stated window, with a guardrail check on sources of truth for marketing performance. Where platform-reported numbers and business outcomes differ, this record says which layer it is quoting.

What they own now

• The Looker Studio report with store, region, and chain views, owned end to end by the coordinator.
• The definition sheet — transfers, sign-ups, sessions — published to leadership and versioned.
• The BigQuery import pipeline with the definition-sheet reconciliation that catches semantic drift.
• Scheduled exports wired to all five sources with the coordinator holding the credentials.
• A written maintenance runbook and two working sessions training her to extend it alone.