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”
- “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
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:
- Char 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
- 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
COALESCE(Date for periods,Date)
Trend and campaigns
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.