Rate sheet “normalization”

Home » Asterisk Users » Rate sheet “normalization”
Asterisk Users 39 Comments

Hi,

this question is not Asterisk specific, but since there are so many
experts present on this list, maybe its OK to ask anyways.

I’m having a hard time “normalizing” rate sheets from different
providers. What I mean with this: the goal is to always get the cheapest
rate for a given destination. What I would like to do is throw like 10
rate sheets from different providers together and as output get a single
rate sheet with only the cheapest rates. However, some providers are
listing a country, lets say Germany, as code “49” with a specific rate,
and another provider will list each city individually, and each code
separately, e.g. Berlin “4930”, Hamburg “4940” etc., and probably
different cities have different rates as well. Now, if the “49” route of
the first provider is cheaper, my system (a2billing) will still use the
more expensive “4930” code because it is more specific.

I’m looking for some awesome, smart tool that will automatically
“normalize” all these code differences and output a clean ratesheet with
only the cheapest rates.

Does such a thing exist? I wonder how everyone else is “normalizing”
their different rate sheets. With a homebrewn script?

Thanks!

39 thoughts on - Rate sheet “normalization”

  • There is a great deal of wisdom in this approach that you may wish to
    consider carefully before abandoning. It is especially true in
    Europe, and really, anywhere outside the North American environment,
    which it doesn’t sound like you’re in to begin with.

    Calls to mobile operators, as well as certain premium number
    allocation prefixes, can be many times more expensive than calls to
    fixed-line operators. If 4945 were a mobile prefix that was three
    times more expensive than the overall +49 rate offered by the same
    provider, are you absolutely certain that you want to use the +49 rate?

    Even if you have some vendor that gives you a blended overall rate for
    +49, and you want to cherry-pick expensive routes off their blended
    rate plan, that still means you need to sort by longest prefix
    (descending) to know what you’re comparing it against.

  • Am 13.03.2012 01:24, schrieb Alex Balashov:

    I understand what you’re saying, but actually, I tried to keep the
    example really simple. In real life, the problem is that some of the
    rate sheets of some providers look like this – example:

    Provider 1:

    Brazil Mobile Tim, 55118, 0.0383

    Provider 2:

    Brazil Tim Mobile, 551181, 0.1785
    Brazil Tim Mobile, 551182, 0.1785
    Brazil Tim Mobile, 551183, 0.1785
    Brazil Tim Mobile, 551184, 0.1785
    etc.

    Now, all calls would go via the more expensive Provider 2 due to the
    more specific codes, and the cheaper route via Provider 1 would not be
    used at all.

    Basically the software I’m looking for knows all the real “base” codes
    throughout the world and will merge such unnecessary breakout codes like
    at Provider 2 to the “base” code. Or, it will understand that there is a
    cheaper route available for a valid base code and drop the more specific
    codes. And if the more specifics are cheaper, it should understand that
    as well.

    Does such a thing exist?

    Thanks!

  • Am 15.03.2012 00:35, schrieb Benny Amorsen:

    a2billing chooses the cheapest rate if there is more than 1 rate for a
    prefix that matches exactly. I’m not a programmer so I can’t tell you
    how they do it internally. The call is simply passed to a2billing.php
    via DeadAGI. I know that the a2billing guys are currently working on
    fixing exactly the mentioned issue, but it’s unknown when that will be done.
    I was just hoping there was some software out there that I can throw 10
    different ratesheets at with different length of codes and it will
    output a single true LCR ratesheet cut down to the “official” worldwide
    codes.
    Thanks!

  • A2Billing doesn’t do that. A2Billing in fact has a lot of shortcomings one
    of which is this exact issue.

    I would suggest running rate sheets against each other for finding true LCR
    and then only uploading the rates that are cheaper into the system. In most
    cases there are not such high differences but if there are then this is the
    only way. I know rate normalization talk comes up all the time on
    FreeSwitch Freenode channel and it probably does on OpenSIPs as well. Check
    there for some good advice.

  • could MS-Excel possibly be the easiest way to do that normalization ! just
    merge two rate sheets put some formulas in there and use it in your
    A2billing or XYZ tool !

  • With like 10 different ratesheets from 10 different providers, of which
    many change their rates every few days, manually doing it in Excel is
    too time consuming…

    Am 15.03.2012 07:26, schrieb SamyGo:

  • Is it possible to get samples? I’d be interested in looking into
    developing a script that can handle this problem generically, and
    presumably you’re available to alpha- and beta-test in any case 🙂

    Regards,

  • Am 15.03.2012 17:20, schrieb Raj Mathur (राज माथुर):

    Most definitely! I’ll get in touch off-list. 🙂

  • I would be more interested in a system where quality routes are tested with
    different providers because rate really doesn’t matter if a call can’t be
    placed or if a destination is a fake one. We have seen many fake
    destinations with top tier providers but they had the best rates so the
    strategy to pick them first really didn’t work.

    So, maybe a subscription service where a dialler system continuously tests
    routes with a list of 10 providers so that it’s established which routes
    actually work and then allow that data to be downloaded for usage.

  • Our system just rolls over until it finds a carrier that will take it. Up to 30 different routes are supported, and rollover is pretty instantaneous in most cases.

  • I think that it may not be humanly possible and also not possible to have a
    separate automated setup to ring destinations and prioritize according to
    Quality or Least-rates. BUT I am sure that real-time call success rate(or
    ASR) via multiple providers and sorting providers accordingly for
    particular destinations is possible or maybe available. Provided a good
    piece of code is written which analyses the call status / quality and then
    picks favourite carrier/provider for any destination. !!
    Not sure if anyone can understand it completely 😉
    I am thinking in terms of DynamicRouting or LCR modules from Kamailio or
    OpenSIPS.

    Regards,
    Sammy

  • How about a central coop that manages the “normalized” rate sheet and distributes it with “unknown” call quality metrics for each route. Coop members report call quality for all calls/routes so the call quality metrics can be updated in the rate sheet and distributed to members.

  • Good Idea but that means all the members of the coop use the same vendors
    and it may not be suitable for servers having a bad network with a premium
    quality provider and thus mark it as bad, whereas others are marking it as
    Good. !!

  • So the best fit is to create a piece of code which mixes providers and
    destinations. 1- Sort those with at first rate and then as time passes
    by(utilization) it rearranges the carriers according to quality metrics or
    economical rates or success ratios. Like an AI system which enhances itself
    with passage of time.

  • Am 16.03.2012 04:14, schrieb Ast Coder:

    Ok, but how would this system handle FAS? Call shows as connected but
    it’s still “ringing” to the caller. And probably won’t get connected to
    the callee ever. I.e. cases where the route is just faulty in some way
    and it will ring forever but actually no one will ever get connected.
    How do you differentiate between “nobody is home” and “route is faulty”…

  • If we had reports of every call, we could downgrade status of routes that
    had frequency calls not completed that were outside the norm.

  • Markus,

    you’re not the first person and certainly not the last person who’s ever
    asked about this. I had tried this on several mailing lists a little while
    ago. A tool that could handle 10 or maybe even 5 provider rate-sheets all
    of which can potentially completely differ in formats from each other. Even
    worse are the rate update sheets from each provider which are many a times
    different from the initial rate sheets that the provider may have given you
    and then again they will differ from the rate updates from the remaining 4
    providers you’ve just painstakingly inserted into your DB.

    Given the popularity of Asterisk and other popular OSS based telephony
    platforms with several successful businesses running 100s of millions of
    minutes, you’d think at least a few have sorted this problem out. But I
    believe those who have, never respond to these emails as it took them quite
    a bit of effort to create such a tool and aren’t willing to just give it
    away.

    Just what I have observed (and was even blatantly told by someone on some
    mailing list, can’t remember exactly)

    You may have to advertise in the commercial / business list or offer a
    bounty. There are several commercial solutions available but I think they
    all come as a “feature” of a larger billing/rating/routing platform

  • We solve this problem for our customers all the time, in various situationally-specific ways. But yes, we are not really in a position to genericise it and give it away. It’s not because we are greedy. The time and resources just aren’t there.

  • Continuing with the top post…

    I believe in open source philosophy. A software or a list of telephone
    prefix makes no difference. If you want to make such list open source,
    you’ll be sure somebody will contribute to maintain it update and all will
    benefit from it.

    Leandro

    2012/3/28 C. Savinovich

  • Christian, your signature says you’re a “VoIP and Telephony Consultant.” That implies that your clients pay you for the value you add personally providing services to them. Open source software doesn’t download itself and configure itself appropriately all by itself—there are lots of people needing your assistance.

  • Umm, like the amount you paid for your copy of Asterisk and the Linux
    server it runs on?

  • They would rather pay you $1500 to fix the a2billing install that fellow X charged them $500 for. It isn’t “right”, that’s just the way it is.

    Sent: Wednesday, March 28, 2012 1:17 PM

    Nobody wants to pay me $2,000 to install and configure A2billing, which in my view, is a fairly low price for my time. There are people who do that for less than $500. I do know the tricks and formulas of how to make money in calling cards, but go tell the customer, they don’t care. I don’t care either, I am very busy in my consulting business, but it is unfortunate that the better income source for developers just doesn’t exist anymore. I would make much more money if A2billing weren’t open source and I would selling copies of my own version of A2billing, probably even developing a better product, definitely with the request that initiated this thread included 😉

    Christian Savinovich

    VoIP & Telephony Consultant

    646-982-3572

  • It is just way you represent your self and what kind of customer you get. I
    have seen customers who buy DIDs from us for calling card have paid upto
    10K for installing asterisk and a2billing and they are very happy that
    there is no limitations on number of calls as per licensing.

    -Jai Rangi
    http://www.didforsale.com
    “Leader in SIP Trunking”

  • Haha, Globalization my dear. Learn to deal with. Dont live with hate :).
    Second,
    Microsoft and google will have no money if they dont hire overseas. Think
    of paying to google for every search. Jobs going overseas have taken some
    jobs but also have opened lots of opportunity. Think what will be happen if
    India and China blocking google and facebook, Microsoft.

    -Jai

    On Wed, Mar 28, 2012 at 11:25 AM, C. Savinovich

  • I sympathise, as a fellow “American” developer. However, it is rather childlike wishful thinking to purport to stop by force of law that natural motion of capital which is certifiably unstoppable.

    Global economic integration and interdependence (“globalisation”) has its pluses and minuses for everyone. To partake of the benefits of modernity, you have to pay to play. You can’t have the good without the bad. Developing nations have every bit as many gripes with it as you do. While you complain that jobs are being taken away from American developers and there is downward wage pressure, the developing world complains of their macroeconomic health being subjugated to the whims of some distant investors in faraway lands, there self-sufficiency destroyed by supposedly organic “competitive advantage”, IMF-sponsored liberalisation and austerity measures that hurt the people and increase concentration of wealth into few hands, expropriation of land and resources into the hands of foreign conglomerates, etc. It is what it is.

    Regardless, there is no going back. It’s like trying to put the milk back into the cow. All you can do is make yourself competitive in global terms. There is still much that you can do that cannot be usefully offshored.

  • A medieval monk trained in copying books by hand sits in his cell
    cursing Gutenberg for inventing the printing press and depriving him of
    his livelihood.

    Not satisfied with that, he also curses the (Arabs|Nigerians|Indians|
    Chinese|your choice of whipping boy) for typesetting cheaper than he can
    and taking business away from him.

    Would be funny if it weren’t so sad. My sympathies.

  • Not the best analogy, since it implies that Christian’s services are legacy and retrograde, while those in India/Arabia/Nigeria/China are progressive, state-of-the-art.

    In general, the opposite tends to be the case. There are printing presses in the US, but Chinese can be paid so little to transcribe books by hand that it is actually more economical to pay a large army of them to do just that, instead of investing in one typesetting machine.

  • The analogy was in two parts, addressing the two parts of the
    gentleman’s claims: (a) FOSS is bad for business (because he couldn’t
    handle new ways of doing business) and (b) the completely uncalled-for,
    prejudiced and insular rant about “we guys” depriving programmers in his
    country of work.

    Times change — the way to deal with that is to adapt; shoving your head
    into a hole (specially one lower down in your anatomy) and keeping it
    there will only result in the sort of self-pitying whining one is
    seeing.

    Fun as this has been, I must now respectfully decline to further clutter
    this list with nonsense and let it get back to the main business of the
    day. No more from me on this topic.

    Regards,

  • For the benefit of those who did not bother following us: ‘>>>’ stands
    for a single level of quoting. The following sentence was:

    Back to Savinovich’s message:

    Maybe. Or may it is because I (the hypothetical me) focus on selling
    something different? If keeping it secret reduces more value than it
    adds to my customers, maybe it’s not a good idea.

  • Wow! …all the poor guy wanted to know was if there was any tool available
    for normalization of carrier rate sheets!

    On Wed, Mar 28, 2012 at 11:58 AM, C. Savinovich
    Something must be said about this…if you have any visibility or are
    cognizant of the startup scene all around the world where more than EVER
    before, 18-yr old students have started companies right out of their dorm
    rooms built on open-source software and created products and services which
    were again offered completely free of charge, played their cards right,
    participated in hackathons, attended every startup igniter and/or incubator
    program that took place, rented RVs, slept in tents outside Austin for SXSW
    and didn’t know night from day for 6-9 mths of building their service, you
    would know that those days are not gone. Those days are NOW, HERE! There
    was hardly ever a time where a startup could be launched right out a dorm
    room without a single cent of capital investment in infrastructure because
    there’s EC2, and zero cent in platforms, OS, frameworks, APIs or marketing.
    If you can code, and have an idea, you can have a company, market
    completely on word-of-mouth via social media, host on EC2 on a
    pay-as-you-go on money you save or borrow from your parents and friends and
    if the service is worthwhile for even 100,000 users, you become the target
    of an acquisition by some other company that you may be just a tiny piece
    of the puzzle.

    90% of the .coms that I see today are completely useless and solve no
    problem that existed but provide a “nice to have” service…or make people
    believe that something WAS an issue when it really wasn’t exactly a pain
    point, but they’d use it if someone other SOD built it and let them use it
    for free. Like people have said, if your product has better value when
    shared than when it’s not, it’s better to share. Because you never know.
    What might be just a “hobby” for you, could become a revolution. Twitter
    wasn’t built to be what it is today. Groupon wasn’t built to be what it
    became…both of these were started to solve a personal problem and was
    almost a bit of a joke (from what I have read coming from the horse’s
    mouth, the founders). It’s the public that found more uses of Twitter that
    gave twitter the boost and critical mass that it became a revolutionary
    service. All it was meant to be was so these guys could avoid sending 200
    texts in the days where we paid per text to figure out if any of their 200
    contacts were up for meeting them at the pub in the city. Much better for a
    central service to send out texts for them to only who *chose* to receive
    those texts i.e. their followers, than for them to send out 200 texts to
    everyone they knew (annoying half of them) when they could just send 1 text
    to a unique number. There’s a reason why twitter is limited to 140 chars.

    So like others have said, it’s essentially survival of the fittest. If what
    you create is of sufficient value, your dues will be paid sooner or later,
    if not in cash, then in Kind or Karma 🙂

    Just to finish my rant off, I find it funny when I read a discussion like
    that when you yourself are sitting here on the list of one of the most
    popular FREE software package which ironically runs on a FREE operating
    system that you didn’t pay a dime for.

    If for e.g. you have a tool that does the above required function and
    really don’t want to give it for free then you could package it in a free,
    lite and pro package where each package handles X number of lines of rates,
    X carriers, and doesn’t handle rate updates and give that away for free.
    The lite does slightly more lines, more carriers, handles updates and
    allows upload to MySQL but costs $50 and then Pro can handle more of
    everything or unlimited, takes input from DBs of various types, provides an
    API or some sort to embed in a routing engine, uploads to more types of
    DBs, and whatever else. There are several ways to skin a cat. Doesn’t have
    to be Free or Paid and nothing in the middle. Remember only a Sith deals in
    Absolutes 😉

  • Internet. Senseless and merciless

    A E [Gmail] wrote 29.03.2012
    10:28:

    tool available for normalization of carrier rate sheets!