1

I'm trying to calculate open ratio for our subscribers by adding up the number of emails they have been sent, number of those emails they have opened and then finally trying to calculate the ratio by taking their opens/sent. The problem I'm finding is that records with results not equal to "100" are loading with "0.00" instead of the true calculation. The source data extension and the target are the same, the query is set to Update.

Example Data Extension:

SubscriberKey   | SentCount | OpenCount | Ratio
(Text Char-254) | (Number)  | (Number)  | (Decimal 18,4)
11111           | 25        | 5         |
22222           | 13        | 8         |
33333           | 25        | 5         |
44444           | 15        | 15        |
55555           | 12        | 0         |

Example of Current Result:

SubscriberKey   | SentCount | OpenCount | Ratio
(Text Char-254) | (Number)  | (Number)  | (Decimal 18,4)
11111           | 25        | 5         | 0.00
22222           | 13        | 8         | 0.00
33333           | 25        | 5         | 0.00
44444           | 15        | 15        | 100.00
55555           | 12        | 0         | 0.00

Example Query:

SELECT
c.SubscriberKey,
CAST(((c.OpenCount / c.SentCount) * 100) AS DECIMAL(18, 2)) as Ratio
FROM DataTable c

Goal Result:

SubscriberKey   | SentCount | OpenCount | Ratio
(Text Char-254) | (Number)  | (Number)  | (Decimal 18,4)
11111           | 25        | 5         | 20.00
22222           | 13        | 8         | 61.54
33333           | 25        | 5         | 20.00
44444           | 15        | 15        | 100.00
55555           | 12        | 0         | 0.00
2

Pretty sure you have to cast each element individually. Like this:

SELECT
c.SubscriberKey,
cast((c.openCount) as decimal(18,4)) / cast(c.SentCount as decimal(18,4)) * 100 as Ratio
FROM DataTable c

or better:

SELECT
c.SubscriberKey
, case
    when ISNULL(c.SentCount,0) = 0 AND ISNULL(c.OpenCount,0) > 0 then 100.0
    when (cast(c.OpenCount as Decimal (18,4))/cast(c.SentCount as Decimal (18,4))) > 1 then 100.0
    else (cast(c.OpenCount as Decimal (18,4))/cast(c.SentCount as Decimal (18,4))) * 100.0
  end as OpenRate
FROM DataTable c
1
  • That worked! I understand that your "better" version helps against trying to divide by 0 in a 0/0 circumstance, but one note I forgot to mention was that previously I had only selected subscribers who had been sent at least 10 emails--and then opens were based on those previous sends. In my situation, there shouldn't be any need for the additional safeguard, but good to know if I decide to calculate more broadly!
    – Brian C
    Jan 3 '19 at 3:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.