Профессия: Аналитик данных

Преобразуйте диапазон в таблицу Excel и присвойте ей имя Orders. Таблица расширяется при добавлении строк, а структурированные ссылки понятнее адресов вроде K2:K4816. Названия исходных колонок можно оставить английскими, чтобы они совпадали с CSV и кодом. Русские пояснения поместите в словарь. Не объединяйте ячейки внутри данных: объединение мешает фильтрации, сортировке и автоматической обработке.

Проверьте типы данных. order_id и quantity — целые числа, денежные поля — числа, order_date — дата. Формат «денежный» меняет внешний вид, но не превращает текст в число. Если сумма не считается или сортировка идёт странно, посмотрите содержимое строки формул и примените явное преобразование. Случайные пробелы и неразрывные пробелы тоже могут мешать распознаванию.

Формулы на уровне заказа

Добавьте столбец net_revenue. В таблице Excel формула одинаково применится ко всем строкам:

=[@gross]-[@discount]-[@refund]

Следующий столбец contribution показывает вклад до маркетинга:

=[@net_revenue]-[@cost]-[@shipping]

Чтобы выделить завершённый заказ, добавьте completed_flag:

=IF([@status]="completed",1,0)

В русской локали IF называется ЕСЛИ, а разделителем аргументов часто служит точка с запятой. Тогда последняя формула выглядит так:

=ЕСЛИ([@status]="completed";1;0)

Здесь и далее английские имена функций помогают сопоставлять примеры с документацией. Если редактор не принимает формулу, сначала проверьте имя функции и разделитель аргументов. Не меняйте смысл вычисления, пытаясь добиться отсутствия сообщения об ошибке.

Поле month можно получить как первое число месяца и отображать форматом год-месяц. Это сохраняет тип даты и облегчает сортировку. Если нужен текстовый ключ для объединения с marketing, используйте YEAR и MONTH либо Power Query. При применении TEXT к дате помните, что коды формата зависят от локали. Не смешивайте текст «2025-01» и дату 01.01.2025 в одной колонке объединения.

Проверяем формулы на нескольких строках

Найдите завершённый заказ без возврата и пересчитайте его вручную. Если gross = 2 000, discount = 100, refund = 0, cost = 1 100, shipping = 180, то net_revenue = 1 900, contribution = 620. Это отдельный условный пример. Он помогает проверить формулу, но не задаёт контрольную сумму всех данных.

Затем проверьте возвратный заказ с теми же исходными значениями и refund = 1 900. Чистая выручка станет нулевой, а вклад — минус 1 280 рублей. Отрицательное значение соответствует нашему допущению об утраченной себестоимости и расходе доставки. Если в вашем результате появляется ноль, где-то было применено необоснованное ограничение отрицательных значений.

Проверьте отменённый заказ. Денежные поля в комплекте равны нулю, completed_flag тоже равен нулю. Количество созданных заказов при этом увеличивается на единицу. Контроль трёх разных ситуаций полезнее, чем просмотр сотни случайных строк с одинаковым поведением.

Сводная таблица без ловушек

После очистки двенадцати точных повторов и нормализации каналов создайте сводную по таблице Orders [10]. Поместите month в строки, channel — в столбцы, net_revenue и contribution — в значения. Проверьте, что для денежных полей выбрана сумма, а не количество. Если Excel предложил количество, часть значений могла остаться текстом. Вернитесь к исходному типу и исправьте его.

Создайте отдельную сводную для числа заказов и суммы completed_flag. Число строк после очистки должно быть 4 815, завершённых заказов — 4 428. Чистая выручка за полугодие равна 9 453 295 рублей, вклад до маркетинга — 1 970 070. Сравните эти суммы с expected.json. При несовпадении ищите первый этап расхождения: импорт, удаление дублей, фильтр статусов или формула.

Не добавляйте spend к каждой строке заказа. Сначала получите сводку с одной строкой на месяц и канал, затем присоедините маркетинг с той же детализацией. Например, январские 97 000 рублей по social должны попасть в итог января один раз. Если присоединить их к 175 заказам и сложить, получится 16 975 000 рублей. Это ошибка модели, которую нельзя исправить форматированием.

В итоговой сводке вычисляйте маржинальность делением общей суммы вклада на общую сумму выручки. Для простой небольшой книги можно вынести формулу за пределы сводной и явно сослаться на итоговые значения. В модели данных используйте меру с делением сумм. В обоих случаях проверьте результат на примере с разными весами из предыдущей главы.

Поиск по справочнику

Чтобы добавить категорию товара, нужно найти product_id в справочнике. В современных версиях Excel доступна XLOOKUP, в русской локали — ПРОСМОТРX; её наличие зависит от версии. Для старой версии можно использовать точный поиск через INDEX и MATCH. Пример ниже предполагает таблицу Products:

=INDEX(Products[category],

MATCH([@product_id],Products[product_id],0))

Ноль в MATCH означает точное совпадение. Приближённый поиск без специальных условий может вернуть соседний товар. Если справочник содержит два одинаковых product_id, поиск скроет проблему, выбрав одно совпадение. Перед поиском всегда проверяйте уникальность ключа справочника.

Не оборачивайте всё в IFERROR с пустой строкой. Так вы избавитесь от видимых ошибок, но потеряете сигнал о неизвестном товаре. Сначала создайте колонку «Ключ найден» и посчитайте несовпадения. Затем решите, какие из них блокируют расчёт. Пустое значение в отчёте должно быть результатом осмысленного правила, а не способом скрыть дефект.

Практическая работа 4. Первая записка

Постройте помесячную сводку и линейный график net_revenue. Рядом покажите contribution. На одном графике эти показатели могут сильно различаться по масштабу; безопаснее использовать два согласованных графика с одинаковой осью времени. Подпишите единицы измерения и не добавляйте декоративную трёхмерность.

Напишите три предложения. Первое описывает наблюдение, второе указывает, что ещё нужно проверить, третье формулирует следующий шаг. Например: «Во втором квартале вклад до маркетинга снизился при более высокой выручке, чем в начале периода. Пока неизвестно, связан ли результат с отдельным каналом или с общим изменением расходов. Далее сравним структуру каналов и составляющие вклада». Не объявляйте рекламу причиной до соответствующей проверки.

Сохраните книгу и убедитесь, что другой человек сможет определить источник каждой итоговой цифры. Если важная сумма введена вручную, подпишите её как ручной параметр и объясните происхождение. Рабочая книга должна помогать искать ошибку, а не только показывать окончательный ответ.

Глава 5.

Power

Query

и повторяемая очистка

Записываем последовательность преобразований

Когда выгрузка приходит регулярно, ручная очистка становится источником расхождений. Один раз вы удалили пробелы, другой раз забыли, третий раз отсортировали только часть диапазона. Power Query сохраняет шаги обработки и позволяет применять их к следующему файлу. Смысл инструмента — в воспроизводимости, а не в количестве освоенных кнопок.

Импортируйте orders_raw.csv через меню данных. В редакторе проверьте разделитель и кодировку. Сначала задайте типы, затем удалите точные повторы всех исходных колонок, приведите channel к нижнему регистру и удалите пробелы по краям. Последовательность и названия шагов сохраните так, чтобы через месяц было понятно, зачем нужен каждый из них.

Удаление дублей только по order_id было бы допустимо после проверки отсутствия конфликтующих версий. В нашем упражнении безопаснее удалить полные повторы, а потом отдельно проверить уникальность order_id. Если уникальность не достигнута, процесс должен сообщить о конфликте. Нельзя молча считать его очередным техническим повтором.

Типы и локаль

Локаль задаёт правила интерпретации текста. Дата 03/04/2025 в разных соглашениях может означать март или апрель. Для учебного ISO-формата год-месяц-день неоднозначность меньше, но реальный источник может прислать другой вариант. Используйте преобразование типа с нужной локалью и проверьте минимальную и максимальную даты после него.

