How to use SQL to analyze content performance & drive conversions

What to focus on when monitoring content performance

Keep your eyes on the seven essential markers that prove readers stick, share, and eventually buy:

1. Conversion rate: assessing if content drives business goals
2. Organic traffic growth: more visits signal rising interest in your pages
3. Traffic sources mix: know which channels deliver readers, not just numbers
4. Backlinks earned: external votes that nudge Google and expand reach
5. Average time on site: longer stays hint that the content matches visitor intent
6. Click-through rate: compelling calls persuade readers to take the next step
7. SERP ranking lift: higher positions capture attention before clicks even happen

Track these seven, and you’ll spot quick wins — like updating headlines that stall or doubling down on topics fans devour.

Finally, remember that data alone won’t rewrite copy; people will. Review numbers as a team, debate the “why,” and let each new post borrow the lessons from the previous one.

1. Page-views leaderboard

To instantly spot your top traffic drivers, paste this page-views leaderboard snippet:

SELECT page_url,
       COUNT(*) AS views
FROM   page_views
GROUP  BY page_url
ORDER  BY views DESC;

This snippet returns one row for each unique page in your site, with a views column that counts exactly how many times that page was viewed. By sorting those rows with ORDER BY views DESC, you instantly see your highest-traffic pages at the top of the list, making it easy to spot your biggest content winners without any duplicate URLs in the results.

2. Unique visitors per article

To see how many individual readers each article attracts, run the unique visitors per article query:

SELECT page_url,
       COUNT(DISTINCT user_id) AS uniques
FROM   page_views
GROUP  BY page_url
ORDER  BY uniques DESC;

This metric and relevant snippet filter out repeated visits and show you the real size of your audience for each piece. 

3. Average time on page

Run this bigger SQL query to find out exactly how long, on average, users stick around each page:

WITH hits AS (
  SELECT user_id,
         page_url,
         MIN(event_time) AS first_hit,
         MAX(event_time) AS last_hit
  FROM   page_events
  GROUP  BY user_id, page_url
)
SELECT page_url,
       ROUND(AVG(EXTRACT(EPOCH FROM last_hit - first_hit))/60, 1) AS avg_minutes
FROM   hits
GROUP  BY page_url
ORDER  BY avg_minutes DESC;

Even a rough time-on-page metric tells you which pieces hold attention.

Run these three snippets each week, drop the results into a simple chart, and you’ll know exactly where to double down, where to cut, or update your content.

1. Segmenting by traffic source

Knowing the source of the incoming traffic that your content generates is the first step to understanding why visitors land on your pages at all. 

Organic, paid, and referral channels each signal different user intent — searchers arrive with questions, ad-clickers arrive with expectations, and referral readers arrive with borrowed trust. Splitting them apart lets you tailor messages instead of lumping every session into a single bucket.

One quick way to see the spread is to run:

SELECT traffic_source,
       COUNT(*) AS visits
FROM   page_views
WHERE  traffic_source IN ('organic','paid','referral')
GROUP  BY traffic_source
ORDER  BY visits DESC;

That single line gives you a tidy count for each channel, so you can spot imbalances at a glance. Add AND page_url = '/your-landing-page' if you want to focus on one piece of content.

Next, go deeper with conversion data:

SELECT traffic_source,
       COUNT(*) FILTER (WHERE converted = TRUE) * 100.0 / COUNT(*) AS conversion_rate
FROM   sessions
GROUP  BY traffic_source;

Now you’ll see whether your ad budget really outperforms organic, or if referrals secretly win on loyalty. Armed with that intel, you can boost spend on channels that convert, tighten copy where engagement lags, or chase guest posts on sites already sending high-quality traffic. 

Small SQL tweaks, big clarity. That’s the essence of SQL marketing analytics — With the right queries, you can spot what’s really working across your channels.

2. Segmenting by user type

Statistics without context are just tales, as numbers alone don’t tell the full story, and can even mislead you if you don’t understand what’s behind them. 

Context arrives when you tag sessions by the industry they belong to, the map where they come from, and the screen in their hand.

Industry velocity first:

SELECT industry,
       COUNT(*) FILTER (WHERE event = 'demo_request') AS demos
FROM   users
JOIN   events USING (user_id)
GROUP  BY industry;

A quick tip: industries requesting demos deserve dedicated case studies.

Now, add a regional reach:

SELECT region,
       COUNT(*) AS sessions
FROM   sessions
GROUP  BY region;

Plot this on an interactive map and you might discover untapped pockets of interest. 

If you make geographic segmentation of your audience a habit, your outreach will be less about guessing and more like shooting a sniper rifle with a scope.

Device shift:

SELECT device_type,
       SUM(conversions) * 1.0 / COUNT(*) AS conv_rate
FROM   sessions
GROUP  BY device_type;

If you spot lower mobile conversion, consider re-evaluating checkout flow on small screens or optimizing SEO for mobile devices in general.

Segmenting by user type isn’t extra polish; it’s the steering wheel for your roadmap.

How segmentation helps inform personalization or retargeting

Personalization and retargeting thrive on context. Segmentation hands you that context in tidy rows and columns.

Instead of treating every bounce as a lost sale, you can see if the user bounced after reading FAQs or after hitting the pricing wall.

