Since June, we have also been building an overall database to better access data for our dashboards, secure the data access (e.g. if customer changes the webpage and the GA stats get messy) and to analyse digital data (e.g. how much is an avg. CPC for a finance company? What has worked best within the previous year?) .
The main idea behind it was to create four databases (two containing data about SEM and two with campaign data) that had the data from all the clients since the beginning of 2018 until now. The need for such database came from the Google Spreadsheet’s inability to manage thousands of data rows. The problem occurred when we noticed that in the Power BI dashboard some data was missing – the numbers and dates just didn’t add up. The problem was that with Google Spreadsheet you can properly store only some amount of data. If the amount exceeds a limit in Google Spreadsheet and the same sheet is connected to a dashboard in Power BI, you might lose a lot of valuable data.
The same happened with us. A big SQL database that had all the clients’ data combined seemed to be the idea to solve our problem. We pulled data through Supermetrics from Google Analytics, Google Ads, Adform and Facebook. In the future it will surely help making Power BI dashboards easier, because we can use the same source for all the dashboards. The SQL database is also important because the data gathered there can’t go missing. Meaning, if for some reason previous data cannot be imported because of some permissions, then data gathered in SQL database is always available.
By the end of August we will have two of the four datasets done (altogether over 2 million rows of data) and the other two in the works. Done datasets have scripts that refresh data once a day so that we don’t have to manually pull data anymore. Also, right now we have data from the beginning of 2018 until now. If we see, that such SQL database solution works, then we can make the database bigger by importing data from the year 2017.
If you too want to have a secure SQL database for your data, do contact us by clicking on the button below: