Checking badge eligibility

Data & reporting
1

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?

3 Likes
2

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.

4 Likes
3

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
6 Likes
4

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

3 Likes
5

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.