Reminding clients to renew their domain names registered with other registrars?

[trappedatuf]

I have many hosting accounts and as a courtesy my old (custom coded) billing system used to send out domain renewal notifications for domain names that weren't registered with us.

 

Does WHMCS warn my hosting clients that are registered with other registrars (and thus don't have a domain noted in the domains tab) that they need to renew it with their registrar to avoid their website going down?

[Zorro67]

i also have a of few these. The biggest reason for this (in our case) is a customer not wanting to pay for a renewal fee when transferring a domain to us whilst they still have some domain time 'up their sleeve', unless it is close to transfer time.

 

Of course for some tlds (Eg .au), you can't get the renewal date, and have to work on other extrapolated dates to guess approximately when the renewal is coming.

 

It could probably be achieved by a custom reminder template (the standard one is lame), that recommends that they complete a renewal as part of the transfer process.

 

However, this obviously isn't automated. Could it be?

[trappedatuf]

Well my idea was more along the lines of adding a Domain Pricing Structure that charges $0.00 for domain names that aren't registered with me. I then add the domain as usual for the client(s) and wouldn't they get a reminder that their domain name was expiring soon? Plus this wouldn't affect my system because the price would be $0... am I correct to assume this?

[handsonwebhosting]

The only problem with adding a 0.00 "renewal" is that it will send them a notice to say that the domain is renewed, and then they come yelling to you that you said it was renewed even though they use someone else. With some SQL and custom coding you could do something like this:

 

Select email addresses of users who have a hosting account with no domain name registration matching that domain.

Run a WHOIS query to get the expiration date

Mass mail customers 60 days prior to expiration date and encourage them to renew their domain (and upsell your own domain registration service).

 

Other option is to select all ACTIVE hosting packages that do not match an ACTIVE domain registration, run a WHOIS query, insert that query into a database, then you're not actively running WHOIS queries on every run of the script. Just run that every 15-30 days in case users have updated their registrations etc.

[trappedatuf]

Handson... that's excellent and a great idea, thanks!

 

Anyone want to tell me what tables to get that info for? If you guys can give me a head-start with that info, I'll create the SQL queries and such and code this thing up for us!

[handsonwebhosting]

I'm actually doing just a quick run through the forums here right now as I'm headed out the door, but when I come back I'lll run through the database and see what tables you'll need and see if I can find a freebie script that will poll to a whois database automatically (and not block you)

[trappedatuf]

Alright, I decided to take a gander and try and get the ball rolling. I think with your help I can get this figured out Handson!

 

So here's the first query I tried that basically lists all domains that have a hosting account and also have an associated domain name. This is the opposite of what we want but since it was a simple INNER JOIN I decided to start here for reference:

 

SELECT tblhosting.domain
FROM tblhosting
INNER JOIN tbldomains
ON tblhosting.domain = tbldomains.domain

 

The above query provides all domains that live in both tables. Now we just want the opposite of the INNER JOIN... which would be all hosting account domains that AREN'T listed in tbldomains.

 

So to get that I found this post in my Google searching... http://forums.devshed.com/ms-sql-development-95/help-for-opposite-of-sql-inner-join-in-access-196345.html and that pointed me in the right direction.

 

We don't need both statements in that query they provide because that gives us the absolute opposite (which would be any domains not represented in either table). Before I confuse everyone I'll shut up and just post the actual query that worked!

 

SELECT tblhosting.domain
FROM tblhosting
 LEFT OUTER JOIN tbldomains
   ON tblhosting.domain = tbldomains.domain
   WHERE tbldomains.domain is null

 

So that gives us all the domains that have a hosting account but no associated domain. These are the people I'd like to send a reminder to renew their domain name registration with their current registrar (or transfer to our registry services) to ensure their domain name doesn't expire and their website goes down.

 

This will save a LOT OF HEADACHE because they think it's our fault their website went down, when in-fact it is their's because they left the domain expire. I think as a good webhosting company we should do this as a courtesy because our clients have better things to do than remember this on their own (even though they should!).

 

 

Now it's your turn Handson... let's try and get the code that uses these domains to work now. Thanks for all your help with this

 

PS: I know we'd have to pull the userid as well in that SELECT statement so we can use that to lookup the user's name, email address, etc. so we can send them the warning email.

[othellotech]

mysql> SELECT tblhosting.id,tblhosting.userid,tblhosting.packageid,tblhosting.server,tblhosting.domainstatus,tblhosting.domain FROM tblhosting WHERE tblhosting.domain NOT IN (SELECT tbldomains.domain from tbldomains) AND tblhosting.server > 0 AND tblhosting.domainstatus IN ("Active", "Suspended") AND tblhosting.packageid NOT IN (146,147,148) ORDER BY tblhosting.packageid;

 

change the (146,147,148) to reflect *your* packageid's that dont have domains associated with them - this will give you it sorted by package - adjust to suit your requirements

[handsonwebhosting]

Well here's SOME code to get started - it's all operating from a shell script right now. I've manually inserted the domain names (but we can read that from a file), it's the output that i'm concerned with at the moment. Right now I have it with "domain - expiration date" and probably I'll need to convert the date into a unix time, and then compare that time with the current time and if less than 30 days, then send email or "do x" whatever that might be.

 

Here's what I have so far on it:

 

#!/bin/bash
# Remove previous expiration list
rm -rf /tmp/expiration.list
# Domain name list - add your domainname here
DOMAIN="handsonwebhosting.com handsondomains.com handsonssl.com handsondedicated.com"
for expiration in $DOMAIN
do
 echo -n "$expiration - " >> /tmp/expiration.list
 whois $expiration | egrep -i 'Expiration|Expires on' >> /tmp/expiration.list
 #
 echo ""
done

 

Basically put that into an "expiration.sh" file, run it and it will dump out a /tmp/expiration.list file.

[trappedatuf]

Whew, I have to say I'm super rusty on my shell scripting ever since the age of PHP!

 

Let me play around and you do the same handson and I'm sure we can come up with some nice code everyone can use!

 

Othellotech: thanks for that SQL code, I'm going to mess with that as well since you're is more robust. How would we tie that in to get the client's name and email address for each domain?

[handsonwebhosting]

hehe.. I found a gem for us all

 

http://www.techdose.com/tutorials/php/domainNotify/

 

I didn't want to just paste their code here as it's THEIR CODE, and not mine, but I'm sure with some tweaking, we can use this information to our advantage.

 

I've got a couple things on my list here today, but maybe I'll get back into this later tonight and see what I can come up with.

[handsonwebhosting]

Well I've been playing for a while with the code that Rob provided (works like a treat) and then implementing that through the code I found on the "techdose.com" site - but for some reason it's only updating ONE row (the first one) with the expiration date, and not continuing to the next row. So I changed up the code to INSERT rather than UPDATE, and again, it's only inserting ONE row (the first from the query) - so I'm missing a loop somewhere.

 

Going to take a break, grab a cigar and think about it for a while and see what I can come up with. Using the code that Rob provided to export a list of domain names works perfectly - now I just need to read those that are exported, run the whois, and either UPDATE or INSERT into a new table with the expiration dates

 

Boy - typing that it sounds like I hadn't done anything, but I SWEAR I've been here for a while working on just this script!

[SilverNodashi]

Most domain registrars will send out email reminders 30 days before a domain needs to be renewed. So, if a client has a domain registered elsewhere, he's going to get a reminder from the current registrar.

[handsonwebhosting]

Only if you have that option enabled We have ours disabled for our own registrar so that the notces come from US and not another company and the wording on the email makes sense to the user (telling them where to login, how to renew etc).

 

I can't tell you how many people use an email address for their domain name that they don't normally check (too much spam etc). This also gives the chance to upsel services that you offer and potentially "TAKE" a customer from another domain registrar.

 

Still playing with the code.

[trappedatuf]

Sorry handson, I've been busy with other projects but hopefully this weekend I can look at that good code you found and help out! You're doing great and I know we'll be able to get this going between the both of us