Loading historical data

Dataddo enables you to load historical data and add them to your Data Source.

If you come across a case where you need to add a specific range of your historical data to your data set, you can do it simply by a one-time request in the Dataddo app depending on your destination, whether it is a dashboarding app (eg. Google Data Studio, Power BI, Tableau... etc) or data warehouse (eg. PostgreSQL, MySQL, Google Big Query, S3... etc).

If you have your data source set up and running already, taking snapshots as you like and sending them to your data destination, and you only want to load some historical data and possibly send them to your data destination as well, you can go to Option 2 right away. If not, we will go through setting up a Data Source and Data Flow here in Option 1

OPTION 1 - While creating Data Source and Data Flow


Let's say that you want to connect Google Analytics to your destination and want to fetch data daily. First, you'll need to navigate to Sources.Top Bar Marked-png-1

Then click on New source in the top right corner.
Source Selection Page-png-1
Select Google Analytics.

search Continue with your selection of data until you reach snapshotting section. Since you're sending your data to MySQL, you'll choose the data warehouse option on the right

snapshotting options

In this section, you may choose the additional parameters for snapshotting. The first line lets you choose the date range which you want to have included in the snapshots. The second line sets how frequently the snapshotting occurs. The hour and timezone lines let you choose at which time the snapshots are taken. The last line asks you whether you want to append the data to the data warehouse or replace the former data.

snapshotting

OPTION 2 - Loading historical data


For most sources (such as Google Analytics, Google Ads, Facebook Ads, Instagram Ads, etc), this process is rather simple and straightforward. Navigate to Sources in your Dataddo account, find a specific source, you want to work with and click on the "Ad-hoc data load" button.

Historic data 1

Now you can set a specific date range of your historical data. Click Apply, choose whether you want to append this historical data to your existing data, or replace them, confirm your action and load the data.

historic data 2

You can also run a Display log on your source panel and check if the operation went successfully.

Historic data 3

If you want to send these historical data to your database, in our case MySQL, you can navigate into the Data Flows tab in your account, find the appropriate Data flow connected to this source, and simply click on Ad-hoc data insert, confirm your action and the historical data are sent to your table.

insert_data


You can run a Log to check if the operation was success. Your historical data have now been loaded into Dataddo and sent to your database.
 

DATE RANGE in Ad-hoc data load not possible

Another case is that selecting DATE RANGE in the Ad-hoc data load panel is not possible. When this occurs, there is still a way to get your historical data. This process is a bit more advanced but feasible following this guide. Nonetheless, if you still need some help with the loading of historical data, feel free to contact us

Navigate to Data Sources in your Dataddo account, find a specific source, you want to work with and click on the "Edit data source" button.

Edit_Source

Scroll down to the URL field. Here, you can see the current setting. You will always see something such as "startDate" and "endDate" in the link, in this example, we have "since_send_time" and "before_send_time". These specify the date range of data to be fetched by Dataddo.

URLL

The expression in the curly brackets defines a dynamic date range. This date range is originally set to the date range you have chosen during the configuration of the source (such as Yesterday, Last 7 days, Last 30 days, etc). In this case, we have the range set to last 30 days including today.

If you want to load historical data, the easiest way how to do this is by either specifying the exact start and end date and time of the period for which you want to get data or using the dynamic date range. You need to set this in the URL.

Case 1: Static date range

If you wanted to pull data from 1 September 2020 until the end of 14 September 2020. Then you would need to change the curly brackets term to the exact date in ISO 8601 time format, hence our example URL would be:

https://us16.api.mailchimp.com/3.0/campaigns?offset=0&count=500&since_send_time=2020-09-01T00:00:00+00:00&before_send_time=2020-09-14T23:59:59+00:00


Case 2: Dynamic date range

If you wanted to pull data for last year. Then the expression in the curly brackets needs to be set to 1y1, hence our example URL would be:

https://us16.api.mailchimp.com/3.0/campaigns?offset=0&count=500&since_send_time={{1y1}}T00:00:00+00:00&before_send_time={{1y1}}T23:59:59+00:00


If you wanted for example 90 days ago till yesterday, we would use 90d1, hence the example URL would be:

https://us16.api.mailchimp.com/3.0/campaigns?offset=0&count=500&since_send_time={{90d1}}T00:00:00+00:00&before_send_time={{90d1}}T23:59:59+00:00 


Once the date range of the historical data that we want to load is set in the URL, Save the changes. Now, you are ready to load the historical data. This is simply done by clicking on the "Ad-hoc data load" button. You need to choose whether you want to append these historical data to the one you have already fetched, or whether you want to replace them. You confirm and the historical data gets loaded. You can once again run the Display log on your source panel and check if the operation went successfully.

The last thing you need to do is either return the URL to the original setting or pick a new date range for which you want to pull data every time Dataddo takes a new snapshot, using the dynamic range.

For example, if we have the schedule set to Weekly and we always want to collect only the data from the last seven days (not including today), we use the expression 7d1 in the URL. We can also choose whether we want the new snapshot to replace all the previous, or append to them. Choosing the further, our edit window would look like this:

Last_step

Now, you have all the historical data loaded and you are all set for the next snapshot. 

If you want to send these historical data to your database, you can navigate into the Data Flows tab in your account, find the appropriate Data flow connected to this source. Simply click on Ad-hoc data insert, confirm your action, and the historical data will be sent to your database.