4
0 Comments

7 steps to get more sales by building developer-friendly conversions funnel (part 2)

❗ 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.

1. Conversions charts

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.

2. New free trials list

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

3. All facts list

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;

Conclusion

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


Trending on Indie Hackers
How I grew a side project to 100k Unique Visitors in 7 days with 0 audience 48 comments Competing with Product Hunt: a month later 33 comments Why do you hate marketing? 28 comments $15k revenues in <4 months as a solopreneur 14 comments My Top 20 Free Tools That I Use Everyday as an Indie Hacker 13 comments Use Your Product 13 comments