One of our users flagged that they felt they should have a specific badge. I had a poke around the Badges admin page and ran the Preview granted badges query and they aren’t in the list. Is the best way to check potential eligibility to use something like Data Explorer to get more info from the DB?

[image] Mark_Walkom: One of our users flagged that they felt they should have a specific badge. [image] Mark_Walkom: Is the best way to check potential eligibility to use something like Data Explorer to get more info from the DB? We get quite a few questions about the Aficionado (100 consecutive days) badge. Usually the problem is that a user has missed a day or more due to differences in time zones between their locale and the server’s time. I’ve always been a little confused by the query that’s used to award the badge. I think I’m understanding it now. Here’s a Data Explorer query that’s using similar logic. It returns the consecutive visit periods for a user. It returns the start date, consecutive days count, and end date for each period of consecutive visits. By default it returns all periods with 10 or more visits. To find what days a user has missed, set the min_days parameter to 1. If the period_end value that’s returned in the first row of results is the current date that you’re running the query on, the user is still in the running for the badge. --[params] -- string :username -- int :min_days = 10 WITH consecutive_visits AS ( SELECT user_id, visited_at, -- The value of s will be the same for each group of consecutive days visited_at - (DENSE_RANK() OVER (PARTITION BY user_id ORDER BY visited_at))::int s FROM user_visits WHERE user_id = (SELECT id FROM users WHERE username = :username) ) SELECT MIN(visited_at) period_start, COUNT(*) AS consecutive_days, MAX(visited_at) period_end FROM consecutive_visits GROUP BY user_id, s HAVING COUNT(*) >= :min_days ORDER BY period_start DESC

On the actual badge page itself (via your Admin dashboard) you can preview the query results, which I think is what you’re saying you’ve done. Running that query through Data Explorer should return the same results. If you’re still stumped, flick us an email and we’ll help you dig.

Perfect, that’s given me the insight I was after :slight_smile:

Checking badge eligibility

Community Building Data & reporting

Mark_Walkom (Mark Walkom) June 16, 2020, 4:54am 4

Perfect, that’s given me the insight I was after

Topic		Replies	Views
30 days visitor badge Product Support	5	711	August 9, 2020
How do I check a badge's status midway to it being awarded? Product Support	2	1420	July 31, 2019
'Seen in last X days' badge? Data & reporting sql-triggered-badge	12	386	August 26, 2016
Devotee Badge and Timezones, relaxing requirements? Feature badges	3	1551	March 17, 2020
How can I see how many days in a row I visited the forum? Product Support	2	1835	October 25, 2018

Checking badge eligibility

Related topics