A collection of stories of evolution, follies, starting from scratch, and what not to do.
When I started my ISP back in the summer of 1996, how to bill customers and collect money from them was an afterthought. I started with nothing, not even a database, and not realizing what a vital chore it would become. There were some ISP billing programs out there, but they were often very expensive, rigid, or expected you to be a Windows NT shop. (I was a UNIX/Solaris shop.) Compared to providing customer technical support, the act of actually generating invoices and processing checks was equally as consuming of time. If I had a better story around payments the business may have been more profitable and lasted longer. It gives me a lot of appreciation for the problem of billing and payments, there’s a lot of considerations that have to go into it.
There were a few decisions early on that set the course of things to come:
Metered billing
I was one of the few ISPs that did metered billing instead of “unlimited” access. You subscribed to a base tier and for $/month you got so many hours online per month, and then paid so many cents/minute of overage after that. (E.g. 25 hours for $15.95/mo or 240 hours/mo for $25, 2 cents/minute overage.) Unlimited access was the norm, where you paid a flat rate of $19-$30/month and use it however you wanted. The idea that somebody could leave their computer on all day sitting there hitting the POP3 email server while not at home, occupying my limited phone lines and modems, was completely ludicrous to me. To me selling “unlimited” service felt shady and a legal liability because there were always buried terms and service to harass or fire the customer if they were online “too much” after just telling them “use it as much as you want”.
This model lead to two immediate problems, how to do recurring monthly billing along with usages, and how to process call accounting? The usage model ruled out using something like Quicken to send out simple invoices. A few ISPs I knew repurposed old pager billing software, as it had the notion of recurring billing and minute rates. I forget what I did in the very beginning, there was probably some perl script I got somewhere that processed RADIUS accounting and generated daily reports.
Checks, invoices, postal mail
Being a rural area and 1996, the standard way people paid was with a check or money order. You mail them a invoice, they mail you a check. Not many people had credit cards, or if they did they were very hesitant to use them for a monthly service. There was a fair amount of money orders coming in, people would take cash to the US Post Office, buy a MO and send it to us. The office door also had a mail slot, so people could also drop off cash or a check there too.
Billing date
Another big decision I had to make was “when” to bill the customer. Most companies billed at the first of the month. If you signed up for service in the middle of the month then they would generally pro-rate the bill and add it to next month’s bill. I realized right off the bat this meant doing a huge batch of work at the end of every month, and all the money would only come in during the middle of the month. It seemed like it would be better to spread out the work throughout the month and ensure money was constantly coming in. Plus I didn’t want to do this pro-rating every time somebody signed up. So, anniversary date billing it was. If you signed up on the 18th, then your service was due for payment on the 18th of every month, and I sent you an invoice two weeks ahead of that on the 4th.
But what happened if you signed up on the 29th-31st of the month? I just set your anniversary to the 1st of the next month.
I also billed ahead for service, because I needed the money now to operate. None of this business you use it for a month, I bill you afterwards for the month on net 30. I didn’t like the idea of people signing up, using the service all month, and never paying a cent. When you signed up your first month was due immediately. It also made service suspension tidy, if you didn’t pay your bill by the start of the new period (with a very brief grace period), your account didn’t work.
In reality this made for a lot of awkward conversations with customers, “yes you see you pay for next month’s service, but we’re also billing you for any overages from the last full month you had”. Some people had problems with my service dates, for instance going from Mar-4 to Apr-4 instead of Mar-4 to Apr-3. I don’t know if it was right but seems to have worked out.
Anniversary date: 18th
Invoice generated + mailed out 3/4
Calculated overages through 1/18-2/18 < Billed for January-February's overage
Due date, anniv date 3/18 < Billed for March-April's service
Excel as a billing system
Customer ledger
From the very beginning, I used Excel and a lot of copy-paste to send out bills. A worksheet would contain a generic form of the invoice to be mailed out, and then another worksheet contained all of the names and addresses of customers, previous balances and payments, arranged by billing date. Every day I would go through the sheet, basically do a hand mail merge of every customer copying their address from one sheet into the other, look up any usage overage, and print out their invoice. This took at an hour or two a day, and often dad would begrudgingly volunteer to do it for me in the evenings (thanks dad!). I forget how usage information got on there, if it was manually running a perl script on the database server to key in by hand, or if I somehow made a .CSV to load into Excel every day to copy from.
I actually found a floppy disk the other day that had a copy of this 16,000+ row .XLS file on it which prompted me to write this post. Excel usage lasted well into 1998, much longer than I thought it did!
Page to be printed and mailed
The first billing software I bought dedicated for the purpose was called BATS (Billing And Tracking System). This was a Unix-based program that was supposed to do it all, billing, RADIUS parsing, overage tracking, accounts receivable, the works. The problem was by the time I got all of my customer information typed into it, we had already blown past the user license and couldn’t add anymore users. The next license level was a huge jump from 75 to 5,000 users, and I couldn’t afford the upgrade. This was back when $1k was a lot of money, as I was still burning cash every month and not remotely profitable. I never did bill any customers with it, I seem to recall it had a lot of nits I didn’t like and/or I couldn’t get it integrated with my setup so I never scraped together the cash to upgrade.
I famously predicted that maybe after a few years I might have a couple hundred users on the service, but this thing took off like a rocket and I had over 100 customers within months. Blowing out the license count and doing things by hand in Excel wasn’t going to work at all. I didn’t what to do, but something had to be done.
Enter perl
I had never done any serious programming at this point, and had never used an SQL database. I dabbled in C but didn’t get it. I knew perl was popular with system administration so I gave it a try. One of the first big problems was processing the customer dial-up usage. I needed to not only put this into a database to do billing but also a way for customers to look up their own usage so they didn’t blow up their bills.
I literally went from print("hello world"); $a = 100; print("a is $a"); into learning to parse RADIUS accounting logs and INSERTing them into a MySQL database. (Back when mSQL vs MySQL was the MySQL vs Postgres of its time.) This made it way easier to look up somebody’s usage. I had a perl script that would parse the accounting logs every day and toss them into a table. Later on FreeRADIUS would do this.
This taught me about database indexing. My schema had no keys or indexes at all, so over time it got slower and slower to do lookups. One day I learned I could do an ALTER TABLE..ADD INDEX (username,time) to set the username and call time as indexes, and holy shit it was mind blowing how fast lookups got.
As I got more familiar with Perl and MySQL I got to the point where I was able to write a very rudimentary billing system and stop using Excel. There were a few tables with the customers’ names and addresses, their account type, and a ledger to keep track of their balance. Every morning billing.pl would run, look for who’s billing anniversary date was coming up, roll the service dates in the database out a month, calculate last month’s overage usage, write out a text invoice to a file, and then shoot that file to the printer with lp. Oddly doing date calculations and re-formatting date fields became the bane of my existence and had to go through a few Perl modules to find something that worked well.
For quite a while this system assumed a customer had one and only one dial-up account, everything was keyed on their username. I couldn’t bill for anything else without creating a new username. It felt like quite a bit of achievement to go back and refactor my code to basically add in another while loop to handle multiple services, but it all worked nicely and gave a lot of flexibility.
Part of this big change required assigning everyone an account number, then that account number would own all the usernames associated with it. I made a fatal flaw here and used the username’s unix UID number along with the account creation date to ensure uniqueness, in the form “uuuu-mmddyyyy“. The major problem was that this scheme leaked the number of subscribers I had, so one could extrapolate how fast I was growing over time if they knew several account numbers. I don’t know if anyone did that, but it was sure there in the open. At 13 characters it was also much longer than it needed to be, 5 digits would have been plenty to identify an account. I really wish I had hashed it to a shorter sequence of letters and numbers, as it was a lot to say over the phone and type in all the time. Toward the end the UID portion was also a sad reminder of how many thousands of dial-up accounts we had created over time and how many were left active.
There were still many invoicing edge cases to deal with by hand. For example if somebody upgraded or downgraded their account this involved manual SQL queries and/or manually editing a text file to send them a new invoice. There were often grandfathered accounts when I’d change around services and had to keep the old rates in code. Or if there was a bug, printer problem, missed day, it involved going into the script and manually setting some variables and running it again, praying it didn’t just nerf everyone’s service dates. I didn’t know about SQL transactions, and MySQL didn’t even have them yet!
Bills had a invoice number and a sequence letter starting with ‘A’ on them. If your sequence was ‘B’ or even ‘C’ that means some of the day’s invoices were manually re-printed due to a problem and to ensure we sent the right ones out.
De-coupling database operations from the print operations was a first big step to unravel the mess. One perl script would go through and roll forward all the service dates, generate pending ledgers, then other scripts would come along to generate invoices and run credit card payments. Several more rewrites would follow, billing.pl became billing2.pl, billing3.pl, get-bills.pl, cc_proc.pl, print-inv2.pl, and god knows what else. I learned to embrace use strict; in perl to prevent a lot of bugs.
There was no grand design here, there were no libraries, barely just a set of included common files. I didn’t have any experience with how billing “should” be done until I worked at other places. This was all learning on the go and doing what was necessary at the time. I copy pasta’d the same boilerplate Perl DBI to MySQL connection code in practically every single script because it was easier than refactoring it all. Source code management was making a tarball of the scripts and MySQL databases.
Perl-based invoice generation
Enter PHP
Around this same time I was starting to learn PHP too out of necessity. Coming off my experience with MySQL I was starting to use PHP to make it easier and possible for other employees to deal with customer data. There were some basic internal web forms backed by Perl CGI scripts to enter new signups, change addresses, enter payments, and see payment history, these were mostly re-written in PHP so the code was in one file per feature.
This became a whole dual codebase beast. Anything on the backend was written in perl and did the bulk of database manipulation, invoicing, and reports. All customer or employee-facing stuff was written in PHP. There was frequent re-implementation of functions in perl and PHP, and the code was littered with global variables. I briefly tried using Perl-Mason (embedded Perl in HTML), but PHP was just faster to write and ran faster.
The internal PHP tools website grew more and more features for employees to interact with. I implemented a rudimentary “issue tracker” which was just a glorified text form for each customer, to keep track of account notes and technical support issues. I had embarked on a complete v2.0 rewrite of all of this that I never finished, which wound up with us using some legacy PHP pages for some work and the new PHP pages for some other work. Like perl these were largely all one PHP/HTML page per function, e.g. add a user, modify a payment, update a credit card, customer search pages, add/cancel services, bad check processing, reports for collections, etc.
I still have all the code from this today and couldn’t tell you of the half dozen half-rewritten directories of code were the latest working versions.
Credit cards
I accepted credit card payments pretty early on, using the payment processor one of my upstream ISPs used. Again, a very manual process at first. When customers signed up at the bottom of the form was an area where they could write in their credit card number for automatic billing. The processing company provided a DOS-based settlement program that was a glorified handheld terminal. You keyed in the credit card number, the address, amount and hit send. This would dial out on a modem to submit the charge.
In the beginning there was a field on the Excel spreadsheet or database to note that this invoice was to be billed with credit card, not to mail it out. So every day or so I would take the stack that was set aside and key in the few credit card numbers and submit them.
Eventually I got to the point where I was storing credit card numbers in MySQL (this was long before PCI DSS and I could do this). I could then write CSV files with payment information, and then submit that batch over modem to the processor for settlement. If the credit card was declined, I wrote a note on the paper invoice and mailed it to you.
Way later we signed up with Authorize.net to do real-time and batch card settlements over the Internet. I recall something really bad happened with them, like they were late to deposit funds or we had a contract dispute, and this resulted in going to yet another payment service, Net1.
Checks, everyone hates checks
Checks were such a pain in the ass, I wish I was able to stop taking them or seriously discourage their use. They cost a lot of time and money to do. Not only did this involve printing invoices and mailing it to customers, it involved receiving them, keying the payments into the system and taking them to the bank. Because I was sending out bills every single day, this meant we received payments every single day and they had to be deposited. We were doing well over a thousand check deposits a month, and because of the janky date adjustment I did for people who signed up between the 28th-31st, this meant at the beginning of the month there would be a huge pile of checks to go through. I’m not joking, there would be literally a stack of checks 1.5″ thick to process at the beginning of every month.
Our bank did not like this one bit! It got so bad they warned me they were spending so much time on our big deposits they couldn’t close their books at the end of the day — either help them out or take our business elsewhere. It was a small town bank, they had no way to take any of this electronically or do ACH.
By now I already had an internal system for bulk check entry. There was a PHP web form where we could enter in a list of daily check payments to apply in bulk without moving off the keyboard or going to other screens (unless somebody didn’t have their account number). Based on this I was able to print a form with check info and total in chunks to send along to the bank with the deposit so they could go down the list and just check that we were right instead of doing the work themselves. Bank was somewhat happier.
Later I started dealing with a new business bank in Tulsa who offered electronic check deposits, but they did not have any kind of API. It turned out all they had was a web form to enter in the routing and account numbers and amount. I thought maybe I could scrape their deposit web page and do a POST request, but their HTML form wound up being a mess of input fields and I gave up.
I forget if I ever did get to a place where we could submit checks electronically or if we were still taking them to the bank up until the very end.
Paper billing
Every day at 11 AM (even weekends) the billing cron job would fire and invoices would start rolling off the printer. It would be somebody’s job to take the pile of paper, fold it, and stuff into envelopes. We tried a few different paper folding machines and they worked until they didn’t, mangling a lot of invoices which needed re-printing. I got to where I would just take 10-20 sheets at a time, fold them by running a metal bar across (my favorite was the backside of a carabiner) them to give a rough shape, then start stuffing them into windowed envelopes. For sealing them I’d hold a tape dispenser in one hand and just dab a 1″ strip of tape across the back flap of each one. For postage we had a postage meter for a while, but Pitney Bowes wanted a lot for the rental and it was annoying to constantly replenish the account. So I wound up buying coils of stamps and either using a stamper or going through the stack and putting them on hand. It could all be carefully organized and done in a mechanical manner by hand, but it had to be done every single damn day.
The post office didn’t like us either, with our volume they demanded we sort our outgoing mail into local and non-local ZIP codes to make their sort easier.
Late payments were always a problem as well as associating the payment with the right account. Nobody would write their usernames or account numbers on their checks, or somebody would write a personal check for a business account, names changed, etc. To help encourage people to send in their payment in a timely manner and figure out who is who, I started including a pre-printed return envelope, and going as far as to buying paper with a perforated bottom for a stub they could tear off and return. This worked pretty well, virtually everyone used them but it was yet an extra expense.
Once we were sitting around thinking “you know we haven’t done bills in a while, did you do them?” “No, not me”. I realized after a system migration the billing cronjob had been commented out and no bills had been generated for at least week! This took a bunch of careful editing of the billing script to step through each missing day and process that day’s invoices to get us caught up. To this day I still have dreams where I freak out that I haven’t done billing in a while.
If this all sounds labor intensive, crazy, and expensive, that’s because it was! It’s hard to say if we pushed back on customers for a credit card or charged a $1 a month for a paper invoice, how many would bail on us. Always a monumental “if” was if we went card/ACH only, could we reduce costs enough to lower prices to attract more customers to offset the ones that we would inevitably lose from the switch.
Homegrown Postscript invoices
Several years in (~2002) I realized Postscript was just text and if I just shot Postscript at the printer, it’d print. This lead me to switch from writing plain text invoices to using Postscript. This provided a much nicer looking bill, I could print logos and barcodes for the account numbers on them. The process was similar to writing text invoices and surprisingly simpler than I imagined. I used the PostScript::TextBlock and PostScript::Elements modules to generate strings to print on the page “canvas”. For example:
my($p_head1) = new PostScript::TextBlock;
$p_head1->addText( text => "CWIS Internet Services\n", font => 'Helvetica-Bold', size => 10, leading => 10 );
$p_head1->addText( text => "203 North Broadway\nStigler, OK 74462",
font => 'Helvetica',
size => 8,
leading => 10
);
...
print BILL "%!PS-Adobe-3.0\n";
print BILL "%%Pages: (atend)\n";
print BILL $code39;
print BILL "%%Page: $pages $pages\n";
$code .= [$p_head1->Write(252, 144, 396, 755)]->[0];
$code .= [$p_msg2->Write(108, 12, 410, 710)]->[0];
$code .= [$p_msg3->Write(108, 12, 370, 692)]->[0];
$code .= [$p_msg4->Write(108, 12, 374, 680)]->[0];
$code .= [$p_msg5->Write(108, 12, 389, 668)]->[0];
$code .= [$p_msg6->Write(108, 12, 421, 656)]->[0];
$code .= [$p_custaddr->Write(230, 60, 72, 702)]->[0];
$code .= [$p_acctnum->Write(72,12,452,692)]->[0];
$code .= [$p_invnum->Write(72,12,452,680)]->[0];
$code .= [$p_invdate->Write(72,12,452,668)]->[0];
$code .= [$p_pagenum->Write(72,12,452,656)]->[0];
print BILL $code;
...
For each invoice line item that was processed, call some more library functions to add a row to the output, and then more function calls to write a footer. It could even do multiple pages. The barcode was just another font, the company logo was included from an .EPS file, and this was all written to a file on disk. Then all these pages were sent to the printer.
Generated Postscript invoice
Because I kept the Postscript and text files around, this provided a nice feature where customers could log into their account management page and see all of the exact invoices that had been sent to them in PDF format.
I had hoped with the barcode this would make processing payments easier somehow, just scan in the account number. In reality by the time you reached over, scanned the barcode, and came back to key in the payment amount, it was just quick to use the 10-key number pad to enter the info.
FreeRADIUS, MySQL, Self-service
I adopted FreeRADIUS fairly early on because I needed to write RADIUS accounting logs to a database for billing, which is what it was designed to do. As time went on the set of PHP and Perl scripts to keep track of users made it possible to add/change/remove RADIUS authentication information in MySQL. This let me do automated account suspensions for non-payment, your dial-up account wouldn’t work but you could still receive e-mails. When the switch was flipped the past due accounts were ruled with an iron fist and people did not like this at all! At least I could blame it on “the system automatically doing it” and not something “we” personally did to you.
We adopted Exim and Courier-IMAP as the e-mail subsystem because it could be backed by MySQL. The same story, the billing scripts could manipulate accounts here, set up new ones, and disable them as needed.
Eventually toward the end this all resulted in a customer self-service system. Customers could log into their account management web page and do things like add extra e-mail accounts, change credit card information, or make credit card payments to re-activate accounts. There was a modem connection information page that would look up what USR Total Control or Portmaster 3 you were connected to, query it via SNMP, and display the speed/protocol/error counters of your connection for troubleshooting. It all was pretty nice but took a long time to get there.
Specials and $9,999.99 bills
Around the same time of the Postscript rewrite, I added several other features to the billing programs. If you were a credit card customer, we’d automatically email you when your card was about to be billed, if it was about to expire, or if it was declined. We could send you e-mail invoices if you really wanted them. If you bounced a check, we mailed you and suspended your account. If you added new services to your account, this was written to a pending table to add to your next bill.
I had never imagined I’d need to code in the ability to do discounts or special sales. The few times we did some new subscriber specials it was a mess, because the billing script had to be manually altered to handle it. I forget what special we did, pay a quarter and get a month free or something, but it resulted in a bug that sent out invoices for $9,999.99. I would have thought a normal person would see this and think “oh that’s clearly a mistake”, but no, several people very much called us up and yelled at us for expecting them to pay $10k! Even if I caught the bills before they went out and scratched out the amount with a pen, people were still upset about it!
I thank god that Internet service was not subject to sales tax or other taxes like it was in Texas. I can’t imagine what it would have been like to implement tax code.
Fortunately when we started selling DSL service no extra tweaking to the billing system needed to happen, we could just drop in new product codes for service and DSL modems and go. Around this time I finally gave in to competition and started offering “unmetered” dial-up plans along with lower cost metered plans.
My all-time favorite customer interaction was somebody that came into the office and very loudly started arguing with me that their bill was wrong, because September was not the 9th month. I had to list out the months on the whiteboard before they finally relented. Good times.
When it came time to close the business and another company to acquire all the accounts, this all blew up in my face because, for example, I didn’t have a good way to list who had pre-paid service and how many dollars were involved. It took me and somebody from the other side working well into the wee hours of the morning of the last day pouring over the database and building custom reports to make sure the numbers were right before we closed.
Tags: isp
