Back to Blog

How to find repeat free trial users in your database

Your trial count may be the same few people. Find repeat free trial users with simple SQL, see why the result is only a floor and which basic fixes break.

How many different people have actually taken your free trial? Slide 9 says 7,200 trials, and your co-founder has just messaged you: "Investor call first thing tomorrow. They asked how many of those were different people. What's the number?"

You start typing and stop. You built the trials table yourself, you've quoted that 4% conversion rate for a year, and you have no idea. Your database knows about accounts. It has never recorded a person.

So you open a SQL console to find repeat free trial users, and while you work out where to start, you run the arithmetic you've been putting off. This is an example, so swap in your own numbers. Say 600 trials of 14 days start every month, and 180 of those "new users" keep coming back.

600 trials a month x 12 months    = 7,200 trials
1,440 repeat trials / 180 people  = 8 extra trials each
1 first trial + 8 extra           = 9 trials each
9 trials x 14 days                = 126 free days each

That's 180 people on your product for about four months each, for nothing. At $49 a month, 126 days (4.2 months) is $205.80 each, or $37,044 across all of them. You'd never have collected all of it, because some would never have paid. But someone who can start over never has to decide.

The trials aren't free to run either. At $9 each, 1,440 repeat trials is $12,960 a year. If you spend $18,000 a month, that's 0.72 months, about 22 days of runway, paid out through the feature you built to win customers.

Your paying customers feel it next. On a busy Tuesday, someone on your $199 plan watches a report sit in the queue for 40 minutes behind an account on its ninth trial, then asks you for a refund.

Then there's slide 9. You've reported 288 paying customers out of 7,200 trials, or 4.0%. If only 5,760 of those trials were someone new (7,200 minus 1,440), the rate is 5.0%, and your funnel is 20% smaller than the one you've been describing. A better percentage sounds like good news until you remember that you called 4% weak in March and cut the trial from 21 days to 14.

It's 5:40 p.m. You can say 7,200 again tomorrow and hope nobody asks twice. You can tell your investors you don't know. Or you can cut the trial again, and make 5,580 people who only wanted to try your product pay for what 180 people did.

Why your database can't see repeat trials

Your trial logic works one account at a time. A signup creates a row, the row gets a trial_ends_at, and anyone who wants another trial only has to become a new row. A unique index on email guarantees that no two rows hold the same string, and that's all it ever promised. This pattern has a name: trial farming.

The obvious fix is a rule that refuses a second trial on an account. Have that rule, but nobody here is asking for a second trial on an account. They're asking for a first trial on a new one. So before you change anything, measure what you can.

How to find repeat free trial users, step by step

This takes an evening and read access to three tables. The names below are placeholders: trials has one row per trial, accounts has the email, and billing_details has whatever payment information you keep. Change the date in each query to 12 months ago.

Trials from the last12 monthsSame email,more than one trialSame billing detail,more than one accountCount eachtrial onceRead 40 rowsby handRemove trials yourteam gave on purposeReport it asat least this many
Trials from the last12 monthsSame email,more than one trialSame billing detail,more than one accountCount eachtrial onceRead 40 rowsby handRemove trials yourteam gave on purposeReport it asat least this many

Write down what a repeat trial is. For example, "a second trial for the same person within 12 months that nobody on our team gave them." The count means nothing without a rule like that.

Look for the same email with more than one trial.

repeat_trials_by_email.sql
select lower(a.email) as email,
       count(*)       as trials
from trials t
join accounts a on a.id = t.account_id
where t.started_at >= '2025-11-01'
group by lower(a.email)
having count(*) > 1
order by trials desc;

If your emails are unique, this mostly finds trials restarted on one account and people who deleted an account and came back. It only finds that second group if trial rows outlive the account, so an empty result can just mean the history is gone.

Look for the same billing detail on more than one account. Run this once per column you keep, such as a billing email or a card reference from your payment processor that stays the same for one card.

repeat_trials_by_billing_detail.sql
select b.card_ref,
       count(distinct b.account_id) as accounts,
       count(distinct t.id)         as trials
from billing_details b
join trials t on t.account_id = b.account_id
where b.card_ref is not null
  and t.started_at >= '2025-11-01'
group by b.card_ref
having count(distinct b.account_id) > 1
order by accounts desc;

If your trial doesn't ask for a card, this comes back nearly empty. That tells you nothing about abuse, only that the column is blank.

Count the repeats, once each. For every row, repeats are trials minus one. When a trial is on both lists, count it once. In our example the email list has 130 repeats, the billing list has 300, and 30 are on both: 130 + 300 - 30 = 400.

Read 40 rows by hand. A shared card is often a company card, an agency might start trials for clients, and your support team might have given someone a second go. Take out anything you'd have allowed. If 4 of your 40 are fine, that's 10%, so 400 becomes 360. Don't ban anyone from this list.

Redo the conversion rate.

