Profile page performance rating over time graph stats query.

[scd]

@brendanheywood I have started experimenting with a single profile page stats query which can give us performance rating over time.

I have a current version in sql/account-power-rating-10-profile2.sql. It has highlighted a couple of secondary things:

• [x] the way ref offset works means we should not include ascents before 1970-01-01.
• [x] partial dates needs way more processing. The default SQL functions we are using to convert partial dates for decay, don't work as we want them to. If there is no month then we have to convert to Jan, and if there is no day then we have to convert to the 1st of the month.

The query uses internal variables to track cumulative power and cumulative decay power. A sub query orders by ascent date.

The cumulative decay power is calculated for the date of the current ascent. So the first ascent in the list has a cumulative power equal to the total power. Then the second ascent decays the previous ascents power to the second ascent date. And so on. This means the that at any ascent date we know the persons decay power.

• [x] note to self: do we want total power. How does total power work when there is no ascent dates. Do we use log date?

So we can do it all in one query I am also doing onsight in the same query.

• [x] note to self: onsight should not be tick shifted.

The query returns a list of ascents for a particular gear style, ordered by ascent date and includes the following info:

• Tick Type
• Ascent Date
• Cumulative Decay Power (decayed to the ascent date)
• Cumulative Onsight Decay Power (decayed to the ascent date)

Does this seem like the right track for the profile page?

• [ ] With the facetSuffix, how will this interact with someone who has logged an ascent in 2016-May without an exact day? These should appear in the slice for may along with the other may ones with a date. I think that all the suffix'es should be like /between/2010-01+2010-12 or between/2010+2010 to work correctly which also makes the urls smaller and nicer.

[brendanheywood]

Yeah this sounds great. I'd also include the raw internal grade, and the effective shifted grade. Does that also include ticks even if they don't rate? I have an idea for an alternate way of displaying all of this as a sorta scatter plot over time.

It would also be good to pass in the decay so that I can visually simulate the decay at any point in time without needing explicit data. It would be even better to have a WebHelper which takes a known date and rating and returns a rating for any future date.

Re 'total power' do you mean nondecayed? If so I don't think it's critical here.

It's really great that this is a single query - after I see the data it's likely I'll want to add in other visual bands for pink point or flash or whatever.

[scd]

Yeh the single query is what I was working hard to achieve. I started with stored procedures the quickly realised that that did not help and remembered the technique I used for ranking using variables and a sub query to order things. The rest is maths in the query.

Yes, total power means non decayed power in the query. However there is a subtle difference in this table versus somebodies total power in a node. The profile query only uses ascents with an ascent date, while node total power includes ascents without an ascent date.

The problem for the profile query is that it is basically a graph over time, so when do we assume ascents without a date should be included in the graph.

The Webhelper/simulation may be really easy if javascript has power and log maths functions. I think we already have that in the perl functions if we need it via api or in templates.

I fully expect some other tick types in the query, but I will wait to see how it pans out.

The internal grade happens after the data is returned to perl in the same way as it is done for rankings.

[brendanheywood]

Yes, total power means non decayed power in the query. However there is a subtle difference in this table versus somebodies total power in a node. The profile query only uses ascents with an ascent date, while node total power includes ascents without an ascent date.

For the purposes of this graph, we are only interested in the current decayed power rating at each point. Total power as a stat over time is quite boring, if added to the graph it will just keep going up. If you peaked in you're mid 20's there would just be a flat line for the rest of the graph, I don't think this adds any value and could be a negative for some almost like saying 'hey you're a has-been climber'

The problem for the profile query is that it is basically a graph over time, so when do we assume ascents without a date should be included in the graph.

We've already decided that ascents without ascent dates don't get included in your current power rating, so they must not be here either. Ideally that chunk of sql would be identical and managed in one place (probably impractical). In theory all the hassles with partial dates you should have already been through and solved for the current power stat, so is this something we need to go back and review to make sure it's consistent with this?

The Webhelper/simulation may be really easy if javascript has power and log maths functions. I think we already have that in the perl functions if we need it via api or in templates.

I only meant perl server side only for use in templates, so all good.

What's your gut feel of performance with this? It seems fairly snappy on dev once the query is warm, but when it's cold it isn't great. I think this is probably a good candidate for not being in the main account page html but pulled in separately as a pre-formed svg reference just like an image. There is going to be a bunch of these graphs, trad sport boulder, can they be shared in the one sql or would that be 3? If we split it into separate svg's then they will be separate but good to know the impact either way.

Doing it this way also gives us a few other easy wins and is really the start of the 'putting graphs at the top of the facets' idea (which really the gradeslider is sorta the first anyway). It also gives us a lot of freedom to try these graphs out in prod before we insert them anywhere. So worth a little bit of thought up front to make it generically useful.

eg in this case right now we have a climber, and we have a gear type, but later we might want to add any of the existing filters we already have.

Here is lee's ticks which count for the rating:

