Correct logic of measuring activation rate without analytics events?
Many thanks, the exercises have been interesting and useful!
So I tried to recreate the results for Exercise 185 Activation rate per country but without using the mobile_analytics.events table.
The results can’t be replicated due to differences in tables i.e. number of unique users in mobile_analytics.events table: 2508 number of unique iphone users in devices table: 4223
But is the logic of my query attempt correct?
WITH mob_readers_and_users AS (
SELECT
country,
COUNT(DISTINCT CASE WHEN last_page > 0 THEN d.user_id END) AS reader_num,
COUNT(DISTINCT d.user_id) AS user_num
FROM users u
LEFT JOIN books_users b
ON u.id = b.user_id
LEFT JOIN devices d
ON u.id = d.user_id
WHERE
device_type = 'iphone'
GROUP BY 1
)
SELECT
country,
reader_num,
ROUND(100.0 * reader_num / user_num, 2) AS activation_rate
FROM mob_readers_and_users
ORDER BY 3 DESC
REPLIES
Hey, Alex 
Awesome question here, happy to share some details.
I love that you’re trying to replicate the same report using different tables. If the data is consistent (i.e. every device has a mobile events, etc), then we should have no contradictions and indeed have multiple ways to achieve the same result.
IMO that’s the level you want to be with your data — know it in and out, joining only necessary tables to answer your questions. The most typical example is pre-aggregated tables. For example, instead of joining marketing campaigns and revenue all the time, we might use a table where where we have revenue per date for all campaigns.
Now let’s look at your query. The original exercise wants you to calculate an activation rate for mobile users. That’s why we have to rely on mobile analytics events — the books_users table doesn’t have device information.
Tables books_users and devices don’t have a causal relationship — a user can have an iphone app, but read via a web app and vice versa. That’s why joining books_users and devices in your query has a slighly different meaning: you’re calculating activation rate for all users with iPhones. It’s different from “activation rate for mobile app users”. 
Not necessarily from the difference in numbers but purely from the database structure — since users can read on mobile and on web, we can’t store the device info in the books_users table. You can think about this table as a user’s library or a bookshelf.
To properly track mobile and web activity, we rely on web and mobile analytics events.
If we’re interested in overall activation (% of readers), we can use only the books_users table. For platform-specific insights, we’d have to dig deeper and use mobile/web events.
Hope it’s clearer now, @Alex? I love this question, I’ll link to this Forum thread a lot, thank you 