Extracting
Collection Data
From Websites
TRMG
April 16, 2012
Agenda
Part 1: Technology
How do web pages work?
Why would you want data from a web page?
What are your options for web page data?
Part 2 How does this relate to Collections?
How do the two go together?
What is the information Im looking for?
What will I do with the info when I get it?
What is my overall benefit?
Technology
Part 1
How Do Web Pages Work?
Your Web Browser Sees This
You See This
Putting it all Together
HTML
F o nt
SIZE
XML
JavaScript
Location
Color
What if You Could Capture This
Data?
$549.95
$549.95
$549.95
$549.95
Robots: Automating Data
Capture
1. Shipping request arrives in
email
4. Login to customers B2B
portal, retype data from
customer app, confirm
pick-up
2. Read, print, file
email
3. Login to customer app,
retype data from email,
schedule pick-up
9
Data Capture Options
Print screen/copy by hand between sessions
Low apparent cost but very costly in staff time
High error rate, unsuitable for a growing business
Write some programs
These programming skills are very hard to find
Browser add-in
Defeated by modern websites (JavaScript, etc.)
Limited targets, cant integrate applications
Data feed/service provider
Easy and convenient if cost and delivery time are OK
Integration and automation engine
Needs in-house tech support
Making a Decision?
How many sites? How much data?
Are updates frequent? Are new sites
added often?
How complex are the sites?
Login
Navigation
Multiple pages
AJAX
Where does the data need to go?
Web Extraction
For
Credit and Collections
Operations
Part 2
Web Extraction and Collections
Operations
How does this apply to me?
What is the information Im looking
for?
What is my overall benefit?
Web Portals aka Payables
Sites
Todays market has driven more and
more A/P Departments to pushing
their payment data to the web.
Examples
Paying Services (ie Cass, Data2, etc)
Large Companies
Logistics Companies
Many Many more.
Multiple Sites Daily = Labor
Hours!
Cut and Paste or
Employees
logging on and
off of sites daily.
manual entry.
My
Employ
ee
Automation
could reduce
your labor
associated with
this by 85
100%
What is available on these
sites?
Payment Date
Check Numbers
Acknowledgement of Receipt
Amount Paid
Dispute
Information
Why does earlier make it
better?
1. Payment Info: Pay date, Check Number,
Amount Paid.
Reduce the number of Invoices your team calls on.
Improve Cash Application.
Recognize potential short pays.
Integrate into cash flow management/forecasting.
2. Acknowledgement of Receipt
But I have EDI. Who sends you the 997? Receipt
Acknowledgement?
Improve Invoice Accuracy by improving address
correction.
Imagine if a customer told you that they hadnt gotten a
bill, and it was already past due?
Why does earlier make it
better?
3. Dispute Information:
Know about Short Pays, How much, and why.
Allow the work flow to start earlier and resolve
issues more timely.
Create a better customer perspective overall.
Improve Cash Flow and DSO.
When is the best time to search for
info?
What is the Sweet Spot?
Manual Sweet Spot
Labor Version Sweet Spot
In attempting to manage exceptions and minimize manual labor, this point of
biggest reduction is a normal spot for labor to look up status.
Approximately 42 Days Old
Payables Process Efficiency
Prime
Point of
Data
Gathering
How do they Match Up?
Approximat
ely 27 Day
Gain!!
A/P
A/R
Carrier Perspective:
Using Technology to Increase
Productivity and Avoid Costs
The Situation
Credit & Collections
Seven collectors handling over 1,500
accounts using 3rd party freight
payment agencies
Over 70 different websites with different
logins and navigation
Manual process to research disputes and
transfer payment information to
commercial C&C software
Initial C&C Solution
Started with service provider capturing the
data
Captured data was integrated with our
collections software
Easy to test and implement - Minimal IT resources
Most of the major freight payment companies
available to turn on
Cost effective No integration fees
Early notification of payment disputes and PTPs
Automated internal workflow to resolve dispute
Customer Service Situation
Needed to update/monitor managed
account websites
Faced similar issues to C&C
Websites with different logins
Different sets of navigation
Varying data and timing requirements
Constant awareness of shipment status so
that information could be updated timely
Process manual, convoluted, and untimely
CS Needed a Different Solution
No service provider solutions captured
data from the CS websites we needed
to access
Purchased software solution to build
robots
Automates navigation and updating of
customer sites
Robots created by non-developers who
understand database structure
Average of four hours effort per customer
site
Current Status & Cost Savings
Started in Credit & Collections area due to
available resource to build robots.
39 processes built out of 70 available
80% of freight payment invoices
Approximately $75,000 labor savings per year
(both solutions)
Customer Service
3 processes built with several in queue
Approximately $190,000 labor savings per year for
these 3 accounts alone.
Overall Benefits
Credit & Collections (both solutions)
Improved productivity by reducing website
inquiries
Shortened time for resolving rejects or
disputes due to earlier notification
Streamlined processes by routing rejects to
the appropriate resolvers
Allows for better analysis of payment
patterns and disputes
Ability to establish robots to capture data
for large customers not using freight
payment agencies
Overall Benefits
Customer Service
Reduced labor by automating managed
accounts processes
Reduced complexity by streamlining
processes
Reduced errors and improved timeliness
of updates through customization
Potential to increase revenue by taking
on more managed accounts without
adding staff
Other Uses
Completed
Credit & Collections Software Upgrade
State Tax Forms
Rate Web Probe
Future
Canadian Customs Form
ECM (TruckLoad) Customer Service
Carrier Perspective:
Web Scraping
Credit & Collections
Company has 178 customers utilizing
23 different 3rd party payers with
websites
Approximately 25,000 invoices statused
per week
Process Before Web
Scraping
Each A/R Analyst visits 3rd party payer's
website and obtains invoice status information
Each A/R Analyst usually has various customers
utilizing 3rd party payers: CASS, Data2; Sterling;
etc.
A/R Analyst downloads/manually captures invoice
status and updates Customer Aging (Excel
spreadsheet) from each individual website
If a customer begins using or changes 3rd party
payers, A/R Analyst has to train on website to
know how to capture invoice status
Inefficiencies/Break Downs before Web
Scraping
Time consumed visiting separate websites
by each A/R Analyst
Administration of website spread across
A/R Analyst population: access/passwords;
training
Lack of Standardization on what and how
information is obtained & retained/tracked
In one word
CHAOS
A/R
Analyst
A/R
Analyst
A/R
Analyst
A/R
Analyst
A/R
Analyst
A/R
Analyst
A/R
Analyst
A/R
Analyst
A/R
Analyst
Payer
Website
A/R
Analyst
Payer
Website
A/R
Analyst
Payer
Website
A/R
Analyst
Payer
Website
Customer
Website
A/R
Analyst
A/R
Analyst
A/R
Analyst
A/R
Analyst
A/R
Analyst
A/R
Analyst
A/R
Analyst
A/R
Analyst
A/R
Analyst
Process With Web Scraping
(1)
A single, assigned individual
generates web scraping results
We web scrape once a week; on
occasion will web scrape an
individual batch if more immediate
update needed
The larger 3rd Party Payers are
accessible, and more are added on
an ongoing basis
Process With Web Scraping
(2)
Steps involved:
Generate our open invoice report for all
customers per payer
Add Paying Agent Import ID
Upload report to the Web-Scraping
website
Click the "start" link once the file is
imported.
Process With Web Scraping
(3)
Steps involved (continued):
Wait for processing to complete:
Can take 4 hours for the largest accounts or slowest
websites
During processing visibility to UTP items meaning
there is a connection error with that 3PL. It could be
expired username/password or the site could just be
down.
After processing, there is a download link for
an Excel file we use to provide A/R Analysts
their invoice status (via V-Lookup by invoice #)
Ope
ce
voi
n In
Payer Website
Data
Capture
a
Dat
Payer Website
Note: Today Analysts Vlookup from excel to
update invoice status on
master agings.
Payer Website
In development feed of
web scrape results directly
to Collection Software
A/R A/R Analyst
ANALYST
A/R Analyst
A/R Analyst
A/R Analyst
Payer Website
Payer Website
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
Payer Website
Benefits of Web Scraping
Significantly reduces the amount of time an A/R
Analyst spends statusing invoices/updating agings
More efficient detection of issues or disputes (i.e.
rejects, missing invoices etc.) before invoices due
A/R Analysts more effectively utilize PTP
Provides more ability to analyze/report on
common rejects by customer
Results retained for a historical record/analysis of
billing/collection issues and/or trends with specific
customers and/or 3rd Party Payers.
Question
s
Thanks for Your Time
Bruce Olsen
Kapow Software
Jeff Jones
Gallium Technologies
Bruce.Olsen@KapowSoftware.com
jjones@ar-360.com
Diana Early
PITT OHIO
DEarly@pittohio.com
Cindy Douglass
Swift Transportation
cindy_douglass@swifttrans.com