/climbing/world/ascents/with-route-gear-style/sport/by/leecujes/

So I think we should create a new end point with more or less the exact same url semantics but something like:

/climbing/world/graph-cpr/with-route-gear-style/sport/by/leecujes/

The only url semantics which would not be honors the same as the original page are the ?pagesize and sort order as these are implied by any aggregate data, eg a graph.

This url would serve an svg which is produced by a new template somewhere like:

/major/primarycontent/graphs/cpr

Then the account page just references it as an image

[scd]

In theory all the hassles with partial dates you should have already been through and solved for the current power stat, so is this something we need to go back and review to make sure it's consistent with this

We totally missed this in our debugging. It is why I checkboxed the task at the top of the issue.

Ideally that chunk of sql would be identical and managed in one place (probably impractical)

Just so you know 90% of the power stuff is in PowerRating.pm including sql chunks. You are right it was difficult to keep them all here because it is impracticable to do this in the facets.

Also when you need a web helper it is probably worthwhile writing a wrapper in the WebHelpers.pm and calling the relevant function in PowerRating.pm. I don't want to include PowerRating.pm directly in templates.

What's your gut feel of performance with this?

The performance should be equivalent to the performance of an ascent facet, with the difference of returning the whole unpaged facet and a little bit of extra maths processing. I am pretty happy we have got a high performant methodology for the underlying query.

I like your idea of componentising, and you are right this is the time to get the framework right. What about also using our embeding framework as well.

Fundamentally what we are doing is providing an aggregation layer to the facet, where order is no longer important. Thinking out aloud aggregation could be anything such as a graph, or excel spreadsheet.

Taking your url modification one step further we could have something like:

/climbing/world/aggregation/graph/sport-cpr/with-route-gear-style/sport/by/leecujes/

Which would produce a page with the graph with standard footers and headers. But if we used:

/climbing/world/ascents/aggregation/graph-sport-cpr/with-route-gear-style/sport/by/leecujes/?embed=svg

or

/climbing/world/ascents/aggregation/graph-sport-cpr/with-route-gear-style/sport/by/leecujes/?embed=chunk

Will trigger our embed framework, and either return a svg object or an html chunk.

Doing it this way also allows multiple aggregation functions on a single page.

/climbing/world/ascents/aggregation/graph-trad-cpr+graph-sport-cpr+graph-boulder-cpr/by/leecujes/?embed=chunk

[scd]

I think this 'aggregate' concept will work well. It is only a couple lines of code to intercept a facet and redirect the output to the aggregate template. All facets should intercept to the same aggregate function so a graph is available to any facet.

There is a bit more work to do to prepare the data for the dynamically generated facet to prepare the data in the right format.

[scd]

Hmmm, I have just come across an issue which may greatly impact performance if cumulative calculation in sql.

What to do about multiple ascents of the same route?

This is handled fine in the rest of the system, it just that the optimisation I have done for the profile page cannot really handle this all in sql. I am thinking I have to do this bit in perl land.

[brendanheywood]

It it's done as a triangular join this should all be handled already. So the inner sql will just be the list of ascents with shifts and dates pretty well verbatim to what we've used elsewhere, and then the outer join to this will do the group by and max on top of it for each date it processes. This will necessarily be slower but it's correct.

[scd]

That is how it works in the normal queries, but because I am building a cumulative score at each ascent the logic is slightly different.

For example let's say you do an onsight then a couple of years later a red point of he same route.

When it comes to working out the cumulative score at the point of processing the onsight we just use the onsight power value as this is the only ascent of the route at this point in time.

We keep processing the cumulative score as we go down the list of ascents we eventually get to the red point. Now we have to consider both the onsight and the red point. If the red point is worth less then the decayed onsight then we simply ignore it. However if the red point is worth more then we have to subtract the decayed onsight from the cumulative score and add the red point.

This comes about because of the shortcut of suming the cumulative scores. If I did a join with earlier routes then I could do what you suggest. But I lose a huge performance gain.

This is easy to do in perl land so I am pushing this calculation to perl and get the best of both worlds. We have to push the records to perl anyway so I think this is the best way to do.

[brendanheywood]

This comes about because of the shortcut of suming the cumulative scores. If I did a join with earlier routes then I could do what you suggest. But I lose a huge performance gain.

Have you tested this performance hit? I wouldn't expect it to be super bad but I'd like to not guess. This would create a temp set of records which worse case would be a few thousands records for the prolific tickers, and then you are joining this to itself.

[scd]

It is worse then just the join because we would be redoing the group by sum again for each ascent. So the first ascent will just sum itself, the second will sum the previous and itself and so on. This is all done on what could be a complex facet in the first place and there may be business rules where we do the same query without this dup logic (for example progress of a whole crag).

At this stage the business records are saying pass all the records back to perl anyway, so it is unnecessary for me to spend a lot of time on getting this benchmark going. When and if there is a business rule saying the whole aggregation (including grade conversions) must be done in sql, then this is the time to do the benchmark.

