Select Distinct helped Ultima Media transform fragmented subscription data into a central Power BI dashboard. We automated reporting, cutting manual work by 80% and delivering real-time insights into subscriber churn and engagement for faster, data-driven decisions.

Transforming fragmented Firestore subscription data into a scalable BigQuery and Looker Studio analytics ecosystem

Frustrated With Messy Firestore Data?

Explore our analytics consulting services or book a call directly with Simon to find out more

Project Snapshot

Client

Ultima Media

Industry

Publishing & Automotive Media

Tech Stack

Firestore, BigQuery and Looker Studio

The Challenge

Ultima Media’s subscription data – spanning volume, type, revenue, churn, and expiry timelines – was stored across Firestore and BigQuery in a raw, inconsistent format.

Key challenges included:

Structure: Messy, unstructured data with inconsistent field naming, IDs, and timestamp logic.
Accuracy: Rows were pulling through based on refreshable dates, making historical reporting unreliable.
Visibility: Limited insight into key subscription lifecycle metrics such as churn, renewals, and corporate activity.
Access: No centralised reporting layer, making it difficult for internal teams to self‑serve insights.
Scalability: A dashboard was needed that could be handed over and maintained in‑house without technical support.

Ultima Media needed a clean, trustworthy data foundation and a dashboard that could surface insights without requiring technical intervention.

The Solution

We rebuilt Ultima Media’s subscription analytics workflow to create a clean, reliable, and scalable reporting ecosystem.

Data Quality: Normalised Firestore and BigQuery tables, clarified field logic, and rebuilt datasets to ensure accurate historical reporting.
Data Models: Created structured BigQuery views covering subscription summaries, revenue, expirations, corporate activity, and individual subscription details.
Collaboration: Worked closely with the data team through regular reviews, feedback cycles, and shared access to evolving dashboards.
Dashboarding: Designed an intuitive Looker Studio dashboard surfacing key metrics including volume, revenue, churn, renewals, and expiry timelines
Handover: Delivered full documentation, mapped all datasets, and transferred ownership so internal teams can maintain and scale the reporting long‑term.

The Results

Accuracy: Reliable, consistent reporting replacing previously inconsistent data pulls.
Foundation: A scalable BigQuery analytics layer that Ultima Media can maintain and extend internally.
Insight: Clear visibility into subscription performance to support forecasting and commercial decisions.
Efficiency: Reduced manual effort through automated data flows and structured datasets.
Usability: A user‑friendly Looker Studio dashboard enabling teams across the business to self‑serve insights.

Ready to Turn Your Subscription Data Into Actionable Insight?

Ultima Media transformed fragmented Firestore data into a clean, scalable BigQuery and Looker Studio reporting ecosystem- giving their teams clarity, accuracy, and confidence in every metric.

Book a free consultation to get started

Share the Post:

Power BI & Azure ERP Dashboards: Agriculture Analytics

Select Distinct integrated Azure and ERP data into Power BI for an agricultural leader. We delivered real-time analytics across the supply chain, from crop yields to distribution, empowering the business with a single source of truth for enhanced operational planning and forecasting.

Power BI Reporting for Sales, Returns & Inventory

Select Distinct developed an integrated Power BI dashboard to unify sales, returns, and inventory data. We provided real-time visibility into stock health and return rates, enabling data-driven procurement and reducing waste for a more profitable, streamlined supply chain.

Subscription Analytics Dashboard: BigQuery & Looker Studio