= Custom Data Import = Our custom data API allows you to push any type of data up to Metricly for viewing as a metric (including metrics with subtotals and filters). The most common reason to do this, is if you have data in your own internal database, and you'd like to generate a Metricly dashboard that summarize that data. == Metric Import File Format == The first thing you will need to do is create a CSV-formatted metric import file that summarizes your internal data for a specific metric. At a mimium, each metric import file needs to be summarized by day. The following is an example of a simple metric import file (in this case, summarizing the number of orders on a e-commerce site): File: orders.metric.csv {{{ date,value 12/1/10,102 12/2/10,108 12/3/10,117 }}} ''Note: Each file must start with a header that includes 'date' and 'value'.'' === Including Dimensions (optional) === If you'd like to view subtotals and filters on your imported metric, your import file must also include summary data based on each "dimension". A "dimension" is a way of grouping the metric for more detailed analysis - for example, if you are tracking orders on an e-commerce site, you might also want to include a dimension called "type" to keep track of returning customers separately from new customers. Doing so will allow you to create subtotals and filters within Metricly based on the the order type. In order to capture this data, your metric import file needs to include a summary for 'type' as well as 'date': File: orders.metric.csv {{{ date,type,value 12/1/10,new,47 12/1/10,returning,55 12/1/10,M:ALL,102 12/2/10,new,40 12/2/10,returning,68 12/2/10,M:ALL,108 12/3/10,new,51 12/3/10,returning,66 12/3/10,M:ALL,117 }}} ''Note: We use the keyword 'M:ALL' to represent grand total values (also called super aggregates). You can also use \N (representing null).''' ''Note: If a value has spaces or commas in it, be sure to wrap it in quotes ("returning visitor").'' === Multiple Dimensions (very optional) === The Custom Data API allows you to have up to 8 dimensions on each metric, but be warned that adding dimensions can make your summarized data VERY LARGE. The best way to create a metric import file with multiple dimension is likely using a SQL query as shown below, but here as an example with two dimensions. File: orders.metric.csv {{{ date,type,country,value 12/1/10,new,US,102 12/1/10,returning,US,114 12/1/10,new,CA,147 12/1/10,returning,CA,127 12/1/10,M:ALL,M:ALL,491 12/1/10,new,M:ALL,250 12/1/10,returning,M:ALL,242 12/1/10,M:ALL,US,218 12/1/10,M:ALL,CA,275 12/2/10,new,US,144 12/2/10,returning,US,105 12/2/10,new,CA,136 12/2/10,returning,CA,120 12/2/10,M:ALL,M:ALL,508 12/2/10,new,M:ALL,282 12/2/10,returning,M:ALL,226 12/2/10,M:ALL,US,251 12/2/10,M:ALL,CA,258 12/3/10,new,US,128 12/3/10,returning,US,144 12/3/10,new,CA,151 12/3/10,returning,CA,132 12/3/10,M:ALL,M:ALL,557 12/3/10,new,M:ALL,281 12/3/10,returning,M:ALL,277 12/3/10,M:ALL,US,273 12/3/10,M:ALL,CA,285 }}} ''Note: We use the keyword 'M:ALL' to represent grand total values (also called super aggregates). You can also use \N (representing null).''' == Summarizing from a Database == One of the most common things you may want to do is to summarize data from your own database. Fortunately, most databases have features that make this fairly easy (provided you have a basic understand of SQL). In the following example, we use MySQL and the ROLLUP modifier to create a SQL query that summarizes the number of orders from the last 30 days with type and country as dimensions - MySQL Query: {{{ SELECT date, type, country, count(id) FROM orders WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date GROUP BY date, type, country WITH ROLLUP INTO OUTFILE '/tmp/orders.metric.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' }}} == Publishing to Metricly.com == Once you have a metric import file, you can post it to Metricly at the following URL: http://api.metricly.com/publish/publish_data.php You can use the web interface at that address to post the file manually, but more likely, you will want to push the file automatically using a tool like 'curl'. The publish data service requires the following POST fields: * metric * company_api_key (you can find your Company API key after connecting 'Custom Data' on your settings page) * file To send a metric import file via curl, use the following syntax: {{{ curl -F metric=Orders -F company_api_key= -F file=@$orders.metric.csv http://api.metricly.com/publish/publish_data.php }}} === Scripts and Cron Jobs === If you create a script that summarizes your data, you can also create a cron or Windows scheduler job to automatically publish your data on a daily basis. You may need to consult your IT administrator to setup a cron or Windows scheduler job, and have then run the script nightly (we recommend between 1-3am). We have an example shell script attached below (for Unix/Linux environments) - [source:custom_data_import/example_scripts/publish_metrics.sh Example Publish Metrics Shell Script]