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.
- person_id - unique identifier of the person
- date - date they were shown the ad
- ad_id - content of the ad:
- 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.
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!