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

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

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

Хорошо, подумал, я — сделаю визуализацию, это не сложно. Обратившись из Google Data Studio к таблице с рекламными расходами, я построил график, в котором нет ни одного пропуска:

Но график не был идеальным, как я себе представлял. Я знал точно, что были дни, когда интеграционные скрипты не работали, т. е. не записывали данные в таблицу с расходами. К сожалению, на графике это было не видно, поскольку график выводил по оси X только те даты, которые были в таблице расходов.

Чтобы показать на графике пустые значения, мне следовало сначала добавить эти пустые значения в используемую таблицу.

На помощь пришел метод, описанный в статье «How to reveal and leverage selected date range of Google Data Studio reports». Привожу здесь мануал, основанный на этой статье, а также свой альтернативный вариант:

Что нам понадобиться для решения?

Автор статьи предлагает использовать дополнительное подключение к Google BigQuery. Т. е. вам понадобиться действующий аккаунт Google Cloud Platform и созданный там проект.

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

Создаем новое подключение к Google BigQuery

Итак, создадим в Google Data Studio новое подключение к BigQuery и выберем тип подключения «Пользовательский запрос»:

Дальше добавляем SQL запрос:

SELECT
   *
FROM 
   UNNEST(
      GENERATE_DATE_ARRAY(
         PARSE_DATE('%Y%m%d',@DS_START_DATE)
        ,PARSE_DATE('%Y%m%d',@DS_END_DATE)
        ,INTERVAL 1 DAY
   )
) AS calendar_date

Этот запрос генерирует таблицу с одной колонкой calendar_date. В колонке находится массив с датами в выбранном диапазоне.

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

Альтернативный вариант с Google Таблицами

Создадим новую таблицу и укажем дату начала диапазона в ячейке A1. Укажите ту дату, с которой у вас начинаются данные в таблице. Например, я ставлю 1 января 2022 года.

В соседней ячейке B1 указываю текущую дату с помощью формулы TODAY().

Добавляем новый лист и в ячейку А1 вставляем формулу, которая генерирует столбец с датами в диапазоне от значений ячеек A1 до B1:

=ArrayFormula(TO_DATE(СТРОКА(ДВССЫЛ("A"&'Лист1'!A1):ДВССЫЛ("A"&'Лист1'!B1))))

Назовите файл так, чтобы вам было легко его найти по названию в дальнейшем.

Пример этого файла по ссылке https://docs.google.com/spreadsheets/d/1OZEO06YnvAoIMrUdU_IU9QUqpzHdMsSLzGLH9Ao-Ei0/edit?usp=sharing

Вернемся в Data Studio и создадим новое подключение с Google Таблицей. Выберите при подключении «Лист 2», где находится столбец с диапазоном дат и снимите флажок «Заголовки — значения первой строки» перед добавлением:

Делаем совмещение данных

Воспользовались ли вы первым вариантом или вторым, результат будет одинаковым. У вас в дашборде появится новый источник с одним столбцом. В первом случае столбец будет называться calendar_date, во втором — буквой А.

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

В открывшемся окне нажимаем «Объединить с другой таблицей» и выбираем добавленную ранее таблицу с датами. После этого переходим к конфигурированию совмещения:

В конфигурации указываем «Полное внешнее соединение». В условии объединения выбираем столбцы с датами из левой и правой таблиц. После этого сохраняем конфигурацию.

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

Настраиваем график

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

• Убираем параметр разбивки
• В качестве параметра выбираем calendar_date (или A, если вы воспользовались вторым вариантом)

Теперь получаем график с пропусками, что делает график более ценным для отслеживания пропусков в данных: