Visualizing Date Gaps in Google Data Studio

I have a dashboard for monitoring integrations with various services. For example, in it I track the completeness of the data collected for inquiries from chat or call tracking, the presence of data on inquiries, and much more.

I decided that, to get the full picture, I needed to add a chart of advertising spend by day to it. The spend is exported by connector scripts from various advertising accounts: Yandex Direct (Russian ad platform), VK, and so on.

This way I plan to track the days for which advertising spend was not imported into the data warehouse (in this case, BigQuery). Later on, I’ll be able to figure out why the scripts didn’t run correctly and backfill the missing data.

Fine, I thought — I’ll build the visualization, it’s not hard. Connecting to the table with advertising spend from Google Data Studio, I built a chart that doesn’t have a single gap:

But the chart wasn’t perfect, as I had imagined it. I knew for sure that there were days when the integration scripts didn’t run, i.e. didn’t write data into the spend table. Unfortunately, this wasn’t visible on the chart, because the chart plotted along the X axis only the dates that were present in the spend table.

To show the empty values on the chart, I first needed to add those empty values into the table being used.

The method described in the article «How to reveal and leverage selected date range of Google Data Studio reports» came to the rescue. Here I provide a guide based on that article, as well as my own alternative approach:

What do we need to solve this?

The author of the article suggests using an additional connection to Google BigQuery. That is, you’ll need an active Google Cloud Platform account and a project created there.

But this isn’t mandatory — below I’ll show another approach that uses Google Sheets.

Creating a new connection to Google BigQuery

So, let’s create a new connection to BigQuery in Google Data Studio and choose the “Custom query” connection type:

Next, we add the SQL query:

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

This query generates a table with a single column, calendar_date. The column contains an array of dates within the selected range.

The query uses date parameters. Therefore, you need to enable the date range parameters, and then save the connection with the Add button:

Let’s create a new sheet and specify the start date of the range in cell A1. Enter the date from which your data begins in the table. For example, I’m setting January 1, 2022.

In the adjacent cell B1, I specify the current date using the TODAY() formula.

We add a new sheet and, in cell A1, insert a formula that generates a column with dates ranging from the values of cells A1 to B1:

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

Name the file so that it’s easy for you to find it by name later.

An example of this file is at this link: https://docs.google.com/spreadsheets/d/1OZEO06YnvAoIMrUdU_IU9QUqpzHdMsSLzGLH9Ao-Ei0/edit?usp=sharing

Let’s go back to Data Studio and create a new connection to the Google Sheet. When connecting, select “Sheet 2”, where the column with the date range is located, and uncheck “Use first row as headers” before adding it: