❗ TLDR; see how to setup basic conversions funnel dashboard in Grafana with a few SQL queries.❗
See part 1 to setup tracking on your site.
The example dashboard is basic (but our conversions funnel is basic too) yet provides quick insights into what's happening. It's based on Grafana, open-source software. The dashboard itself can be split into 3 parts; let's discuss each of them.
Set visualization to "Bar gauge."
SQL to
SELECT
COUNT(DISTINCT t1.user_id) AS "Trial Started",
COUNT(DISTINCT t2.user_id) AS "Email confirmed",
COUNT(DISTINCT t3.user_id) AS "Trial Finished",
COUNT(DISTINCT t4.user_id) AS "Converted"
FROM facts t1
LEFT OUTER JOIN facts t2 ON (t1.user_id=t2.user_id AND t2.label='Email confirmed')
LEFT OUTER JOIN facts t3 ON (t1.user_id=t3.user_id AND (t3.label='Free trial finished' OR t3.label='Paid subscription plan' OR t3.label='Free trial canceled' OR t3.label='Account delete'))
LEFT OUTER JOIN facts t4 ON (t1.user_id=t4.user_id AND t4.label='Paid subscription plan')
WHERE
$__timeFilter(t1.created_at)
AND t1.label='Free trial started'
AND t1.email NOT IN('your-test-email.com','another-test-email')
This way, you'll be able to see all conversions. To narrow down for the specific channel (if you're tracking the first-page visit):
SELECT
COUNT(DISTINCT t1.user_id) AS "Trial Started",
COUNT(DISTINCT t2.user_id) AS "Email confirmed",
COUNT(DISTINCT t3.user_id) AS "Trial Finished",
COUNT(DISTINCT t4.user_id) AS "Converted"
FROM facts t1
LEFT OUTER JOIN facts t2 ON (t1.user_id=t2.user_id AND t2.label='Email confirmed')
LEFT OUTER JOIN facts t3 ON (t1.user_id=t3.user_id AND (t3.label='Free trial finished' OR t3.label='Paid subscription plan' OR t3.label='Free trial canceled' OR t3.label='Account delete'))
LEFT OUTER JOIN facts t4 ON (t1.user_id=t4.user_id AND t4.label='Paid subscription plan')
WHERE
$__timeFilter(t1.created_at)
AND t1.label='Free trial started'
AND t1.email NOT IN('your-test-email.com','another-test-email')
AND t1.meta LIKE '%"source":"google:%'
As you see, the only addition is AND t1.meta LIKE '%"source":"google:%'
condition at the end. You can filter out any source like this.
A simple list of new free trials to quickly see your new customers.
Set visualization to "Table."
SQL:
SELECT
DATE_FORMAT(t1.created_at, "%Y-%m-%d") AS day,
t1.email,
IF(t2.id, 'TRUE', '-') AS "Email confirmed",
IF(t3.id, 'TRUE', '-') AS "Trial finished",
IF(t4.id, 'TRUE', '-') AS "Converted"
FROM facts t1
LEFT OUTER JOIN facts t2 ON (t1.user_id=t2.user_id AND t2.label='Email confirmed')
LEFT OUTER JOIN facts t3 ON (t1.user_id=t3.user_id AND (t3.label='Free trial finished' OR t3.label='Paid subscription plan' OR t3.label='Free trial canceled' OR t3.label='Account delete'))
LEFT OUTER JOIN facts t4 ON (t1.user_id=t4.user_id AND t4.label='Paid subscription plan')
WHERE
$__timeFilter(t1.created_at)
AND t1.label='Free trial started'
AND t1.email NOT IN('your-test-email.com','another-test-email')
GROUP BY 1,2
ORDER BY 1 DESC
For debugging and noticing bad patterns.
Set visualization to "Table."
SQL:
SELECT created_at as date, user_id, email, label, value, meta FROM facts ORDER BY date DESC;
Here you go, nothing fancy, nothing to distract your attention. Yet, you can quickly build it and tweak/pivot from this structure when your SAAS gets more users. So, while proper BI tools are essential to a bigger business, these charts can serve a long way if your journey is only starting.
Hey, I love building and learning stuff. You can follow me on:
🧑💻 Indiehacker arminas
🐦 Twitter @arminas
🧠 To get more brain dumps like this