Adjusted conversion=Paying customersTrials−Repeat trials found\text{Adjusted conversion} = \frac{\text{Paying customers}}{\text{Trials} - \text{Repeat trials found}}

Here that's 288 ÷ (7,200 - 360) = 288 ÷ 6,840 = 4.2%. Measuring your free trial abuse rate goes further.

What to send your co-founder

At least 360 of our 7,200 trials in the last 12 months were repeats, so trial-to-paid is at least 4.2%, not 4.0%. That's a floor. The real count is higher, and we can't measure it from our own data.

Why the audit result is only a floor

Both queries ask whether two rows hold the same value. A second account with a different email and no card on file shares nothing with the first, so it doesn't come back at all.

Email makes this worse, because one inbox can sit behind many addresses. Gmail ignores dots in a standard address, so sam.lee@gmail.com and samlee@gmail.com reach the same inbox, and mail to sam+anything@gmail.com lands there too. Your first query counts those as different people. Catching that takes alias rules that need upkeep.

Repeat trials in one year, illustrative02004006008001000120014001600Same emailSame billingFoundLikelyRepeat trials
Repeat trials in one year, illustrative050010001500SameemailSamebillingFoundLikelyRepeat trials

Nobody can read that last bar out of your tables. It's the 1,440 from our example, there to show the shape of the gap. If the gap looks like that, your evening's work found a quarter of the problem (360 ÷ 1,440 = 25%). The other 1,080 repeat trials cost another $9,720 a year (1,080 × $9), and on every chart they still look like new customers. Meanwhile, this month's 600 trials have already started.

The basic fixes, and how each one breaks

Now you have a number, and you'll want it smaller.

Basic fixWhat it catchesWhat it missesWho it wrongly blocksWhat it costs to keep working
Email verificationTypos and dead addressesAny second real inboxPeople whose code lands in spamDeliverability work
Unique emailsThe same address twiceEvery new addressAlmost nobodyVery little
Disposable-domain blocksKnown throwaway servicesNew domains, free mailboxesPeople using privacy forwardingA list someone updates forever
CAPTCHASimple scriptsA person signing up by handScreen reader usersFees and lost signups
Card or phone requiredCasual repeat trialsA second card or numberStudents, careful buyersFewer trial starts
One trial per IP address, VPN blocksRepeats from one connection, known rangesAny other networkOffices, campuses, company VPNsExceptions and tickets
Rate limits and usage capsOne account overrunningNine accounts inside the limitReal users on a busy dayTuning each endpoint
Shorter trialsSome cost per trialMore trials make up the daysEvery honest userFewer people reach paying
Manual reviewWhat a person can spotAnything spread over monthsWhoever looks odd to a tired reader10 hours a month at 1 minute per trial

Take the shorter trial, because it's the one you'll be tempted by at 5:40 p.m. Cut 14 days to 7, and the same 126 free days takes 18 trials instead of 9. The 180 people lose a few minutes each. Everyone else gets half the time to find out if your product works for them. The same trade-off applies to requiring a card and adding a CAPTCHA.

Every row in that table needs someone to look after it, usually you. The audit is still worth keeping. Run it every quarter and before any number goes to an investor, keep a small record of each trial after an account closes (within what your privacy policy promises), and always write the result as "at least".

Asking before the next trial starts

Your audit runs after the trial has been paid for. AbuseZero evaluates the claim before a trial goes out, and it starts where those queries and that table run out. Your second query needed the same card on file twice. AbuseZero detects repeated payment fingerprints across users, even when new accounts and emails hide the connection. Your first query needed the same string twice, while AbuseZero checks mailbox aliases and disposable domains and recognizes repeat devices across new accounts, even when email addresses keep changing. In AbuseZero, signals are read together, never one at a time. AbuseZero flags abuse that single checks miss and detects linked claims, with multi-layer AI risk analysis examining 140,000+ signals. There's also a proprietary algorithm, with AI that checks the claim again. Creating a fresh account isn't enough to erase previous activity.

You make one API call before the trial starts and get a clear allow or deny, under rules you control. Observe mode shows you what would be denied before anyone is blocked, so the next time someone asks about slide 9, you have more to go on than an evening of SQL.

You can also import your claim history, so claims you've already granted count from your first evaluation. The quickstart walks you through your first benefit.

Frequently asked questions

How do I find users who took a free trial twice?

Group past trials by email, then by each billing detail you keep, and look for groups with more than one trial. Read a sample by hand before you trust the count.

Can SQL find every repeat free trial user?

No. It finds people who reused a value you stored. Someone signing up again with a new email and no shared billing detail stays invisible.

Should I ban the accounts this audit finds?

No. Some will be teams, families or agencies. Start with how to tell a returning customer from a duplicate account.

Spend your next trial on someone new

Keep reading

Copying your prompt…

Copied the to your clipboard. Give it to your coding agent & it will handle the rest.