Для денежных сумм исходного проекта подходит целое число. В реальной выгрузке с копейками выбирайте подходящий точный тип и согласуйте округление. Если данные представлены текстом «1 250,50», нужно убрать разделитель тысяч и распознать десятичную запятую. Простая замена всех запятых точками до разбора CSV может разрушить разделение колонок.

Ошибки преобразования не удаляйте без просмотра. Создайте отдельный запрос с ошибочными строками и посчитайте их. Если это обязательная дата, такие строки могут менять период отчёта. Если это необязательное описание, общие суммы могут сохраниться. Действие выбирается по смыслу поля и объёму проблемы.

Объединение и добавление

Добавление запросов складывает строки одинаковой структуры друг под другом. Так можно объединить январскую и февральскую выгрузки заказов. Объединение запросов сопоставляет строки по ключу и добавляет колонки. Так к заказу присоединяют город из customers. Эти операции решают разные задачи, хотя обе увеличивают содержимое результата.

Перед объединением заказов с customers запишите ожидаемое отношение: много заказов к одному покупателю. После операции число строк заказов не должно измениться. Затем посчитайте ненайденные ключи. В учебном комплекте их нет. Если строки выросли, проверьте дубли в справочнике; если сократились, возможно, выбран внутренний тип объединения и часть заказов потеряна.

Для расходов сначала сгруппируйте заказы по month и channel. Суммируйте net_revenue, contribution и completed_flag, посчитайте заказы. Затем объедините агрегат с marketing по двум полям. Порядок выбора полей должен соответствовать друг другу. Совпадение только по channel размножит каждую месячную строку на шесть месяцев расходов.

Обновление и проверка результата

Сохраните путь к файлу так, чтобы его можно было изменить в одном месте. При передаче книги коллеге локальный путь вашего компьютера может оказаться недоступным. В инструкции укажите, где выбрать новый источник. Не храните пароли в видимых ячейках и не включайте их в опубликованную книгу.

После обновления повторите контроль числа строк, периода и денежных итогов. Факт успешной загрузки означает только отсутствие технической ошибки выполнения. Источник мог прислать пустой файл, другой месяц или другую валюту. Поэтому у отчёта должны быть содержательные проверки, а не только зелёный индикатор обновления.

Создайте лист «Контроль обновления». Покажите время обработки, максимальную дату заказа, исходное и итоговое число строк, число конфликтов ключа и итог выручки. При использовании реальных поступающих данных дополните его датой формирования источника. Дата запуска Excel и свежесть данных — разные вещи.

Практическая работа 5. Испытайте обновление

Сделайте копию сырого CSV и добавьте ещё один точный повтор. Обновите запрос. Итоговое число уникальных заказов должно остаться 4 815, а число удалённых повторов — вырасти с двенадцати до тринадцати. После проверки верните исходный файл, чтобы дальнейшие контрольные результаты относились к комплекту.

Второе испытание: измените название обязательной колонки в копии источника. Обработка должна завершиться заметной ошибкой или проверкой схемы. Если она выдаёт старый результат без предупреждения, пользователь может принять его за свежий. В инструкции опишите, где видно неудачное обновление и как вернуть рабочий источник.

Итог этапа — не только сводка магазина. У вас появился процесс, который можно повторить и объяснить. Дальше тот же принцип перенесём в SQL: запрос должен ясно задавать исходные строки, условия и уровень детализации результата.

Глава 6.

SQL

от выборки к проверяемой сводке

Подключаем учебную базу

Откройте shop.sqlite в редакторе, который умеет работать с SQLite. Можно использовать любой доступный графический клиент с таким подключением. Укажите файл существующей базы, а не создание новой пустой. Найдите таблицы orders, customers, products, marketing и traffic. Перед выполнением запроса проверьте имя подключения: одинаковые названия таблиц могут находиться в разных файлах.

