In the world of content marketing, we often ask: “Which topics are actually resonating with our audience?” Standard Google Analytics 4 (GA4) reports tell you which URLs are popular, but they don’t easily tell you which Content Categories are winning over time. If you want to know if your “Industry News” posts performed better in 2025 than your “Product Guides,” you’re usually stuck with a manual spreadsheet nightmare.
We recently developed a custom PHP pipeline to automate this, pulling live data directly from the WordPress database and merging it with GA4 API metrics. Here’s how the process works and why it’s a game-changer for content strategy.
The Challenge: The “Siloed Data” Problem
WordPress knows your categories, but it doesn’t know your traffic. GA4 knows your traffic, but it doesn’t understand your WordPress taxonomy. To get a bird’s-eye view, you have to join these two worlds.
The Sightfactory Solution: A 3-Stage Pipeline
First, we bypass the slow WordPress REST API and go straight to the source: the MySQL/MariaDB database. By querying the tables directly, we can instantly map thousands of posts to their specific categories.
Tech Tip: We use GROUP_CONCAT to handle posts that live in multiple categories, ensuring no data point is left behind.
The GA4 Data API is powerful but has strict quotas. Rather than asking for data post-by-post (which would take hours), we execute a single, high-limit “RunReport” request. We pull the pagePath and year dimensions into a local lookup table.
This is where the magic happens. We normalize URLs (handling those pesky trailing slashes) and match the GA4 traffic to the WordPress categories.
The Result: High-Value Intelligence
Instead of a messy dashboard, the system generates clean, “Excel-ready” CSV files:
- The Post Audit: Every post, its URL, and its traffic year-over-year.
- The Category Leaderboard: Which categories have the highest average views?
- The Frequency Report: A “Production Heatmap” showing how many posts you actually published in each category per year.
Why This Matters for Your Business
Stop Guessing: Identify exactly which content pillars are growing and which are stagnating.
Audit Production vs. Performance: You might find you are publishing 50% of your content in a category that only generates 5% of your traffic.
Historical Trends: By pulling data by year, you can see if your 2023 “Viral Hits” are still providing “Evergreen” value in 2026.
Are you interested in seeing what your content data is hiding? We specialize in building custom data bridges that turn standard analytics into clear business directions. Reach out to see how we can automate your reporting.
