Automated Annual Scorecard with SQL/ DOMO - PART 2: Data Snapshots, Archive, and Transform
So in the previous post, we have brought our indexed metrics and targets into DOMO. Now let’s work on our ‘Actuals’ data.
Here I have a set of Sample Order Data, the calculation of our metrics 2.1a Order SLA and 2.1b Order Cancellation will be derived from this dataset.
However, note that the Sample Order Data is a live dataset that is connected to our system. This means that if we want to report on order cancellation for January, and the same cancelled ticket got reopened (ticket status changed to ‘Pinned’) in February, this will mess up the cancellation numbers in January.
This is why when reporting on a trend, we always need to use static data. Today we are going to create an archive of the Sample Order Data that takes a snapshot on the 1st of every month.
PART 1 : DOMO Transform with Snapshot Timestamp
To create the an archive of our Sample Order Data, we open up DOMO transform and start building in our snapshot timestamp logic.
For now, we only have 1 input dataset - Sample Order Data. The transform is fairly simple, select everything in the dataset and add one new column :
DATE(SUBDATE(CURRENT_TIMESTAMP,INTERVAL 8 HOUR)) AS `Snapshot Date`
As simple as that. Name the transform as ‘Today’s_Snapshot’ and create output dataset: ‘(Snapshot Archive) Sample Order Data’.
PART 2 : Monthly Archive
Now that we have a dataset that records the snapshot date, we can start building our archive logic.
We first go back and edit the transform, add ‘(Snapshot Archive) Sample Order Data’ as another input dataset. Yes, this dataset is now both the output and input dataset.
Here we have 2 needs:
Take snapshot on the 1st of every month.
Don’t keep duplicate records
So let’s start working on our SQL logic.
Other than the transform ‘todays_snapshot’ where we create the snapshot date. We are going to add a transform named ‘this _months _snapshot’.
In this particular transform, we will exclude anything that has already been captured in the ‘(Snapshot Archive) Sample Order Data’. To identify that, we make use of the snapshot date, and set the parameters to limit only one set of ‘YEAR MONTH’ combination.
In short, we will only record things in ‘todays_snapshot’ if the snapshot date has a YEAR MONTH combination has not yet been recorded in our archive.
Finally, we will union the new records (from ‘this_months _snapshot’) we want to keep with our archive.
The complete transform will now look like this :
PART 3: Timing of Monthly Archive
So now we have our (Snapshot Archive) Sample Order Data, and we know that it will keep a snapshot every month. How do we ensure the snapshot records being kept are the ones taken on the 1st of every month?
If we set the run schedule to daily, our logic will take snapshot everyday but only keeps the one with the new YEAR MONTH combo. That means that every 1st day of the month, the transform will recognize it as the first new YEAR MONTH combo and automatically omits any records that happens after that.
In this case, this is a happy accident, we benefit from the nature of the ‘1st of every month’. If you need your snapshot to be taken on specific day of the month such as the 15th, or 3rd. You will have to set your run time to that specific day of the month.
And now we have our static data for Sample Order Data. We still need to create the same archive data for Escalations Data then we are good to go!
