This article describes how to create a table that represents the concept of a brand funnel (similar to a purchase funnel) using R code. For an approach that doesn't use code, see: How to Create a Funnel or Pyramid Chart.
Requirements
- A data set in Displayr
- Variables in your data set representing the following:
- Awareness
- Consideration (including Top 2 Box NET category)
- Preferred brand/item
Method
Set up your source tables
The easiest way to create the statistics we need for the funnel is to create tables in Displayr. This is done by dragging from the Data Sources section onto your page.
We later reference certain cells within those tables and rearrange them in the final summary table. For the worked example, I created three tables upfront, shown below:
Optional: Select matching rows on tables
You will either specify the brands to include in your funnel via R code or use brands shown in the tables directly. You can use the Select Matching Rows rule (see How to Select and Sort Rows and Columns Based on Another Item) to set up your tables so that the order of the rows matches across them (you can also use this rule to only show certain rows as well).
Create a custom Calculation
- From the toolbar, click Calculation
> Custom Code.
- If you'd like to specify the rows within the R code, paste the following into the R Code editor:
# Set the name of the brands
# UPDATE: replace the brands inside c() with your own brands
brands <- c("Coca-Cola","Diet Coke","Coke Zero","Pepsi","Diet Pepsi","Pepsi Max")
# Pull in the tables that will be used to create the brand funnel table
# UPDATE: replace the table. names below with the names of your actual tables
# UPDATE: if the values are in a column of a table like the Top 2 Box, put the name after [brands, columnName]
aware <- table.Awareness[brands]
consider <- table.Brand.consider[brands,"NET: Top 2 Box"]
main <- table.Preferred.cola[brands]
# Combine the tables into a single table and rename the rows
table <- cbind("Awareness" = aware, "Consider" = consider, "Preferred" = main)
# Set which output the funnel will use
funnel <- t(table)
# Create ratio calculation
ratio <- funnel["Consider",]/funnel["Preferred",]
# Add calculated output to the table and name the row
funnel <- rbind(funnel, "Ratio" = ratio)
3. If the rows are already matching in the correct order in your source tables, paste the following into the R Code editor:
# Pull in the tables that will be used to create the brand funnel table
# UPDATE: replace the table. names below with the names of your actual tables
# UPDATE: if the values are in a column of a table like the Top 2 Box, add the following after the table name [, columnName]
aware <- table.Awareness
consider <- table.Brand.consider
main <- table.Preferred.cola
# Combine the tables into a single table and rename the rows
table <- cbind("Awareness" = aware, "Consider" = consider, "Preferred" = main)
# Set which output the funnel will use
funnel <- t(table)
# Create ratio calculation
ratio <- funnel["Consider",]/funnel["Preferred",]
# Add calculated output to the table and name the row
funnel <- rbind(funnel, "Ratio" = ratio)
Next
How to Work with R in Displayr
How R Works Differently in Displayr Compared to Other Programs
How to Import a Data Set Using R
How to Create a Custom R Variable
How to Add a Custom Calculation
How to Work with R Data Types and Structures
How to Reference and Distinguish between Different R Objects in Displayr
How to Work with Conditional R Formulas
How to Perform Mathematical Calculations Using R
How to Work with Regular Expressions Using R
How to Use R's Paste Formulas in Displayr
How to Extract Information from an R Object