Hello, and thank you in advance for your help,
I've been driving myself nuts with trying to do this correctly. I am trying to build out a drill downs on cards according to the hierarchy of my company. These drill downs would show how often a user clicked on a link (%) at the lowest level, their organization above that, and then the company at the highest level. Eventually, I would like to show a summary drill down number that compares the lower level with the average against the entire company.
My question is two fold:
The data set is based on campaigns and users. For each campaign the user will have their own row, with a date of a click (or delivered) or a null value if no click was recorded. Their are multiple campaigns and users may or may not be in each campaign (in which case it would have a row with null fields after their name).
First I'm partitioning the data set on three levels:
Org (example Below)
Then I'm running beastmode calculations against those partitions to return the % at each level.
(The 'Count' Columns are partitions)
PieValue = max(`Count of Clicked by Org`)/max(`Count of Delivered by Org`)
PieName = Org
Summary Number = count(`clickedAt`)/count(`deliveredAt`)
This seems to work at this point, and I get something that looks like this:
While the summary number isn't the average of the three Orgs, it should be accurate as "Org 3" is significantly larger than the other two combined.
Primary Question: If you were to click on 'Org 2' I would drill to the next level (below), and at that level I would like to be able to show "Your organization click rate is 8.49% compared to the Enterprise Benchmark of 7.59%. ( Specifically: I know how to do the concat statement, but not pull the unfiltered click rate (7.59%) in this case.).
@JasonM519 oh man... this old chestnut.
instead of JOINING a cumulative metric (rank & window clicked by org)
consider UNIONing the data at two granularities,
Make sure to put the clicksByOrg in a separate colun from clicksByLowestLevel)
Add a column ActivityType to differentiate lowestLevel and companyLevel
This way when you build your pie chart you don't have to use MAX ... this is half of your problem.
Now when you visualize your data, at the top level you can use companyLevelMetrics and when you drill through you can visualize lowestLevel Metrics.
Same filters apply, and you get the right results.
If you want to show improvement over time, Pie Charts are absolutely not a great tool for showing trend. Consider a Stacked Bar?
I guess you could have two pie charts side by side ... but I do have a burning hatred for pie charts ... so i'm biased against it. (it's not just me, Stephen Few the granddaddy of visualization ... after Tufte ... agrees).
Hope that helps!
I appreciate the reply, but the numbers displayed were accurate. Perhaps a union would have simplified the overall solution, but If I can keep this to a ~mostly~ ETL solution I don't need an engineer to maintain it, which is key.
I just needed to get granularity by date, which required me to group the rank and file by an additional column. By adding in the date of the email sent and continuing to seek the max I was able to get to what I wanted.
I still son't know how to show a rollup number in w card that has been drilled into, which would be very useful.
I wouldn't use a pie chart for this either, the goal was to get to a scatter plot, but without an X axis the visualization dosen't work all that well, and using a pie allowed me to work through the issue.