Points Redemption Custom Fields
QUERIES FOR POINTS REDEMPTION CUSTOM FIELD
Table Name:
user_management__loyalty_redemptions
user_management__custom_fields_data
user_management__custom_fields
All Points redemption custom fields
Exporting all points redemption custom fields data for Org ID and for a given time period.
To find all custom field data of points redemptions in a time period. <replace org_id and date according to the requirement in the where clause>
select lr.org_id, lr.user_id, lr.bill_number, from_unixtime(lr.`date`/1000,'yyyy-MM-dd') as redemption_date, cf.name as custom_field_name, cfd.value as custom_field_value from source_delta.user_management__loyalty_redemptions lr
join source_delta.user_management__custom_fields_data cfd on lr.org_id=cfd.org_id and lr.id=cfd.assoc_id
join source_delta.user_management__custom_fields cf on cfd.cf_id=cf.id and cfd.org_id=cf.org_id
where lr.org_id=313 and lr.`date` between to_unix_timestamp('2022-01-01', 'yyyy-MM-dd')*1000 and to_unix_timestamp('2022-12-31', 'yyyy-MM-dd')*1000
Please refer to the image below for your reference:
Particular points redemption custom field
Exporting points redemption custom field data of a particular custom field for Org ID and a given time period.
To find the value of a particular custom field data of points redemptions in a time period. <replace org_id, custom_field name, and date according to the requirement in the where clause>
select lr.user_id, lr.bill_number, lr.points_redeemed, from_unixtime(lr.`date`/1000,'yyyy-MM-dd') as redemption_date, cf.name as custom_field_name, cfd.value as custom_field_value from source_delta.user_management__loyalty_redemptions lr
join source_delta.user_management__custom_fields_data cfd on lr.org_id=cfd.org_id and lr.id=cfd.assoc_id
join source_delta.user_management__custom_fields cf on cfd.cf_id=cf.id and cfd.org_id=cf.org_id
where lr.org_id=313 and cf.name='birthday' and lr.`date` between to_unix_timestamp('2022-01-01', 'yyyy-MM-dd')*1000 and to_unix_timestamp('2022-12-31', 'yyyy-MM-dd')*1000
Please refer to the image below for your reference
Updated 10 months ago