Data QA - New Dashboard - Sitewide Homepage Search

michelle-dooley commented 2 years ago

Issue Description

This is an internal ticket for the data QA portion of the Sitewide Homepage Search.

Tasks

[x] Add QA table to this ticket for tracking.
[x] QA data

Acceptance Criteria

[ ] When data QA has been completed and signed off on for relaase.

How to configure this issue
[x] Attached to a Milestone (when will this be completed?)
[x] Attached to an Epic (what body of work is this a part of?)
[x] Labeled with Team (product support, analytics-insights, operations, service-design, Console-Services, tools-fe)
[x] Labeled with Practice Area (backend, frontend, devops, design, research, product, ia, qa, analytics, contact center, research, accessibility, content)
[x] Labeled with Type (bug, request, discovery, documentation, etc.)

jonathan-epstein13 commented 2 years ago

QA : VA Homepage	Ticket #41512 - Sitewide Search Dashboard (VA Homepage)
Highlights
Chart title	Assets	Pass / Fail
Successful searches	Event Label = onsite-search-results-click/, Total events	🟢 PASS
Search funnel	Event Label = view_search_results/, Total events - page is www.va.gov/	🟢 PASS
Search refinement rate	BigQuery metric (SUM(`refinement`) / count(distinct concat(`session_id`, `hit_number`))*1.0	🟢 PASS

Task Completion
Chart title	Assets	Pass / Fail
Search click rate	BigQuery metric/ custom metric ((COUNT(DISTINCT CASE WHEN `event_label`= 'onsite-search-results-click' THEN CONCAT(`session_id`, `hit_number`) END)/COUNT(DISTINCT CASE WHEN `event_label` = 'view_search_results' THEN CONCAT(`session_id`, `hit_number`) END)) as search_click_rate)	🟢 PASS
Clicks by position	BigQuery metric - CASE WHEN `search_results_position` <= 2 THEN '1st or 2nd' WHEN `search_results_position` > 2 AND `search_results_position` <= 5 THEN '3rd - 5th' WHEN `search_results_position` > 5 THEN '6th or more' END as Click_rate_by_position,	🟢 PASS
Results clicked	BQMetric	see below comment

Ease of use (search abandonment)
Chart title	Assets	Pass / Fail
Searched sessions	Sessions tab of report	Blocker
Abandoned sessions	BigQuery metric	Blocker
Search abandonment rate	BigQuery metric	Blocker
Abandonment rates by most common search terms	BigQuery metric	Blocker

Ease of use (search refinement)
Chart title	Assets	Pass / Fail
Refinement rate	BigQuery metric - SUM(`refinement`) / count(distinct concat(`session_id`, `hit_number`))	🟢 PASS
Search sessions by number of refinements	BigQuery metric sum(refinement) as total_refinements, ( CASE WHEN `total_refinements` = 0 then '0 refinements' WHEN `total_refinements` = 1 then '1 refinement' WHEN `total_refinements` = 2 then '2 refinements' WHEN `total_refinements` > 2 then '3 or more' END , Total sessions = COUNT(DISTINCT `session_id`)	🟢 PASS (total sessions)
No results found	BigQuery metric (COUNT(DISTINCT CASE WHEN `results_returned` = 'No Search Results Returned' THEN CONCAT(`session_id`, `hit_number`) END)/COUNT(DISTINCT CASE WHEN `event_label` = 'view_search_results' THEN CONCAT(`session_id`, `hit_number`) END)) as No_results_found	🟢 PASS

Findability
Chart title	Assets	Pass / Fail
Top pages viewed before searching	BQMetric	see below comment
Total search users	Users tab of report	🟢 PASS
Avg. searches per user	BigQuery metric - COUNT(DISTINCT CASE WHEN `event_label` = 'view_search_results' THEN CONCAT(`session_id`, `hit_number`) END) / COUNT(DISTINCT `user`)	🟢 PASS
Search users by device	Device tab of report	🟢 PASS
Top browsers types used to search	Browser tab of report	🟢 PASS

michelle-dooley commented 2 years ago

Adding slack convo for reference.

jonathan-epstein13 commented 2 years ago

Task Completion
Chart title	Assets	Pass / Fail
Results clicked	BigQuery metric - selectsearch_result_chosen_title as results_chosen, count(distinct concat(session_id, hit_number)) as total_eventsfrom `vsp-analytics-and-insights.vsp_sites.vw_search_last_week` where search_scope = 'All VA.gov' and event_label = 'onsite-search-results-click' and previous_page in ('www.va.gov/','www.va.gov/index') group by search_result_chosen_title order by total_events desc	🟢 PASS

Findability
Chart title	Assets	Pass / Fail
Top pages viewed before searching	BigQuery metric selectdate,previous_page,search_scope,count(distinct concat(session_id, hit_number)) as total_events,from `vsp-analytics-and-insights.vsp_sites.vw_search_last_week` where search_scope = 'VA Homepage' --and previous_page LIKE 'www.va.gov/search/%' and event_label = 'view_search_results' group by date,previous_page,search_scopeorder by date desc;	🟢 PASS

jonathan-epstein13 commented 2 years ago

Ease of use (search abandonment)
Chart title	Assets	Pass / Fail
Searched sessions	BigQuery metric - selectCOUNT (DISTINCT `session_id`) as search_sessions,from `vsp-analytics-and-insights.vsp_sites.vw_search_last_week` where search_scope = 'VA Homepage' and event_label = 'view_search_results' order by search_sessions desc	🟢 PASS

chloedotbrown commented 2 years ago

Hi @jonathan-epstein13 - I'm pasting code below for the 3 search abandonment metrics still in need of QA.

Notes for using:

All 3 metrics use the same 2 CTEs (search_sessions, abandoned sessions) at the top. To keep this code block short, I included all on top of each other and commented out the start of each QA test. You'll either need to comment out both metric queries you're NOT using at a time, or copy/paste this block 3x and delete the other 2 for it to run. Let me know if you have questions.
Reminder to filter the full Search dashboard by "previous week." Abandonment cards 2 and 3 show data for all time, but for performance, the QA is built from the last week view only. If you don't filter, the numbers won't match.

I'll also flag this in Slack for @michelle-dooley - but in going through these, I noticed an error in the Domo ETL that I've corrected. The grouping and join criteria did not include the search_scope field, which impacted abandonment metrics by undercounting them for users who used more than one search product in a session. The ETL now includes this field in both steps and QA logic reflects the change as well.

with search_sessions as (
  select
    date,
    session_id,
    user,
    search_scope,
    device_category,
    browser,
    count(distinct event_label) as unique_labels,
    string_agg(event_label, ' ~ ') as concat_labels
from `vsp-analytics-and-insights.vsp_sites.vw_search_last_week` 
group by 
  date, session_id, user, search_scope, device_category, browser
),

abandoned_sessions as (
  select 
    session_id,
    search_scope,
    (case when unique_labels = 1 
      and concat_labels like 'view_search_results%'
      then 'true' else 'false' end) as is_abandoned
  from search_sessions
)

-- QA for "Abandoned Sessions"
select
  s.search_scope,
  count(distinct s.session_id) as total_sessions
from `vsp-analytics-and-insights.vsp_sites.vw_search_last_week` s
left join abandoned_sessions a
  on s.session_id = a.session_id 
  and s.search_scope = a.search_scope
where is_abandoned = 'true'
group by search_scope

-- QA for "Search Abandonment Rate"
select
  s.search_scope,
  round(cast(
          (count(distinct case when is_abandoned = 'true' then s.session_id end) /
              count(distinct s.session_id))as numeric), 2) as abandonment_rate
from `vsp-analytics-and-insights.vsp_sites.vw_search_last_week` s
left join abandoned_sessions a
  on s.session_id = a.session_id 
  and s.search_scope = a.search_scope
group by search_scope

-- QA for "Abandonment Rates by Most Common Search Terms"
select
  s.search_scope,
  search_query,
  count(distinct case when event_label = 'view_search_results' 
    then concat(s.session_id, hit_number) end) as total_queries,
  round(cast(
          (count(distinct case when is_abandoned = 'true' then s.session_id end) /
              count(distinct s.session_id))as numeric), 2) as abandonment_rate
from `vsp-analytics-and-insights.vsp_sites.vw_search_last_week` s
left join abandoned_sessions a
  on s.session_id = a.session_id 
  and s.search_scope = a.search_scope
where s.search_scope = 'VA Homepage'
  and search_query != '(not set)'
group by search_scope, search_query
order by total_queries desc
limit 10

michelle-dooley commented 2 years ago

@chloedotbrown thanks for identifying and fixing the ETL. I do not think it warrants a communication as long as the dictionary is in sync. If we get questions then we can communicate it out but I don't think we will.

@jonathan-epstein13 just let us know once you have been able to complete this last bit of QA

jonathan-epstein13 commented 2 years ago

@michelle-dooley the last QA that I needed help with was the abandonment section and @chloedotbrown was able to help with that BQ side of things. so we are good to go. I can post the entire QA-GA to the ticket#41512 What are your thoughts @chloedotbrown ?

chloedotbrown commented 2 years ago

I think we don't need to post the QA table in the external-facing ticket. We usually don't, and I imagine it could be overwhelming! Glad to hear that things went well with last few abandonment metric checks.

Sounds like we should be able to close this ticket and let Michelle M know she's good to go in #41512?

michelle-dooley commented 2 years ago

This work is complete.

department-of-veterans-affairs / va.gov-team