Run Queries on Data

Query 1: Send Notifications

This query calls a function that matches offers with subscribers and sends notifications to subscribers.

SQL

USE martech;

INSERT INTO notifications (ts,city_id, subscriber_id, offer_id, cost_cents, lonlat)
SELECT now(),* FROM match_offers_to_subscribers("second");

Query OK, 5234 rows affected (2.166 s)

Query 2: Analyze Conversion Rate

This query calculates the number of customers who have made purchases so far and determines the conversion rate.

Note

As time progresses, more users will have made purchases, and the conversion rate may change.

SQL

USE martech;

SELECT
    *, 
    CONCAT(FORMAT((total_conversions / total_notifications) * 100,2),"%") AS conversion_rate, 
RANK() OVER (ORDER BY ((total_conversions / total_notifications)) desc) AS rank
FROM (
    SELECT
        offer_notification.customer,offer_notification.notification_zone,
        COUNT(offer_notification.offer_id) as total_notifications,
        COUNT(purchases.vendor) as total_conversions 
    FROM (
        SELECT offers.offer_id, offers.customer , notifications.ts, notifications.city_id, notifications.subscriber_id, offers.notification_zone
        FROM offers, notifications
        WHERE offers.offer_id = notifications.offer_id
    ) offer_notification
    LEFT JOIN purchases ON
        offer_notification.city_id = purchases.city_id
        AND offer_notification.subscriber_id = purchases.subscriber_id
        AND purchases.ts > offer_notification.ts
        AND purchases.vendor = offer_notification.customer
    GROUP BY 1
    LIMIT 5	/* remove LIMIT to see full result */
);

+----------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-----------------+------+
| customer | notification_zone                                                                                                                           | total_notifications | total_conversions | conversion_rate | rank |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-----------------+------+
| Jazzy    | POLYGON((-74.01000000 40.71250000, -74.00750000 40.71250000, -74.00750000 40.71500000, -74.01000000 40.71500000, -74.01000000 40.71250000)) |                  90 |                51 | 56.67%          |    1 |
| Eidel    | POLYGON((-74.00000000 40.74500000, -73.99750000 40.74500000, -73.99750000 40.74750000, -74.00000000 40.74750000, -74.00000000 40.74500000)) |                  78 |                34 | 43.59%          |    2 |
| Skibox   | POLYGON((-74.01500000 40.72500000, -74.01250000 40.72500000, -74.01250000 40.72750000, -74.01500000 40.72750000, -74.01500000 40.72500000)) |                  40 |                17 | 42.50%          |    3 |
| Kazu     | POLYGON((-73.99750000 40.72250000, -73.99500000 40.72250000, -73.99500000 40.72500000, -73.99750000 40.72500000, -73.99750000 40.72250000)) |                  88 |                24 | 27.27%          |    4 |
| Rhyloo   | POLYGON((-73.98250000 40.72250000, -73.98000000 40.72250000, -73.98000000 40.72500000, -73.98250000 40.72500000, -73.98250000 40.72250000)) |                 181 |                23 | 12.71%          |    5 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-----------------+------+
5 rows in set (0.51 sec)

Run Queries on Data

On this page

Query 1: Send Notifications

Query 2: Analyze Conversion Rate

Was this article helpful?

On this page

Was this article helpful?