How to estimate the cost of translation using the translator plugin

Support
3

Here’s how I did the cost estimate for my forum. All of the queries are for the Data Explorer.

Estimate the average number of characters per post

The plugin sends the cooked text to the translation service last I checked.

SELECT AVG(LENGTH(p.cooked))
  FROM posts AS p
  JOIN topics AS t ON p.topic_id = t.id
 WHERE t.archetype != 'private_message'

Estimate the number of posts read per user visit

I took the last 30 days to get a relatively recent estimate.

-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 30

WITH t AS (
    SELECT CURRENT_TIMESTAMP - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) AS START,
        CURRENT_TIMESTAMP - (:from_days_ago * (INTERVAL '1 days')) AS END
)

SELECT AVG(posts_read)
  FROM user_visits
  JOIN t ON visited_at > t.START AND visited_at < t.END

Number of user visits in the last 30 days

-- [params]
-- int :from_days_ago = 0
-- int :duration_days = 30

WITH t AS (
    SELECT CURRENT_TIMESTAMP - ((:from_days_ago + :duration_days) * (INTERVAL '1 days')) AS START,
        CURRENT_TIMESTAMP - (:from_days_ago * (INTERVAL '1 days')) AS END
)

SELECT COUNT(1)
  FROM user_visits
  JOIN t ON visited_at > t.START AND visited_at < t.END

Estimate of number of characters read in the last 30 days

Multiplying the three previous figures together gave me an estimate of the number of cooked characters of posts that were read in the last 30 days.

Estimate of number of non-primary language users

Since English is the primary language for our forum, I used Google Analytics to determine the percentage of users that had their browsers configured for a non-English language.

Final estimate

Then I did a low/medium/high estimate by assuming that the current rate of non-English visitors would be the “common case”, halved that for the low estimate, and doubled it for the high estimate. That gave me a low/medium/high number of characters in 30 days and multiplied that by the rate per X characters for the translation service.

I hope that helps!

11 Likes