An Analytics Export With Percent Signs and Dashes Cleaned

Exports from analytics tools are written for people. Visits come as 12,430, rates as 42.5%, revenue as $1,204.50, a change as +8.2% or (4.1%), and anything unavailable as a dash, two dashes, n/a or a typographic em dash, depending on which column and which day. Opened in a spreadsheet, it looks right. Loaded into a database or a script as numbers, every one of those cells fails. The cleaning is mechanical and the risk is in the details: a placeholder missed becomes a zero or an error, a parenthesis read wrongly flips a sign. This article cleans a four-row file with one function, shows the proof that nothing escaped and discusses what the placeholders mean. It is one of the problems in worked CSV problems and the patterns behind them, and a close relative of turning currency strings into numbers in a CSV.

Look at the shapes

Here is the file. Every value is display text:

page,visits,bounce_rate,conversion,revenue,change
/home,"12,430",42.5%,3.1%,"$1,204.50",+8.2%
/pricing,"3,018",-,--,$0.00,(4.1%)
/blog/csv,"8,764",61.0%,0.4%,"$310.00",-1.5%
/contact,0,n/a,n/a,—,

Profile the shapes with digits replaced by 9, as in worked CSV problems, and each column states its own rules. bounce_rate has 99.9%, a lone - and a/a (the letters of n/a replaced by a). revenue has $9,999.99, $9.99, $999.99 and a single —. change has (9.9%), +9.9% and -9.9%, and one empty value. From those shapes the rules write themselves: the % and $ are decoration, the commas are thousands separators, a leading + is decoration, parentheses mean negative, and four tokens (-, --, n/a, —) plus the empty string mean no value.

One function, applied to every column

Read everything as text and define two small macros: one that recognizes placeholders, one that converts a value.

CREATE MACRO is_placeholder(s) AS s IS NULL OR trim(s) IN ('', '-', '--', '—', 'n/a', 'N/A');

CREATE MACRO num(s) AS CASE
    WHEN is_placeholder(s) THEN NULL
    WHEN s ~ '^\(.*\)$' THEN -1 * TRY_CAST(regexp_replace(s, '[()%$,+\s]', '', 'g') AS DECIMAL(18,4))
    ELSE TRY_CAST(regexp_replace(s, '[%$,+\s]', '', 'g') AS DECIMAL(18,4)) END;

SELECT page, num(visits) AS visits, num(bounce_rate) / 100 AS bounce_rate, num(conversion) / 100 AS conversion,
       num(revenue) AS revenue, num(change) / 100 AS change
FROM read_csv('analytics.csv', all_varchar=true);

page       visits   bounce_rate  conversion  revenue  change
/home      12430    0.425        0.031       1204.5   0.082
/pricing   3018     NULL         NULL        0.0      -0.041
/blog/csv  8764     0.610        0.004       310.0    -0.015
/contact   0        NULL         NULL        NULL     NULL

Walk through the conversions. 12,430 lost its comma and became 12430. 42.5% lost the sign and, divided by 100, became 0.425, a proportion, which is the form a database should store: store 0.425 and format it as a percent when displaying. $1,204.50 became 1204.5. +8.2% became 0.082. (4.1%) matched the parenthesis rule, was stripped to 4.1, multiplied by minus one and divided by 100, giving -0.041. The placeholders became NULL, which is what "no value" should be.

A few details of the macros matter. The conversion uses TRY_CAST, which returns NULL for a value it cannot read instead of raising, so a new junk token does not stop the load, and for that reason the check below is not optional. The regular expression removes the characters you listed and nothing else, so a letter left in a value, such as 12.5k or 3.1x, survives and fails the cast, which is correct. The DECIMAL(18,4) type keeps four decimal places, enough for a percentage given to two. And the macro takes the whole cell as text, so the same function works on every column, which is why the cleaning is a few lines and not a script per column.

The proof: nothing escaped

Because TRY_CAST turns anything unreadable into NULL, a NULL in the output is ambiguous: it could be a placeholder you intended or junk you did not. Separate the two by counting values that are not placeholders and still did not parse:

SELECT sum(CASE WHEN NOT is_placeholder(v) AND num(v) IS NULL THEN 1 ELSE 0 END) AS unparsed
FROM (SELECT visits AS v FROM raw UNION ALL SELECT bounce_rate FROM raw UNION ALL SELECT conversion FROM raw
      UNION ALL SELECT revenue FROM raw UNION ALL SELECT change FROM raw);

unparsed
0

Zero, on twenty cells. Wire it as a hard failure: if the count is not zero, stop and print the offending values, because it means a token you have not met, perhaps N.A., null or a currency code. A second proof is a total: the visits summed to 24,212 after cleaning, and the same figure computed by hand from the original, 12,430 plus 3,018 plus 8,764 plus 0, is 24,212. Totals that match a number published elsewhere, such as a dashboard's headline figure, are the strongest evidence that the cleaning kept the meaning.

What a dash means

The macros turn four different tokens into NULL, and it is worth asking whether they mean the same thing. - in a bounce rate column often means the metric could not be computed, because there were no sessions to compute a rate from. n/a may mean the same or "not applicable for this page type". The em dash in revenue for /contact may mean no revenue tracking is set up, while $0.00 for /pricing means tracking works and nothing was earned. Converting both to NULL loses a distinction, and converting both to zero invents data: a zero revenue and an untracked page are different facts, and an average that includes zeros for untracked pages is wrong.

So decide per column, write it down and keep the information if it matters. A cheap way is to preserve the original in a flag column: revenue_raw beside revenue, or a revenue_status with ok, no_data and not_tracked. Averages should then be over rows that have a value, with the count of rows reported. In the test file, /contact shows 0 visits and no rates: zero visits explains why a bounce rate cannot exist, and a rule of "a missing rate with zero visits is expected" turns a mystery NULL into a checked one.

Percent as a proportion

Storing 42.5 versus 0.425 is a source of long-running confusion. Pick one. I prefer proportions (0.425) in storage, since they multiply and combine without conversion, and a column named bounce_rate holding 0.425 is clear when the type or comment says so. If the target system wants percent as 42.5, say so in the column name (bounce_rate_pct). The bug to avoid is a feed in which some rows are 0.425 and some 42.5 because the source mixed conventions: check the range, which for a proportion should lie between 0 and 1, and flag rows outside it.

When a placeholder means something

If the export is the only record of why a number is missing, do not discard it. Analytics tools often use distinct tokens for distinct reasons, and a new token appearing in the file is information: a tracking change, an outage, a new page type. The unparsed check surfaces it. Add the token to the list only after you know what it means, and put the meaning in a comment. For the broader decision about missing values, see handling missing values in a CSV. The functions used are documented in DuckDB's regular expression reference and its numeric types page.