[brendanheywood]

Yeah but the entire join will be on a named subselect temp table. But either way I don't care about the impl too much I just care it's correct.

[scd]

I know you wanted to do a scatter plot, but I think we should just do a simple time series first. Now for a little bit of a ramble to make sure we are on the same page. @brendanheywood please check the checkboxes below if you agree with the technique.

Not that some of this I am pretty sure you will agree with because it is only a slight modification to your suggestions anyway.

• [ ] I have created an 'aggregate' template which returns aggregated facet data from any facet. Currently I have only hooked up cpr time series data from ascent facet, but if you agree with the technique we can hook up the other facets over time. This template is there but mostly empty. It only shows the outputted data for cpr time series.

• [x] the aggregate display does not have to be limited to graphs but could be other aggregates, such as an table, csv, etc.

• [x] the aggregate data can be presented as a web page or embedded as per our normal embedding procedure. It can also have a specific return type.

• [x] facet aggregate data is specified in the url like normal facet data. This allows the aggregate sql to be dynamically built into the facet. For example:

/ascents/aggregate/graph-sport-cpr/with-route-gear-style/sport/by/leecujes/

• [x] if aggregates have the same dimension (effectively what we end up grouping by in the sql) then they can be combined. For example the sport and trad rating is a time series which has the underlying dimension of an ascent in the facet, so both of these can be combined in a single query.

/ascents/aggregate/graph-sport-cpr+graph-trad-cpr/by/leecujes/

But we could not mix dimensions so something like /aggregate/graph-sport-cpr+style-pie/ would not work because the facet would have two different group by queries.

• [x] with respect to the cpr graph I think we need a time series. At the moment I have defined a 'year', 'month' (default) and 'day' time series. The time starts in the month of the first ascent in the series and finishes in the month of the last ascent. It is specified using a 'period=year' param.

• [x] Rating is decayed to the last day of the time series slice (ie end of month). If there is no ascents in a particular month then the power is carried forward from previous month and decayed.

• [x] At this stage I am not sure exactly what we will want to display, but at the moment it includes the

  • count of ascents in that time series slice
  • decayed cumulative power, rating, internal grade and converted grade
  • onsight decayed cumulative power (non tick shifted), rating, internal grade and converted grade

Note that the cumulative is cumulative for all ascents in the facet, ordered by climb date.

Here is an example:

          'graph-sport-cpr' => [
                                 {
                                   'onsightPower' => '46735613.9667333',
                                   'startIndex' => 9496,
                                   'count' => 2,
                                   'endEpoch' => 823132799,
                                   'decayOffset' => 9526,
                                   'power' => '12330987212.6346',
                                   'startEpoch' => 820454400,
                                   'label' => '1996-1',
                                   'cpr' => {
                                              'band' => 2,
                                              'rating' => 1662,
                                              'grade' => '16',
                                              'internalGrade' => '166.2',
                                              'systems' => [
                                                             {
                                                               'grade' => '16',
                                                               'low' => 151,
                                                               'high' => 167,
                                                               'system' => 'AU'
                                                             }
                                                           ]
                                            },
                                   'onsightCpr' => {
                                                     'band' => 2,
                                                     'rating' => 1264,
                                                     'grade' => '14',
                                                     'internalGrade' => '126.4',
                                                     'systems' => [
                                                                    {
                                                                      'grade' => '14',
                                                                      'low' => 118,
                                                                      'high' => 133,
                                                                      'system' => 'AU'
                                                                    }
                                                                  ]
                                                   },
                                   'endIndex' => 9526
                                 },

• [x] Because we are hooking into the facet, we can graph a time series of any ascent facet. It could be Lee at Arapiles, or Lee in 2016 by day. It does not even have to be related to an account, and could be all ascents at Arapiles. I think this could be really cool.

• [x] On a technical side, I think the maths is close to right, but not yet perfect. For Lee's account there was one rating point difference when calculating rating via the time series of ascents versus the account node. The former is essentially decaying a cumulative score one day at a time over 20 years, while the latter is propagating individual account node scores up the index hierarchy. Theoretically they should be the same, unless there is a bug, rounding errors, or I have changed something since calculating the statistics. Anyway I think it is close enough for now but I plan to do a deeper investigation.

[brendanheywood]

Some mostly we're all on the same page:

Generally:

• [x] I'm not sure about the 'aggregate' template, we'd instead need 1 template for every aggregation type, ie one for pie charts, one for CPR over time, etc. is the aggregate template just going to punt it off to a subtemplate?
• [x] it would be good if each aggregated data point had a partial url in it's data so we can easily link back to the same facet showing just that bit of data. This may not always make sense, but where it does would be good to be consistent. eg for any time slice return the facet urls so you can do and see exactly what those ticks were.
• [ ] All of these should be as automatic as possible, so if there was say a clustering of thing on a map, then the viewport of the map would be chosen for you, or with a time series, the start and stop date and the year/month/week/day granularity would be chosen for you. It will be normal for the potentially calling page to not know anything about the data in order to choose the best option, eg a remote blog page definitely won't. We should be able to blindly embed these from any page without much context. You can add options to make it explicit if you want but I don't think we'll use it much if at all.
• [ ] I think the only fixed things we might want to routinely pass in are an embed viewport width / height or at least an aspect ratio.

[scd]

is the aggregate template just going to punt it off to a subtemplate?

Yes. The aggregate template will handle some high level stuff, such as embedding or on it's own page.

it would be good if each aggregated data point had a partial url in it's data so we can easily link back to the same facet showing just that bit of data.

If I understand you correctly, it will return the original facet url without the the aggregation, plus a bit of logic which adds dates. If the original facet already had a date then this would modify the dates.

All of these should be as automatic as possible, so if there was say a clustering of thing on a map, then the viewport of the map would be chosen for you, or with a time series, the start and stop date and the year/month/week/day granularity would be chosen for you

Good point. Start and stop date are already automatic.

It should be fairly simple to select rules for default granularity. Eg (first ticked less than a month ago then use day granularity).

I think the only fixed things we might want to routinely pass in are an embed viewport width / height or at least an aspect ratio

Is this best way to get the viewport size in the template?

The main difficulty with embedding is auto height when height may be variable with the content. In this case, because it is a graph this should not be an issue.

• [x] One thing I forgot to ask. What should we be using to generate the the charts?

For the profile page I am currently using Raphael, but do we want to build SVG direct? There are a bunch of perl SVG libraries.

I guess if we want to align with offline mobile app using javascript for web views then we could probably just continue to use Raphael or other javascript graphing equivalent. This would mean that the charts would be available in the mobile app.

@brendanheywood I am guessing you have already worked out what you want to see here.

[brendanheywood]

Is this best way to get the viewport size in the template?

It depends. Short answer is the more things are automatic the better, but I just want access to any extra params sent through in the template. Anything generated server side won't have access to anything like the iframe size, it's just a chunk of svg. Most graphs would be a fixed aspect ratio, and be required to fit that space. Things like a table embed are different as in that case you want the iframe to auto resize to fit the content.

Which leads me to the question of svg vs lib, again it all depends, best tool for the job and I'll decide graph by graph. Where possible I'd prefer more things to be server side and optionally augmented client side but not requiring js at all.

[scd]

best tool for the job

What is your feeling for this graph? javascript like chart.js or perl svg module?

I presume mobile apps will have their own charting libraries.

Also do you want to work on this graph or do you want me to?

[brendanheywood]

So we are essentially wanting to rebuild this graph, but using finer grained data, having 2 lines for the onsight perf grade and the red / pink point grade and possible other lines if they work. The graph currently isn't dynamic at all, it's just a couple hovers, so redoing this I'd probably go with server side static svg so we can include it purely and an svg inside an img tag. I'd probably hand roll it myself. I'm happy to take this off your plate

This particular graph really has potentially a whole bunch of things in it eg red point cpr, hardest grade, the total grade breakdown for each year or month. And likewise many other graphs are gonna have a whole bunch of different thinks in them. It seems a bit academic to try have all that in the url:

/ascents/aggregate/graph-sport-cpr+hardest+total+breakdown/with-route-gear-style/sport/by/brendan/

really I think we just something like this would suffice:

/ascents/aggregate/graph-performance-timeline/with-route-gear-style/sport/by/brendan/

I don't think would we ever want to graph with each bit individually. This would still work even if you want to apply it to a crag instead of a user or whatever.

Also in this particular graph, I see value in having the performance fairly fine grained say every month like a little spark line, but the tick type breakdown could be every year. Or should we just decide to always keep the aggregations in lock step for all the components?

Also if we are going to have potentially a bunch of different bit of data for each time slice or whatever, it would be really good to keep the data much more neat and consistently names, eg:

• [x] move decay to inside cpr
• [x] move power to inside cpr
• [x] move onsightPower to inside onsightCpr and rename to just power
• [x] move onsightPower to inside onsightCpr and rename to just power

In theory the only top level attributes should be directly related to the time slice itself, ie the start and end epoch, and the label. What is start / end Index?

[brendanheywood]

Been poking around a few perl libs, these two looks promising:

http://onemogin.com/chart-clicker/

http://leo.cuckoo.org/projects/SVG-TT-Graph/examples/timeseries.html

I'm still in two minds about perl vs client side, and perl lib vs hand rolled. I know we'll get a better result hand rolling the svg on that end of the spectrum. The argument is really time vs quality. And on the other extreme end for the highly interactive stuff I'd be using d3 for the interactive visualizations

[scd]

I moved the params inside cpr and moved the count param as well.

                                 {
                                   'startIndex' => 9496,
                                   'label' => '1996-1',
                                   'endIndex' => 9526,
                                   'onsightCpr' => {
                                                     'rating' => 1264,
                                                     'decayOffset' => 9526,
                                                     'power' => '46735613.9667333',
                                                     'band' => 2,
                                                     'ascentCount' => 1,
                                                     'grade' => '14',
                                                     'systems' => [
                                                                    {
                                                                      'system' => 'AU',
                                                                      'high' => 133,
                                                                      'low' => 118,
                                                                      'grade' => '14'
                                                                    }
                                                                  ],
                                                     'internalGrade' => '126.4'
                                                   },
                                   'startEpoch' => 820454400,
                                   'endEpoch' => 823132799,
                                   'cpr' => {
                                              'rating' => 1662,
                                              'decayOffset' => 9526,
                                              'power' => '12330987212.6346',
                                              'band' => 2,
                                              'ascentCount' => 2,
                                              'grade' => '16',
                                              'systems' => [
                                                             {
                                                               'system' => 'AU',
                                                               'high' => 167,
                                                               'low' => 151,
                                                               'grade' => '16'
                                                             }
                                                           ],
                                              'internalGrade' => '166.2'
                                            }
                                 },

The startIndex and endIndex is the day index for epoch, which matches how we do decay. I have just made it a general parameter when I create the time series base records. In this case the logic will decay power to the endIndex if the last ascent was not already at the endIndex date. I thought about calling it startDay and endDay, but there may be other functions where we need different epoch aggregation. I actually don't really care what I call it.

I think there is real benefit for both of us working on this one as some of the decisions we make may have longer term architectural implications. If you work on the graph side of things and I work on getting you the data we want, we will both be highly engaged and probably come up with a better solution.

I am wondering if it is worthwhile doing a time series with just the cpr info first. I am intrigued about what cpr alone is telling us.

It sounds like you are having the same server side/client side thoughts as me. Server side svg is way better in terms of a web system. However I am also thinking about the mobile app. I guess the mobile app is not going to work out the data for this graph without calling the server, so we may as well do server side svg.

I guess that means our first go should be server side svg. I had a look around at perl modules yesterday and also came to the conclusion that SVG::TT seemed good.

Also in this particular graph, I see value in having the performance fairly fine grained say every month like a little spark line, but the tick type breakdown could be every year. Or should we just decide to always keep the aggregations in lock step for all the components

My gut feeling is to keep aggregations in lock step, otherwise we have two overlapping time series. Sure if we find a lot of value doing this then we could try and work it out. Let's keep this as a secondary issue.

Maybe the alternate workflow would be to start at the year level then the user can easily breakout to a more fine grained view.

Note that if they do that we still need to analyse the full ascent list because the cumulative power will be different if you do a facet for a single year. Our current decision is to do the cumulative power on the facet giving us more flexibility.

[brendanheywood]

I guess that means our first go should be server side svg. I had a look around at perl modules yesterday and also came to the conclusion that SVG::TT seemed good.

Ok, but I'll make that call once I start doing the graphics side.

Maybe the alternate workflow would be to start at the year level then the user can easily breakout to a more fine grained view.

Re granularity for this particular graph, I think the current behavior of showing months or even days for a brand new user who has lots of data but only over a short time span is the way to go. So I think the log should be something like: find the largest granularity which gives me at least 50 data points, then start at years, then quarters, then months, then weeks, then days until we get that many time slices. Also one thing that really bugs me about the current graph is if you didn't log anything for a year then it just skips it and looks like that year never happened. If I had a break in climbing I was to see that on the graph so this means we could have lots of time slices with 0 data if someone climbed 20 years ago then stopped then start again this year. For this particular graph (or I guess anything else timeseries) please add the granularity to the base data eg granularity = "year" "quarter" "month" "week" "day". I'll use that to customize the bottom x axis.

Can you also please as a top level variable to facet url for the data but without the date facet. ie at the top of the template data some thing:

baseFacetUrl : "/ascents/by/brendan/with-gear-type-sport/"

and then inside each time slice the extra bit for that slice eg

{ startEpoch: blah, endEpoch: blah, facetSuffix: "between/2016-01..2016-02/ }

[scd]

Also one thing that really bugs me about the current graph is if you didn't log anything for a year then it just skips it and looks like that year never happened

This fix has already been made. I now create the time series separately then fill it it with the stats.

For this particular graph (or I guess anything else timeseries) please add the granularity to the base data eg granularity = "year" "quarter" "month" "week" "day".

Currently you can use 'year', 'month', 'day'. I will add the others as a priority but it will be a couple of days before I am on this again.

• [x] simon to add 'quarter', 'week' time series
• [x] add base facet to params - baseFacetUrl : "/ascents/by/brendan/with-gear-type-sport/"
• [x] add facet suffic in slice: facetSuffix: "between/2016-01..2016-02/

[brendanheywood]

Ok I've just pushed a super rough start which is enough to show the shape of things, here is lee's sport cpr

and mine:

The obvious thing that jumps out is the cpr saw tooths due to intermittent steps up a grade and the decay. I was expecting the saw tooth, and to have to deal with it to make it look nicer, but seeing the raw little jags is actually a good thing to expose right now. I'm pretty sure we use the general smoothness of the steps, or not, and how regular they are, can be used as another validation method similar to what simon just did with the powerfactor and the grade slider.

If all our theorising is right then we should see somebody have lots of incremental new highest cpr's as a good predictor before they crack the next grade. And if the power factor is right then those jagged steps should form a reasonably straight line slowly trending up. If the power factor is wrong then we would either see lots of low steps as they repeat a lower grade and then a big jump when they tick the next level up, or the opposite where when they actually tick the next grade up.

@scd my dev I assume is still using power factor of 7. Can you please take screen shots of the two graphs (lees + mine) and past them here for comparison

https://dev.thecrag.com/ascents/aggregate/graph-sport-cpr/with-route-gear-style/sport/by/brendanheywood/

https://dev.thecrag.com/ascents/aggregate/graph-sport-cpr/with-route-gear-style/sport/by/leecujes

Purely on the visual side, ultimately I don't want to show a jagged line like this. I have some ideas on that but I'll worry about that a bit later. Simon to support whatever smoothing works best, is there enough in the current data for me to infer when the slice has a 'real' cpr vs one that has been calculated via decay? If not then perhaps the easiest would just be to add to each time slice the epoch of when the last cpr was? if it's between the start / end of this slice I know it's current

[scd]

Lee - grade factor 5

Brendan - grade factor 5

[scd]

And here is another good reference - vwills

And here is jakob

[scd]

This is really awesome.

• Although we are not tuning for it now, the decay feels about right in these graphs.
• Saw tooth will be exaggerated on month to month basis (cpr at end of month). We really should compare the day graph. There was a problem with doing period=day which I will look into
• @brendanheywood please look at Application/PowerRating.pm to see what grade factor you have. I have not been pushing that file, so it should be still set to 1.15 (maps to ewbanks 7)
• Is it worth while starting the graph at the minimum for the purposes of the initial analysis?
• I am now second guessing my original recommendation and maybe we need a time series of daily CPR within each slice.
• I am super interested to see this compared with number of ascents

[scd]

Here is lee, power factor 5 graphed by day.

[scd]

FYI I have put quarter and week into the time series now

Here is lee, grafactor 5 plotted by quarter

This is the first level of granularity where there is an obvious difference in the graphs. I think this might be telling us something, but I am not sure if it is not enough data points or something inciteful about training cycles.

I am wondering what the business rules should be for auto select of time period. Do we want to auto select quartery/yearly ever?

[brendanheywood]

Can you refresh me on the monthly cpr? I was expecting the cpr here to be in sync with whatever time slice width each graph is auto set to. If they are a new climber with 2 months of ticking the cpr is daily. For long climbers like lee month or quarter is fine.

Did you end up implementing all year / quarter / month / week / day? And with a min number of slices of say 50? If so, and we feel it is not showing enough data I would simply ramp the 50 up to say 100. I don't think we need too much more sub slice data as there won't be enough pixels to display it anyway

Re minimum cpr at the start of the graph, also at the end, just leave that for the time being I can deal with that visually and it's on my radar already

Don't second guess your impl too soon, I think it's ok, let me sort the graph more to inform how much we need to tweak the data grain

[brendanheywood]

And yeah please start working on the second volume by tick type time series template data

[brendanheywood]

I am wondering what the business rules should be for auto select of time period. Do we want to auto select quartery/yearly ever?

Yes. If our 'data density' min level is 50 slices then: someone has climbed for 50 years with get 50 year slices, someone who has 15 years will get 60 quarters, 5 years with get 60 months, 1 year will get 52 weeks and 2 months or less will get days

[scd]

I have only just pushed the auto selection of time series. Algorithm is slightly different but broadly equivalent. If climbed for 50 or more years then default to 'year', 15 or more years then default to 'quarter', if 5 or more years then default to 'month', if 1 or more years then default to 'weeks', otherwise 'days'.

Note that if a climber has been climbing for 350 days then it will default to days with 350 time slices. This implies to me we probably need a biweekly. Do you agree?

For the purposes of the initial analysis we should probably be explicit.

All other time slices are now implemented in repo

Also I forgot to make an observation about the saw tooth function being an excellent way to trigger a communication with either the climber of the people who are following the climber.

Hopefully there are no bugs in repo. I am off for the afternoon.

[brendanheywood]

Ok, I've implemented some basic detection of when we are just getting a decayed value vs a real one and only showing the real ones (although I would prefer this outside the templates). I've also added onsight cpr. To take a little bit of the harshness out of the steps I've made this a bezier curve which slightly 'droops' into each step:

straight lines maybe look a little better, but I find it a little misleading as that's not an accurate indication of their cpr at that point - but neither is the curve so maybe we don't care too much (except for the right side if they haven't climbed at all for a while - on the list...)

[scd]

I definitely prefer the straight line vs curve.

Are you using the 'count' parameter as a heurestic whether or not it is pure decay? Is there something more you need for the backend.

I think we need red point, flash and pink point curves as well. This will help us visually verify our tick shifts. I will get onto this tomorrow.

[brendanheywood]

Can you also add a band for what people are attempting / dogging, I think that will be an interesting thing to see if attempting highly challenging routes is a consistent indiicator of future performance growth

Re straight lines, I prefer this too visually. Do you care that it doesn't reflect the hidden real score? If not then it does free us up to tune the decay more agrressivly if we wished - there has been a couple things which have suggested to me that the decay should be stronger but I wanted to get the graphs in place first to help judge this. I'll collect details on this for discussion

[scd]

Can you also add a band for what people are attempting / dogging, I think that will be an interesting thing to see if attempting highly challenging routes is a consistent indiicator of future performance growth

Hmmm, let's put this on the to do list. There is an outstanding question as to whether we rate these routes. Note that I have also got the same outstanding question for rating top ropes and seconds. These need more consideration and possibly need the ticking pitches properly solved first.

Having not rated these routes, they are not in the assessment list. So we have two alternatives:

1. Rate the routes and work through the tick shifts.

2. Change the query to recalculate the rating from raw data and include these routes. This is something I want to do anyway to test the grade factor real time.

   Re straight lines, I prefer this too visually. Do you care that it doesn't reflect the hidden real score? If not then it does free us up to tune the decay more agrressivly if we wished - there has been a couple things which have suggested to me that the decay should be stronger but I wanted to get the graphs in place first to help judge this. I'll collect details on this for discussion

Don't care. Will they be accurate for the last day of each time slice.

There is a potential issue with the final time slice. The cpr returned by the server will be the decayed to the last day of the month. It is possible that this will be in the future and therefore a lower cpr than on the current day.

I have no alarm bells with a decay somewhere between 0.5 and 1 ewbanks grade a year.

[brendanheywood]

1. Rate the routes and work through the tick shifts.

I think we should have a rating for every single tick type. If we want to exclude something like dog then I don't see any practical difference between excluding a tick type vs giving it a tick shift of -40. I don't think it would make any difference to the performance and it makes the algorithm simpler. I think especially for beginner climbers who might only ever top rope at KP and who might only ever do a 'top rope clean' a rating still has value for seeing their progression and also comparing to their mates. Every tick type represents some fundamental effort which in turn would progress your climbing ability, the problem is not figuring out 'if' but figuring out how much. Possibly even a 'target' has a physiological positive effect.

Also isn't what I'm asking for exactly what you've already done with the onsightCpr? It doesn't have the tick shift applied and is just anything with a where type = 'onsight', all I want is the exactly same but include every possible tick type. Either that or just give me the hardest grade of any tick type at all for that time slice which should be even easier.

[scd]

I think we should have a rating for every single tick type

I will add a few more. Do you think we should include the 'Attempt', 'Ghost', 'Working', 'Retreat' tick types?

Here is a list of what I propose to put in for Trad. Please review the subjective logic on how I came up with each tick shift.

#'Attempt' => -91, # 7 x 13
#'Ghost' => -91, # same as Attempt
#'Working' => -91, # same as Attempt
#'Retreat' => -91, # same as Attempt

'Dog' => -65, # 5 x 13
'Top rope with rest' => -65, # same as Dog
'Second with rest' => -65, # same as Dog

'Second clean' => -52, # 4 * 13, little bit less then Pink Point
'Second' => -52, # same as Second clean

'Top rope onsight' => -39, # same as Pink Point
'Top rope flash' => -46, # 3.5 * 13
'Top rope clean' => -52, # same as Second clean
'Top rope' => -52, # same as Top rope clean

[brendanheywood]

I will add a few more. Do you think we should include the 'Attempt', 'Ghost', 'Working', 'Retreat' tick types?

Yes, specifically I'd like to see if the kind of people who progress through the grades are the ones who are consistently trying things much harder than their current performance grade. The question is whether for this band we simple do the hardest of these types, or whether we do the whole rating + decay logic for them. I think just hardest is a more useful thing, as that would more clearly show when they stopped trying ambitious projects. But this could also be solved if it used the same decay but the decay was stringer everywhere. I've already seen a few profiles where their performance has clearly gone down but out ranking doesn't clear reflect this reality because the decay is much too weak. I'll find some examples for discussion

[scd]

I've already seen a few profiles where their performance has clearly gone down but out ranking doesn't clear reflect this reality because the decay is much too weak. I'll find some examples for discussion

What do you want to change it too? 1 Ewbanks per year?

Also are you happy to commit to power factor of 5. I will have to redo all the stats, so I might as well redo it for tick shift, decay and grade factor all at once.

[scd]

Actually I don't need to ask. I am pushing power factor to 5, and doing decay of 1 grade per year as the next step in the gut feel test. Note that I think we will always be able to find examples where the decay or power factor or tick shift does not match a particular account.

The decay is live for the time series, but the tick shift and power factor needs a total stats rebuild so that will be a couple of days.

[scd]

I have added un-tick shifted cpr for flash, red point, and pink point. Also added 'allCpr' with the new tick shifts. Let's see how this all looks. Actually for the purposes of the time series graphs there is only one function you need to run (cd /opt/CIDS/script; ./fix-ascent-power). It will take a couple of hours on your dev. Either pull and do it yourself or pull and let me know so that I can do it?

The question is whether for this band we simple do the hardest of these types, or whether we do the whole rating + decay logic for them

IMO we should definitely use decay and use this as a signal to make decay stronger.

[scd]

I have just reviewed the data in existing time series profile page and I think that you should be able to recreate the profile page time series graph with the data I am now providing. Except for the crags where the top ascents were and the hardest grade climbed in that time slice. Both of these can be retreived via an interactive sub facet call.

• counts of various tick types
• cpr performance grade (equivalent to existing performance grade)
• performance grades various tick types (new)
• performance grade of your ambitious projects (new)

I reckon we can put this together.

[scd]

FYI, I have just put baseFacetPath and facetSuffix into the template data

[brendanheywood]

Ok I've added those to the chart and turned them into area fills.

This shows a vague tendency to have periods of 'grey' with lots of attempts before progressing up the grades. But it is very poor data wise, mostly because most people would never logging all their failed attempts. For this reason I considered just not having it, but instead I've kept it in but made it very faint grey.

This is exposing an issue where a 'lower' shape like onsight ends up higher than that an area above it, like red point:

This makes no sense to me, the onsight band should include onsight, while the red point band should include redpoint + onsight. So I can't see how this is possible @scd ?

There is a smaller issue where because we are filling in the steps with straight lines, but I think fixing the issue above will also mitigate this one a fair bit too

[scd]

This makes no sense to me, the onsight band should include onsight, while the red point band should include redpoint + onsight. So I can't see how this is possible @scd

This is definitional based on returning exact matches to tick style.

Now that we see this in the graph it is very clear that red point cpr should include onsight cpr, rather than exact match. So an onsight will add to the pink point, red point, flash and onsight cprs.

Do we want to treat the ascent count in the same way?

[brendanheywood]

Now that we see this in the graph it is very clear that red point cpr should include onsight cpr, rather than exact match. So an onsight will add to the pink point, red point, flash and onsight cprs.

Cool. I think for consistency and to avoid the same issue we should do that for all the shapes? ie the light grey 'other' shape will include everything, and the pink point will also include red and below, and red will include the green shape. I suspect we have end up with other shapes / color regions as we find more edge cases but the same would universally apply to each containing the ones below it.

Do we want to treat the ascent count in the same way?

I guess, I haven't actually used it yet but yes lets make it consistent anyway.

[brendanheywood]

Just thinking through all the possible bands that would have value. I think for bouldering at least, but probably for all of the three ratings we should have a 'flash' band. This also brings it up to parity with the existing charts.

I also think there is a good case for beginners having a top rope band but this needs a lot more thought though and we can leave that for a couple months - because it potentially involves the top rope gear style as well as the top rope tick types. And if you top rope a trad route that's worth mostly the same as top roping a sport route. yikes don't want to think about it at all for now!

[scd]

Just thinking through all the possible bands that would have value. I think for bouldering at least, but probably for all of the three ratings we should have a 'flash' band. This also brings it up to parity with the existing charts

Yup I have included flash, but have to map it to include onsights. I think when I update the CPRs the redpoint area will become very thin where people are onsighting harder then a straight redpoint. I think this will look really good, especially as the grades get harder the red will get thicker.

And if you top rope a trad route that's worth mostly the same as top roping a sport route. yikes don't want to think about it at all for now

I put a little bit of thought into this with the new tick shifts for top ropes and seconds. It is not based on stats, and I am happy to run with a gut feel for a while. One issue I have not come to terms with is that it is probably harder to onsight a top rope than pink point the same route. I think the stats show this and sounds logical, however we really don't want any top rope routes to be worth more than a lead route.

[scd]

I have just made the change on repo to update cpr tick types to cpr tick type groups. It was a good observation because it brought in more stuff such as First Ascent is a red point. I was unable to test what the graphs now look like because your stuff was not pushed.

I also added second and toprope. I am not sure how these will fit in just yet, but it is there in case you want to experiment. I imagine we will eventually break top rope up into onsight/flash/etc. I will do that once we know that is what we want.

[scd]

Wow, I just had a look at some graphs with what you just pushed to repo. This looks really good and is hugely informative. Some thoughts?

• [x] Can we list test accounts which show different issues, so if we make a change we can quickly run through them?

• [x] How hard will it be to make decay a percentage rather than a fixed shift? My brain cannot get around whether the maths holds together.

• [x] One thing I would also like to see is number of ascents. One of the predictors of performance is time on the rock, which is really number of ascents.