В этой статье рассматривается вопрос сбора статистики в свою базу данных по страницам сайта - сколько показов, кликов, позиция и CTR.
В итоге эти данные будут храниться у нас по дням и можно использовать их для аналитики по страницам сайта.
Сначала получаем токен доступа по инструкции https://yandex.ru/dev/webmaster/doc/ru/tasks/how-to-get-oauth
Примечания:
Чтобы выполнить основной запрос, нам необходимы userID и hostID.
Получаем userID https://yandex.ru/dev/webmaster/doc/ru/reference/user
https://api.webmaster.yandex.net/v4/user
с http header Authorization: OAuth
Получаем нужный хост https://yandex.ru/dev/webmaster/doc/ru/reference/hosts
https://api.webmaster.yandex.net/v4/user/{user-id}/hosts
с http header Authorization: OAuth
Примечание: эти запросы можно сделать через Postman или Тест форму запросов API на /asapi
Делаем запрос общей статистики по страницам
https://yandex.ru/dev/webmaster/doc/ru/reference/host-query-analytics
Лучше сначала добиться правильной работы метода через тест форму отправки запроса АПИ.
Body запроса (описание параметров в документации):
{
"offset": 0,
"limit": 50,
"device_type_indicator": "ALL",
"text_indicator": "URL",
"region_ids": [
],
"filters": {
"text_filters": [
],
"statistic_filters": [
{
"statistic_field": "IMPRESSIONS",
"operation": "GREATER_THAN",
"value": "0",
"from": "2025-03-01",
"to": "2025-03-02"
}
]
}
}
Если мы в итоге получаем JSON с данными - все хорошо, можем приступать к накоплению данных.
Схема такая:
В /ssysp создаем задачу, котоорая запускается раз в неделю с такой SP:
CREATE PROCEDURE [dbo].[sync_webmasterPageStat]
@code nvarchar(128),
@result bit output,
@msg nvarchar(max) output
AS
BEGIN
select 'apirequest' type, 'webmasterPageStat' code
set @result = 1
set @msg = 'ok'
END
Процедура Request для подготовки запроса:
CREATE PROCEDURE [dbo].[api_webmasterPageStat_request]
@parameters ExtendedDictionaryParameter READONLY,
@username nvarchar(32)
AS
BEGIN
declare @token nvarchar(max) = dbo.as_setting('YandexWebmasterToken', '')
declare @hostID nvarchar(max) = dbo.as_setting('YandexWebmasterHostID', '')
declare @userID nvarchar(max) = dbo.as_setting('YandexWebmasterUserID', '')
declare @url nvarchar(max) = 'https://api.webmaster.yandex.net/v4/user/'+@userID+'/hosts/'+@hostID+'/query-analytics/list'
-- SELECT 1
select '' Msg, 1 Result, @url Url
-- SELECT 2 PARAMETERS -
select 'Authorization' name, 'OAuth '+@token value, 'header' [type]
union
select '' name, '
{
"offset": 0,
"limit": 500,
"device_type_indicator": "ALL",
"text_indicator": "URL",
"region_ids": [
],
"filters": {
"text_filters": [
],
"statistic_filters": [
{
"statistic_field": "IMPRESSIONS",
"operation": "GREATER_THAN",
"value": "0",
"from": "'+ FORMAT(dateadd(day,-10, getdate()), 'yyyy-MM-dd')+'",
"to": "'+FORMAT(dateadd(day,-3, getdate()), 'yyyy-MM-dd')+'"
}
]
}
}
' value, 'json' type
END
Создаем и заполняем настройки для токена, hostID и userID в /settings.
Процедура Response для обработки отклика:
CREATE PROCEDURE [dbo].[api_webmasterPageStat_response]
@response nvarchar(max),
@parameters ExtendedDictionaryParameter READONLY,
@username nvarchar(32)
AS
BEGIN
exec [dbo].[webmaster_processPageStat] @json = @response
-- SELECT 1
select '' Msg, 1 Result, '' Response
END
В ней мы вызываем процедуру webmaster_processPageStat, которая разбирает JSON и преобразует формат сохранения в url, date, impressions, clicks, ctr, position.
Создадим таблицу хранения статистики:
CREATE TABLE [dbo].[kw_yandexWebmasterStat](
[id] [int] IDENTITY(1,1) NOT NULL,
[url] [nvarchar](256) NULL,
[date] [date] NULL,
[impressions] [int] NULL,
[clicks] [int] NULL,
[ctr] [decimal](18, 2) NULL,
[position] [decimal](18, 2) NULL,
CONSTRAINT [PK_kw_yandexWebmasterStat] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Процедура для разбора JSON webmaster_processPageStat и сохранения в созданную таблицу:
CREATE PROCEDURE [dbo].[webmaster_processPageStat]
@json nvarchar(max)
AS
BEGIN
execute as_print @json
IF OBJECT_ID('tempdb..#pages') IS NOT NULL DROP TABLE pages
CREATE TABLE #pages ( url nvarchar(512) , stat nvarchar(max))
insert into #pages([url], stat)
SELECT JSON_VALUE(value, '$.text_indicator.value') url, cast(JSON_QUERY(value, '$.statistics') as nvarchar(max)) stat
FROM OPENJSON(@json, '$.text_indicator_to_statistics')
IF OBJECT_ID('tempdb..#pages2') IS NOT NULL DROP TABLE pages2
CREATE TABLE #pages2 ( url nvarchar(512) collate Cyrillic_General_CI_AS, date date, field nvarchar(max), value decimal(18,2))
DECLARE @url nvarchar(256), @stat nvarchar(max)
DECLARE cur CURSOR FOR
Select url, stat FROM #pages
OPEN cur
FETCH NEXT FROM cur INTO @url, @stat
WHILE @@FETCH_STATUS = 0
BEGIN
insert #pages2 (url, date, field, value)
select @url, JSON_VALUE(value, '$.date') date, JSON_VALUE(value, '$.field') field, JSON_VALUE(value, '$.value') value
FROM OPENJSON(@stat, '$')
FETCH NEXT FROM cur INTO @url, @stat
END
CLOSE cur
DEALLOCATE cur
insert into kw_yandexWebmasterStat(url, date, impressions, clicks, ctr,position)
select url, date,
(select top 1 try_cast(value as int) from #pages2 t2 where url = t1.url and date = t2.date and field = 'IMPRESSIONS') impressions,
(select top 1 try_cast(value as int) from #pages2 t2 where url = t1.url and date = t2.date and field = 'CLICKS') clicks,
(select top 1 value from #pages2 t2 where url = t1.url and date = t2.date and field = 'CTR') ctr,
(select top 1 value from #pages2 t2 where url = t1.url and date = t2.date and field = 'POSITION') position
from ( select distinct url, date from #pages2) t1
where not exists(select id from kw_yandexWebmasterStat yws where yws.url = t1.url and yws.date = t1.date)
drop table #pages
drop table #pages2
END
Теперь наши данные будут сохраняться в таблице
Далее необходимо задействовать эти данные для вывода таблиц статистики. Мы используем данную интеграцию в рамках relmap модуля:
Выводим на странице отдельного материала контента:
Здесь идет группировка по неделям (хотя данные собираются по дням).
И выводим общую статистику по страницам (с вложенным отчетом по конкретной странице):
SQL запрос под подобную статистику:
select url, sum(impressions) impressions, sum(clicks) clicks, avg(ctr) ctr, avg(position) position
from kw_yandexWebmasterStat
where date > dateadd(month , -1, getdate())
group by url
order by impressions desc
Таким образом можно собирать себе данные по состоянию ваших страниц в Яндексе.
Это позволит понимать динамику движения страниц в Яндексе - как кликают, как часто показывают, какая позиция в среднем по странице.
Есть аналогичный отчет по ключам - т.е. критерием разделения будет ключевой запрос, а не URL страницы.