Database Model for Simple ad Performance Tracking
I have a mobile app that i will be running exclusive advertisements on for a single brand. I wont have an ad server, i will just upload the ad image to my storage and serve up all ad information from my database to the front end (iOS, Android).
I am wondering what the best way to model my database would be to track the ad performance. The front end will register impressions and clicks and send this back to my database for storage and reporting along with some meta data.
I was thinking to model it like this:
Each campaign has one to many ads, each ad has none to many impressions, each ad has none to many clicks.
1. Campaign Table
- id
- name (name of campaign)
- description (description of campaign)
- company (who the campaign belongs to, incase we stop with this advertiser and use another)
- start_date (date campaign starts)
- end_date (date campaign ends)
2. Ads Table
- id
- campaign_id (id of campaign)
- description (ad description)
- name (ad name)
- image_url (url to ad image to serve to FE)
- destination (where to send user on click)
- type (type of ad, banner, native, etc)
- country (would i have duplicate ads for every country i am targeting, ads might be different offers between canada and USA so front end would request ads from the ad table where country code = CA or USA?)
3. Impressions table
- id
- campaign_id (id of campaign)
- ad_id (id of the ad impression was registered for, FK)
- device (device name to register unique views)
- latitude (where impression was served)
- longitude (where impression was served)
- timestamp
- user_sex (user demographic)
- user_age (user demographic)
4. Click table (same as impression be registers a click instead)
- id
- campaign_id
- ad_id
- device
- latitude
- longitude
- timestamp
- user_sex (user demographic)
- user_age (user demographic)
From this information i can create performance reports like click through rate, total impressions, unique impressions, etc.
Is this a decent start?
I am wondering what the best way to model my database would be to track the ad performance. The front end will register impressions and clicks and send this back to my database for storage and reporting along with some meta data.
I was thinking to model it like this:
Each campaign has one to many ads, each ad has none to many impressions, each ad has none to many clicks.
1. Campaign Table
- id
- name (name of campaign)
- description (description of campaign)
- company (who the campaign belongs to, incase we stop with this advertiser and use another)
- start_date (date campaign starts)
- end_date (date campaign ends)
2. Ads Table
- id
- campaign_id (id of campaign)
- description (ad description)
- name (ad name)
- image_url (url to ad image to serve to FE)
- destination (where to send user on click)
- type (type of ad, banner, native, etc)
- country (would i have duplicate ads for every country i am targeting, ads might be different offers between canada and USA so front end would request ads from the ad table where country code = CA or USA?)
3. Impressions table
- id
- campaign_id (id of campaign)
- ad_id (id of the ad impression was registered for, FK)
- device (device name to register unique views)
- latitude (where impression was served)
- longitude (where impression was served)
- timestamp
- user_sex (user demographic)
- user_age (user demographic)
4. Click table (same as impression be registers a click instead)
- id
- campaign_id
- ad_id
- device
- latitude
- longitude
- timestamp
- user_sex (user demographic)
- user_age (user demographic)
From this information i can create performance reports like click through rate, total impressions, unique impressions, etc.
Is this a decent start?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, the first reason, as you mentioned is schema flexibility. If you need data from both tables in data analysis, your select statements must be cheaper. In general prepared insert statement with bind values also must work faster.
But if you never mix data from impressions and clicks in one query, first option is better. Also it's better if you expect many records with the same values for person table. You could make multiple references to the same "person" from impressions and clicks
But if you never mix data from impressions and clicks in one query, first option is better. Also it's better if you expect many records with the same values for person table. You could make multiple references to the same "person" from impressions and clicks
ASKER
What are the pros and cons of combining the impressions/clicks table into an actions table? Is it just cleaner/less tables? I guess i can add an actions table and expand my actions beyond those two without having to add a table for every action.
Thanks!