Get quantitative data on how the search API is currently used in Wikidata
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Ifrahkhanyaree_WMDE
	Dec 23 2024, 11:15 AM

Description

In order to get a better understanding of the use cases, we need to find data to answer the following questions:

What queries are run after wbsearchentities by the same user agent/IP in the same "session"?
What is the % new user agents per month i.e. growth of user agents over time?
Search calls per month
How many times do people reformulate their search queries?
How many people do a simple text search AND wikibase cirrussearch keywords (i.e. haswbstatement)
Dist of simple v "complex" (wikibase cirrussearch keywords) searches

Related Objects

Mentioned In: T392063: [Analytics] Action API search metrics
Mentioned Here: T383135: Some search entries in wmf.webrequest have their query appended to their uri_path

Event Timeline

Ifrahkhanyaree_WMDE created this task.Dec 23 2024, 11:15 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 23 2024, 11:15 AM

Ifrahkhanyaree_WMDE moved this task from Incoming tickets outside WPP to Backlog on the Wikibase Reuse Team board.Jan 2 2025, 1:56 PM

Ifrahkhanyaree_WMDE updated the task description. (Show Details)Jan 3 2025, 10:16 AM

AndrewTavis_WMDE subscribed.Jan 3 2025, 11:49 AM

AndrewTavis_WMDE claimed this task.Jan 6 2025, 1:06 PM

AndrewTavis_WMDE added a project: Wikidata Analytics (Kanban).

AndrewTavis_WMDE moved this task from Incoming to In Progress on the Wikidata Analytics (Kanban) board.

AndrewTavis_WMDE added a subscriber: karapayneWMDE.

Added this to the kanban board and assigned myself as discussed with @Ifrahkhanyaree_WMDE as I'll be working on the last two points:

How many people do a simple text search AND wikibase cirrussearch keywords (i.e. haswbstatement)
Dist of simple v "complex" (wikibase cirrussearch keywords) searches

The number of search calls in a month will be calculated along the way in case that's also needed. All of the numbers are coming for December 2024 :)

Maintenance_bot added a project: Wikidata.Jan 6 2025, 1:30 PM

WMDE-leszek subscribed.Jan 6 2025, 2:23 PM

Ifrahkhanyaree_WMDE edited projects, added Wikibase REST API (WPP); removed Wikibase REST API Search.Jan 7 2025, 1:13 PM

Ifrahkhanyaree_WMDE moved this task from Backlog to Happening now on the Wikibase REST API (WPP) board.

Initial report for feedback on this, with the following being totals for December 2024. The work took a bit more time as initial explorations honed in on some search queries that were a malformed subset of the rest of the searches. T383135: Some search entries in wmf.webrequest have their query appended to their uri_path was made to report the bug in question, and the following does include both the normal and malformed searches.

Note that the work below can be found in T382698_wd_search_exploration.ipynb

Total Searches

Point 3 above

SELECT
    count(*) AS total_searches

FROM
    wmf.webrequest

WHERE
    year = 2024
    AND month = 12
    AND webrequest_source = 'text'
    AND normalized_host.project_family = 'wikidata'
    AND (
        (
            uri_path = '/w/index.php'
            AND uri_query LIKE '?search%'
        )
        -- Accounting more malformed search queries: %3F is '?'.
        OR uri_path LIKE '/w/index.php%3Fsearch%'
    )
;

Result: 12,596,105

Devices Doing Simple and Complex Searches

Point 5 above

WITH wd_search_ips_and_uris AS (
    SELECT
        ip,
        uri_path,
        uri_query

    FROM
        wmf.webrequest

    WHERE
        year = 2024
        AND month = 12
        AND normalized_host.project_family = 'wikidata'
        AND (
            (
                uri_path = '/w/index.php'
                AND uri_query LIKE '?search%'
            )
            -- Accounting more malformed search queries: %3F is '?'.
            OR uri_path LIKE '/w/index.php%3Fsearch%'
        )
),

simple_search_ips AS (
    SELECT DISTINCT
        ip

    FROM
        wd_search_ips_and_uris

    WHERE
        (
            uri_path = '/w/index.php'
            AND uri_query LIKE '?search%'
            AND uri_query NOT LIKE '%haswbstatement%'
            AND uri_query NOT LIKE '%inlabel%'
            AND uri_query NOT LIKE '%incaption%'
            AND uri_query NOT LIKE '%wbstatementquantity%'
            AND uri_query NOT LIKE '%hasdescription%'
            AND uri_query NOT LIKE '%haslabel%'
            AND uri_query NOT LIKE '%hascaption%'
        )
        OR (
            uri_path LIKE '/w/index.php%3Fsearch%'
            AND uri_path NOT LIKE '%haswbstatement%'
            AND uri_path NOT LIKE '%inlabel%'
            AND uri_path NOT LIKE '%incaption%'
            AND uri_path NOT LIKE '%wbstatementquantity%'
            AND uri_path NOT LIKE '%hasdescription%'
            AND uri_path NOT LIKE '%haslabel%'
            AND uri_path NOT LIKE '%hascaption%'
        )
),

