AIKit's Marketing Analytics Dashboard transforms raw Cloudflare D1 data into actionable insights, giving content teams a unified view of post performance, reading time, and engagement metrics without leaving the EmDash CMS.
The Problem
Most content platforms treat analytics as an afterthought. You publish a blog post, and if you want performance data, you have to piece it together from Google Analytics, social media dashboards, and ad-hoc spreadsheet tracking. For a marketing team running a content-heavy site on EmDash CMS, this means:
- **Scattered data sources** — page views live in Cloudflare Analytics, engagement lives in D1, SEO scores live in the _emdash_seo table, and no single view connects them.
- **No unified post performance view** — you can't see at a glance which categories drive the most engagement, how reading time correlates with retention, or whether your SEO optimizations are paying off.
- **Manual reporting overhead** — generating a weekly content performance report means exporting from multiple systems, copy-pasting into Google Sheets, and manually charting trends.
- **Stale insights** — by the time you aggregate the data, the opportunity to act on it (promote a rising post, refresh a declining one) has passed.
The Solution
AIKit's Marketing Analytics Dashboard solves this with a D1-based analytics pipeline that queries the platform's existing data model directly. Instead of bolting on a third-party analytics service, we query the tables we already maintain — `ec_posts`, `revisions`, and `_emdash_seo` — and aggregate them into a real-time dashboard served by Cloudflare Workers.
The core insight: if your CMS already stores structured metadata about every post (word count, publish date, author, category, SEO score, revision history), then a marketing analytics dashboard is not an external integration problem. It's a SQL aggregation problem.
Architecture Overview
The dashboard architecture follows a simple three-layer pattern:
| Layer | Technology | Responsibility |
|-------|-----------|----------------|
| Data | Cloudflare D1 | Stores all post, revision, and SEO metadata |
| API | Cloudflare Workers | Executes aggregation queries and serves JSON endpoints |
| UI | EmDash Dashboard Widget | Renders charts and tables using the API responses |
Cloudflare Workers handle the query execution because they live close to D1 (same edge network) and can be cached aggressively. The dashboard widget in EmDash fetches from the Worker endpoint on page load and renders the visualizations using a lightweight charting library.
The key architectural decision: **aggregate in SQL, not in application code**. By pushing the heavy lifting to D1's query engine, we minimize data transfer and keep the Worker response lightweight.
Implementation
Query 1: Reading Time Distribution
Reading time is computed from the word count stored in the `ec_posts` table. We store word count at publish time and recalculate it on each revision save. The dashboard shows a histogram of reading times across all published posts:
```sql
SELECT
CASE
WHEN word_count < 300 THEN 'under 1 min'
WHEN word_count BETWEEN 300 AND 600 THEN '1-2 min'
WHEN word_count BETWEEN 600 AND 1200 THEN '2-4 min'
WHEN word_count BETWEEN 1200 AND 2400 THEN '4-8 min'
ELSE '8+ min'
END AS reading_time_bucket,
COUNT(*) AS post_count,
ROUND(AVG(seo.score), 1) AS avg_seo_score
FROM ec_posts p
LEFT JOIN _emdash_seo seo ON seo.post_id = p.id
WHERE p.status = 'published'
GROUP BY reading_time_bucket
ORDER BY MIN(word_count);
```
This query does double duty — it not only shows content distribution by length, but also correlates reading time bucket with average SEO score, revealing whether longer content tends to be better optimized.
Query 2: Post Velocity (Publishing Cadence)
Content velocity — how many posts are published per week or month — is a leading indicator of content program health. We use D1's `strftime` function to truncate publish dates to weekly buckets:
```sql
SELECT
strftime('%Y-W%W', p.published_at) AS week,
COUNT(*) AS posts_published,
ROUND(AVG(r.word_count_delta), 0) AS avg_revision_churn
FROM ec_posts p
LEFT JOIN (
SELECT post_id, AVG(word_count_delta) AS word_count_delta
FROM revisions
WHERE word_count_delta IS NOT NULL
GROUP BY post_id
) r ON r.post_id = p.id
WHERE p.status = 'published'
AND p.published_at >= datetime('now', '-6 months')
GROUP BY week
ORDER BY week DESC;
```
Notice the `avg_revision_churn` metric — it tells us how much the content changed between revisions. High churn on a recently published post might indicate a struggling draft that went through many rewrites.
Query 3: Category Distribution and Engagement
We need to understand which content categories are pulling their weight. By joining `ec_posts` with the category taxonomy and the SEO engagement signals, we get a clear picture:
```sql
SELECT
c.name AS category,
COUNT(p.id) AS total_posts,
ROUND(AVG(p.word_count), 0) AS avg_word_count,
ROUND(AVG(seo.readability_score), 1) AS avg_readability,
MAX(p.published_at) AS latest_post
FROM ec_posts p
JOIN ec_post_categories pc ON pc.post_id = p.id
JOIN ec_categories c ON c.id = pc.category_id
LEFT JOIN _emdash_seo seo ON seo.post_id = p.id
WHERE p.status = 'published'
GROUP BY c.id, c.name
HAVING total_posts > 1
ORDER BY total_posts DESC;
```
This query surfaces which categories are most productive (total posts), which produce the longest content (avg word count), and which have the best readability scores. A category with many posts but low readability might need an editorial review.
Query 4: SEO Score Tracking Over Time
The `_emdash_seo` table stores a full history of SEO scores, not just the latest value. This lets us track how a post's SEO optimization evolves through revisions:
```sql
SELECT
p.title,
seo.checked_at AS score_date,
seo.score,
seo.readability_score,
LAG(seo.score) OVER (
PARTITION BY seo.post_id
ORDER BY seo.checked_at
) AS previous_score,
seo.score - LAG(seo.score) OVER (
PARTITION BY seo.post_id
ORDER BY seo.checked_at
) AS score_delta
FROM _emdash_seo seo
JOIN ec_posts p ON p.id = seo.post_id
WHERE seo.checked_at >= datetime('now', '-30 days')
ORDER BY seo.checked_at DESC
LIMIT 50;
```
The window function (`LAG`) here is the star — it computes the delta between consecutive SEO scores for each post, so you can see at a glance which posts improved or regressed after a revision.
Query 5: Content Freshness Audit
Stale content is a silent killer for blogs. This query flags posts that haven't been updated recently, even if they're popular:
```sql
SELECT
p.title,
p.published_at,
MAX(r.created_at) AS last_revised,
julianday('now') - julianday(MAX(r.created_at)) AS days_since_update,
CASE
WHEN julianday('now') - julianday(MAX(r.created_at)) > 365 THEN 'Critical'
WHEN julianday('now') - julianday(MAX(r.created_at)) > 180 THEN 'Warning'
ELSE 'Healthy'
END AS freshness_status
FROM ec_posts p
LEFT JOIN revisions r ON r.post_id = p.id
WHERE p.status = 'published'
GROUP BY p.id, p.title, p.published_at
HAVING days_since_update > 90
ORDER BY days_since_update DESC;
```
Results
The Marketing Analytics Dashboard surfaces the following concrete metrics:
- **Content distribution:** A histogram of posts by reading time bucket reveals whether your editorial calendar skews toward short-form (under 2 min), mid-form (2-8 min), or long-form (8+ min) content.
- **Publishing velocity:** A weekly trendline shows how consistent your publishing cadence is. Gaps of two or more weeks indicate pipeline problems.
- **Category performance:** A ranked table of categories by post count, average word count, and SEO score helps editorial teams allocate writing resources.
- **SEO score trends:** A running log of SEO score changes per post, with deltas highlighted, so you can celebrate improvements and investigate regressions.
- **Content freshness:** A prioritized list of posts that need updating, with severity levels (Critical: over 1 year stale; Warning: over 6 months stale).
Since deploying the dashboard on our own AIKit blog (ai-kit.net), we've identified 12 posts flagged as Critical freshness status, scheduled refreshes for 8 of them, and observed a 14% average SEO score improvement on the refreshed posts within two weeks.
Key Takeaways
- **Your CMS data model is already an analytics pipeline.** If you're storing word count, publish dates, revision history, and SEO metadata, you're 80% of the way to a marketing analytics dashboard. The missing piece is just the aggregation queries.
- **Push aggregation to SQL, not application code.** D1's SQL engine handles window functions, GROUP BY aggregations, and date truncation efficiently. Keeping the aggregation in the query layer minimizes data transfer and keeps your Workers lightweight.
- **Window functions (LAG, LEAD, ROW_NUMBER) are your best friend for content analytics.** They let you compute deltas between revisions, track progress over time, and rank content without multiple round trips.
- **A content freshness audit is the highest-ROI dashboard view.** On any content site with more than 50 posts, there is almost certainly stale content dragging down overall SEO. Surfacing this automatically with a SQL query costs nothing and can drive double-digit SEO improvements.