1716074 - Derive search tables from clients_daily

Status: RESOLVED FIXED

(Data Platform and Tools :: General, enhancement)

Description

[Ben Wu [:benwu]]

search_clients_(engines_sources_)daily (scd) is currently still querying main_summary which is a deprecated table. It should instead be derived from clients_daily (cd).

Deriving from clients daily would have a few benefits:

• It will allow us to remove duplicated code and have better consistency across the tables since cd and scd share many fields in common so there's a lot of duplicated logic for calculating the values per client. clients_daily is useful for search analysis in certain use cases.
• It will be easier to add new search probes for analysis. For new search related probes, if we want to use the in the search tables, we need to add them to main_summary which is an unwieldy process. On top of that, there's a desire from data science to add new probes to clients_daily so we would need to deal with both tables.
  • This is very relevant at the moment because we're looking to add a bunch of new search probes to both cd and scd bug. The new workflow would be to aggregate once in cd and count in scd instead of aggregating in both
• This simplifies scd a lot since the aggregation is done in cd, and scd only needs to look at the total counts for each search source and count them up. The current model of scd is a bit convoluted

Steps to do this:

• Add values needed by scd that aren't in cd to cd
  • These are some search related prefs and the actual search maps (cd currently only has total search counts)
• Update scd to use aggregated values from cd instead of aggregating from main_summary
• Validate search counts across engine/source
  • Whole table won't be an exact match (see below) but total counts per client/engine/source should match

Some things to note:

• The resulting table won't be an exact match because cd is per client and scd is per client/engine/source.
  • For most clients this doesn't matter but an example where it does mater is if a client id is shared across multiple browsers. e.g. a client_id is used across three different versions of firefox and they all have different search activity. scd currently will recognize the different versions, e.g. all ad_clicks were done on one version, the ad_clicks will be attributed to that version. Whereas cd will get the same ad_click count but attribute that to the mode app version across the client
    • This is a subtle difference that I think is unlikely to affect conclusions from analysis but worth noting
    • In any case, total counts per client/engine/source should match, just for some other dimensions they may be different
• Since we need to add new fields to cd, the new scd query won't work for past dates unless cd is backfilled
  • This means the new scd can't be backfilled for past dates but that's not likely to be needed
  • Maybe this warrants a table version bump and the view can union the versions based on date but this seems a bit clunky

Comment 1

[Jeff Klukas [:klukas] (UTC-4)]

Since we need to add new fields to cd, the new scd query won't work for past dates unless cd is backfilled

A long-term cd backfill is probably doable and warranted for this case. I was able to run a complete clients_last_seen backfill using reserved slots, and it finished within a week. clients_daily may be somewhat more intensive, but I think this is tractable.

The resulting table won't be an exact match because cd is per client and scd is per client/engine/source

It is probably possible to aggregate these in cd as key/value arrays aggregated per engine and source, so that we could end up presenting a view that has the same grain as scd by unnesting.

Comment 2

[Ben Wu [:benwu]]

(In reply to Jeff Klukas [:klukas] (UTC-4) from comment #1)

It is probably possible to aggregate these in cd as key/value arrays aggregated per engine and source, so that we could end up presenting a view that has the same grain as scd by unnesting.

The issue here i with the ungrouped columns like country, app_version, os, etc. So currently with scd for a single client you can get source=newtab, search_count=4, app_version=88.0 along with source=urlbar, search_count=3, app_version=89.0 because the app_version is the mode value after grouping by engine and source. Deriving from clients_daily, app_version will always be the same for a given client. The source and engine combinations and the search counts will be the same. I don't see a way around that due to the structure of clients daily. Does that make sense? Here's an example https://sql.telemetry.mozilla.org/queries/80549/source

Comment 3

[Jeff Klukas [:klukas] (UTC-4)]

(In reply to Ben Wu [:benwu] from comment #2)

(In reply to Jeff Klukas [:klukas] (UTC-4) from comment #1)

It is probably possible to aggregate these in cd as key/value arrays aggregated per engine and source, so that we could end up presenting a view that has the same grain as scd by unnesting.

The issue here i with the ungrouped columns like country, app_version, os, etc. So currently with scd for a single client you can get source=newtab, search_count=4, app_version=88.0 along with source=urlbar, search_count=3, app_version=89.0 because the app_version is the mode value after grouping by engine and source. Deriving from clients_daily, app_version will always be the same for a given client. The source and engine combinations and the search counts will be the same. I don't see a way around that due to the structure of clients daily. Does that make sense? Here's an example https://sql.telemetry.mozilla.org/queries/80549/source

Thanks for the explanation. Yes, this makes sense!

This is a subtle difference that I think is unlikely to affect conclusions from analysis but worth noting

I agree. It seems unlikely this will materially affect analyses. I'm glad you've documented the issue here for future reference if we do see some discrepancies.

Comment 4

[GitHub Bugzilla PR Linker]

Attachment: Link to GitHub pull-request: https://github.com/mozilla/bigquery-etl/pull/2127

Comment 5

[Ben Wu [:benwu]]

Here are the results of some light validation that somewhat quantifies the differences in the results https://sql.telemetry.mozilla.org/dashboard/search-from-clients-daily-vs-main-summary-comparison

Client counts and search counts per engine and source are identical so it correctly counts the same searches.

With respect to the differences in some dimensions, the totals grouped by country and os show that the differences are very small since only a small number of clients are affected. In my opinion, the differences are negligible and won't affect results.

Comment 6

[Ben Wu [:benwu]]

Comment from discussion with Xuan:

The client count from clients_daily vs. main_summary for null engine and source has a large difference. That's due to the search_clients_daily query adding a null search for clients with no search activity so that all clients can be represented in the table. The difference is that in the main_summary version, it looks at the pings, so if a client a has multiple pings in a day and one of them doesn't have search activity, a null search is added for that client even though they may have search activity in another ping. clients_daily aggregates all the search activity together for a day so it will only create a null search if no pings for the client have search activity.

The clients_daily version seems "more correct" since null searches only exist for clients who don't have any search activity for the day.

Comment 7

[GitHub Bugzilla PR Linker]

Attachment: Link to GitHub pull-request: https://github.com/mozilla/bigquery-etl/pull/2151

Comment 8

[Ben Wu [:benwu]]

This is now done and backfilled to 2018-10-30