Telecom usage records

A mobile network records every call, text and data session as a usage record, often called a CDR (call detail record). A mediation service collects them from the network, cleans them up and publishes them to Kafka, where rating and billing, fraud detection and analytics each read them.

Mediation has to get the details right, because every mistake is a billing mistake. A record the network sent twice must be billed once, a long session reported in parts must be billed in full, and a record for a subscriber the operator doesn’t know must not be billed at all. Akema lets you watch the records on their way to billing and check that the mediation service caught what it should.

The example

A mobile operator’s mediation service writes one topic per kind of usage, keyed by the subscriber’s IMSI (the ID on their SIM card), plus a topic of records it rejected:

Topic	Key	Holds
usage.voice	imsi	Calls: direction, duration, cell
usage.sms	imsi	Text messages
usage.data	imsi	Data sessions: bytes up and down, and the network used: 3G, 4G, 5G or Wi-Fi
usage.rejects	imsi	Records the mediation service rejected, with a reason

A long call or data session can be reported in two parts: a partial record and then a final one, which share a charging_id. The final record carries the whole session, so billing uses only finals. Every record also says whether the subscriber was roaming and on which partner network (visited_plmn).

{
  "event_type": "data",
  "imsi": "310260000000621",
  "msisdn": "+14150000621",
  "cell_id": "cell-0002",
  "rat_type": "4G",
  "roaming": false,
  "charging_id": "chg-6fb981893bfa",
  "is_duplicate": false,
  "record_status": "final",
  "partial_seq": 2,
  "duration_sec": 258,
  "apn": "iot.telemetry",
  "bytes_up": 959855,
  "bytes_down": 16965741
}

The mediation service rejects three kinds of record:

• duplicate: the network sent a record again, marked is_duplicate.
• partial_without_final: a partial record whose final never came.
• unknown_imsi: a subscriber that isn’t in the operator’s catalog. In the example, their IMSIs start with 00101, a test network code.

Follow along

This traffic comes from the Akema Kafka demo suite. With its Kafka running, start the generator and connect Akema to localhost:9092:

python 06-usage-mediation/produce.py --duration-sec 600

1. Connect

On Connect, add a source for your cluster with the four topics. Set Start mode to Latest (tail) to watch records as they’re mediated, or Timestamp with a Stop time to look at the records behind a disputed bill. See Connect to Kafka.

2. Trap records that affect billing

Add these traps:

Trap	Expression	Catches
duplicate_cdr	topic != "usage.rejects" && value_json.is_duplicate == true	Records the network sent twice
unknown_imsi	topic != "usage.rejects" && value_json.imsi.startsWith("00101")	Usage from subscribers outside the catalog
orphan_partial	topic == "usage.rejects" && value_json.reason == "partial_without_final"	Sessions that started and never finished, as rejected by mediation
roaming_heavy_data	topic == "usage.data" && value_json.roaming && value_json.bytes_down > 100000000	Over 100 MB downloaded in one session while roaming: a likely bill-shock complaint

The first two traps check the usage topics directly, not the rejects, so they show the problem even if mediation misses it.

In a minute of traffic, 3,000 sessions and 3,438 records, Akema caught 105 duplicates, 32 records from unknown subscribers, 31 rejected partials and 9 heavy roaming sessions.

A long roaming session can trip roaming_heavy_data twice, once on its partial record and once on its final. To see everything one subscriber did, copy their IMSI and add a trap on key == "…": the key is the IMSI on every topic, so the trap collects their calls, texts, data and rejects.

3. See where the usage comes from

Usage records are the network’s activity in numbers. Explore charts them by network, cell and partner, so you can see the traffic behind the bills. Choose Load to start.

Click through to the networks

1. Drag rat_type from usage.data onto the canvas to see how much data goes over each network. In the example, about 9% went over Wi-Fi.
2. Drag roaming from usage.data and click true, then drag visited_plmn next to it to see which partner networks your roaming subscribers use. About 8% of records were roaming, on 8 partner networks.
3. Drag cell_id from any usage topic to see the busiest cells. Usage piles up on a few of them.

Weigh the traffic, not just the records

A count of records treats a 2 KB session like a 2 GB one. Drop bytes_down onto the rat_type chart and choose SUM to see how much data each network carried. In the example, 5G carried about half of it and 4G about 40%, close to their shares of records, so no network was carrying unusually heavy sessions. If one network’s share of bytes ran far ahead of its share of records, that’s where the heavy users are.

For the cells, drag cell_id from usage.data and choose Horizontal bar with Max rows set to 10. In the example, the busiest cell alone carried about a fifth of all usage records, and the next one about half as many. That’s a cell to watch for congestion, and a good one to check first when subscribers complain about speed.

See the spread of calls

Drag duration_sec from usage.voice onto empty canvas, then choose Histogram in its ⋯ menu. Most calls in the example were under 2 minutes, with a thin tail of longer ones; the longest ran 15 minutes. A sudden pile of calls at one exact length, such as 0 seconds or a round hour, usually means a switch is cutting or padding calls, not that subscribers changed how they talk.

