Content translation/analytics/Daily stats

This is knowledge sharing about how to collect daily Content Translation statistics.

Unless otherwise noted, "" is eight digits: yyyymmdd. For example 20160425 is April 25 2016.

The scripts can be found in the bash/ directory in the limn-language-data Gerrit repo.

crontab configuration
The scripts for collecting deletion and suggestions stats run every day using cron. To configure it in your account, run  and write.

Yandex

 * 1) Go to https://translate.yandex.com/developers/stat
 * 2) Copy the number from the top chart to the Characters column in the Yandex stats spreadsheet.
 * 3) Copy the number from the bottom chart to the Request column in the Yandex stats spreadsheet.

Daily suggestions accepted
NB: The events_.txt file is generated by the script events_scheduled.sh. It automatically runs for yesterday. To get the numbers until a particular day, run the following:
 * 1) Run:
 * 2) In the query results table, copy just the numbers from the "count(event_targetLanguage)" column. (In OSX terminal this is easy by selecting with the mouse while holding the Alt key.)
 * 3) Paste them to a text editor.
 * 4) Select them in the text editor and copy. (The intermediate step is needed because direct copying from the terminal to a spreadsheet is broken.)
 * 5) Open the "Suggestions Accepted Daily" tab in the "Suggestions Enablement" spreadsheet.
 * 6) Find today's date (NB: one day after the ).
 * 7) Click row 3 and paste.
 * 8) Scroll down and check whether the list is not too long. The last number must align with the last language. If not:
 * 9) Delete the pasted numbers (the Undo action usually accomplishes that).
 * 10) Find the missing language by examining the query result.)
 * 11) Add a row for that language.
 * 12) Go back to step "Click row 3 and paste".
 * 13) The number in the "Per day" row is the number of accepted suggestions per day.
 * 1) Go back to step "Click row 3 and paste".
 * 2) The number in the "Per day" row is the number of accepted suggestions per day.
 * 1) Run the following query—it takes about three minutes:
 * 1) Run the following query—it takes about three minutes:

Save, restore, publish errors (s/r/p)

 * 1) Make the terminal window wide. The numbers row can be long and you don't want it to clip.
 * 2) Run:
 * 3) Just for yesterday:
 * 4) NB 1: It is usually fast in the beginning, and slowish in the end. The total time is up to two minutes.
 * 5) NB 2: The reason "a week ago" is shown for "What was published" is that this number can sometimes change (E.g., it took a few days until the translator actually published.)
 * 6) For a particular day:
 * 7) NB: The last step is can take up to two minutes.
 * 8) For a range of dates:
 * 9) (etc.)
 * 10) Carefully copy the row of numbers, and paste it to a text editor.
 * 11) Select them in the text editor and copy. (The intermediate step is needed because direct copying from the terminal to a spreadsheet is broken.)
 * 12) Open the s/r/p spreadsheet and paste the numbers from the text editor in the appropriate column at the appropriate date.
 * 1) NB: The last step is can take up to two minutes.
 * 2) For a range of dates:
 * 3) (etc.)
 * 4) Carefully copy the row of numbers, and paste it to a text editor.
 * 5) Select them in the text editor and copy. (The intermediate step is needed because direct copying from the terminal to a spreadsheet is broken.)
 * 6) Open the s/r/p spreadsheet and paste the numbers from the text editor in the appropriate column at the appropriate date.
 * 1) Select them in the text editor and copy. (The intermediate step is needed because direct copying from the terminal to a spreadsheet is broken.)
 * 2) Open the s/r/p spreadsheet and paste the numbers from the text editor in the appropriate column at the appropriate date.

Deletion stats

 * 1) Run
 * 2) For a particular day in the recent past:
 * 3) For older reports see the schedule-archive directory, but it's better to just run the script, because the data may have changed.
 * 4) For a particular day:
 * 5) Default is yesterday.
 * 6) For a range of dates:
 * 7) NB 1: Note that this is very silly and only works for dates within the same month.
 * 8) NB 2: The default range is from the beginning of the current month until yesterday.
 * 9) Copy the numbers to the deletion stats spreadsheet.
 * NB: The data changes practically every day, so the numbers are an approximation. They are less likely to change after two weeks. It's suggested to check not only yesterday, but also a week ago.
 * 1) NB 1: Note that this is very silly and only works for dates within the same month.
 * 2) NB 2: The default range is from the beginning of the current month until yesterday.
 * 3) Copy the numbers to the deletion stats spreadsheet.
 * NB: The data changes practically every day, so the numbers are an approximation. They are less likely to change after two weeks. It's suggested to check not only yesterday, but also a week ago.
 * 1) Copy the numbers to the deletion stats spreadsheet.
 * NB: The data changes practically every day, so the numbers are an approximation. They are less likely to change after two weeks. It's suggested to check not only yesterday, but also a week ago.

Adding a language

 * 1) Add a new row, alphabetically, and put the code there.
 * 2) Make sure to fill the bottom totals rows by copying from adjacent languages' columns.

Adding a new month tab

 * 1) Create a new spreadsheet tab. Make it the first one, because that's the one you'll need most frequently from now on.
 * 2) Copy the data from the previous month.
 * 3) Select all the deletion numbers cells (but not the totals cells) and delete.
 * 4) Select the whole tab and remove the background color.
 * 5) Select the Sundays' rows and make their background light gray.
 * 6) In the weekly total column, write the SUM formula for the week (for the first week this will usually include a few days from the previous month).

Monthly report stats

 * 1) Run
 * 2) That's pretty much it! Just be sure to get the deletion number from the deletion spreadsheet.
 * 1) That's pretty much it! Just be sure to get the deletion number from the deletion spreadsheet.
 * 1) That's pretty much it! Just be sure to get the deletion number from the deletion spreadsheet.
 * 1) That's pretty much it! Just be sure to get the deletion number from the deletion spreadsheet.