chloedotbrown
commented
2 years ago Data QA - complete
Created a TEST_last_week_homepage view in the analytics_testing folder in BigQuery. This mirrors the logic in the current Domo Content Engagement connector query, but instead of joining with the content table, data is filtered to only pull in the homepage. Fields from the content table, such as product and page_name, are hard-coded.
Reference: BigQuery QA instructions
Structure tests
Uniqueness
✅ Passed! Total rows is the same value as the count of distinct hit IDs (combination of session and hit number).
Conducted on 7/3-7/9 data.
| Total rows | Unique IDs |
|---|---|
| 1,347,636 | 1,347,636 |
Expand for test code
```sql select count(*) as total_rows, count(distinct concat(session, hit_number)) as total_uuid from`vsp-analytics-and-insights.analytics_testing.TEST_last_week_homepage`; ```Nulls
✅ Passed! Identified errors in underlying content last week view in BigQuery introduced in April edits. Added page detail data back in and switched subqueries to CTEs to improve readability, both for last week and full backfill versions. There remain some areas of improvement for the link_label field, affecting both content and homepage data – notably, some CTA are resulting in nulls where they shouldn't. These will be wrapped into the content QA to be added to tech debt.
Conducted on 7/10 data – full week of data too large to process efficiently.
| Column | Null rate | Explanation |
|---|---|---|
search_keyword |
100% | ✅ Not used in any cards or calculations, deleting field from content views and connectors before backfill |
event_label |
84% | ✅ Null for all pagehits, corresponds with ratio of page vs. event hits |
event_action |
84% | ✅ (see above) |
mobile_brand |
60% | ✅ Corresponds with device_category = desktop rates for each (values only available for mobile, tablet). |
mobile_model |
60% | ✅ (see above) |
previous_page |
41% | ✅ Corresponds with percent of rows that where pageview was first hit of session |
time_to_event |
41% | ✅ Corresponds with percent of rows where hit was the first one included in content view, and therefore nothing for lag function to process. |
time_on_page |
41% | ✅ Corresponds with percent of rows where pageview was the first one included in content view, and therefore nothing for lag function to process. |
link_label |
9% | ✅ Corresponds with percent of scroll event rows, where label is null. |
Expand for test code
```sql with content_nulls as ( select col_name, count(1) nulls_content, 'a' as join_key from `vsp-analytics-and-insights.analytics_testing.TEST_last_week_content` t, unnest(regexp_extract_all(to_json_string(t), r'"(\w+)":null')) col_name group by col_name ), content_all as ( select 'a' join_key, count(*) as total_content from `vsp-analytics-and-insights.analytics_testing.TEST_last_week_content` ), pct_content as( select col_name, round((nulls_content/total_content), 2) as pct_content from content_nulls left join content_all using (join_key) ), home_nulls as ( select col_name, count(1) nulls_home, 'a' as join_key from `vsp-analytics-and-insights.analytics_testing.TEST_last_week_homepage` t, unnest(regexp_extract_all(to_json_string(t), r'"(\w+)":null')) col_name group by col_name ), home_all as ( select 'a' join_key, count(*) as total_home from `vsp-analytics-and-insights.analytics_testing.TEST_last_week_homepage` ), pct_home as( select col_name, round((nulls_home/total_home), 2) as pct_home from home_nulls left join home_all using (join_key) ) select * from pct_content full join pct_home using (col_name) ```Completeness
✅ Passed! Checked row distributions for main filters to be used in new dashboard.
Expand for test code
```sql /************************************** NOTE: Highlight and run separately **************************************/ -- Completeness test - Date select date, count(*) as total_rows from `vsp-analytics-and-insights.analytics_testing.TEST_last_week_homepage` group by 1 -- Completeness test - Device select device_category, count(*) as total_rows from `vsp-analytics-and-insights.analytics_testing.TEST_last_week_homepage` group by 1 -- Completeness test - Browser select browser, count(*) as total_rows from `vsp-analytics-and-insights.analytics_testing.TEST_last_week_homepage` group by 1 order by 2 desc -- Completeness test - Page title (multiple for language variations) select page_title, count(*) as total_rows from `vsp-analytics-and-insights.analytics_testing.TEST_last_week_homepage` group by 1 -- Completeness test - Product (should be 1) select product, count(*) as total_rows from `vsp-analytics-and-insights.analytics_testing.TEST_last_week_homepage` group by 1 ```Accuracy tests
Conducted on 7/3-7/9 data.
Reference: QA for standardized dashboards; WIP Content QA spreadsheet
Highlights
| Chart Title | Pass/Fail |
|---|---|
| Usage trends - unique users | ✅ |
| Usage trends - total interactions | ✅ |
| Unique users - new vs. returning | ✅ |
| Unique pageviews | ✅ |
| Total clicks by device | ✅ |
Task completion
| Chart Title | Pass/Fail |
|---|---|
| User interaction rate | ✅ |
| Total interaction clicks | ✅ |
| Avg. clicks per user | ✅ |
| Interactions with most clicks | ✅ |
Ease of use
| Chart Title | Pass/Fail |
|---|---|
| Avg. scroll depth distribution | ✅ |
| Avg. minutes on page | ✅ |
| Avg. scroll depth | ✅ |
Findability
| Chart Title | Pass/Fail |
|---|---|
| Top pages previously viewed | ✅ |
| Devices used to access the page | ✅ |
| Top browsers used to access the page | ✅ |
michelle-dooley
pavanhothi
Issue Description
Duplicate Content Dashboard and separate data source for Sitewide Homepage team. Cannot add to Standardized Content KPIs due to data size and existing latency issues (to be addressed in future tech debt ticket, likely once Data Engineer role filled).
See #41503 for VFS-facing ticket.
Tasks
Data @chloedotbrown
Dashboard @chloedotbrown
QA @pavanhothi & @jonathan-epstein13
Delivery @chloedotbrown
Acceptance Criteria