SWZD Report
- Danny Hannah
The following report will be used for reconciliation reporting for SWZD. The report will provide the following columns:
Convertr Campaign Id- self explanatoryCampaign Manager- self explanatoryConvertr Campaign Name- self explanatoryNumber of Billable Leads- self explanatoryOli- This is an Order Line Item id which is provided with every lead in SWZD and used to reference specific budgets internally.
The complexities of this report comes from their reporting being based on the delivery date or the lead, and their heavy use of the “Mark as Delivered” functionality which doesn’t have Job level delivery date, meaning there is often gaps when using the report builder.
The report will only show records that:
Lead is visible (not deleted)
Linked to Advertiser which is visible (not deleted)
Linked to a Campaign which is visible (not deleted)
The lead is billable to the Advertiser
The lead is currently in a delivered state AND has a Integration Job timestamp from within the defined period OR was marked as billable in the defined period (our closest thing to the delivered date when using Mark As Delivered flag)
The report is grouped by the OLI
Query
This is the final query for the report, please change the WHERE clause for the dates you would like to pull”
SELECT
c.id as "Convertr Campaign Id",
c.campaignManager as "Campaign Manager",
c.name as "Convertr Campaign Name",
COUNT(l.id) as "Number of Billable Leads",
ld.value as "Oli"
FROM Lead l
JOIN LeadData ld ON (ld.leadId = l.id AND ld.name = 'oli' AND ld.source = 'publisher' AND ld.visible = 1)
JOIN Campaign c ON (c.id = l.campaignId AND c.visible = 1)
JOIN Advertiser a ON (a.id = c.advertiserId AND a.visible = 1 AND a.id != 22431)
WHERE l.visible = 1
AND l.advertiserBillable = 1
AND l.integration = 1
AND l.firstDeliveredTs IS NOT NULL
AND CONVERT_TZ(l.firstDeliveredTs,'+00:00','-5:00') BETWEEN '2022-05-01 00:00:00' AND '2022-05-31 23:59:59'
GROUP BY Oli, c.id
ORDER BY l.id DESC;
This query is useful for reconciling, providing a lead level breakdown:
SELECT
l.id,
c.id as "Convertr Campaign Id",
c.campaignManager as "Campaign Manager",
c.name as "Convertr Campaign Name",
COUNT(l.id) as "Number of Billable Leads",
CAST(AES_DECRYPT(ld.value, CONCAT('C0NVERTR', l.campaignId, 'KkI6vmgEy4Hp55j2cVrRhEnFzwIQ3uAAQdEOjonQdvpwAAcHv71pqTLRvrV')) AS CHAR) as "Oli",
IFNULL(null, CONVERT_TZ((
SELECT
MAX(pjl.executedTs)
FROM ProcessrJobLead pjl
JOIN CampaignProcessrJob cpj ON (pjl.campaignJobId = cpj.id)
JOIN ProcessrJob pj ON (cpj.processrJobId = pj.id AND pj.processrJobCategoryId = 2)
WHERE pjl.visible = 1
AND pjl.passed = 1
AND pjl.leadId = l.id
),'+00:00','-06:00')) as integrationResult,
CONVERT_TZ((SELECT
MAX(createdTs)
FROM LeadNote
WHERE `type` = 'payment_change_adv'
AND note IN (
"The lead was validated via a flag and delivered - the lead is billable to the advertiser",
"The lead is valid and delivered - the lead is billable to the advertiser"
)
AND leadId = l.id
AND visible = 1),'+00:00','-06:00') as billableResult
FROM Lead l
JOIN LeadData ld ON (ld.leadId = l.id AND ld.name = 'oli' AND ld.source = 'publisher' AND ld.visible = 1)
JOIN Campaign c ON (c.id = l.campaignId AND c.visible = 1)
JOIN Advertiser a ON (a.id = c.advertiserId AND a.visible = 1)
WHERE l.visible = 1
AND l.advertiserBillable = 1
AND l.integration = 1
AND CONVERT_TZ(IFNULL(
(
SELECT
MAX(pjl.executedTs)
FROM ProcessrJobLead pjl
JOIN CampaignProcessrJob cpj ON (pjl.campaignJobId = cpj.id)
JOIN ProcessrJob pj ON (cpj.processrJobId = pj.id AND pj.processrJobCategoryId = 2)
WHERE pjl.visible = 1
AND pjl.passed = 1
AND pjl.leadId = l.id
),
(SELECT
MAX(createdTs)
FROM LeadNote
WHERE `type` = 'payment_change_adv'
AND note IN (
"The lead was validated via a flag and delivered - the lead is billable to the advertiser",
"The lead is valid and delivered - the lead is billable to the advertiser"
)
AND leadId = l.id
AND visible = 1)),'+00:00','-06:00') BETWEEN '2021-12-01 00:00:00' AND '2022-01-20 23:59:59'
#GROUP BY Oli
GROUP BY l.id
ORDER BY l.id DESC