Joining CSAT Survey Scores to Page View Data in SQL

Leave a Comment / By /

Analytics Engineering · SQL · Feedback Scoring

Joining CSAT Survey Scores to Page View Data in SQL

How to connect customer feedback to behavioural analytics so satisfaction scores can be analysed by journey, page and service area.

The Problem

Customer satisfaction survey data is useful on its own, but it becomes much more useful when joined back to page view and journey data.

Instead of only asking what was the average CSAT score?, you can ask which page, journey or service area was the user on when they gave that score?

The approach below joins page view data to feedback responses using the session ID and date.

SQL

LEFT JOIN {{time_aware_table("your_join_table_name_here", alias="RP", timerange="3 days")}}
ON PC.tags.x_session_id = RP.tags.x_session_id
AND PC.date = RP.date

This makes it possible to analyse satisfaction scores alongside page path, referrer, service origin, status code, session ID and comments.

The Complete Query

Here is the full SQL in context. The sections below break down the scoring and classification logic.

SQL

WITH f1 AS (
SELECT
  PC.generated_at AS "Time",
  PC.date AS "Date",
  PC.tags.x_session_id AS "SessionID",
  RP.origin AS "Service",
  PC.status_code AS "Status",
  PC.referrer AS "Referrer",

  split(coalesce(
    regexp_replace(regexp_extract(PC.referrer, '(.*)\?.*$', 1), '\/\d{1,3}$', '/[AN]/'),
    regexp_replace(PC.referrer, '\/[0-9]+[A-Za-z].+[\-].+|\/[A-Za-z]+[0-9].+[\-].+|\/(?![0-9]{4}\-[0-9]{4})(?:[A-Z]|[0-9])+[\-].+\/|\/[0-9]{1,3}(?:$|\/)', '/[AN]/'),
    regexp_extract(PC.referrer, '(.*)\?.*$', 1),
    regexp_extract(PC.referrer, '(.*)', 1)
  ), '/') AS "short_referrer_array",

  coalesce(
    regexp_extract(PC.tags.path, '(.*)\/[0-9]{1,3}$', 1),
    regexp_extract(PC.tags.path, '^(\/.*)', 1)
  ) AS "Page",

  coalesce(
    regexp_replace(regexp_extract(PC.tags.path, '(.*)\?.*$', 1), '\/\d{1,3}$', '/[AN]/'),
    regexp_replace(PC.tags.path, '\/[0-9]+[A-Za-z].+[\-].+|\/[A-Za-z]+[0-9].+[\-].+|\/(?![0-9]{4}\-[0-9]{4})(?:[A-Z]|[0-9])+[\-].+\/|\/[0-9]{1,3}(?:$|\/)', '/[AN]/'),
    regexp_extract(PC.tags.path, '(.*)\?.*$', 1),
    regexp_extract(PC.tags.path, '(.*)', 1)
  ) AS "short_page",

  PC.user_agent_string AS "UAString",

  CASE
    WHEN RP.how_do_you_feel_score = '-' THEN 0
    WHEN RP.how_do_you_feel_score = '1' THEN CAST(RP.how_do_you_feel_score AS INT)
    WHEN RP.how_do_you_feel_score = '2' THEN CAST(RP.how_do_you_feel_score AS INT)
    WHEN RP.how_do_you_feel_score = '3' THEN CAST(RP.how_do_you_feel_score AS INT)
    WHEN RP.how_do_you_feel_score = '4' THEN CAST(RP.how_do_you_feel_score AS INT)
    WHEN RP.how_do_you_feel_score = '5' THEN CAST(RP.how_do_you_feel_score AS INT)
    ELSE 0
  END AS CScore,

  CASE
    WHEN RP.how_do_you_feel_score = '5' OR RP.how_do_you_feel_score = '4' THEN 'Pos'
    WHEN RP.how_do_you_feel_score = '3' OR RP.how_do_you_feel_score = '2' OR RP.how_do_you_feel_score = '1' THEN 'Neg'
    ELSE '0'
  END AS CSRating,

  IF(RP.how_easy_score = '-', 0, CAST(RP.how_easy_score AS INT)) AS NScore,

  CASE
    WHEN RP.how_easy_score = '5' OR RP.how_easy_score = '4' THEN 'Pos'
    WHEN RP.how_easy_score = '3' OR RP.how_easy_score = '2' OR RP.how_easy_score = '1' THEN 'Neg'
    ELSE '0'
  END AS NSRating,

  IF(RP.able_to_do = '-', 100, CAST(RP.able_to_do AS INT)) AS Ascore,
  RP.why_give_score AS Comments,
  regexp_replace(RP.why_give_score, '\d', '#') AS Comments_Redacted

FROM {{time_aware_table("your_table_name_here", alias="PC", timerange="3 days")}}
LEFT JOIN {{time_aware_table("your_join_table_name_here", alias="RP", timerange="3 days")}}
  ON PC.tags.x_session_id = RP.tags.x_session_id
  AND PC.date = RP.date

WHERE RP.origin = 'TES'
AND PC.tags.path NOT LIKE '%/assets/%'
AND PC.tags.path NOT LIKE '%/images/%'
AND PC.tags.path NOT LIKE '%/fonts/%'

GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
)

