This tutorial is part of the Building Your Startup With PHP series on Envato Tuts+. In this series, I'm guiding you through launching a startup from concept to reality using my Meeting Planner app as a real-life example. Every step along the way, I'll release the Meeting Planner code as open-source examples you can learn from. I'll also address startup-related business issues as they arise.
As Meeting Planner nears alpha launch, we need a way to address support requests with users and monitor activity. In other words, we need to build an administrative dashboard with user management and reporting. In discussions with an advisor, we discussed that as I approach potential investors, I'll need to have excellent data detailing user behavior and growth of the service.
In today's episode, we'll build the foundation for our administrative dashboard and take on and create some of the initial live and historical reporting. For example, we'll know how many people have registered at any time, how many meetings have been scheduled, and what percentage of invited participants like the service enough to go on to organize their own meeting. It's actually been pretty fun to build this stuff and see the data, even if we're pre-launch.
If you haven't tried out Meeting Planner yet (and want to show up in the aggregate data yourself), go ahead and schedule your first meeting. I do participate in the comment threads below, so tell me what you think! You can also reach me on Twitter @reifman. I'm especially interested if you want to suggest new features or topics for future tutorials.
As a reminder, all of the code for Meeting Planner is written in the Yii2 Framework for PHP. If you'd like to learn more about Yii2, check out our parallel series Programming With Yii2.
Building the Foundation of the Dashboard
The Yii Advanced Template
Yii2 offers front and back office websites within its advanced application setup. You can read more about it in my Envato Tuts+ tutorial, How to Program With Yii2: Using the Advanced Application Template. Essentially, the advanced template's front-end site provides people-facing functionality, and the back-end site is made for a service's dashboard and administration site.
In order to activate it, I just needed to set up Apache sites in my MAMP localhost environment and on my production Ubuntu server. For instance, here's the Apache configuration on the production server to load the /backend/web site:
<IfModule mod_ssl.c>
<VirtualHost *:443>
ServerName your-administration-site.com
DocumentRoot "/var/www/mp/backend/web"
<Directory "/var/www/mp/backend/web">
# use mod_rewrite for pretty URL support
RewriteEngine on
# If a directory or a file exists, use the request directly
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
# Otherwise forward the request to index.php
RewriteRule . index.php
</Directory>
SSLCertificateFile /etc/letsencrypt/live/meetingplanner.io/cert.pem
SSLCertificateKeyFile /etc/letsencrypt/live/meetingplanner.io/privkey.pem
Include /etc/letsencrypt/options-ssl-apache.conf
SSLCertificateChainFile /etc/letsencrypt/live/meetingplanner.io/chain.pem
</VirtualHost>
</IfModule>
Configuring Our Back-End Site
Next, I built a new layout for the back-end site based on the front-end site, but with different menu options. I decided the home page would redirect to a page of real-time statistics. And the menus would offer links to real-time data, data from yesterday at midnight and historical data. I'll explain a bit more of this as we proceed.
Here's the \backend\views\layouts\main.php with the menu:
<body>
<?php $this->beginBody() ?>
<div class="wrap">
<?php
NavBar::begin([
'brandLabel' => Yii::t('backend','Meeting Planner'),
'brandUrl' => 'https://meetingplanner.io',
'options' => [
'class' => 'navbar-inverse navbar-fixed-top',
],
]);
$menuItems[] = [
'label' => 'Real Time',
'items' => [
['label' => Yii::t('frontend','Usage'), 'url' => ['/data/current']],
]
];
$menuItems[] = [
'label' => 'Yesterday',
'items' => [
['label' => Yii::t('frontend','User Data'), 'url' => ['/user-data']],
]
];
$menuItems[]=[
'label' => 'Historical',
'items' => [
['label' => Yii::t('frontend','Statistics'), 'url' => ['/historical-data']],
],
];
if (Yii::$app->user->isGuest) {
$menuItems[] = ['label' => 'Login', 'url' => ['/site/login']];
} else {
$menuItems[] = [
'label' => 'Account',
'items' => [
['label' => 'Logout (' . Yii::$app->user->identity->username . ')',
'url' => ['/site/logout'],
'linkOptions' => ['data-method' => 'post'],
],
],
];
}
echo Nav::widget([
'options' => ['class' => 'navbar-nav navbar-right'],
'items' => $menuItems,
]);
NavBar::end();
?>
<div class="container">
<?= Breadcrumbs::widget([
'links' => isset($this->params['breadcrumbs']) ? $this->params['breadcrumbs'] : [],
]) ?>
<?= $content ?>
</div>
</div>
Building the Initial Reporting
For my initial statistics reporting, I focused on simple real-time data and detailed historical data. For example, real-time data would tell you the number of users and meetings built on the system to date and their status.
The historical data would tell you the number of users and meetings completed over time as well as other interesting data—especially growth curves that I and prospective investors may care about.
Real-Time Data
The real-time data page needs to show a live snapshot of what's happening on the site. Initially, I wanted to know:
- How many meetings are there in the system?
- How many users are there?
- What's their status?
To accomplish this, I created a back-end DataController.php and Data.php model. I also took a step forward and rather than create raw HTML in my view to display this, I created ActiveDataProviders from my queries and fed them to Yii's grid widgets; the result looks better and is simpler to build and maintain.
This code queries the number of meetings in the system grouped by their status:
public static function getRealTimeData() {
$data = new \stdClass();
$data->meetings = new ActiveDataProvider([
'query' => Meeting::find()
->select(['status,COUNT(*) AS dataCount'])
//->where('approved = 1')
->groupBy(['status']),
'pagination' => [
'pageSize' => 20,
],
]);
This code in /backend/views/data/current.php displays it:
<?php
/* @var $this yii\web\View */
use yii\grid\GridView;
use common\models\User;
use frontend\models\Meeting;
$this->title = Yii::t('backend','Meeting Planner');
?>
<div class="site-index">
<div class="body-content">
<h1>Real Time Data</h1>
<h3>Meetings</h3>
<?= GridView::widget([
'dataProvider' => $data->meetings,
'columns' => [
[
'label'=>'Status',
'attribute' => 'status',
'format' => 'raw',
'value' => function ($model) {
return '<div>'.Meeting::lookupStatus($model->status).'</div>';
},
],
'dataCount',
],
]); ?>
It looks like this (the data are small since the site hasn't launched yet!):
Then, I created a few more real-time queries, and the rest of the page looks like:
Regarding the People Active and Via invite columns above, if you invite a person to a meeting, we count them as a User via Invite until they create a password or link their social account. Until then, their only access to Meeting Planner is through your email invitation link and its authentication id.
Obviously, I'll expand the real-time reporting options as the project evolves.
Reporting Historical Data
Generating historical reporting for systemwide activities proved a bit more involved. I decided to create some dependent data-gathering layers.
The bottom layer is a UserData table that summarizes the state of a person's historical account activity up to a specific day at midnight. Essentially, we'll do this nightly.
The top layer is the HistoricalData table that builds its calculations using the UserData table from the night before.
I also needed to write code which built up the two tables from scratch since our service had been a bit active for several months.
I'll guide you through how I did this. The result turned out quite well.
Creating Table Migrations
Here is the table migration for UserData—it contains the data I wanted to calculate nightly to assist the historical calculations:
public function up()
{
$tableOptions = null;
if ($this->db->driverName === 'mysql') {
$tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
}
$this->createTable('{{%user_data}}', [
'id' => Schema::TYPE_PK,
'user_id' => Schema::TYPE_BIGINT.' NOT NULL',
'is_social' => Schema::TYPE_SMALLINT.' NOT NULL',
'invite_then_own' => Schema::TYPE_SMALLINT.' NOT NULL',
'count_meetings' => Schema::TYPE_INTEGER.' NOT NULL',
'count_meetings_last30' => Schema::TYPE_INTEGER.' NOT NULL',
'count_meeting_participant' => Schema::TYPE_INTEGER.' NOT NULL',
'count_meeting_participant_last30' => Schema::TYPE_INTEGER.' NOT NULL',
'count_places' => Schema::TYPE_INTEGER.' NOT NULL',
'count_friends' => Schema::TYPE_INTEGER.' NOT NULL',
'created_at' => Schema::TYPE_INTEGER . ' NOT NULL',
'updated_at' => Schema::TYPE_INTEGER . ' NOT NULL',
], $tableOptions);
$this->addForeignKey('fk_user_data_user_id', '{{%user_data}}', 'user_id', '{{%user}}', 'id', 'CASCADE', 'CASCADE');
}
For example, count_meeting_participant_last30 is how many meetings this person was invited to in the last 30 days.
Here is the table migration for HistoricalData—almost all of the columns in this table need to be calculated from different layers of data:
public function up()
{
$tableOptions = null;
if ($this->db->driverName === 'mysql') {
$tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
}
$this->createTable('{{%historical_data}}', [
'id' => Schema::TYPE_PK,
'date'=> Schema::TYPE_INTEGER.' NOT NULL',
'percent_own_meeting' => Schema::TYPE_FLOAT.' NOT NULL',
'percent_own_meeting_last30' => Schema::TYPE_FLOAT.' NOT NULL',
// % of users invited by others who own a meeting
'percent_invited_own_meeting' => Schema::TYPE_FLOAT.' NOT NULL',
'percent_participant' => Schema::TYPE_FLOAT.' NOT NULL',
'percent_participant_last30' => Schema::TYPE_FLOAT.' NOT NULL',
'count_users' => Schema::TYPE_INTEGER.' NOT NULL',
'count_meetings_completed' => Schema::TYPE_INTEGER.' NOT NULL',
'count_meetings_planning' => Schema::TYPE_INTEGER.' NOT NULL',
'count_places' => Schema::TYPE_INTEGER.' NOT NULL',
'average_meetings' => Schema::TYPE_FLOAT.' NOT NULL',
'average_friends' => Schema::TYPE_FLOAT.' NOT NULL',
'average_places' => Schema::TYPE_FLOAT.' NOT NULL',
'source_google' => Schema::TYPE_INTEGER.' NOT NULL',
'source_facebook' => Schema::TYPE_INTEGER.' NOT NULL',
'source_linkedin' => Schema::TYPE_INTEGER.' NOT NULL',
], $tableOptions);
In discussion with my advisor, we realized that prospective investors will want to know how people are responding to the site. I created a measurement for a metric called percent_invited_own_meeting, short for the percentage of users invited to their first meeting who liked the service enough to use it to schedule their own meeting in the future. I'll review more about the calculations a bit further below.
The migrations all reside in /console/migrations. Here's what it looks like when you run the database migrations.
$ ./yii migrate/up
Yii Migration Tool (based on Yii v2.0.8)
Total 2 new migrations to be applied:
m160609_045838_create_user_data_table
m160609_051532_create_historical_data_table
Apply the above migrations? (yes|no) [no]:yes
*** applying m160609_045838_create_user_data_table
> create table {{%user_data}} ... done (time: 0.003s)
> add foreign key fk_user_data_user_id: {{%user_data}} (user_id) references {{%user}} (id) ... done (time: 0.004s)
*** applied m160609_045838_create_user_data_table (time: 0.013s)
*** applying m160609_051532_create_historical_data_table
> create table {{%historical_data}} ... done (time: 0.003s)
*** applied m160609_051532_create_historical_data_table (time: 0.005s)
2 migrations were applied.
Migrated up successfully.
Gathering the Reporting Data
Each night after midnight, a background task will calculate the previous night's statistics. Here's the background method:
public function actionOvernight() {
$since = mktime(0, 0, 0);
$after = mktime(0, 0, 0, 2, 15, 2016);
UserData::calculate(false,$after);
HistoricalData::calculate(false,$after);
}
I set up a cron job to run actionOvernight at 1:15 am daily. Note: When you're focused intently programming on a startup day and night, a cron job is about all the actionOvernight you'll get.
To build up the history of the past, I created a one-time recalc() function. This flushes the tables and builds up each table as if it was happening a day at a time.
public static function recalc() {
UserData::reset();
HistoricalData::reset();
$after = mktime(0, 0, 0, 2, 15, 2016);
$since = mktime(0, 0, 0, 4, 1, 2016);
while ($since < time()) {
UserData::calculate($since,$after);
HistoricalData::calculate($since,$after);
// increment a day
$since+=24*60*60;
}
}
Note: The after time is a workaround to exclude some of the early users who signed up before they could schedule a meeting. I wanted the historical data to reflect a more accurate depiction of recent activity (currently there are a couple hundred older accounts without any activity). I'll likely remove this at a later date.
Calculating the User Data Table
Here's the code that populates the UserData table nightly:
public static function calculate($since=false,$after = 0) {
if ($since===false) {
$since = mktime(0, 0, 0);
}
$monthago = $since-(60*60*24*30);
$all = User::find()->where('created_at>'.$after)->andWhere('created_at<'.$since)->all();
foreach ($all as $u) {
// create new record for user or update old one
$ud = UserData::find()->where(['user_id'=>$u->id])->one();
if (is_null($ud)) {
$ud = new UserData();
$ud->user_id = $u->id;
$ud->save();
}
$user_id = $u->id;
// count meetings they've organized
$ud->count_meetings = Meeting::find()->where(['owner_id'=>$user_id])->andWhere('created_at<'.$since)->count();
$ud->count_meetings_last30 = Meeting::find()->where(['owner_id'=>$user_id])->andWhere('created_at<'.$since)->andWhere('created_at>='.$monthago)->count();
// count meetings they were invited to
$ud->count_meeting_participant = Participant::find()->where(['participant_id'=>$user_id])->andWhere('created_at<'.$since)->count();
$ud->count_meeting_participant_last30 = Participant::find()->where(['participant_id'=>$user_id])->andWhere('created_at<'.$since)->andWhere('created_at>='.$monthago)->count();
// count places and Friends
$ud->count_places = UserPlace::find()->where(['user_id'=>$user_id])->andWhere('created_at<'.$since)->count();
$ud->count_friends = Friend::find()->where(['user_id'=>$user_id])->andWhere('created_at<'.$since)->count();
// calculate invite than Own - participant first, then organizer
$first_invite = Participant::find()->where(['participant_id'=>$user_id])->andWhere('created_at<'.$since)->orderby('created_at asc')->one();
$first_organized = Meeting::find()->where(['owner_id'=>$user_id])->andWhere('created_at<'.$since)->orderby('created_at asc')->one();
$ud->invite_then_own =0;
if (!is_null($first_invite) && !is_null($first_organized)) {
if ($first_invite->created_at < $first_organized->created_at && $first_organized->created_at < $since) {
// they were invited as a participant earlier than they organized their own meeting
$ud->invite_then_own =1;
}
}
if (Auth::find()->where(['user_id'=>$user_id])->count()>0) {
$ud->is_social =1;
} else {
$ud->is_social =0;
}
$ud->update();
}
}
It's mostly just counting totals for users of meetings, places, friends and in some cases within time ranges of the last 30 days.
Here's the code that detects whether this user chose to schedule a meeting using the service after having been invited:
$ud->invite_then_own =0;
if (!is_null($first_invite) && !is_null($first_organized)) {
if ($first_invite->created_at < $first_organized->created_at && $first_organized->created_at < $since) {
// they were invited as a participant earlier than they organized their own meeting
$ud->invite_then_own =1;
}
}
Calculating the HistoricalData
Here's the code that leverages UserData to populate HistoricalData:
public static function calculate($since = false,$after=0) {
if ($since === false) {
$since = mktime(0, 0, 0);
}
// create new record for date or update existing
$hd = HistoricalData::find()->where(['date'=>$since])->one();
if (is_null($hd)) {
$hd = new HistoricalData();
$hd->date = $since;
$action = 'save';
} else {
$action = 'update';
}
// calculate $count_meetings_completed
$hd->count_meetings_completed = Meeting::find()->where(['status'=>Meeting::STATUS_COMPLETED])->andWhere('created_at<'.$since)->count();;
// calculate $count_meetings_planning
$hd->count_meetings_planning = Meeting::find()->where('status<'.Meeting::STATUS_COMPLETED)->andWhere('created_at<'.$since)->count();;
// calculate $count_places
$hd->count_places = Place::find()->where('created_at>'.$after)->andWhere('created_at<'.$since)->count();
// calculate $source_google
$hd->source_google = Auth::find()->where(['source'=>'google'])->count();
// calculate $source_facebook
$hd->source_facebook = Auth::find()->where(['source'=>'facebook'])->count();
// calculate $source_linkedin
$hd->source_linkedin = Auth::find()->where(['source'=>'linkedin'])->count();
// total users
$total_users = UserData::find()->count();
// calculate $count_users
$hd->count_users = $total_users;
//User::find()->where('status<>'.User::STATUS_DELETED)->andWhere('created_at>'.$after)->count();
$total_friends = Friend::find()->where('created_at>'.$after)->andWhere('created_at<'.$since)->count();
$total_places = Place::find()->where('created_at>'.$after)->andWhere('created_at<'.$since)->count();
if ($total_users >0) {
$hd->average_meetings = ($hd->count_meetings_completed+$hd->count_meetings_planning)/$total_users;
$hd->average_friends = $total_friends/$total_users;
$hd->average_places = $total_places/$total_users;
$hd->percent_own_meeting = UserData::find()->where('count_meetings>0')->count() / $total_users;
$hd->percent_own_meeting_last30 = UserData::find()->where('count_meetings_last30>0')->count() / $total_users;
$hd->percent_participant = UserData::find()->where('count_meeting_participant>0')->count() / $total_users;
$hd->percent_participant_last30 = UserData::find()->where('count_meeting_participant_last30>0')->count() / $total_users;
$query = (new \yii\db\Query())->from('user_data');
$sum = $query->sum('invite_then_own');
$hd->percent_invited_own_meeting=$sum/$total_users;
}
if ($action=='save') {
$hd->save();
} else {
$hd->update();
}
}
It's summarizing totals and calculating percentages and averages.
Here's what the finished product looks like:
Even though we're seeing the analysis of only pre-alpha usage, the data is intriguing, and the potential usefulness of this appears excellent. And, of course, it will be easy to expand the data collection and analysis using the foundational code I shared with you today.
By the way, the percentage of invited users going on to schedule their own meetings is about 9% (but it's a small dataset).
You're probably wondering if we can chart these columns. I hope to address that in a follow-up tutorial, which always requires interaction with the editorial goddesses. Just FYI, not everyone walks away from those conversations. I'll also ask her to allow me to write about administration features such as disabling users, resending passwords, etc.
If you don't hear more from me, know that the Lord of Light has found a use for me.
What's Next?
As mentioned, I'm currently working feverishly to prepare Meeting Planner for alpha release. I am primarily focused on the key improvements and features that will make the alpha release go smoothly.
I'm tracking everything in Asana now, which I'll write about in an upcoming tutorial; it's been incredibly helpful. There are also some interesting new features still on their way. (As a yoga teacher, I think Asana is the worst product name ever. They've basically taken a common term in yoga pronounced āsana or ah-sana and changed the pronunciation to a-sauna — and put that in their introductory videos. It wasn't easy consulting last year talking to client team members about what they put in a sauna and talking to yogis about āsana. But I digress.)
I'm also beginning to focus more on the upcoming investment gathering effort with Meeting Planner. I'm just beginning to experiment with WeFunder based on the implementation of the SEC's new crowdfunding rules. Please consider following our profile. I will also write more about this in a future tutorial.
Again, while you're waiting for more episodes, schedule your first meeting and try out the templates with your friends with Gmail mailboxes. Also, I'd appreciate it if you share your experience below in the comments, and I'm always interested in your suggestions. You can also reach me on Twitter @reifman directly. You can also post them at the Meeting Planner support site.
Watch for upcoming tutorials in the Building Your Startup With PHP series.
Comments