One of my recent work projects was publishing a widget which others can add to their websites by copying some code and using it on their site. Similar to how YouTube makes it easy to embed their videos elsewhere.
Of course we want to know how and where this widget is being used so we can decide if it’s worth doing more widgets in the future, so we needed to include tracking. What we want to know is where the widget is being used, and how many times it is being displayed on those URLs.
The tracking table is very simple:
Technically, the id column is not needed since the primary key is a combination of the referral_url and the date_viewed field, but it can be handy in some situations.
When the widget is displayed, the following query is run:
If this is the first visit from http://example.com for the day, a new record will be added to the table. If it’s not the first record for the day, the counter will be incremented by one. Using the date type for the date_viewed field means only the date is stored, not the time. If a datetime field were used, this method would not work the same way; we’d have separate entries in the table every time someone viewed the widget.
With this table structure it’s very easy to get stats. Say you want to know all of the pages where the widget is displayed and how many times it has been displayed on each:
Want to know the 10 sites that are displaying your widget the most?