Building a Real-Time Analytics Dashboard for URL Shorteners

One of the most powerful features of a URL shortener is the ability to track and analyze how your links perform. Today, we're sharing how we built qz-l's comprehensive analytics dashboard—a system that gives users instant insights into their traffic patterns, audience demographics, and referral sources.

The Challenge

When we started building the analytics feature, we knew we needed:

• Fast data retrieval: Users want instant results when they search for a link
• Comprehensive insights: Not just page views, but traffic sources, device types, browsers, and time patterns
• Beautiful visualizations: Raw numbers are boring; interactive charts tell the story
• Mobile-responsive design: Analytics should work everywhere
• User agent intelligence: Convert cryptic UA strings into readable "Chrome on macOS" format

Architecture Overview

Database Schema

The foundation is a single link_visits table that captures everything about each visit:

CREATE TABLE link_visits (
  id BIGSERIAL PRIMARY KEY,
  short_link_id BIGINT REFERENCES short_links(id),
  ip_address INET,
  user_agent TEXT,
  referrer TEXT,
  language TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX ON link_visits(short_link_id);
CREATE INDEX ON link_visits(created_at);

Every time someone clicks a shortened link, we capture:

• IP Address: For geo-location and unique visitor counting
• User Agent: Browser, OS, and device information
• Referrer: Where the click came from
• Language: Browser language preference
• Timestamp: When the visit occurred

Backend: 8 Parallel Queries

Instead of running queries sequentially, we use Promise.all() to fetch all analytics data in parallel:

const [
  totalVisits,
  uniqueVisitors,
  topReferrers,
  topUserAgents,
  languages,
  dailyVisits,
  hourlyVisits
] = await Promise.all([
  query(`SELECT COUNT(*) as count FROM link_visits WHERE short_link_id = $1`, [linkId]),
  query(`SELECT COUNT(DISTINCT ip_address) as count FROM link_visits WHERE short_link_id = $1`, [linkId]),
  query(`SELECT referrer, COUNT(*) as count FROM link_visits WHERE short_link_id = $1 GROUP BY referrer ORDER BY count DESC LIMIT 20`, [linkId]),
  // ... more queries
]);

This parallel approach means the entire analytics dashboard loads in ~200ms regardless of how many queries we need.

Key Features

1. User Agent Parsing

Raw user agent strings look like this:

Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36

That's useful for debugging, but not for business analytics. We built a parser that extracts meaningful information:

export function parseUserAgent(ua: string): UserAgentInfo {
  const osMatch = ua.match(/Windows|Macintosh|Linux|iPhone|iPad|Android/);
  const browserMatch = ua.match(/Chrome|Firefox|Safari|Edge|Opera/);
  const deviceMatch = ua.match(/Mobile|Tablet|Desktop/);
  
  return {
    os: extractOS(ua),
    browser: extractBrowser(ua),
    device: detectDevice(ua),
    label: `${extractBrowser(ua)} on ${extractOS(ua)}`
  };
}

Now those cryptic strings become: "Chrome on macOS", "Safari on iOS", "Firefox on Windows"—much better for understanding your audience!

2. Interactive Charts with Recharts

We use Recharts for professional, responsive visualizations:

30-Day Trend (Line Chart)

Shows visitor growth over the past month with a smooth line chart. Perfect for spotting trends and seasonal patterns.

24-Hour Distribution (Column Chart)

Horizontal scrolling column chart showing which hours get the most traffic. Helps determine when your audience is most active.

Top Referrers (Horizontal Bar Chart)

See where your traffic comes from: direct links, specific websites, social media platforms, etc. Shows domain names clearly with color-coded bars.

Platform & Browser Distribution (Pie Chart)

At a glance, see what devices and browsers your visitors use. This helps prioritize which platforms to support.

3. Key Metrics

Every analytics view displays four primary metrics:

• Total Views: Raw page view count
• Unique Visitors: Deduplicated by IP address
• Conversion Rate: Unique visitors as a percentage of total views
• Created Date: When the link was created

<StatsCard label="Total Views" value={analytics.totalVisits} icon="👁️" />
<StatsCard label="Unique Visitors" value={analytics.uniqueVisitors} icon="👥" />
<StatsCard label="Conversion Rate" value={`${conversionRate}%`} icon="📊" />

Mobile Responsiveness: A Deep Dive

Building analytics for mobile is tricky. Charts need space, but mobile screens are limited. Here's how we solved it:

Responsive Data Limiting

On mobile, we don't overwhelm users with 10 data points—we show 5:

const [isMobile, setIsMobile] = useState(false);

useEffect(() => {
  setIsMobile(window.innerWidth < 768);
  const handleResize = () => setIsMobile(window.innerWidth < 768);
  window.addEventListener("resize", handleResize);
  return () => window.removeEventListener("resize", handleResize);
}, []);

const limit = isMobile ? 5 : 10;
const displayData = data.slice(0, limit);

Adaptive Chart Layouts

Different charts need different strategies:

Horizontal Bar Charts (Referrers)

• Mobile: Horizontal scrolling container (fixed 400px width)
• Desktop: Full width, no scrolling

Pie Charts (Platforms)

• Mobile: Pie at center (cx="50%"), horizontal legend below
• Desktop: Pie offset left (cx="40%"), vertical legend on right

Line Charts (30-Day Trend)

• Both: ResponsiveContainer handles scaling automatically

Overflow Prevention

The killer issue on mobile is horizontal scrollbars. We prevent them with:

<main className="max-w-7xl mx-auto px-4 sm:px-6 py-8 overflow-x-hidden">
  <div className="grid grid-cols-1 lg:grid-cols-2 gap-6">
    {/* Charts wrapped in overflow-hidden */}
    <div className="overflow-hidden">
      <ReferrersChart />
    </div>
  </div>
</main>

Search Functionality

Users can search by either short code or original URL:

const handleSearch = async (e) => {
  const searchParam = searchInput.includes("/")
    ? `originalUrl=${encodeURIComponent(searchInput)}`
    : `shortCode=${encodeURIComponent(searchInput)}`;
  
  const response = await fetch(`/api/analytics?${searchParam}`);
};

This flexibility means users don't need to remember their short codes—they can search by the original URL they shortened.

Performance Optimizations

1. Database Indexes

CREATE INDEX ON link_visits(short_link_id);
CREATE INDEX ON link_visits(created_at);

These indexes make queries fast even with millions of visits.

2. Parallel Query Execution

Using Promise.all() reduces response time from 800ms (sequential) to 200ms (parallel).

3. Client-Side Rendering

The analytics page is a client component, allowing instant interactivity without waiting for server-side rendering.

4. Responsive Container Scaling

Recharts' ResponsiveContainer handles chart resizing without re-fetching data.

Tech Stack

Component	Technology
Frontend Framework	Next.js 15
Charts	Recharts
Styling	Tailwind CSS
Language	TypeScript
Database	PostgreSQL
User Agent Parsing	Custom parser + regex

Future Enhancements

As we expand the analytics feature, we're planning:

• Geographic insights: Map showing traffic by country
• Export analytics: Download as CSV or PDF
• Scheduled reports: Email analytics summaries
• Comparison mode: Compare multiple links side-by-side
• Anomaly detection: Alert when traffic spikes unexpectedly
• Custom date ranges: Not just last 30 days
• Real-time dashboard: Live updates as clicks happen

Lessons Learned

1. Parallel Queries Are Essential

Sequential database queries turned our analytics slow. Parallel queries fixed it instantly.

2. User Agent Parsing Matters

Showing readable labels ("Chrome on macOS") instead of raw strings made the analytics 10x more useful.

3. Mobile-First Chart Design

Designing charts for mobile first, then scaling up, led to better layouts across all devices.

4. Index Your Analytics Table Early

We added database indexes after performance became an issue. Do it from day one.

5. Show Useful Defaults

Even when data is limited, showing all four metric cards and charts gives a complete picture.

Getting Started with Your Own Analytics

If you're building a URL shortener or similar service, here's the minimal checklist:

• ✅ Capture user agent, referrer, and IP on each click
• ✅ Create database indexes on frequently-queried columns
• ✅ Use parallel queries to fetch analytics data
• ✅ Parse user agent strings into readable format
• ✅ Choose a charting library (we love Recharts)
• ✅ Test mobile responsiveness early and often

Conclusion

Analytics transforms a URL shortener from "just a link tool" into a powerful marketing analytics platform. By combining smart database design, parallel query execution, intelligent user agent parsing, and beautiful visualizations, we created an analytics dashboard that users genuinely love to use.

The best part? The core logic is surprisingly simple—it's the details (mobile responsiveness, chart optimization, data parsing) that make it shine.

If you're using qz-l, head to the Analytics Dashboard and see what you discover about your traffic!

---

Want to dive deeper?

• Check out Recharts documentation for advanced chart customization
• Learn more about User-Agent parsing
• Explore PostgreSQL indexing strategies

Happy analyzing! 📊