Case Study: Building a Content Operations Framework & Reporting Architecture

Problem

Before my role as Content Manager, the team had:

• No content operations framework, so production wasn’t structured or measurable.
• No reporting architecture and leadership couldn’t see what had been produced or present metrics to investors.
• No granularity and no way to break content down by week, month, year, or category.
• No accountability and the status of the content (proofread, in CMS, published) wasn’t tracked anywhere.

Solution: A Content Operations Framework in Google Sheets

I built a content operations and reporting framework that functioned like a mini relational database:

Data Modeling (Granularity & Count System)

• Introduced a “count” field, now every article/script was assigned a measurable unit.
• Enabled pivot tables and charts to aggregate content at multiple levels: weekly, monthly, yearly.
• Delivered reporting granularity that didn’t exist before.

ETL Workflow (Extract, Transform, Load)

• Used IMPORTRANGE to extract data from multiple team sheets.
• Used XLOOKUP with a reference table (a data dictionary) to transform raw categories into standardized content types.
• Loaded the clean data into a master content roundup sheet for monthly reporting.

This was essentially an ETL pipeline, but fully inside Google Sheets.

Interactive Reporting Architecture

• Designed an editorial calendar with data validation (dates) and TEXT function (weekdays).
• Created a weekly reporting dashboard:
  • Dropdown control (Week 1–5).
  • Linked bar chart updating counts by content category.
  • Real-time visualization of weekly output.

Vertical Ownership: Nutrition 2.0

• Built and managed a dedicated system for the Nutrition vertical:

  • Article title (hyperlinked to Drive).
  • Category.
  • Creatives.
  • Count.
  • Status (proofread, CMS, app).
  • Inspiration articles.
  • Provided end-to-end visibility into one of the app’s most critical content areas.

Course Design Planning

• Collaborated using Miro boards to plan courses.
• Defined content mix: cards, articles, videos, podcasts.
• Created a framework for multi-format learning pathways.

Impact

• Created the first content operations framework for the company.
• Delivered a reporting architecture that gave leadership real-time insights.
• Brought granularity into production tracking (weekly, monthly, yearly views).
• Eliminated manual duplication via an ETL-style workflow with IMPORTRANGE and XLOOKUP.
• Introduced a data dictionary to standardize categories across the organization.
• Provided investor-ready reporting enabling the owner to present credible, data-backed metrics.
• Professionalized the content process, transforming it from ad hoc to structured, measurable, and scalable.

🎥 Watch the Demo

Watch the video demo to see the content tracker, editorial calendar, and reporting dashboards in action:
Case Study Demo