Как построить профиль скважины в эксель

Обновлено: 08.07.2024

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

Легко рисовать картинки, но для того, чтобы они считались и отображались правильно “под капотом” нужно проделать немало работы. В этой статье мы поговорим о том, как реализовать когортный анализ. Я расскажу про реализацию при помощи Excel, а в другой статье при помощи R.

Хотим мы этого или нет, но по факту Excel это инструмент анализа данных. Более “высокомерные” аналитики будут считать, что это слабый и не удобный инструмент. С другой стороны по факту сотни тысяч людей делают анализ данных в Excel и в этом отношении он легко побьет R / python. Конечно, когда мы говорим о advances analytics и машинном обучении, мы будем работать на R / python. И я был бы за то, чтобы большая часть аналитики делалась именно этими инструментами. Но стоит признать факты, в Excel обрабатывают и представляют данные подавляющее большинство компаний и именно этим инструментом пользуются обычные аналитики, менеджеры и product owners. Вдобавок Excel трудно победить в части простоты и наглядности процесса, т.к. вы мастерите свои расчеты и модельки буквально руками.

И так, как же нам сделать когортный анализ в Excel? Для того, чтобы решать подобные задачи нужно определить 2 вещи:

Какие данные у нас в начале процесса

Как должны выглядеть наши данные в конце процесса.

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

Дата регистрации клиента

Объем продаж этого клиента в эту календарную дату

Первая сложность, которую предстоит преодолеть — это получить эти данные. Если у вас правильное хранилище, то они уже должны быть у вас. С другой стороны, если пока реализовали только запись данных о совокупных продажах по дням, то данные по клиентам у вас есть только на “проде”. Для когортного анализа вам придется реализовать ETL и сложить в ваше хранилище данные в разрезе клиентов, иначе у вас ничего не выйдет. И лучше всего если вы разделите “прод” и аналитику в разные базы, т.к. У аналитических задач и задач функционирования вашего продукта разные цели конкуренция за ресурсы. Аналитикам нужны быстрые агрегаты и расчеты на по многим пользователям, продукту нужно быстро обслужить конкретного пользователя. Об организации хранилища я напишу отдельную статью.

Итак, вы имеете стартовые данные:


Первое, что нам нужно сделать это преобразовать их в “лесенки”. Для этого нужно над этой таблицей построить сводную таблицу, по строкам — дата регистрации, по столбцам — календарная дата, в качестве значений — кол-во id клиентов. Если вы верно извлекли данные, то у вас должен получится вот такой треугольник/лесенка:


В целом лесенка это наш когортный график, в котором каждая строка отображает динамику отдельной когорты. Клиенты во времени в этой отображении двигаются только внутри одной строки. Таким образом динамика когорты отображает развитие отношений с группой клиентов пришедший в один период времени. Часто для удобства и без потери качества, можно объединить когорты в “блоки” строк. Например, вы можете сгруппировать их по неделям и месяцам. Точно так же вы можете сгруппировать и колонку, т.к. Возможно ваш темп развития продукта не требует детализации до дней.

На основе этой лесенки вы можете влоб построить график из моей статьи (я правда указывал, что сгруппировал несколько строк в одну, чтобы когорт было поменьше):


Это график с накопительными областями, где каждый ряд — это строка, по горизонтали даты.

Чуть сложнее логика для реализации графика “потоков”. Для потоков мы должны сделать некоторые дополнительные вычисления. В логике потоков каждый клиент прибывает в различных состояниях:

  1. Новый — любой клиент, у кого разница между датой регистрации и календарной дате <7 дней
  2. Реактивированный — любой клиент, кто уже не новый, но в прошлом календарном месяце не генерировал выручку
  3. Действующий — любой клиент, кто не новый, но в в календарном месяце генерировал выручку
  4. Ушедший — любой клиент, кто не генерирует выручку 2 месяца подряд

Во-первых вам стоит в компании закрепить эти определения, чтобы вы могли корректно реализовать эту логику и автоматически рассчитывать состояния. Эти 4 определения имеют далеко идущие последствия в целом и для маркетинга. Ваши стратегии по привлечению, удержанию и возвращению будут базироваться на том, в каком состоянии вы считаете находится клиент. А если вы начнете внедрять модели машинного обучения в прогнозировании ухода клиентов, то определения станут вашим краеугольным камнем успешности этих моделей. Вообще про организацию работы и важность аналитической методологии я напишу отдельную статью. Выше я привел просто пример того, какими могут быть эти определения.

