Analytics for a European Video Vault
At ViralVidVault, understanding which European regions produce the most viral content is critical for tuning our fetch pipeline. We built a lightweight analytics dashboard with Go serving JSON data and Chart.js rendering the visualizations.
Data Structures
package analytics
import "time"
type RegionViralStats struct {
Region string `json:"region"`
TotalVideos int `json:"total_videos"`
ViralCount int `json:"viral_count"`
AvgScore float64 `json:"avg_score"`
TopCategory string `json:"top_category"`
}
type ViralityTimeline struct {
Date string `json:"date"`
Region string `json:"region"`
ViralCount int `json:"viral_count"`
AvgScore float64 `json:"avg_score"`
}
type DashboardPayload struct {
Regions []RegionViralStats `json:"regions"`
Timeline []ViralityTimeline `json:"timeline"`
UpdatedAt time.Time `json:"updated_at"`
}
The Analytics Repository
package analytics
import (
"database/sql"
_ "github.com/mattn/go-sqlite3"
)
type Repo struct {
db *sql.DB
}
func NewRepo(dbPath string) (*Repo, error) {
db, err := sql.Open("sqlite3", dbPath+"?mode=ro&_journal_mode=WAL")
if err != nil {
return nil, err
}
return &Repo{db: db}, nil
}
func (r *Repo) RegionStats() ([]RegionViralStats, error) {
rows, err := r.db.Query(`
SELECT
v.region,
COUNT(*) as total,
SUM(CASE WHEN v.virality_score >= 85 THEN 1 ELSE 0 END) as viral,
ROUND(AVG(v.virality_score), 1) as avg_score,
(SELECT c.name FROM categories c
WHERE c.id = (
SELECT v2.category_id FROM videos v2
WHERE v2.region = v.region
GROUP BY v2.category_id ORDER BY COUNT(*) DESC LIMIT 1
)) as top_cat
FROM videos v
WHERE v.region IN ('PL','NL','SE','NO','AT','GB','US')
GROUP BY v.region
ORDER BY viral DESC
`)
if err != nil {
return nil, err
}
defer rows.Close()
var stats []RegionViralStats
for rows.Next() {
var s RegionViralStats
var topCat sql.NullString
if err := rows.Scan(&s.Region, &s.TotalVideos, &s.ViralCount, &s.AvgScore, &topCat); err != nil {
return nil, err
}
s.TopCategory = topCat.String
stats = append(stats, s)
}
return stats, nil
}
func (r *Repo) ViralTimeline(days int) ([]ViralityTimeline, error) {
rows, err := r.db.Query(`
SELECT DATE(fetched_at) as d, region,
SUM(CASE WHEN virality_score >= 85 THEN 1 ELSE 0 END) as viral_count,
ROUND(AVG(virality_score), 1) as avg_score
FROM videos
WHERE fetched_at >= DATE('now', ? || ' days')
GROUP BY d, region
ORDER BY d
`, -days)
if err != nil {
return nil, err
}
defer rows.Close()
var timeline []ViralityTimeline
for rows.Next() {
var t ViralityTimeline
if err := rows.Scan(&t.Date, &t.Region, &t.ViralCount, &t.AvgScore); err != nil {
return nil, err
}
timeline = append(timeline, t)
}
return timeline, nil
}
HTTP Server
package main
import (
"encoding/json"
"log"
"net/http"
"time"
"myapp/analytics"
)
func main() {
repo, err := analytics.NewRepo("./data/videos.db")
if err != nil {
log.Fatal(err)
}
http.HandleFunc("/api/analytics", func(w http.ResponseWriter, r *http.Request) {
regions, _ := repo.RegionStats()
timeline, _ := repo.ViralTimeline(14)
payload := analytics.DashboardPayload{
Regions: regions,
Timeline: timeline,
UpdatedAt: time.Now(),
}
w.Header().Set("Content-Type", "application/json")
w.Header().Set("Cache-Control", "max-age=600")
json.NewEncoder(w).Encode(payload)
})
log.Println("Analytics API on :8080")
log.Fatal(http.ListenAndServe(":8080", nil))
}
Chart.js Dashboard
<div style="display:grid;grid-template-columns:1fr 1fr;gap:20px">
<canvas id="regionRadar"></canvas>
<canvas id="viralTimeline"></canvas>
</div>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
async function render() {
const res = await fetch('/api/analytics');
const data = await res.json();
// Radar chart: region comparison
const regions = data.regions;
new Chart(document.getElementById('regionRadar'), {
type: 'radar',
data: {
labels: regions.map(r => r.region),
datasets: [
{
label: 'Viral Count',
data: regions.map(r => r.viral_count),
borderColor: '#ef4444',
backgroundColor: 'rgba(239,68,68,0.1)',
},
{
label: 'Avg Score',
data: regions.map(r => r.avg_score),
borderColor: '#3b82f6',
backgroundColor: 'rgba(59,130,246,0.1)',
},
]
},
options: {
plugins: { title: { display: true, text: 'Viral Activity by Region' } },
scales: { r: { beginAtZero: true } },
},
});
// Line chart: viral count over time per region
const dates = [...new Set(data.timeline.map(t => t.date))];
const euRegions = ['PL', 'NL', 'SE', 'NO', 'AT', 'GB'];
const colors = ['#ef4444', '#f59e0b', '#3b82f6', '#10b981', '#8b5cf6', '#ec4899'];
new Chart(document.getElementById('viralTimeline'), {
type: 'line',
data: {
labels: dates,
datasets: euRegions.map((reg, i) => ({
label: reg,
data: dates.map(d => {
const pt = data.timeline.find(t => t.date === d && t.region === reg);
return pt ? pt.viral_count : 0;
}),
borderColor: colors[i],
fill: false,
tension: 0.3,
})),
},
options: {
plugins: { title: { display: true, text: 'Viral Videos per Region (14 days)' } },
},
});
}
render();
</script>
Insights We Discovered
Running this dashboard at ViralVidVault surfaced some interesting patterns. Poland consistently produces the most viral music content. The Netherlands and Sweden punch above their weight in comedy. Norway's outdoor adventure videos spike every weekend. These insights helped us tune fetch timing to capture peak virality windows.
Go's performance means the analytics endpoint responds in 2-3ms, and Chart.js radar charts make regional comparisons immediately intuitive.
This article is part of the Building ViralVidVault series. Check out ViralVidVault to see these techniques in action.
Top comments (0)