Marketing Analytics
Getting an error
From Exercise 66:
Let’s practice our newly learned skill of grouping and counting. Which email domain has the most amount of users at Bindle?
I am writing a code other way, by forming function. I am getting error. Could anyone help me to identify where I have mistaken?
WITH domain AS (
SELECT
SPLIT_PART (email, '@', 2) AS email_domain,
FROM users
)
SELECT
email_domain,
COUNT(*)
FROM domain
GROUP BY 1
ORDER BY 2 DESC
87 - using sub queries
Hi there,
I don’t quite understand why this sub query (below) is turning up zeros, rather than purchase rate to question 87. I see from the answer I could have simplified this into one query, but I don’t understand why this way doesn’t work as well? Thanks for your help!
with tabletime as (
select
utm_campaign,
COUNT(*) AS userscount,
Count(CASE WHEN status = 'customer' THEN id END) AS payingcustomers
from users
WHERE
utm_campaign IS NOT NULL
GROUP BY utm_campaign
)
SELECT
utm_campaign,
100 * (payingcustomers / userscount) as purchase_rate
FROM tabletime
106. How order in FROM clause changes the results
I tried solving 106 problem using the following query:
SELECT
utm_campaign,
SUM(amount) AS total_revenue,
COUNT(DISTINCT a.id) AS total_users,
SUM(amount)/COUNT(DISTINCT b.id) AS ARPU
FROM purchases a
JOIN users b
ON a.user_id = b.id
AND a.refunded = FALSE
-- WHERE utm_campaign IS NOT NULL
GROUP BY 1
ORDER BY 4 DESC
which should be quite similar with the proposed solution, the difference is on the order of FROM clause. I used purchase table before user table and it gives a very different results.
I’d like to understand why is this the case as this is a quite surprising SQL behavior to me.
Biggest age group in USA
I’m doing the “Biggest age group in USA” exercise, however, I was trying to experiment and to a little change in the analysis by trying to visualize the groups by country, but I keep getting an error with the following query:
SELECT
CASE
WHEN age < 13 THEN 'kid'
WHEN age < 18 THEN 'teenager'
WHEN age < 25 THEN 'college'
WHEN age < 35 THEN 'young adult'
WHEN age < 56 THEN 'middle age'
ELSE 'older adult'
END AS age_group,
country,
COUNT(*)
FROM users
GROUP BY 1
GROUP BY country
Thank you all!!
Q107 - ARPPU
Hey! Why are we saying the below?
If we use refunded = FALSE filter in the WHERE clause (filtering out all users who don’t have purchases) we’d calculate ARPPU.
Don’t we still have all the free/trial customers that shouldn’t be in ARPPU but wouldn’t be filtered out if we only use refunded as a filter? Thanks!
Don't know what is failing in this query
I was trying to solve the “Most popular email domain” exercise, and I already completed it, however at first I was trying to do the following query, but it keeps getting me an error, don’t know where is failing, any clues?
WITH domains AS(
SELECT
SPLIT_PART(email, '@', 2) AS email_domain,
COUNT(*)
FROM users
)
SELECT
SPLIT_PART(email_domain, '.', 1) AS main_domain
FROM domains
Tracking
How to distinguish between direct visits, referral visits and clicks from organic search when tracking with a custom pixel? In the course, the main info on campaigns comes from UTMs from the URL, but you would also wanna analyze organic traffic, that does not usually have tracking parameters in a link.