How to import existing data

Integrating SimpleStats into your existing application doesn't mean leaving behind your historical data. Recognizing the value of your application's past insights, we've developed the Data Import feature. This functionality enables a smooth transition by allowing you to seamlessly import your existing data into SimpleStats, ensuring that you retain access to all historical metrics and insights.

INFO

The Data Import feature is not available on the free subscription plan.

Supported Formats

The Data Import supports the following file formats: xls, xlsx and csv.

Import Capabilities

The import feature is designed to manage substantial volumes of data efficiently. Therefore, unless you approach the limits of your subscription, we encourage you to import the entirety of your data. This capability ensures that you can leverage the full extent of your historical insights without reservations.

Importing User Data

Though it is not mandatory, it's highly recommended to first import the user data and only then the corresponding payment data. To start a new import, you have to provide a supported file format containing the user data to import.

Allowed User Fields

The following fields are allowed:

Core fields:

Key	Type	Description
id	mixed `required`	The user id.
time	string `required`	The time when the user has registerd. Format: `Y-m-d H:i:s`, before or equal `now`

Optional fields

Key	Type	Description
ip	string `nullable`	The `ip` of the user. (does not get stored)
user_agent	string `nullable`	The `user_agent` of the user. (does not get stored)
track_referer	string `nullable`	The tracking `referer` of the user.
track_source	string `nullable`	The tracking `source` of the user.
track_medium	string `nullable`	The tracking `medium` of the user.
track_campaign	string `nullable`	The tracking `campaign` of the user.
track_term	string `nullable`	The tracking `term` of the user.
track_content	string `nullable`	The tracking `content` of the user.
location_country	string `nullable`	The location `country` of the user. (If the `ip` is set, we take the country that refers to it.)
location_region	string `nullable`	The location `region` of the user. (If the `ip` is set, we take the region that refers to it.)
location_city	string `nullable`	The location `city` of the user. (If the `ip` is set, we take the city that refers to it.)
device_type	string `nullable`	The device `type` of the user. (If the `user_agent` is set, we take the device type that refers to it.)
device_platform	string `nullable`	The device `platform` (OS) of the user. (If the `user_agent` is set, we take the device platform that refers to it.)
device_browser	string `nullable`	The device `browser` of the user. (If the `user_agent` is set, we take the device browser that refers to it.)
page_entry	string `nullable`	The page `entry` point of the user.

Here is an example of how such an SQL query may look like:

MySQLPostgreSQL

sql

SELECT
    id,
    DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS `time`
FROM
    users;

sql

SELECT
    id,
    to_char(created_at, 'YYYY-MM-DD HH24:MI:SS') AS "time"
FROM
    users;

With Condition

Sometimes you may not want to count all users as "valid" registrations. So you may only export the users which pass a certain condition, such as the user has verified their email address.

The query could look like this:

MySQLPostgreSQL

sql

SELECT
    id,
    DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS `time`
FROM
    users
WHERE
    email_verified_at IS NOT NULL;

sql

SELECT
    id,
    to_char(created_at, 'YYYY-MM-DD HH24:MI:SS') AS "time"
FROM
    users
WHERE
    email_verified_at IS NOT NULL;

Now export the output of the query, save the file and then you can upload the file inside the app to start a new import!

INFO

Please note, the provided queries serve merely as examples. Your actual table structures and data might vary significantly. The implementation is completely up to you!

WARNING

The first row of your import file must be a heading row (a row in which each cell contains the key of the allowed fields)!

In many cases, historical login data for users isn't readily available. Without logins, activity metrics like DAU/WAU/MAU stay empty for the imported period. To address this, your user import file may optionally include a last_known_activity column. This field could correspond to the updated_at attribute, a last_login field, or any similar date in your users table that best approximates the user's most recent activity.

By supplying this information, we generate simulated login entries to mimic natural login patterns, based on the assumption that each user logged in at least once a week from their registration date to their last known activity. This provides a richer, more accurate representation of your historical user engagement over time.