complex_search_ips AS (
    SELECT DISTINCT
        ip

    FROM
        wd_search_ips_and_uris

    WHERE
        (
            uri_path = '/w/index.php'
            AND uri_query LIKE '?search%'
            AND (
                uri_query LIKE '%haswbstatement%'
                OR uri_query LIKE '%inlabel%'
                OR uri_query LIKE '%incaption%'
                OR uri_query LIKE '%wbstatementquantity%'
                OR uri_query LIKE '%hasdescription%'
                OR uri_query LIKE '%haslabel%'
                OR uri_query LIKE '%hascaption%'
            )
        )
        OR (
            uri_path LIKE '/w/index.php%3Fsearch%'
            AND (
                uri_path LIKE '%haswbstatement%'
                OR uri_path LIKE '%inlabel%'
                OR uri_path LIKE '%incaption%'
                OR uri_path LIKE '%wbstatementquantity%'
                OR uri_path LIKE '%hasdescription%'
                OR uri_path LIKE '%haslabel%'
                OR uri_path LIKE '%hascaption%'
            )
        )
)

SELECT
    count(s.ip) AS total_simple_and_complex_searchers

FROM
    simple_search_ips AS s

INNER JOIN
    complex_search_ips AS c

ON
    s.ip = c.ip
;

Result: 2,029

Simple vs. Complex Search Distribution

Point 6 above

SELECT
    sum(
        CASE
            WHEN
                (
                    uri_path = '/w/index.php'
                    AND uri_query LIKE '?search%'
                    AND uri_query NOT LIKE '%haswbstatement%'
                    AND uri_query NOT LIKE '%inlabel%'
                    AND uri_query NOT LIKE '%incaption%'
                    AND uri_query NOT LIKE '%wbstatementquantity%'
                    AND uri_query NOT LIKE '%hasdescription%'
                    AND uri_query NOT LIKE '%haslabel%'
                    AND uri_query NOT LIKE '%hascaption%'
                )
                OR (
                    uri_path LIKE '/w/index.php%3Fsearch%'
                    AND uri_path NOT LIKE '%haswbstatement%'
                    AND uri_path NOT LIKE '%inlabel%'
                    AND uri_path NOT LIKE '%incaption%'
                    AND uri_path NOT LIKE '%wbstatementquantity%'
                    AND uri_path NOT LIKE '%hasdescription%'
                    AND uri_path NOT LIKE '%haslabel%'
                    AND uri_path NOT LIKE '%hascaption%'
                )
            THEN
                1
            ELSE
                0
        END
    ) AS total_simple_searches,

    sum(
        CASE
            WHEN
                (
                    uri_path = '/w/index.php'
                    AND uri_query LIKE '?search%'
                    AND (
                        uri_query LIKE '%haswbstatement%'
                        OR uri_query LIKE '%inlabel%'
                        OR uri_query LIKE '%incaption%'
                        OR uri_query LIKE '%wbstatementquantity%'
                        OR uri_query LIKE '%hasdescription%'
                        OR uri_query LIKE '%haslabel%'
                        OR uri_query LIKE '%hascaption%'
                    )
                )
                OR (
                    uri_path LIKE '/w/index.php%3Fsearch%'
                    AND (
                        uri_path LIKE '%haswbstatement%'
                        OR uri_path LIKE '%inlabel%'
                        OR uri_path LIKE '%incaption%'
                        OR uri_path LIKE '%wbstatementquantity%'
                        OR uri_path LIKE '%hasdescription%'
                        OR uri_path LIKE '%haslabel%'
                        OR uri_path LIKE '%hascaption%'
                    )
                )
            THEN
                1
            ELSE
                0
        END
    ) AS total_complex_searches

FROM
    wmf.webrequest

WHERE
    year = 2024
    AND month = 12
    AND webrequest_source = 'text'
    AND normalized_host.project_family = 'wikidata'
    AND (
        (
            uri_path = '/w/index.php'
            AND uri_query LIKE '?search%'
        )
        -- Accounting more malformed search queries: %3F is '?'.
        OR uri_path LIKE '/w/index.php%3Fsearch%'
    )
;

Result: total_simple_searches: 12,414,591 (98.56%); total_complex_searches: 181,514 (1.44%)
Check: 12414591 + 181514 == 12596105 ✅

Let me know if the above works! :)

AndrewTavis_WMDE moved this task from In Progress to In Review on the Wikidata Analytics (Kanban) board.Jan 8 2025, 9:39 AM

This is grand, thanks so much!

AndrewTavis_WMDE awarded a token.Jan 8 2025, 2:26 PM

Ifrahkhanyaree_WMDE moved this task from Backlog to Sprint 36 on the Wikibase Reuse Team board.Jan 8 2025, 2:34 PM

Ifrahkhanyaree_WMDE edited projects, added Wikibase Reuse Team (Sprint 36); removed Wikibase Reuse Team.

Ifrahkhanyaree_WMDE moved this task from To Do to Product Verification on the Wikibase Reuse Team (Sprint 36) board.

2025 w3 review: all good!