Если графического клиента нет, запросы можно выполнять через Python, где модуль sqlite3 входит в стандартную библиотеку. В этой главе достаточно знать способ запуска; устройство Python подробно разберём позже. Создайте рядом с базой файл run_sql.py:

from pathlib import Path

import sqlite3

root = Path(__file__).resolve().parent

database = root / 'shop.sqlite'

if not database.exists():

raise FileNotFoundError(database)

with sqlite3.connect(database) as con:

rows = con.execute(

'SELECT COUNT(*) FROM orders'

).fetchall()

print(rows)

Результат содержит число 4815. Скобки вокруг него — форма представления списка строк, а не часть данных. Если получено сообщение no such table, проверьте путь: SQLite может создать новый файл при подключении к несуществующему пути. Полезно сначала убедиться, что файл существует, и только потом открывать его.

SQL описывает, какой результат вы хотите получить. В SELECT перечисляются выходные поля, в FROM — источник, в WHERE — условия для строк. Порядок строк без ORDER BY не гарантирован. Даже если первые пять запусков показывают одинаковую последовательность, рассчитывать на неё нельзя [2].

Выбираем нужные поля

Начните с запроса, который показывает несколько заказов и не меняет данные:

SELECT

order_id,

order_date,

channel,

status,

gross,

discount,

refund

FROM orders

ORDER BY order_id

LIMIT 10;

Каждая запятая разделяет выражения. Последнее поле перед FROM пишется без запятой. Точка с запятой завершает запрос. Ключевые слова можно писать в разном регистре; единый стиль облегчает чтение. В готовых запросах перечисляйте поля явно: SELECT * полезен для первичного осмотра, но делает результат зависимым от будущих изменений схемы.

Добавим рассчитанный показатель и дадим ему имя через AS:

SELECT

order_id,

gross - discount - refund AS net_revenue,

gross - discount - refund

- cost - shipping AS contribution

FROM orders

ORDER BY order_id

LIMIT 10;

Псевдоним делает вывод понятным и позволяет использовать результат в следующем уровне запроса. Не рассчитывайте, что имя net_revenue будет доступно в соседнем выражении того же SELECT во всех системах. Для переносимого кода повторите выражение или вынесите расчёт в подзапрос.

Фильтруем даты и статусы

Получим завершённые заказы первого квартала:

SELECT

order_id,

order_date,

channel,

gross - discount - refund AS net_revenue

FROM orders

WHERE status = 'completed'

AND order_date >= '2025-01-01'

AND order_date < '2025-04-01'

ORDER BY order_date, order_id;

Полуоткрытый интервал включает начало и исключает следующую границу. Он удобен и для дат, и для временных меток. Условие «до 31 марта 00:00» при наличии времени исключило бы почти весь последний день. В учебной базе даты хранятся ISO-текстом, поэтому их лексикографический порядок совпадает с календарным. Для произвольного текста это утверждение неверно.

Если нужны два канала, используйте IN. Если объединяете AND и OR, ставьте скобки вокруг смысловой группы. Иначе условие может допустить любой social-заказ вне периода:

SELECT order_id, channel, order_date

FROM orders

WHERE channel IN ('search', 'social')

AND order_date >= '2025-04-01'

AND order_date < '2025-07-01'

ORDER BY order_id;

Текстовые значения заключаются в одинарные кавычки. Равенство в SQL записывается одним знаком =. Для отсутствующих значений применяют IS NULL, а не = NULL. NULL обозначает неизвестное или отсутствующее значение и участвует в логике иначе, чем обычное число или пустая строка.

Считаем итоги

Проверим число строк, завершённые заказы и денежные суммы:

SELECT

COUNT(*) AS orders,

SUM(CASE WHEN status = 'completed'

THEN 1 ELSE 0 END) AS completed,

SUM(gross - discount - refund) AS net_revenue,

SUM(gross - discount - refund

- cost - shipping) AS contribution

