Summary Number Issues with Partitioning

JasonM519

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:

1. However as you click down and filter... I can't find a way to reference the overall number above that. (listed again at the bottom in more detail)
2. Is there a better way to do this, so I could show by date as well?
   1. Primary, so I could show improvement over time, however with needing to use the max value under each partitioning this dosen't seem possible. (More detail below)
   2. Secondarly, so that I can age out past campaigns. I believe the easiest way to do this is to filter prior to the partitioning action in ETL??

 

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)

SubOrg

User

 

Then I'm running beastmode calculations against those partitions to return the % at each level. 

Examples:

(The  'Count' Columns are partitions)
Top Card:

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.).

 

 

jaeW_at_Onyx

@JasonM519  oh man... this old chestnut.

 

instead of JOINING a cumulative metric (rank & window clicked by org) 

consider UNIONing the data at two granularities, 

 

SELECT

a.*

b.clicksByOrg

b.deliveredByOrg

FROM

clicks_at_lowestLevel a

UNION

clicks_at_companyLevel b

 

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!

JasonM519

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.

 

Thank you!