See what mediation rejected

Drag reason from usage.rejects onto the canvas as a Donut. In the example, duplicates were nearly two thirds of everything rejected, with unknown subscribers and partial records without a final making up the rest. A new reason, or one slice growing, is the earliest sign that a switch or the mediation rules changed.

Build a network board

Card	How to make it
Data by network	rat_type from usage.data, with SUM of bytes_down
Busiest cells	cell_id from usage.data, as a Horizontal bar with Max rows 10
Roaming	roaming from usage.data, as a Donut
Call lengths	duration_sec from usage.voice, as a Histogram
Rejects	reason from usage.rejects, as a Donut
Data carried	bytes_down from usage.data dropped on empty canvas for a KPI, with SUM

Group the cards in a container, add a title, turn on Live, and use Present for a screen in the network operations center. See Explore and charts.

4. Check mediation with a playbook

In Explore, choose Playbook, add a playbook, and add these queries with + SQL. They use v_messages, which holds all four topics, and the view for each topic, such as v_usage_rejects.

Mediation cross-check: for each reason, what Akema finds in the usage topics next to what mediation rejected. The numbers should match.

SELECT 'duplicate' AS reason,
       (SELECT COUNT(*) FROM v_messages WHERE topic <> 'usage.rejects' AND is_duplicate) AS found,
       (SELECT COUNT(*) FROM v_usage_rejects WHERE reason = 'duplicate') AS rejected
UNION ALL
SELECT 'unknown_imsi',
       (SELECT COUNT(*) FROM v_messages WHERE topic <> 'usage.rejects' AND imsi LIKE '00101%'),
       (SELECT COUNT(*) FROM v_usage_rejects WHERE reason = 'unknown_imsi')
UNION ALL
SELECT 'partial_without_final',
       (SELECT COUNT(*) FROM v_messages p
        WHERE p.topic IN ('usage.voice', 'usage.data') AND p.record_status = 'partial'
          AND NOT EXISTS (SELECT 1 FROM v_messages f
                          WHERE f.charging_id = p.charging_id AND f.record_status = 'final')),
       (SELECT COUNT(*) FROM v_usage_rejects WHERE reason = 'partial_without_final')

Duplicates without a reject: every duplicate should have a reject that points at it, or it may be billed twice.

SELECT topic, event_id, charging_id
FROM v_messages m
WHERE topic <> 'usage.rejects' AND is_duplicate
  AND NOT EXISTS (
    SELECT 1 FROM v_usage_rejects r
    WHERE r.reason = 'duplicate' AND r.source_event_id = m.event_id)

Data at risk: how much data would be billed with and without the duplicates.

SELECT COUNT(*) FILTER (WHERE is_duplicate) AS duplicate_sessions,
       round(SUM(bytes_down) / 1e9, 1) AS gb_counted,
       round(SUM(bytes_down) FILTER (WHERE NOT is_duplicate) / 1e9, 1) AS gb_billable
FROM v_usage_data
WHERE record_status = 'final'

Roaming flag mismatches: roaming should be true exactly when the visited network isn’t the home network. A wrong flag means the wrong rate.

SELECT topic, imsi, roaming, visited_plmn
FROM v_messages
WHERE topic <> 'usage.rejects'
  AND roaming <> (visited_plmn <> home_plmn)

Busiest cells: the cells carrying the most records, and how many subscribers used each.

SELECT cell_id, site_id, COUNT(*) AS records,
       COUNT(DISTINCT imsi) AS subscribers
FROM v_messages
WHERE topic <> 'usage.rejects'
GROUP BY ALL
ORDER BY records DESC
LIMIT 10

Choose Run all. The cross-check matched for every reason: 105 duplicates, 32 unknown subscribers and 31 partials without a final, both found and rejected. Duplicates without a reject and Roaming flag mismatches returned no rows.

Data at risk shows what was at stake: the 33 duplicate data sessions carried 1.3 GB, about 4% of the 33.4 GB counted. Had mediation missed them, every one of those bytes would have been billed twice. Busiest cells showed the top cell carrying 728 records from 244 subscribers, twice as many as the next.

If the found and rejected numbers ever differ, the difference is the list to look at: run the query for that reason on its own to see the records.

Keep personal data out of view

Usage records identify people: the IMSI, phone number (msisdn), handset (imei) and the other party’s number (peer_msisdn), together with where they were. Add a PII redaction profile to the four topics that covers those fields, and add a Key rule, because the message key is the IMSI. The tokens are consistent, so joins on IMSI, the per-subscriber trap and the distinct subscriber counts still work.

Next steps

• Investigate a bill dispute: start a source at a Timestamp with a Stop time around the disputed period, and add a trap on the subscriber’s IMSI.
• Run the cross-check on every window: Reload and Run all regularly. A gap between found and rejected is the earliest sign mediation has changed.