E-commerce website analysis using MySQL

This is some of my practices in SQL for e-commerce analysis using million of rows relational database built by Maven Analytics.

  1. Monthly trend for gsearch utm_source sessions and order rate :

Result :

2. Monthly trend for non-brand and brand campaign through gsearch source, split by device type

Result :

3. New page test (lander-1) revenue compared to old home page

Result :

4. Full conversion funnel rates from lander / home page to finished order

1.create a temporary table (sortiran) that holds page hits value in every website session

2. then create temporary table (groupan) that holds home page landing hits grouped by website session (no more duplicate session)

3. then create temporary table (groupan) that holds lander-1 page landing hits grouped by website session (no more duplicate session)

4. menjumlahkan sesi tiap page hits dari home page

5. menjumlahkan sesi tiap page hits dari lander-1 page

6. melakukan conversion rate

Result :

  1. sortiran

2. groupan (home landing)

3. groupan1 (lander-1 landing)

4. menjumlah session tiap page dari home dan lander-1

5. Finally, conversion rate

5. Growth of specific utm_source channel

Result :

6. Monthly trend for revenue and margin from every product

Result:

7. Monthly trend of product session clickthrough and order rate

8. Cross selling of each product

9. Investigate new vs repeat session

10. Average web session time in hour, hourly in each day

11. Bounce Rate