CREATE TABLE senzai.
aggregate_users_subscribers AS
SELECT
u.user_id,
u.emp_name,
u.created_at AS user_created_at,
u.active_subscription,
u.sessions_total,
[Link] AS subscription_status,
s.subscription_created,
s.end_date,
-- Calculate subscription duration in months
DATE_DIFF('month', s.subscription_created, CURRENT_DATE) AS
subscription_duration_months,
-- Flag for users who have been active this month
CASE
WHEN u.sessions_last_30_days > 0 THEN 'Active this month'
ELSE 'Inactive this month'
END AS activity_this_month
FROM [Link] u
LEFT JOIN [Link] s ON u.user_id = s.user_id;
This table aggregates key information from both the users and subscribers
tables, combining user activity and subscription data in a unified view. It
includes the following important fields:
• user_id: The unique identifier for each user, linking the two
tables.
• emp_name: The name of the user or employee.
• user_created_at: The date the user was created in the system,
useful for tracking account age.
• active_subscription: A boolean or flag indicating whether the
user currently has an active subscription.
• sessions_total: The total number of sessions this user has
participated in, providing a measure of user engagement.
• subscription_status: The current status of the subscription
(e.g., Active, Ended), important for identifying whether the user is
subscribed.
• subscription_created: The date the subscription was created,
useful for calculating subscription duration and understanding user
lifecycle.
• end_date: The date the subscription ended, important for
churn analysis.
• subscription_duration_months: A calculated field that shows
the duration of the subscription in months. This is useful for understanding
how long users typically stay subscribed.
• activity_this_month: A flag indicating whether the user has
been active in the last 30 days. This can be used for tracking recent user
engagement and can help in identifying users who need to be re-engaged.
Potential Uses of the Table in the Future
1. Churn Analysis:
• The combination of subscription_status, end_date, and
subscription_duration_months can help identify users who have churned
(i.e., ended their subscriptions) and how long they were subscribed before
leaving.
• You can also track whether users with long-term subscriptions
are more likely to churn than those with short-term subscriptions.
2. User Engagement Tracking:
• The sessions_total and activity_this_month fields can be used
to monitor user engagement. Users who have been inactive for long
periods but still have subscriptions might need targeted marketing or
retention efforts.
• It also helps identify active users who could be upsold
additional services or subscriptions.
3. Customer Lifecycle Management:
• By combining user_created_at, subscription_created, and
subscription_duration_months, you can monitor how long users stay
engaged with your service from account creation to the end of their
subscription. This data will help guide customer retention strategies.
4. Subscription Retention and Loyalty Programs:
• The data on how long users have been subscribed can be
used to reward loyal customers with discounts, promotions, or loyalty
programs to increase retention.
• Users with long subscription durations could be targeted with
loyalty campaigns, while those with short durations might need incentives
to extend their subscriptions.
5. Revenue Forecasting:
• Though not directly visible here, by adding revenue fields
(e.g., subscription fee, total spend), this table can be extended to forecast
potential revenue based on active subscriptions and engagement metrics.
6. Segmentation for Marketing Campaigns:
• With the activity_this_month flag and active_subscription data,
you can easily segment users into groups for targeted marketing. For
example, you can run re-engagement campaigns for users who are still
subscribed but haven’t been active recently.
7. Subscription Growth Analysis:
• Over time, you can track how the number of active
subscriptions grows or declines by periodically querying and comparing
the subscription_duration_months and active_subscription flags.
Conclusion
This table provides a solid foundation for monitoring the relationship
between users and their subscriptions, user engagement, and subscription
durations. It is highly valuable for business insights around customer
retention, user engagement, churn prevention, and marketing
segmentation, and can be extended in the future to include additional
revenue or performance-related metrics.