SELECT
  Time,
  SessionID,
  Date,
  Service,
  Page,
  concat('/', try(array_join(slice(short_referrer_array, 4, cardinality(short_referrer_array) - 1), '/'))) AS "short_referrer",
  short_page,
  Status,
  UAString,

  CASE
    WHEN regexp_like(Referrer, '.*print-your.*') THEN 'Printing'
    WHEN regexp_like(Referrer, '.*(update-income|your-income|income-calculation).*') THEN 'Update Income'
    WHEN regexp_like(Referrer, '.*current-year.*') THEN 'Current Year'
    WHEN regexp_like(Referrer, '.*historic-paye.*') THEN 'Historic Years'
    WHEN regexp_like(Referrer, '.*(/paye-income-tax-estimate|/detailed-income-tax-estimate|/tax-code/|/tax-codes/|/tax-free-allowance|/your-income-calc).*') THEN 'Understanding Tax'
    WHEN regexp_like(Referrer, '.*tax-code-change.*') THEN 'Tax Code Change'
    WHEN regexp_like(Referrer, '.*employment.*') THEN 'Employment'
    WHEN regexp_like(Referrer, '.*(messages|profile).*') THEN 'User Account'
    WHEN regexp_like(Referrer, '.*pension.*') THEN 'Pension'
    WHEN regexp_like(Referrer, '.*underpayment.*') THEN 'Underpayment'
    WHEN regexp_like(Referrer, '.*(company-benefit|car|medical-benefit|marriage|uniform).*') THEN 'Benefits Reliefs'
    WHEN regexp_like(Referrer, '.*state-benefits.*') THEN 'State Benefits'
    WHEN regexp_like(Referrer, '.*income-tax-history.*') THEN 'Income Tax History'
    WHEN regexp_like(Referrer, '.*(income-summary|paye|income-details|check-income-tax\/income-tax).*') THEN 'Income Tax Summary / Details'
    WHEN regexp_like(Referrer, '.*get-help.*') THEN 'Help Tax'
    WHEN regexp_like(Referrer, '.*jrs-claims.*') THEN 'Job Retention Scheme'
    WHEN regexp_like(Referrer, '.*comparison.*') THEN 'Tax Comparison'
    WHEN regexp_like(Referrer, '.*p800.*') THEN 'P800'
    WHEN regexp_like(Referrer, '.*telephone-number.*') THEN 'Telephone'
    WHEN regexp_like(Referrer, '.*update-income.*') THEN 'Update Income'
    WHEN regexp_like(Referrer, '.*signout.*') THEN 'Signout page'
    WHEN regexp_like(Referrer, '.*what-do-you-want-to-do.*') THEN 'Home Dashboard'
    ELSE 'Other'
  END AS "Subject",

  CScore,
  CSRating,
  NScore,
  NSRating,
  Ascore,
  Comments_Redacted

FROM f1
WHERE regexp_like(short_page, '.*\/(signout|session-expired).*')
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17
ORDER BY SessionID, Time

The Scoring Fields

The query creates three main score fields: CScore, NScore and Ascore.

1
CScore

Customer satisfaction score from the “how do you feel” question.