FROM orders;

Вы получите 4 815, 4 428, 9 453 295 и 1 970 070 соответственно. CASE проверяет условие для каждой строки. SUM складывает полученные значения. COUNT(*) считает строки, а COUNT(column) — строки, где указанная колонка не равна NULL. В наших обязательных ключах пропусков нет, но различие станет важным при внешнем объединении.

SUM по пустому набору в SQLite возвращает NULL. Если по смыслу нужна сумма ноль, используйте COALESCE(SUM(...), 0), предварительно убедившись, что отсутствие строк действительно означает отсутствие событий, а не сбой загрузки. Замена неизвестного значения нулём не должна скрывать проблему полноты данных.

Теперь сгруппируем результат по каналам:

SELECT

channel,

COUNT(*) AS orders,

SUM(gross - discount - refund) AS net_revenue,

SUM(gross - discount - refund

- cost - shipping) AS contribution

FROM orders

GROUP BY channel

ORDER BY net_revenue DESC, channel;

После GROUP BY одна строка результата описывает канал. Все выбранные поля должны либо входить в группировку, либо вычисляться агрегатной функцией. SQLite допускает некоторые более свободные конструкции, но использовать их для учебного отчёта не стоит: произвольная дата из группы рядом с суммой всех заказов вводит читателя в заблуждение.

WHERE

и

HAVING

WHERE отбирает исходные строки, HAVING — группы после агрегации. Чтобы найти каналы с выручкой выше заданной суммы, условие по сумме нужно поставить в HAVING:

SELECT

channel,

SUM(gross - discount - refund) AS net_revenue

FROM orders

WHERE status = 'completed'

GROUP BY channel

HAVING SUM(gross - discount - refund) > 2000000

ORDER BY net_revenue DESC;

Запрос не означает «заказы дороже двух миллионов». Он сначала выбирает завершённые заказы, складывает их внутри канала и затем оставляет группы, чей итог превышает порог. Разница между фильтром строк и фильтром групп — частая тема собеседований, потому что она отражает понимание уровня расчёта.

Практическая работа 6. Напишите первые запросы

Получите все отменённые заказы за июнь, затем число таких заказов по каждому каналу. Отдельно найдите десять завершённых заказов с наибольшей чистой выручкой. При равенстве суммы используйте order_id как дополнительный критерий сортировки, чтобы результат был воспроизводимым.

Затем напишите запрос с выручкой по месяцам. Для SQLite возьмите substr(order_date, 1, 7). Не добавляйте в SELECT полный order_date рядом с месячной суммой. Сверьте январь: 1 435 795 рублей. Если получен другой результат, проверьте таблицу, выражение выручки и фильтр периода. Отменённые заказы в нашей модели имеют нулевые суммы, поэтому их исключение не меняет выручку, но меняет число созданных заказов.

Глава 7. Объединения и модель данных

Как соединяются таблицы

JOIN сопоставляет строки по условию. Внутреннее объединение INNER JOIN оставляет найденные пары, левое LEFT JOIN сохраняет все строки слева и добавляет совпадения справа. Если соответствия нет, поля правой таблицы становятся NULL. Если соответствий несколько, левая строка повторяется для каждого. Именно последнее свойство часто приводит к завышенным суммам.

Соединим заказ с городом покупателя:

SELECT

o.order_id,

o.customer_id,

c.city,

o.gross - o.discount - o.refund AS net_revenue

FROM orders AS o

LEFT JOIN customers AS c

ON o.customer_id = c.customer_id

ORDER BY o.order_id

LIMIT 20;

Буквы o и c — короткие имена таблиц. Они помогают различать одноимённые колонки. Перед объединением мы ожидаем один customer_id в справочнике. После объединения число строк должно остаться 4 815. Это ожидание нужно проверять, а не держать в уме.

SELECT customer_id, COUNT(*) AS copies

FROM customers

GROUP BY customer_id

HAVING COUNT(*) > 1;

