SQL > Marketing

Landscape Report

Breakdown of event and financial data grouped by category/genre.

TIP

toggle outer SELECT, ORDER BY and GROUP BY to switch between event and promoter views

SELECT sub1.promoterAccountTypeDesc AS accountCategory, sub1.promoterAccountCategoryDesc AS accountGenre, 
//SELECT sub1.eventCategory AS eventCategory, sub1.eventGenre AS eventGenre, 
    COUNT(DISTINCT sub1.promoter_id) AS promoterCount, 
    COUNT(DISTINCT sub1.event_id) AS eventCount, 
    COUNT(DISTINCT sub1.performance_id) AS perfCount,
    COUNT(sub1.transactionRef) AS transactions,
    SUM(sub1.transactionAmountFeeTicketSource) AS TSFee,
    SUM(sub1.transactionAmountGross) AS grossIncome,
    SUM(sub1.bookedSeats) AS ticketsSold
FROM (
    SELECT pat1.promoterAccountTypeDesc, pac1.promoterAccountCategoryDesc, p1.promoter_id, e1.event_id, e1.eventCategory, e1.eventGenre, per1.performance_id, t1.transactionRef, t1.transactionPaymentMethod, t1.transactionOriginatorDesc, 
        t1.transactionAmountFeeTicketSource, t1.transactionAmountGross, SUM(seatingSeatBookedSeatCount) AS bookedSeats
    FROM DBA.promoter p1
    JOIN DBA.promoterAccountType pat1 ON p1.promoterAccountType_id = pat1.promoterAccountType_id
    JOIN DBA.promoterAccountCategory pac1 ON p1.promoterAccountCategory_id = pac1.promoterAccountCategory_id
    JOIN DBA.event e1 ON p1.promoter_id = e1.promoter_id
    JOIN DBA.venue v1 ON e1.event_id = v1.event_id
    JOIN DBA.performance per1 ON v1.venue_id = per1.venue_id
    JOIN DBA.transaction t1 ON per1.performance_id = t1.performance_id
    JOIN DBA.seatingSeat ss1 ON t1.transaction_id = ss1.transaction_id
    WHERE p1.territory_id = 1 //UK = 1, EU = 2, US = 3
    AND p1.promoter_id >= 88
    AND e1.eventAccountSettled = 'Y'
    AND e1.eventAccountSettledDateTime BETWEEN '2024-01-01' AND '2024-12-31'
    AND t1.transactionCategory = 'TICKETS'
    AND t1.transactionType = 'DR'
    AND t1.transactionPaymentMethod IN('TS', 'STRIPE')
    AND seatingSeatBookedSeatCount > 0
    GROUP BY pat1.promoterAccountTypeDesc, pac1.promoterAccountCategoryDesc, p1.promoter_id, e1.event_id, e1.eventCategory, e1.eventGenre, per1.performance_id, t1.transactionRef, t1.transactionPaymentMethod, t1.transactionOriginatorDesc, 
        t1.transactionAmountFeeTicketSource, t1.transactionAmountGross
) sub1
GROUP BY sub1.promoterAccountTypeDesc, sub1.promoterAccountCategoryDesc
ORDER BY sub1.promoterAccountTypeDesc, sub1.promoterAccountCategoryDesc
//GROUP BY sub1.eventCategory, sub1.eventGenre
//ORDER BY sub1.eventCategory, sub1.eventGenre

Seasonal Event Report

Events for a given date range with admin email addresses and creation dates for retention purposes.

SELECT DISTINCT(eventTitle), companyName, promoter.promoter_id, contactEmail, MIN(DATE(eventCreated)) AS firstEventCreated, eventCategory, eventGenre, MIN(performanceDate) AS firstDate, SUM(transactionAmountFeeTicketSource) 
FROM DBA.PROMOTERUSERACCOUNT JOIN DBA.PROMOTER JOIN DBA.EVENT JOIN DBA.VENUE JOIN DBA.PERFORMANCE JOIN DBA.TRANSACTION
WHERE eventCategory = 'seasonal'
  AND DATE(performanceDate) BETWEEN '2025-01-01' AND '2025-12-31'
  AND contactPermissionsJSON LIKE '%"administrator":"True"%'
  AND eventActivated = 'Y'
  AND promoter.promoter_id >= 88
  AND transactionCategory IN ('TICKETS', 'SUNDRY')
GROUP BY eventTitle, companyName, promoter.promoter_id, contactEmail, eventCategory, eventGenre
ORDER BY firstEventCreated, promoter.promoter_id;