Reducing SMS Message Segments to Save $$$ — A SQL Optimization Story.

01 — DiscoveryThe 161-character problem

SMS billing is a step function. A message is one segment up to 160 characters (using GSM-7 encoding). Hit 161, and you pay for two segments — even though the receiver still sees one message. Hit 307, you pay for three. The carrier's bill doesn't care that you only meant to add one more word.

The product had thousands of message templates. Some were old, some were new, some had been edited a dozen times by people who had no idea about segment boundaries. Every appointment reminder, every confirmation, every nudge — all flowing through templates that nobody had ever audited for length.

The bill was rising. Nobody knew why. So we measured.

02 — The SQLCounting segments at scale

Step one was attribution: which templates were sending the most messages, and how many segments each was costing. The actual segment count depends on encoding (GSM-7 vs UCS-2 for emoji), variable substitution, and length — so we had to compute it for the rendered message, not the template.

WITH rendered AS (
  SELECT
    template_id,
    template_name,
    rendered_body,
    LENGTH(rendered_body) AS char_len,
    CASE
      WHEN REGEXP_LIKE(rendered_body, '[^\\x00-\\x7F]') THEN 'UCS-2'
      ELSE 'GSM-7'
    END AS encoding
  FROM sms_messages
  WHERE sent_at > DATEADD(day, -30, CURRENT_DATE())
)
SELECT
  template_id, template_name,
  COUNT(*) AS sends,
  CEIL(
    char_len / CASE encoding
      WHEN 'GSM-7' THEN 160
      ELSE 70
    END
  ) AS segments_per_msg,
  COUNT(*) * segments_per_msg AS total_segments
FROM rendered
GROUP BY template_id, template_name, char_len, encoding
ORDER BY total_segments DESC;

The output was illuminating. A handful of templates — many of them the most-sent ones — were sitting at 161–175 characters. Doubling the segment count for the sake of a few unnecessary words.

03 — The fixRewriting under 150

The target became 150 characters or fewer, with some buffer for variable substitution. Why 150 and not 160? Because templates contain variables ({name}, {date}, {time}) that expand at send time. A template that's 158 characters with placeholders might render to 165. The buffer protects against that.

Rewriting was tedious but mechanical. Drop unnecessary salutations. Use shorter date formats. Remove "please" where it didn't add meaning. The customer-facing voice didn't change; the segment count did.

04 — Phase twoDecommissioning unused numbers

While we were in the SMS bill, we audited the phone numbers themselves. Each provisioned number carries a small monthly charge — a few dollars per number, which sounds trivial until you realize there are 600 of them and 300 haven't sent a message in over a year.

SELECT
  n.phone_number,
  n.tenant_id,
  n.provisioned_at,
  MAX(m.sent_at) AS last_send,
  DATEDIFF(day, MAX(m.sent_at), CURRENT_DATE()) AS days_idle
FROM phone_numbers n
LEFT JOIN sms_messages m
  ON n.phone_number = m.from_number
GROUP BY n.phone_number, n.tenant_id, n.provisioned_at
HAVING MAX(m.sent_at) IS NULL
   OR MAX(m.sent_at) < DATEADD(day, -365, CURRENT_DATE())
ORDER BY days_idle DESC NULLS FIRST;

Three hundred numbers came back with no activity for over a year. After a brief review with operations to confirm none were reserved for future campaigns, they were decommissioned. The recurring savings showed up on the next month's bill.

05 — TakeawaysHidden costs are SQL problems

The SMS bill wasn't a vendor problem. It wasn't a budget problem. It was a measurement problem. Once we could see the segment counts, the fix was obvious. The reason it had grown was that nobody had ever made it visible.

• Step-function pricing rewards measurement. Find the cliffs and live below them.
• Audit recurring resources. Numbers, licenses, subscriptions — they accumulate quietly.
• Buffer for variable substitution. A template that fits the threshold doesn't mean a rendered message will.
• Cost visibility is a SQL feature, not a finance feature.

The lesson generalizes: any cost driven by usage that's emitted as data is a cost you can govern with SQL. Make it visible. Find the cliffs. Live below them.