FanMaker

Overview

FanMaker provides a variety of tools to keep fans engaged, including loyalty and rewards programs. This integration uses the FanMaker API to copy data about your users and their FanMaker activities into DWA for reporting and analysis.

To set up this integration, first contact FanMaker and request these credentials:

• vendor ID
• site ID
• secret key

Then contact your KORE Success Manager and provide them with these credentials using our secure information portal.

ERD and data dictionary

(This information is provided on a best-effort basis without guarantees. For clarity, some column names are omitted from this ERD.)

Download data dictionary (Excel file)

Example queries

List the fans who have redeemed rewards points for the largest number of prizes:

SELECT o.user_id, SUM(o.quantity) AS total_qty, u.first_name,
       u.last_name, u.email
FROM fanmaker.[order] o
INNER JOIN fanmaker.users u ON o.user_id = u.user_id 
GROUP BY o.user_id, u.first_name, u.last_name, u.email
ORDER BY total_qty DESC
LIMIT 50;

List the fans with the highest current points balance who redeemed for a prize within the past 30 days:

SELECT u.user_id, u.first_name, u.last_name,
       u.email, u.points_available, o.latest_order_date
FROM fanmaker.users u 
INNER JOIN (
        SELECT user_id, MAX(ordered_on) AS latest_order_date
        FROM fanmaker.[order]
        WHERE ordered_on > DATEADD(day, -30, GETDATE())
        GROUP BY user_id
       	) o
    ON u.user_id = o.user_id
ORDER BY u.points_available DESC
LIMIT 50;

Use timestamps to identify transactions that took place during an event (up to 3 hours before or after the planned start or end times, respectively) in the past month:

SELECT t.transaction_id, t.user_id, t.location_id, t.purchased_at,
       e.event_name, e.id AS event_id, e.date_start, e.date_end
FROM fanmaker.transactions t
INNER JOIN (
        SELECT * FROM fanmaker.events 
        WHERE events.date_start > DATEADD(month, -1, GETDATE())
        ) e
    ON (t.purchased_at >= DATEADD(hour, -3, e.date_start)
        AND t.purchased_at <= DATEADD(hour, 3, e.date_end))
ORDER BY t.purchased_at DESC
LIMIT 50;