Most susceptible users

This report is a per-user 12-month susceptibility rollup: one row per person who received at least one simulation in the trailing year, with click / credential / report counts and rates, a current click streak, and a concatenated last-5 outcome list. The window is 12 calendar months (midnight UTC on the same day-of-month twelve months ago), not a 365-day duration. Setup, credentials, pagination, and rate-limit retry are the same as the Power BI / Excel connectivity guide. Add the shared Fn_FetchAll query first; this report calls it by name.

​

Required key scopes

This report uses the operational per-person results list, not the reporting preview family. A key granted only reporting:read cannot call GET /v1/simulation_results (the API returns 403). See choose the right grain.

​

Source endpoints

Allowlisted filters on the results list: limit, page_token, simulation, entity. There is no delivered_after / start_date on this list, so the trailing 12 months is applied in Power Query after the full walk. Fields used on each result: id, simulation, entity, status, targeted_at, delivered_at, bounced_at, opened_at, clicked_at, attachment_opened_at, credential_submitted_at, reported_at, reported_before_interaction, bot_activity_detected, email_subject, sender_email, difficulty_level, created_at, updated_at.

​

Grain

One row per entity that has at least one result with delivered_at in the trailing 12 calendar months. A person targeted but never delivered does not appear. Multiple simulations for the same person collapse here. Request budget: one paginated walk of every simulation result in the organization, plus one walk of entities. Always set limit = "500".

​

Outcome precedence (last_5_assessments)

Each of the five most recent delivered results (delivered_at descending) is summarized as YYYY-MM-DD: <outcome>. The first matching timestamp wins, highest priority first: Delivered-only is no action (the message landed; the person did nothing). A person who clicked and later reported is clicked — report does not outrank a risky interaction on this list. Bot-flagged rows still get an outcome label from the timestamps that are present; they just do not increment click / credential counts. Current click streak: walk those same delivered results newest-first and count consecutive rows where clicked_at is non-null and bot_activity_detected is not true. Stop at the first miss.

​

Column mapping

Legacy column names are from your previous provider’s export. /v1 fields are the public API names; the query derives the counts and rates. Join key kept for a star-schema model: entity_id.

​

Divergences from the legacy export

• Bot activity is excluded from click and credential counts. A previous provider’s export counted automated scanner / bot interactions as clicks and credential submissions. This query does not. A result with bot_activity_detected = true is still a delivered simulation — it counts in total_received and in reported aggregates — but it is treated as a non-click and a non-submission: it is omitted from clicks / credential_submissions and it breaks a current click streak. Do not “fix” this by counting bots to match the legacy export.
• Report does not outrank a click on last_5_assessments. A person who clicked and later reported is labeled clicked.
• Directory extras are omitted, not nulled. Department and similar fields are not on /v1.
• No server-side time filter. GET /v1/simulation_results has no delivered_after. The query walks every result, then keeps delivered_at in the trailing 12 calendar months. A large historical org may spend more of the per-key rate budget than a filtered list would.

​

Next steps