CoolaData enables you to upload data contained in file attachments (CSV, JSON or Excel files) received by a Gmail account into your CoolaData project. This type of integration is typically used to enrich your CoolaData project with your own data or to upload reports you receive regularly for querying alongside your behavioral data.
Each hour, CoolaData searches the subject of incoming emails (of the Gmail account that you specify) in order to search for a specific string in the email’s subject and then uploads their content into your CoolaData project. CoolaData learns the schema of these file attachments by understanding the values of each property.
Note: Only Gmail is supported. You can use any Gmail account: business/private/freemium.
- Follow the instructions in Integrating with Predefined Data Sources and then select Email Report .
The following displays:
- Fill in the following:
- Integration Name: The name of this integration.
- Date Format: Fill in this field if the file attachments to be uploaded contain date fields. Specify the format of the date fields: as they are in the file. CoolaData can then automatically convert these date fields to timestamp format. Only one format can be defined. For example, the format of the date 18/04/2016 16:21:16 should be entered here as: dd/MM/yyyy HH:mm:ss.
- Report File Delimiter: Select Comma or Tab to specify the delimiter between each data row and column in the email attachment.
- Email Subject: Enter a string that specifies which emails are to be integrated by CoolaData. This can be any string, including a complete or partial name. All incoming emails that contain this string in their subject are checked for an attached CSV, JSON or Excel file. The data the attached file of matching emails is then integrated into CoolaData. We recommend making this string as unique as possible, in order to avoid CoolaData attempting to integrate irrelevant emails. If needed you can define more than one integration for the same Gmail address/inbox: each containing a different subject: and each being stored in a different table.
- Table Name: The name of the table that is created in CoolaData to contain the data that is uploaded. This is the table name to be used in the queries that you will perform on the uploaded data. Like all table names, it is case sensitive and cannot include spaces or special characters.
- In the Insert Strategy field, select either:
Append: New data is added to the table each time data is uploaded.
Replace: The table is overwritten each time the data is uploaded.
Note: If the Insert Strategy field is set to Append, then the schema of each file attachment must be identical. Otherwise, the integration fails.
- Add Date Column to Table: Select this option to specify that a date column is added to each table that is created by CoolaData when a new email attachment is uploaded. This date specifies the timestamp when the integration took place. This option is particularly useful when the uploaded reports do not include dates. This option is also helpful when you use the Insert Strategy: Append (described above), because it indicates when each row was added to the table.
- [Optional] You can use the Google Project ID and Google Dataset fields to define that CoolaData uploads the email attachments into your own Google project instead of into your CoolaData project. Contact your CoolaData customer success representative for more information.
- Click the Connect button. The following displays:
- Enter the Gmail account credentials of the email inbox to be monitored by CoolaData. If you are already logged into your Gmail account in a different window on the same computer, then there is no need to enter your Gmail account credentials. Click the Allow button.
Each hour CoolaData will search for an email with the specified project and if found, it will then integrate the relevant email attachments into your project. The first integration process should take place within a few minutes.
After CoolaData has integrated the files in your project, the email is labeled in Gmail as Uploaded.
The Status column of the Integrations list changes to show Data Fetched.
If the integration process fails for any reason, then the email is labeled in Gmail as Failed. An email is sent to the specified recipient(s) (described above) alerting them regarding the integration failure.
- Data can now be queried using the following syntax.
SELECT * from tablename limit 100
Define the Date Format to recognize and convert timestamps columns so they can be used as such in Cooladata. All JAVA standard time formats are supported. The following characters can be used – 1-3 chars represents the abbreviated form, if one exists; 4+ chars represents the full form. The date syntax is case sensitive, so (for example) MM represents month and mm represents seconds.
|Letter||Date or Time Component||Presentation||Examples|
|M||Month in year||Month||July; Jul; 07|
|w||Week in year||Number||27|
|W||Week in month||Number||2|
|D||Day in year||Number||189|
|d||Day in month||Number||10|
|F||Day of week in month||Number||2|
|E||Day in week||Text||Tuesday; Tue|
|H||Hour in day (0-23)||Number||0|
|k||Hour in day (1-24)||Number||24|
|K||Hour in am/pm (0-11)||Number||0|
|h||Hour in am/pm (1-12)||Number||12|
|m||Minute in hour||Number||30|
|s||Second in minute||Number||55|
|z||Time zone||General time zone||Pacific Standard Time; PST; GMT-08:00|
|Z||Time zone||RFC 822 time zone||-800|
|18/04/2017 19:36:55||dd/MM/yyyy HH:mm:ss|
|18/04/2017 07:36:55||dd/MM/yyyy hh:mm aa|
|Tue 04/18/2017||EEE MM/dd/yyyy|