Правильный результат — пустая выборка. Чтобы найти отсутствующие соответствия, используйте ключ правой таблицы:

SELECT o.order_id, o.customer_id

FROM orders AS o

LEFT JOIN customers AS c

ON o.customer_id = c.customer_id

WHERE c.customer_id IS NULL;

В комплекте таких строк нет. В реальной задаче выясните, означает ли отсутствие задержку обновления справочника, ошибочный идентификатор или допустимого гостевого покупателя. Выбор INNER JOIN без проверки просто убрал бы эти заказы из выручки.

Почему

LEFT

JOIN

может стать внутренним

Предположим, вам нужны заказы и, если есть, город Москва. Условие c.city = 'Москва' в WHERE удалит все строки без совпадения справа, потому что сравнение с NULL не даёт истинного результата. Если задача — оставить все заказы и присоединить только московское соответствие, условие нужно поместить в ON. Если задача — посчитать именно московские заказы, WHERE подходит. Размещение условия определяется смыслом, а не правилом «всегда пишите здесь».

Рассмотрите запрос перед выполнением. Что означает строка после объединения? Что должно произойти с ненайденным ключом? Сколько совпадений справа допустимо? Эти три вопроса полезнее попытки запомнить все виды JOIN по картинке.

Сначала агрегируем, затем соединяем расходы

Нам нужна одна строка на месяц и канал. Создадим общий табличный блок через WITH. Такой блок называется CTE; он задаёт именованный промежуточный результат внутри запроса:

WITH sales AS (

SELECT

substr(order_date, 1, 7) AS month,

channel,

COUNT(*) AS orders,

SUM(CASE WHEN status = 'completed'

THEN 1 ELSE 0 END) AS completed,

SUM(gross - discount - refund) AS net_revenue,

SUM(gross - discount - refund

- cost - shipping) AS contribution

FROM orders

GROUP BY substr(order_date, 1, 7), channel

)

SELECT

s.month,

s.channel,

s.orders,

s.completed,

s.net_revenue,

s.contribution,

m.spend,

s.contribution - m.spend AS after_marketing

FROM sales AS s

LEFT JOIN marketing AS m

ON s.month = m.month

AND s.channel = m.channel

ORDER BY s.month, s.channel;

В результате должно быть 24 строки. Сумма spend — 1 614 000 рублей, сумма after_marketing — 356 070. Если в marketing есть канал без единого заказа, такой запрос его не покажет, потому что слева находятся продажи. Для нашего полного учебного набора это допустимо. В рабочем процессе создайте каркас всех месяцев и каналов либо объединение ключей из продаж и расходов, а затем присоединяйте оба источника.

Не заменяйте отсутствующий spend нулём до проверки полноты. Отсутствующая строка могла означать, что рекламный кабинет не выгрузился. Для organic ноль записан явно и является значением. Пустое значение после JOIN говорит о несовпадении ключа или отсутствии записи. Эти ситуации нельзя смешивать.

Заказы и позиции

Рассмотрим отдельный маленький пример, которого нет в основной таблице. Есть два заказа: у первого доставка 300 рублей и две позиции, у второго доставка 200 и одна позиция. Общая доставка равна 500. Если объединить заказы с позициями, получатся три строки, и сумма доставки станет 800. Выручку позиций суммировать можно, доставку заказа — уже нет.

Одно решение — сначала агрегировать позиции до заказа, затем соединить с таблицей заказов. Другое — распределить доставку между позициями по согласованному правилу. Например, пропорционально выручке позиции. Тогда сумма распределённых частей должна точно восстанавливать стоимость заказа, а остаток округления нужно отнести по явному правилу.

Нельзя исправлять такую ошибку выражением SUM(DISTINCT shipping). Если у двух настоящих заказов одинаковая доставка 300 рублей, DISTINCT оставит одно значение и занизит сумму. Уникальность суммы не равна уникальности заказа. Сначала восстановите нужную детализацию, затем складывайте.