В Excel вам нужно создать дополнительную колонку, куда вписать описанную выше логику. В нашей случае нам придется “попотеть”. У нас есть 2 типа критериев:

  • Разница между датой регистрацией и календарной датой — эти данные есть у каждой строки и тут просто нужно ее посчитать (вычитание дат в Excel просто дает разницу в днях)
  • Данные о выручке в текущем и прошлом месяце. Эти данные нам не доступны в строке. Более того, с учетом того, что в нашей таблице не гарантирован порядок, то вы не можете точно сказать, где у вас данные по другим дням месяца для этого клиента.

Решить проблему 2 типа критериев можно 2 способами:

  1. Попросите сделать это в базе данных. SQL позволяет при помощи аналитической функции вычислить для каждого клиента сумму выручки за текущий и прошлый месяц (для текущего месяца SUM(revenue) OVER (PARTITION BY client_id, calendar_month, а потом LAG, чтобы получить смещение по прошлому месяцу):
  2. В экселе вам придется реализовать это так:
    • Для текущего месяца: СУММЕСЛИ(), критериями будет id клиента и месяц ячейки календарного дня
    • Для прошлого месяца: СУММЕСЛИ(), критериями будет id клиента и месяц ячейки календарного дня минус ровно 1 календарный месяц. При этом обращу внимание, что вы должны вычесть именно календарный месяц, а не 30 дней. Иначе вы рискуете получить смазанную картину из-за неодинакового числа дней в месяцах. Также используйте функцию ЕСЛИОШИБКА, чтобы заменить ошибочные значения для клиентов у кого не было прошлого месяца.

Добавив колонки выручки текущего месяца, прошлого месяца вы можете построить вложенное условие ЕСЛИ, учитывающие все факторы (разницу дат и суммы выручки в текущем/прошлом месяце):
ЕСЛИ( разница дат <7; “новый”;
ЕСЛИ( И (выручка прошлого месяца = 0; выручка текущего месяца > 0); “реактивация”;
ЕСЛИ( И (выручка прошлого месяца > 0; выручка текущего месяца > 0); “действующий”
ЕСЛИ( И (выручка прошлого месяца = 0; выручка текущего месяца = 0); “ушедший”; “ошибка”))))

У вас должно получится вот так:


Теперь эту таблицу можно пересобрать при помощи сводной таблицы в таблицу для построения графика. Вам нужно трансформировать ее в таблицу:

Календарная дата (колонки)
Состояние (строки)
Кол-во id клиентов (значения в ячейках)

Далее мы просто должны на основе данных построить диаграмму столбчатую диаграмма с накоплениями, по оси Х календарная дата, ряды это состояния, кол-во клиентов это высота столбцов. Вы можете поменять порядок состояний на графике, изменив порядок рядов в меню “выбрать данные”. В итоге мы получим такую картину:

Привет Хабр! По основной профессии я инженер по разработке нефтяных и газовых месторождений. Я только погружаюсь в Data Sciense и это мой первый пост, в котором хотел бы поделиться опытом применения машинного обучения в нефтяной сфере.


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

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

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

Обзор: как прогнозируют добычу нефти и газа

В настоящий момент кроме классического аналитического способа оценки (эксель + метод матбаланса) общепринятым является построение геологической (статической) и гидродинамической (динамической) моделей, на основе которой принимаются решения.

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

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

Гидродинамическая модель в 3D представлена на самой первой картинке (выше).

Недостаток подхода с построением полноценной гидродинамической модели в том, что на постройку модели нужно очень много времени (от нескольких месяцев до года и даже больше). Это зависит от количества скважин и имеющихся данных по месторождению. Более того, построенная гидродинамическая модель является сложной системой с высокой чувствительностью к входным данным. Поэтому любая некорректность в данных может привести к неверным результатам. Это не удивительно. Протяжённость месторождений достигает десятков километров. А типичный диаметр скважин находиться в пределах 10 - 15 см. Скважины в свою очередь, пробурены на глубины порядка 3-х километров и на расстояниях 250 - 1000 метров друг от друга. Таким образом, модель строится по крайне ограниченном данным, которые можно охарактеризовать как "точечные уколы".

Обычно скважину представляют себе как дырку в земле. Это не совсем так. Классическое определение скважины звучит так. Скважина - это цилиндрическая горная выработка, длинной многократно превышающей её диаметр. Типично - это 3-х километровое (бывает конечно и больше, бывает и меньше) отверстие в которую спущены несколько вложенных друг в друга колонн обсадных труб (для предотвращения обвалов). Пространство между колоннами обсадных труб и горной породой для герметичности цементируется. На поверхности устанавливается фонтанная арматура, которая герметизирует скважину от окружающей среды.

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

Скважинные данные включают в себя:

данные полученные при спуске приборов в скважину (например давление, температура, глубина нефте/газо-насыщенного пласта, кажущееся сопротивление породы, радиоактивность и прочие..),

замеряемые на поверхности - количество добываемых нефти, газа и попутной воды, их состав.

По типу основного добываемого флюида скважины можно разделить на нефтяные и газовые. В данной статье рассматриваются нефтяные скважины. Это значит, что на поверхности мы получаем нефть с растворённым в ней газом и попутную воду. Как правило в начале эксплуатации скважин добывается чистая нефть, но позже скважина обводняется и доля воды увеличивается. Когда доля воды увеличивается, а доля нефти уменьшается до определённого предела - скважина перестаёт быть рентабельной и её останавливают. Обводнённость выражают в процентах и рассчитывают как отношение количества добытой воды к добытой жидкости т.е. Qнефти/(Qнефти + Qводы)*100%

Типичный профиль добычи нефти во времени (в данном случае по годам) выглядит вот так:


Построение модели

Cum oil: накопленная добыча нефти

Days: количество дней работы скважины (до момента обводнения (и её остановки по нерентабельности) или до текущего момента в случае если скважина в работе).

In prod: скважина в работе/остановлена по обводнению

Q oil: текущий дебит нефти

wct: текущая обводнённость

Top perf: глубина верха интервала перфорации - глубина верха и низа

Bottom perf: глубина низа интервала перфорации

ST: 0 - основной ствол скважины, 1 - боковой ствол

x, y: координаты скважины

Импортируем необходимые библиотеки

Загружаем исходные данные на определённую дату из экселя и визуализируем датафрейм.


Проверяем местоположение скважин - строим карту местоположений скважин. В данном случае это карта забоев (нижних точек окончаний скважин).


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

Конструирование признаков

Рассчитываем матрицу евклидовых расстояний между скважинами из их координат.


Извлекаем список имён скважин. Присваиваем имена скважин колонкам матрицы расстояний.

Объединяем датасет параметров работы скважин с матрицей расстояний между скважинами. Таким образом в датасет добавляем новый признак - удалённость, который является весом скважин друг на друга.


Проверка модели

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


Создаём тестовый датасет


Создаём тренировочный DataFrame признаков X_1. Удаляем категорийный признак (имя скважины) и предсказываемое значение wct.


Создаём тренировочный вектор целевых значений y_1

Создаём тестовый вектор целевых значений y_test_1

В качестве алгоритма был выбран обычный Random Forest Reggressor, как наиболее универсальный алгоритм, подходящий для большинства типов данных.

R2 метрика на тренировочной метрике превышает R2 на тестовой 1%. Это означает, что модель отлично обучилась.

Сравним предсказанную обводнённость с фактической на тестовой выборке, которая не использовалась при обучении модели (blind test)


Сравним предсказанную обводнённость с фактической на тренировочной выборке


Вычислим среднее отклонение обводнённости:

Видим, что среднее отклонение по обводнённости составляет 8%, что является приемлемым результатом.

Создание модели на всех доступных данных

Создаём тренировочный дата сет, удаляя из него скважины, выбранные для прогноза

Создаём датасет для прогнозирования из скважин, удалённых на предыдущем шаге.

Предсказываемый параметр WCT (обводнённость) сейчас = NaN.

Создаём тренировочный DataFrame признаков x_2. Удаляем категорийный признак (имя скважины) и предсказываемое значение wct.

Создаём тренировочный вектор целевых значений y_2 и обучаем модель.

R2 повысилось. Или модель переобучилась или большее количество данных помогло точнее настроить модель

Сравним предсказанную обводнённость с фактической на тренировочной выборке.


Величина средней ошибки обводнённости снизилась до 6,5. Отлично!

Предсказываем обводнённость по боковым стволам:


Выводим список признаков в порядке убывания их важности и строим диаграмму важности признаков.


Видим, что наиболее важный признак - это расстояние до 2-й скважины. Возможно стоит проанализировать признаки дополнительно и исключить какие то из них из обучения.

Сравнительный график реальных и предсказанных значений. Чем дальше скважина от красной линии - тем хуже она предсказана.


Вывод

Предсказание параметров работы скважин возможно различными методами. Одни из них являются очень сложными и трудозатратными (геолого- гидродинамические модели), другие простыми и быстрыми (матбаланс, кривые падения добычи).

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

Примечания

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

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

Цель исследования была в том, чтобы оценить применимость методов машинного обучения в этой области. Задача выбрать наилучший алгоритм не ставилась, поэтому сравнения разных алгоритмов не проводилось.

Скважинные данные не являются открытыми данными, а являются собственностью компании, владеющей лицензией на разработку месторождения. Поэтому для иллюстрации выполнной работы были сгенерированы искусственные скважинные данные, которые доступны для данной работы.

Читайте также: