Multi Lottery

General Data Requirements

Before preparing your data tables, please review the following universal formatting rules.

Data Formatting

Decimal Precision: All decimal-type attributes must be rounded to a maximum of four decimal places. For example, a value of 4.56789 must be formatted as 4.5679. This applies to all monetary fields and other decimal values.
Currency Conversion: All monetary figures must be converted into a single, consistent currency across all tables.

Required Data Tables

The following data tables describe the content, format and labels of the required data.

Customers – each row describes the attributes of a single customer
Transactions – lists all transactions that affect the player's account balance
Draws – contains a record of all purchase draws for all players
InstantPlay Games – records the games played by customers
Lottery Catalog – contains information on available lotteries
InstantPlay Game Catalog – details the available instant play games

Customers

Key	Key Field	Data Type	Description
PK	PLAYER_ID	String	Unique player identifier
	REGISTRATION_DATE	Date	Date the player registered
	EMAIL	String	Mandatory when using Optimail or if required by external ESP
	MOBILE_NUMBER	String	Mandatory if required by external service provider
	DATE_OF_BIRTH	Date	Player's date of birth
	IS_OPTIN	String	Determines whether it is acceptable to send promotional messages to the player (values: 'Yes' or 'No')
	ALLOW_EMAIL	String	Determines whether promotional email messages can be sent to the player's email address (values: 'Yes' or 'No')
	ALLOW_SMS	String	Determineswhether promotional SMS messages can be sent to the player's mobile number (values: 'Yes' or 'No')
	ALLOW_PUSH	String	Determines whether promotional push messages can be sent to the player's mobile number (values: 'Yes' or 'No')
	IS_EMAIL_VERIFIED	String	Indicates whether the email address is verified (values: 'Yes' or 'No')
	IS_SMS_VERIFIED	String	Indicates whether the mobile number is verified (values: 'Yes' or 'No')
	IS_BLOCKED	String	Indicates whether the player is blocked (values: 'Yes' or 'No')
	IS_TEST	String	Indicates whether the player is a test user (values: 'Yes' or 'No')
	GENDER	String	Player’s gender (values: 'Male', 'Female', 'Unknown')
	COUNTRY	String	Player’s country
	CITY	String	Player’s city
	ADDRESS	String	Player’s address
	FIRST_NAME	String	Player’s first name
	LAST_NAME	String	Player’s last name
	LANGUAGE	String	Player’s language
	BALANCE	Decimal	The monetary value of the player’s current balance
	CASINO_NAME	String	When multiple casino platforms exist
	ALIAS	String	User name
	CURRENCY	String	Player’s currency
	REFERRAL_TYPE	String	Method of player referral (e.g., SEO, Affiliate, Advertising, etc.)
	AFFILIATE_ID	String	Affiliate identifier or name
	REGISTERED_PLATFORM	String	The platform the player registered with (e.g., Web, Android App, iOS App, etc.)
	LAST_LOGIN_DATE	Date	The date of the player’s last login
	ALLOW_WHATSAPP	String	Determines whether promotional WhatsApp messages can be sent to the player (values: 'Yes' or 'No')
	IS_OPTIN_EMAIL_TIMESTAMP	Timestamp	Determines from when it is acceptable to send promotional Email messages to the specified email address in case of approval

Notes:

Please include any additional information that you find helpful
IsOptinEmailTimeStamp* - In case Optimove will manage the “unsubscribe” for the Opti-Mail integration please include additional column to describe when the customer opt in \ out, in order to allow perfect sync with the daily batch.

Transactions

Key	Field	Data Type	Description
PK	Transaction_ID**	string	Unique transaction identifier
	Player_ID**	string	Unique player identifier
	Transaction_Date**	date	Transaction date
	Transaction_Timestamp	timestamp	Transaction Timestamp
	Transaction_Type**	string	Transaction type. Should hold values 'DEPOSIT', 'WITHDRAWAL', or 'BONUS'
	Transaction_Amount**	decimal	Monetary value of the transaction
	Platform	string	Platform from which the transaction made (e.g. Web, Mobile, Download)
	Status**	string	Transaction status (e.g. Approved, Rejected, Pending). Must have status ‘Approved’ for approved transactions.
	Last_Updated	Date	Date when the record was last modified or added (Mandatory in case of DB to DB connection)

Note:

Any other gaming dimension available can be added if needed.

Draws

Key	Field	Data Type	Description
	PLAYER_ID	String	Unique player identifier
	PURCHASE_ID	String	Purchase identifier
	LOTTERY_ID	String	Unique lottery identifier
PK	DRAW_ID	String	Unique draw identifier (Primary Key)
PK	LINE_NUMBER	Integer	Line number (default “1” if not utilized)
PK	DRAW_DATE	Date	Date of the draw
	TICKET_COST	Decimal	Cost of the ticket
	WIN_AMOUNT	Decimal	Amount won in the draw
	PLATFORM	String	Platform used for the lottery
	BOOST	String	Boost chosen by the player
	JACKPOT	String	Jackpot for the draw
	STATUS	String	Draw status (e.g., Won, Loss, Open)
	IS_SECONDCHANCE	Boolean	Whether the ticket is part of a second chance draw
	PURCHASE_DATE	Date	Purchase date

Instant Play Games

Key	Field	Data Type	Description
	PURCHASE_ID	String	Unique purchase identifier
	PLAYER_ID	String	Unique player identifier
PK	INSTANTPLAY_ID	String	Unique instant play game identifier (Primary Key)
	GAME_ID	String	Game identifier
PK	ENTRY_NUMBER	Integer	Entry number for multiple entries per INSTANTPLAY_ID
PK	GAME_PLAYED_TIMESTAMP	Timestamp	Timestamp of game play
	GAME_TYPE	String	Type of game (Instant or Scratch)
	GAME_COST	Decimal	Cost of the game
	WIN_AMOUNT	Decimal	Amount won in the game
	GAME_STATUS	String	Status of the game (values: "WIN", "LOSS")
	PLATFORM	String	Platform used for the instant play game
	PURCHASE_DATE	Date	Purchase date_

Lottery Catalog

Key	Key Field	Data Type	Description
PK	LOTTERY_ID	String	Unique lottery identifier
	LOTTERY_NAME	String	Name of the lottery
	LOTTERY_CATEGORY	String	Category of the lottery
	UPDATE_AT	Timestamp	Last update timestamp of the lottery catalog

Instant Play Game Catalog

Key	Key Field	Data Type	Description
PK	GAME_ID	String	Unique game identifier (Primary Key)
	GAME_NAME	String	Name of the instant play game
	UPDATE_AT	Timestamp	Last update timestamp of the game catalog

Database Schema

Batch-Data Process

Once Optimove successfully receives all historical data, a batch-data update process is required to ensure that Optimove’s data warehouse is updated every day, with the latest available data.

This data extraction process is similar to the method used for the initial historical data delivery, but will include only incremental (new/changed) data.

For additional details about the batch-data process, see Batch data process.

Ensuring Data Integrity

The following points will help ensure the integrity of the data you provide to Optimove:

The primary keys (unique identifiers) that were initially defined for each table must remain present and consistent in files delivered daily, without Null values. Please avoid duplications on the primary key level.
When providing historical data, ensure that data for the entire date range is provided. If this is not possible, inform Optimove in advance of any dates missing from the date ranges provided in each table.
It is important that all identifiers in all transactional tables exist in the relevant DIM table. For example, all game IDs in the Games table must appear in the Game Types and Categories table, or all product IDs in the Orders table must appear in the Catalog table.
It is important that all customer IDs that appear in the transactional and fact tables also appear in the Customers table. Only customer IDs appearing in the Customers table will be included in Optimove.
Every change made to the data schema must be brought to Optimove’s attention in advance. Uncoordinated schema changes will cause the batch-data process to fail.
Do not include any retroactive data (older than initially agreed upon) in the batch-data tables. If you feel it is necessary to do so, coordinate with Optimove in advance.
Do not use any default dates such as ‘1900-01-01,’ instead please use Null (blank in files) as default.

For further explanation see Data QA Processes.

Questions?

We are here to help! Please contact us with any questions, and we will be happy to assist you. Email us at [email protected] or call us (seven days a week from 5:00 am to 8:00 pm GMT) at +972-3-672-4546.