Instructions for Snowflake Reporting with CallPotential Data
- Matt Myford (Unlicensed)
RETURN TO THE MAIN SUPPORT PAGE
CallPotential integrates with Snowflake, a data warehouse and analytics platform that allows users to access their data, create custom reports, and better understand their operations. This guide provides access to CallPotential’s Snowflake Data Dictionary and SQL Queries to help optimize your Snowflake data reporting.
Note: Users need to create their own Snowflake account to access their CallPotential data. See Instructions for Snowflake Data Sharing for more information.
Snowflake provides a single platform to store your data with managed, secure access. Snowflake is a cloud-based application, so you have access to data CallPotential automatically sends. Once your CallPotential data is linked to your Snowflake account, you can leverage CallPotential’s Data Dictionary and SQL Queries to create custom data reports, data visualizations, dashboards, and more.
Data Sharing Between CallPotential and Snowflake
When your Snowflake account is linked to CallPotential, a nightly load process pushes the prior days’ data from the CallPotential database into your Snowflake data share.
Once the CallPotential data is transmitted to Snowflake, the ETL process pulls data from all the individual MySQL tables, and pushes them into Elastic Search, which houses the data in a normalized format. This data normalization is achieved when all ledger integration tables go into a single ledgers shard. Then, the data is extracted and sent to the V2EXPORTS data dictionary from a single JSON structure into the normalized table views displayed in V2EXPORTS.
After the data is normalized, you can use CallPotential SQL Queries to interpret the data in Snowflake for custom reporting. CallPotential’s SQL queries extract real-world business models (leads, follow ups, collections, etc.) and the V2EXPORTS Data Dictionary defines all of the physical table names.
Use the resources below to better understand CallPotential’s data structure and get insights on how to get started running SQL queries and create custom reports in Snowflake.
Data Dictionary
With access to our Data Dictionary, you can view data sets for Leads, Follow-Ups, Calls, Payments, Collections, and other processes. The CallPotential Data Dictionary for shared data is organized into individual tiers, so you can copy data into your Snowflake account and execute it with shared data.
Click here to access the CallPotential Data Dictionary. Each type of data point in the TABLE_NAME column includes a brief description, which is viewable by mousing over the top cell of each type of unique data point. For example, the ADTYPES note is “The type associated with an ad (i.e. Web).”
SQL Queries
CallPotential created pre-baked queries to choose from, depending on your data reporting needs. These queries can be run directly from your Snowflake account, and will calculate commonly asked questions about different data points, such as how many follow-ups your employees have completed.
Click here to access CallPotential’s SQL Queries.
Data Analysis – Reporting Example
Let’s say you want to calculate the number of follow ups completed by your employees.
See the Data Dictionary for the followup_reports view to understand the data parameter. The file includes an employee_id parameter, which can be linked to users to gather the employee name. This parameter is further explained in the spreadsheet as a shared key (for example, followup_reports.employee_id == users.user_id).
Reference the SQL Queries to understand counts for different follow up types. For example, the follow up type completed via employee includes information related to the call, email, and SMS.