Extracting Collection Data From Websites

1
Extracting Collection DataFrom Websites

• TRMG
• April 16, 2012

2
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?

3
Technology

• Part 1

4
How Do Web Pages Work?
5
Your Web Browser Sees This
6
You See This
7
Putting it all Together
HTML
SIZE
Font
XML
JavaScript
Color
Location
8
What if You Could Capture This Data?
549.95
549.95
549.95
549.95
9
Robots Automating Data Capture
10
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

11
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?

12
Web Extraction ForCredit and Collections
Operations

• Part 2

13
Web Extraction and Collections Operations

• How does this apply to me?
• What is the information Im looking for?
• What is my overall benefit?

14
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.

15
Multiple Sites Daily Labor Hours!
Cut and Paste or manual entry.
Employees logging on and off of sites daily.
Automation could reduce your labor associated
with this by 85 100
My Employee
16
What is available on these sites?
Payment Date
Check Numbers
Acknowledgement of Receipt
Amount Paid
Dispute Information
17
Why does earlier make it better?

• 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.
• 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?

18
Why does earlier make it better?

• 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?
19
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
20
Payables Process Efficiency
Prime Point of Data Gathering
21
How do they Match Up?
A/P
A/R
Approximately 27 Day Gain!!
22
Carrier PerspectiveUsing Technology to Increase
Productivity and Avoid Costs
23
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 CC software

24
Initial CC 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

25
Customer Service Situation

• Needed to update/monitor managed account
  websites
• Faced similar issues to CC
• 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

26
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
• Can schedule robots to run as needed to meet
  timing requirements

27
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.

28
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

29
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

30
Other Uses

• Completed
• Credit Collections Software Upgrade
• State Tax Forms
• Rate Web Probe
• Future
• Canadian Customs Form
• ECM (TruckLoad) Customer Service

31
Carrier PerspectiveWeb Scraping
32
Credit Collections

• Company has 178 customers utilizing 23 different
  3rd party payers with websites
• Approximately 25,000 invoices statused per week

33
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

34
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

35
CHAOS
36
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
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
37
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

38
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.

39
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 )

40
Data Capture
Open Invoice Data
Note Today Analysts V-lookup from excel to
update invoice status on master agings. In
development feed of web scrape results directly
to Collection Software
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
A/R Analyst
A/R Analyst
A/R Analyst
A/R Analyst
41
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.

42
Questions
43
Thanks for Your Time
Bruce Olsen Kapow Software Bruce.Olsen_at_KapowSoftwa
re.com
Jeff Jones Gallium Technologies jjones_at_ar-360.com
Cindy Douglass Swift Transportation cindy_douglass
_at_swifttrans.com

• Diana Early
• PITT OHIO
• DEarly_at_pittohio.com