Going fast! #DataquestChallenge Premium Annual Offer:
500 get 50% & the next 1000 get 40% off.
GET OFFER CODE

Computing Conversion Rate w/ SQL

Sorry if this sounds very basic; bear with me.

I need to determine the conversion rate of 3 ads, each representing a product; that would be total clicks divided by the number of people who started a subscription after seeing that ad.

COLUMNS:

  • person_id - unique identifier of the person
  • date - date they were shown the ad
  • ad_id - content of the ad: ad_1_product1, ad_2_product2, or ad_3_product3
  • clicked (TRUE/FALSE) - clicked on the ad
  • signed_up (TRUE/FALSE) - created an account
  • subscribed (TRUE/FALSE) - started a paid subscription

I set clicked, signed_up and subscribed as boolean.

MY CODE:

SELECT ad_id, (count(clicked) / count(subscribed)) as CR
 FROM  videoadcampaign
 WHERE subscribed = 'TRUE' AND clicked = 'TRUE'
 GROUP BY ad_id;

The code above gives me a ratio of 1, so apparently SQL is still counting the total.

I am totally stuck.

I will also need to calculate other KPIs for clicks and signed_up, so filtering those booleans and put them into a ratio is the core of what I need to do.

Thank you tons for your help!

SQL is not yet one of my strengths, so difficult to discuss more for me without looking at the data itself and spending time on it.

But does count(clicked) / count(subscribed) result in 1 no matter what? Because that could mean you need to CAST it (numerator or denominator) as Float. It seems it’s being rounded to 1 based on your description.

1 Like
SELECT ad_id, count(cast(clicked AS FLOAT)) / count(cast(subscribed AS FLOAT)) as CR
  FROM videoadcampaign
 WHERE subscribed = 'TRUE' AND clicked = 'TRUE'
 GROUP BY ad_id;

Unfortunately, I still get:

ad_id
product 1 (1)
product 2 (1)
product 3 (1)

Also, wouldn’t the conversion rate be the inverse of that?

It would be the number of conversions (subscriptions) divided by the total number of interactions (number of clicks)?

https://support.google.com/google-ads/answer/2684489?hl=en

Conversion rates are calculated by simply taking the number of conversions and dividing that by the number of total ad interactions that can be tracked to a conversion during the same time period.

Ah, yeah my mistake there.

But that wouldn’t change the result in this case because it is still dividing by itself.

So, the issue is most likely because of this -

You are only selecting rows where both of them are True. If both of them are True then their counts will always be the same. It would be something like -

subscribed clicked
True True
True True
True True
True True
True True

That’s the data you are working with as a result. That’s why it’s always 1. Both of them are True no matter what.

Think about the conversation rate definition again and see what exactly the logic needs to be here.

Right, so I think the logic should be CR = SUBS (TRUE) / SUBS (TRUE + FALSE) [or total count] and then filter by CLICK = TRUE

The thing is I don’t know how to tell SQL to apply ‘TRUE’ only for the denominator.

You will have to look into (or learn about) SQL Views or Subqueries, in this case, I believe. Dataquest does have content related to both that could help.

Try something like SUM(subscribed) / SUM(clicked) without any filter.

I’m too lazy at the moment to try to figure out if this is enough, but it should be. You should watch out for nuances about the data though. For example, can the same person be shown the ad more than once? Does that matter? The answers to these questions can — and probably will — change the query that gives you what you want.