2
NScore

Ease score from the “how easy was it?” question.

3
Ascore

Ability-to-complete score from the task completion response.

Handling missing scores

In this dataset, missing answers are stored as - rather than NULL. The query avoids cast errors by checking for this value before converting the score to an integer.

SQL

IF(RP.how_easy_score = '-', 0, CAST(RP.how_easy_score AS INT)) AS NScore
This is a defensive SQL pattern. Without the check, CAST('-' AS INT) would fail.

CScore: customer satisfaction

The customer satisfaction score comes from the “how do you feel” survey field.

SQL

CASE
  WHEN RP.how_do_you_feel_score = '-' THEN 0
  WHEN RP.how_do_you_feel_score = '1' THEN CAST(RP.how_do_you_feel_score AS INT)
  WHEN RP.how_do_you_feel_score = '2' THEN CAST(RP.how_do_you_feel_score AS INT)
  WHEN RP.how_do_you_feel_score = '3' THEN CAST(RP.how_do_you_feel_score AS INT)
  WHEN RP.how_do_you_feel_score = '4' THEN CAST(RP.how_do_you_feel_score AS INT)
  WHEN RP.how_do_you_feel_score = '5' THEN CAST(RP.how_do_you_feel_score AS INT)
  ELSE 0
END AS CScore

Positive and negative banding

Raw scores are useful, but for dashboards it is often easier to reduce them into positive and negative bands.

SQL

CASE
  WHEN RP.how_do_you_feel_score = '5' OR RP.how_do_you_feel_score = '4' THEN 'Pos'
  WHEN RP.how_do_you_feel_score = '3' OR RP.how_do_you_feel_score = '2' OR RP.how_do_you_feel_score = '1' THEN 'Neg'
  ELSE '0'
END AS CSRating
Score Band
5 Pos
4 Pos
3 Neg
2 Neg
1 Neg
– 0

This makes reporting simpler: percentage positive, percentage negative, negative feedback by service area and negative feedback by page type.

NScore: ease score

The same pattern is used for the “how easy was it?” question.

SQL

IF(RP.how_easy_score = '-', 0, CAST(RP.how_easy_score AS INT)) AS NScore

The numeric value is then grouped into Pos, Neg or 0.

Ascore: task completion

The query also creates an ability score.

SQL

IF(RP.able_to_do = '-', 100, CAST(RP.able_to_do AS INT)) AS Ascore

This gives a task-completion indicator alongside satisfaction and ease. CSAT alone does not always explain whether the user achieved what they came to do.

Classifying the Subject from the Referrer

In this query, the subject classification is deliberately applied to the Referrer, not just the current page path.

SQL

CASE
  WHEN regexp_like(Referrer, '.*print-your.*') THEN 'Printing'
  WHEN regexp_like(Referrer, '.*employment.*') THEN 'Employment'
  WHEN regexp_like(Referrer, '.*pension.*') THEN 'Pension'
  WHEN regexp_like(Referrer, '.*underpayment.*') THEN 'Underpayment'
  ELSE 'Other'
END AS "Subject"

This matters because the page where feedback is submitted may not be the page the user was trying to use. It may be a feedback page, signout page or session-expired page.

The referrer is often a better signal of the actual service area that triggered the feedback.

For journey analysis, be explicit about whether subject classification is based on the current page, the referrer, or the normalised page path. Those choices answer different analytics questions.

Why Join Scores to Page Views?

Once survey responses are joined to page view data, the dataset can answer more useful questions:

  • Which service areas create the most negative feedback?
  • Which referrers lead to poor satisfaction?
  • Are users failing on a specific journey step?
  • Are low CSAT scores linked to signout or session-expired pages?
  • Are users able to complete the task even when satisfaction is low?

Conclusion

This SQL pattern turns raw feedback into structured analytics fields. It joins survey responses to behavioural data, converts text scores into numeric values, creates positive / negative bands, and classifies feedback by service area.

The result is a practical CSAT analysis dataset that can be used directly in dashboards, QA checks or journey investigations.

CSAT scoring and page-view joining pattern for analytics engineering workflows.

Leave a Comment

Your email address will not be published. Required fields are marked *