Topics Covered: Creating a flowchart, Custom shapes, Formatting numbers within string labels
A colleague recently challenged me to create an interactive, automatically-updated version of his user acquisition flowchart in Tableau. The chart in question was a simple diagram of a website's user registration funnel, and he had been updating it manually for weeks at a considerable cost to his sanity. The product team laid out the following requirements:
The funnel needed to represent four basic steps. Without going into detail, we'll call them "landing", "sign-in page 1", "sign-in page 2", and "complete."
Three absolute metrics needed to be displayed for each of these stages: Users, estimated user value in dollars, and visits. Conversion rates also needed to be calculated for each (so we could say 25% of users went from "landing" to "sign-in page 1", 10% from "sign-in page 1" to "sign-in page 2", etc).
The graphic should allow for at-a-glance diagnosis of under-performing pages.
All metrics, both absolutes and conversion rates, should be able to be trended over time.
While I can't share the actual dashboard I designed for this project, below is one I put together from scratch based on a traditional eCommerce checkout funnel. Try out the filters and click on the icons to see trended views of each metric. More details on construction behind the jump.
The "Eureka!" moment came when I realized that a flowchart is really nothing more than a table of images and text. Tables are something that Tableau handles really well, so all that was required was a dataset that spelled out what to put within each cell.
As usual with Tableau, getting the data into the right format proved to be the biggest hassle. I settled for a table structure that included one row for each "cell" within the flowchart. I put together an Excel sheet consisting of the following columns:
Date: Applicable date for the data.
Metric: Specifies whether the value column contains users, transactions, or dollars. Used to allow the selection of different metrics.
State: Position within the checkout process. Used to determine placement of each data point on the flowchart.
Show As: Indicates whether to display the value as a percentage (%) or an absolute. Also used to determine placement of each data point on the flowchart.
Once I had the dataset put together, it was time for the placement of icons within the grid. I created two calculated fields, X and Y, placing X on the Columns shelf and Y on the Rows shelf. Here's the final worksheet with the X and Y headers shown:
X and Y consisted of large If statements that used the State and Show As fields to determine where to put each value. For example, {1, 1} (the Cart icon) refers only to rows where State = 'Checkout' and Show As = 'Absolute'. {2, 1} refers to rows where State = 'Billing' and Show As = 'Percentage'.
I dropped the Value column into the Text area for now. This would have to change later, as I wanted to do some conditional formatting based on whether the value was a percentage or an absolute, but this served as a place to start.
Next, I changed the Mark type to "Shape", filling the screen with default geometric shapes. Now I needed some pictures to represent each step. I probably ended up spending more time on this step than on the coding of the actual report. I used Google to search out some icons free for public use, cut them down to the recommended 64x64 pixel size, and saved them into my \Documents\My Tableau Repository\Shapes\My Custom Shapes folder.
After assigning the appropriate shape to each X/Y value, it was time to deal with the label formatting. I wanted my percentages to include the "%" character at the end, but not my absolutes. This meant recasting the Value column as a string, evaluating the value of the Show As column, and either concatenating the % character or display the number as-is. Easy enough using the str() function, I thought.
That's when I ran into an annoying problem. All my labels looked like this:
Notice something missing? When you're dealing with numbers over a certain size, not having comma-separated thousands can really hurt readability. Tableau lacks any sort of dynamic format() function for calculated fields, so I turned to a trick I learned about from Joe Mako on the Tableau forums. Instead of saying:
str(sum([Views]))
I used this instead:
str(lookup(sum([Value]), 0))
This formats the number so that the resulting string includes commas in the correct places. Such use of the lookup() function is kind of a hack... The function is meant for looking up values on previous rows in the partition, but if you provide 0 as the second argument, that will make sure it only looks at the current row.
The rest was easy. I created a calculated field for determining arrow sizing and dropped it onto the Marks shelf under "Size." The arrows would now shrink or grow depending on the conversion rates (anything with a Show As value of "Percentage"). Then I dropped it all on a dashboard, added a line graph and tied it all together with a few actions, and voila! A fully functional Tableau flowchart with just a couple of hours of work.
Want help setting up your own flowchart in Tableau? Email the author at jrhege at gmail dot com.
No comments:
Post a Comment