Key	Type	Description
last_known_activity	string `nullable`	The time of the user's most recent activity. Format: `Y-m-d H:i:s`, must be equal or after `time`

How it works:

The first simulated login is placed at the registration time, followed by logins at least once a week with natural variation, and a final login exactly at last_known_activity.
Values in the future are clamped to the current time.
Users that already have login data are skipped, so re-imports won't create duplicates.
Rolling back the import also removes the generated logins.

Here is an example of how the SQL query from above may look like with SNLB:

MySQLPostgreSQL

sql

SELECT
    id,
    DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS `time`,
    DATE_FORMAT(updated_at, '%Y-%m-%d %H:%i:%s') AS `last_known_activity`
FROM
    users;

sql

SELECT
    id,
    to_char(created_at, 'YYYY-MM-DD HH24:MI:SS') AS "time",
    to_char(updated_at, 'YYYY-MM-DD HH24:MI:SS') AS "last_known_activity"
FROM
    users;

Importing Payment Data

After you've imported your user data, you may start another import for your payment data. Again you have to provide a supported file format containing the payment data to import.

Allowed Payment Fields

The following fields are allowed:

Key	Type	Description
id	mixed `required`	The payment id.
stats_user_id	integer `required`	The user id associated with the payment.
time	string `required`	The time when the payment happened. Format: `Y-m-d H:i:s`, before or equal `now`
gross	numeric `required`	The gross amount of the payment in cents. (e.g.: $1 = 100 Cent)
net	numeric `required`	The net amount of the payment in cents. (e.g.: $1 = 100 Cent).
currency	string `required`	The ISO-4217 currency code of the payment. (e.g.: EUR, USD, GBP ...)
subscription_interval	string `nullable`	The billing interval for subscription payments: `month` or `year`. Omit or `null` for one-time payments.
subscription_plan	string `nullable`	The subscription plan the payment belongs to (free-form, e.g. `Starter`, `Business`). Omit or `null` for one-time payments.

Assuming the table is called transactions, here is an example of how such an SQL query may look like:

MySQLPostgreSQL

sql

SELECT
    id,
    user_id AS stats_user_id,
    total AS gross,
    total - tax AS net,
    'USD' AS currency,
    DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS `time`
FROM
    transactions;

sql

SELECT
    id,
    user_id AS stats_user_id,
    total AS gross,
    total - tax AS net,
    'USD' AS currency,
    to_char(created_at, 'YYYY-MM-DD HH24:MI:SS') AS "time"
FROM
    transactions;

With Condition

Sometimes you may not want to count all payments as "valid". So you may only export the payments which pass a certain condition, such as having a "completed" status.

The query could look like this:

MySQLPostgreSQL

sql

SELECT
    id,
    user_id AS stats_user_id,
    total AS gross,
    total - tax AS net,
    'USD' AS currency,
    DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS `time`
FROM
    transactions
WHERE
    status = 'completed';

sql

SELECT
    id,
    user_id AS stats_user_id,
    total AS gross,
    total - tax AS net,
    'USD' AS currency,
    to_char(created_at, 'YYYY-MM-DD HH24:MI:SS') AS "time"
FROM
    transactions
WHERE
    status = 'completed';

Now export the output of the query, save the file and then you can upload the file inside the app to start a new import!

INFO

Please note, the provided queries serve merely as examples. Your actual table structures and data might vary significantly. The implementation is completely up to you!

WARNING

The first row of your import file must be a heading row (a row in which each cell contains the key of the allowed fields)!

DANGER

Remember that Gross and Net must be stated in cents!

How to import existing data ​

Supported Formats ​

Import Capabilities ​

Importing User Data ​

Allowed User Fields ​

With Condition ​

Simulated Natural Login Behavior (SNLB) ​

Importing Payment Data ​

Allowed Payment Fields ​

With Condition ​

How to import existing data

Supported Formats

Import Capabilities

Importing User Data

Allowed User Fields

With Condition

Simulated Natural Login Behavior (SNLB)

Importing Payment Data

Allowed Payment Fields

With Condition