Suppose you see that manufacturing executives from Germany often browse your ROI calculators at 8 a.m. their time. Armed with that intel, you can schedule LinkedIn ads promoting a German-language calculator white paper right before breakfast. 

Meanwhile, U.S. freelancers who binge on your “getting started” content on mobile might receive a simplified onboarding email instead of a dense PDF guide.

Here’s how segmented data powers personal touches:

• Schedule ads when users browse your pages.
• Tailor CTAs to device context (e.g., shorter and sharper CTAs on mobile).
• Show currency based on the user’s current location.
• Highlight features by industry pain (e.g., in healthcare, users look for safety testing and adherence to regulations).
• Adjust the tone for new visitors, who need to be engaged first.

These tweaks feel small, yet they add up to highly personalized user experiences. That’s the difference between an ignored impression and a remembered brand, and when a matching retargeting ad reinforces those tailored touches, it draws hesitant prospects back to complete the journey.

Using SQL to identify high-converting content pages

A page that converts at 3% is worth more than one that converts at 0.3%, even if the latter brings twice the traffic. SQL helps quantify that truth.

Combine views and events:

SELECT pv.page_url,
       COUNT(*) FILTER (WHERE ev.event_type = 'signup') AS signups,
       COUNT(*)                                        AS views,
       ROUND(
         COUNT(*) FILTER (WHERE ev.event_type = 'signup') * 100.0 / COUNT(*),
         1
       ) AS signup_rate
FROM   page_views pv
LEFT   JOIN events ev ON pv.session_id = ev.session_id
GROUP  BY pv.page_url
ORDER  BY signup_rate DESC;

Pages with stellar signup rates become templates for future content. That’s the kind of insight you only get from SQL in marketing, where data illuminates creative wins.

You should analyze their structure, placement of CTAs, and reading level, and do the same for your competitors’ pages. As a matter of fact, interactive website lead generation tools — like pricing estimators — often double form submissions.

For laggards, try testing new button colors, shorter forms, or stronger social proof. Repeat monthly and watch the lagging pages climb.

Using SQL to optimize lead-generating CTAs

Most marketers judge a CTA by gut feeling — red button, blue button, who knows? 

SQL replaces hunches with proof. By pairing click events with the pages that host them, you can see which words, colors, or positions actually persuade.

Start with a simple count:

SELECT cta_id,
       COUNT(*) AS clicks
FROM   cta_clicks
GROUP  BY cta_id
ORDER  BY clicks DESC;

Good, but if you only look at the total number of clicks on each CTA, the busiest pages will naturally rack up more clicks simply because more people saw them, not necessarily because their CTAs are more effective.

What you can do next is normalize by view, i.e., adjust raw click totals based on how many times the CTA was seen, so you get a true effectiveness rate rather than just a popularity count:

WITH views AS (
  SELECT page_url, COUNT(*) AS pageviews
  FROM   page_views
  GROUP  BY page_url
)
SELECT cc.cta_id,
       pv.page_url,
       COUNT(*) AS clicks,
       v.pageviews,
       ROUND(COUNT(*)*100.0 / v.pageviews, 2) AS click_rate
FROM   cta_clicks cc
JOIN   page_views pv ON cc.session_id = pv.session_id
JOIN   views      v ON pv.page_url   = v.page_url
GROUP  BY cc.cta_id, pv.page_url, v.pageviews
ORDER  BY click_rate DESC;

CTAs with high click rates, but modest impressions, are low-hanging fruit — place them higher on the page or reuse them elsewhere. CTAs with traffic but no love need copy tweaks or, perhaps, color and shape/size changes.

Using SQL to find underperforming content

Big traffic is flattering, but it can hide ineffective or low-quality content. Your goal is to pinpoint pages that attract visitors yet fail to keep them reading, clicking, or converting. 

SQL offers a quick, repeatable way to surface those problems so you can fix them before Google demotes them and readers bounce for good.

Start by pairing page views with average time on page:

WITH views AS (
  SELECT page_url,
         COUNT(*) AS total_views
  FROM   page_views
  GROUP  BY page_url
),
dwell AS (
  SELECT page_url,
         ROUND(AVG(time_on_page_seconds),1) AS avg_seconds
  FROM   session_stats
  GROUP  BY page_url
)
SELECT v.page_url,
       v.total_views,
       d.avg_seconds
FROM   views v
JOIN   dwell d USING (page_url)
WHERE  v.total_views > 500
  AND  d.avg_seconds < 30
ORDER  BY v.total_views DESC;

Pages with >500 views and <30 seconds average dwell time top the priority fix list. Maybe the introduction makes big claims, but the rest feels disappointing, or large images push your key text too far down.

 Check formatting, tighten the lead, or add a clearer CTA.

Next, layer in conversions to spot high-traffic, low-yield pages:

SELECT p.page_url,
       v.total_views,
       c.leads,
       ROUND(c.leads*100.0 / v.total_views,2) AS conv_rate
FROM   views v
JOIN   conversions c USING (page_url)
JOIN   popular_pages p USING (page_url)
WHERE  conv_rate < 0.5;      -- adjust threshold

If a page converts under 0.5%, try shorter forms, clearer headlines, or extra social proof nearby. Running these queries regularly turns guesswork into a clear improvement list, saving time and protecting your ROI.