How to insert your marketing planning as part of Looker Studio trend charts

---

Few years ago, I wrote a tutorial about how we can include annotations inside Looker Studio line charts. My custom solution was nice for instant events and correlation analysis.

This time, we will take care about periods and events occurring during a date range.

As example, why not including a Gantt chart of a marketing/campaigns planning directly inside a trend chart of metric performance?

---

Ingredients of the Gantt chart as part of time series chat

For this custom recipe, we will need the following elements to build the chart below:

• Your planning data as a list of periods, stored in a Google Sheets
• Your data set from your main data source providing dynamic daily metric
• Blended data tables with mentioned data
• A time series chart to display the final customized visualization
• Some chart calculated fields

Let’s see below the result of the combination of these ingredients.

---

---

---

1. Google Sheets file with periods

First, create a Google Sheets file and fill your periods, including the following columns:

• Start date
• Last date
• Event: Name of the period
• Event id: Incremental integer value than would help later to calculate the height of the period in the chart
• Up to you to add also grouping/filtering fields like categories.

Then, add it as a new data source in your report.

---

---

---

2. Blended data

Now, we configure blended data “BD – Data with marketing planning” as described below.

This blending combines 2 hacks: We cross join to access to all date rows (first table) by campaign, then we outer join without correspondance to mimic union all operator (2nd & 3rd tables to group rows from 2 data sets). 😈

• First table – GA demo account
  • Table name: “DFP”
  • Dimension: “Date” renamed “Date for periods”
  • Metric: None
• Second table – Google Sheets with the data of the marketing planning
  • Table name: “Periods”
  • Dimensions: “Start date”, “End date”, “Event”
  • Metric: “Event height for trend chart” using the formula of calculated field below
• Third table – GA demo account
  • Table name: “Daily metric”
  • Dimensions:
    • “Date”
    • “Data metric” as a calculated field with the name of the metric, here “Sales conv. rate”, as value
  • Metric: Ecommerce conversion rate for my example, renamed “Sales conv. rate”
• 1st join configuration
  • Operator: Cross join
  • No key
• 2nd join configuration
  • Operator: Full outer join
  • Conditions and keys: “Event” from Periods table with “Data metric” from Daily metric table

---

---

---

Event height for trend chart

(-0.01)-0.01*(Event id-1)/2

Formula explanation: For my example, I choose to display my Gantt chart below the trend line based on conversion rate but feel free to adapt this formula following the metric you use and where you wish to display the periods (the campaigns in my example).

---

3. Chart configuration

At last, we create the trend chart:

• Chart type: Time series chart
• Setup tab
  • Data source: Your blended data “BD – Data with marketing planning”
  • Dimension: “Date” corresponding to the formula of chart calculated field below
  • Breakdown dimension: “Trend and campaigns” corresponding to the formula of chart calculated field below
  • Metric: “Metric” with the formula of calculated field below
  • Breakdown dimension sort: “Metric” Descending
• Style tab
  • Color by dimension values, changing the colors as wished
  • Adapt the line weight for each series
  • Add a black reference line to 0 as constant value, hiding the related label
  • Display the legend at the left bottom corner
  • Adapt the other options to improve the final rendering

---

Date

COALESCE(Date for periods,Date)

---

Trend and campaigns

COALESCE(Event,Data metric)

---

Metric

IF (
     Data metric IS NULL
     ,
     IF( Date for periods <= End date  AND   Date for periods >= Start date
         ,
         Event height for trend chart
         ,
         NULL
     )   
     ,
     IF(Date IS NOT NULL, Sales conv. rate, NULL)
 )

---

5. Configuration review

As usual, let’s review the full configuration.

---

---

Voilà, c’est fini.